… enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6…. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…on server. There is no comparable variables of how many user variables are allocated (and how much memory they use). Memory Tables MEMORY tables…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl …how many times the buffer used by MRR had to be reinitialized, because the buffer was small and not all index…at what are the good and bad query execution plans. a. Bad Plan id select_type table type…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… index lookup is performed on the second table. This means many point queries, say for example if table1 yields 1000 rows then 1000 index lookups are performed on table2… LIMIT 10; In-memory workload Now let’s see how effective are the join optimizations when the workload fits entirely in memory…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 11 Comments
…_sys_indexes ON id = index_id JOIN innodb_sys_tables ON table_id = innodb_sys_tables.id JOIN innodb_index_stats ON innodb_index_stats.table_name = innodb_sys_tables… about how many pages are in buffer pool for given table (cnt), how many of them are dirty (dirty), and what is the percentage of index …
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… plan to see how many index parts are actually used for row lookup. Very common problem I see is multi column indexes which are used but… clauses on the table. In such case you would see “Index” type in explain which correspond to scanning (potentially) complete table in the index order…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE Trying to add in child table, in index `par_ind` tuple: DATA TUPLE: 2… on the row basics and some system information. It shows innodb thread queue status – how many threads are waiting and being active. How many read views are…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… are, both in terms of size and spindle speed. I need to know how many physical spindles are under the box, and how fast they are… default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and so on. As with settings and status…’s table and index structures, and the queries that run against them, is by far the most important thing to concentrate on. All…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 116 Comments
…’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with…-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk. innodb_buffer_pool_size This is very…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… the ratio has discarded vital information. You don’t know how many Key_reads and Key_read_requests those servers have done… large enough to hold your working set — the index blocks that are frequently used. How large is that? This is yet another thing… choose an amount of memory based on how important those tables are to you, how big the indexes are on disk, and so on. Raise the key_buffer_size…
