June 19, 2013

Post: Troubleshooting MySQL Memory Usage

March 21, 2012 By 5 Comments

…with MySQL because there are no clear resource …Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table cache…mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index…

Filed Under: Insight for DBAs, MySQL

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

…which would look as following in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** … Still substantial. Sorting needs OS Cache It is not clearly seen from the graphs but with index built by sort enabled the …

Filed Under: Insight for DBAs, MySQL

Post: Getting real life query speeds with MySQL

January 10, 2007 By 10 Comments

To check for query performance improvements followed indexing/query changes or MySQL configuration changes our customers often decide to run the query… of page caches inside MySQL (key_buffer and innodb_buffer_pool_size) which can be cleared by MySQL restart. Even if you restart MySQL server…

Filed Under: Benchmarks, Insight for Developers

Post: Distro Packages, Pre-built Binaries or Compile Your Own MySQL

September 4, 2012 By 7 Comments

… be as easy as stopping MySQL, updating symlinks or your my.cnf basedir, starting MySQL and running mysql_upgrade. Unlike package managers, you… alter MySQL‘s default behavior i.e. disabling and totally disallowing use of query cache or increasing maximum total number of indexes per…. This is true, my mistake as I have not been clear and I have made a few modifications to reflect those…

Filed Under: MySQL

Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By 12 Comments

… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

….ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_DELETED_CACHE.ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 16….ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 15:54 FTS_0000000000000114_DELETED_CACHE.ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 15…_S.innodb_ft_index_table is changing, so it’s pretty clear that I do actually have a FT index available. At the…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

…| Aborted_clients | 205174 | | Aborted_connects | 29 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 9630066 | | Bytes_received | 38563413074 | |… of the output, so it’s clear what I’m showing them. Then,… in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and …

Filed Under: Insight for Developers Tagged With:

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

…index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index…MySQL 5.5 and MySQL 5.6/MariaDB 5.5 This is another area of improvement in the optimizer, as it is clearly…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Caching could be the last thing you want to do

July 24, 2010 By 14 Comments

…designed to be “optional” (but clearly they weren’t). I …Cache management is really hard – have you planned for cache stampeding, or many cache…queries.  Run them through MySQL‘s EXPLAIN command. Do your …potentially) limiting further optimizations like covering indexes. Do you have information about…

Filed Under: Insight for Developers, MySQL Tagged With: , , , ,

Post: The two even more fundamental performance metrics

May 5, 2011 By 6 Comments

…to-mean ratio (a.k.a. the index of dispersion, an amazingly useful metric…a bit longer. This is common in MySQL servers I work with when they… to have something interesting: You can clearly see that there is a spike …happened here was something like a small cache stampede. The application sent a burst …

Filed Under: MySQL
« Older Posts