More Stable Query Execution Times by Improving InnoDB Statistics

As part of ensuring that changes to the MySQL Optimizer does not introduce performance regressions, we have started a project to create a new performance regression test suite. One component in this test suite will be the DBT-3 test suite. However, we observed that the execution times for DBT-3 varied so much that, in its present form, it was not usable for detecting performance regressions.

In order to get a better understanding of what was going on, I looked closer at one of the queries that were run, Query 8. For this particular query, which contains an 8-table join, the execution times varied from 1 minute to 5 hours! Looking at 100 runs of this query, I detected 8 different query execution plans. Four of these plans represented differences in which sequence the tables were joined, while the last four differed from the first four with respect to which indexes were used.

Since the MySQL Optimizer is cost based, when the execution plans vary, this is usually because the underlying statistics reported from the storage engine varies. One way to investigate the statistics that the optimizer bases its decisions on, is to use the SHOW INDEX command:

mysql> show index from customer;
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customer |          0 | PRIMARY       |            1 | c_custkey   | A         |      150000 |     NULL | NULL   |      | BTREE      |         |               |
| customer |          1 | i_c_nationkey |            1 | c_nationkey | A         |          47 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

In the above example, the customer table has two indexes, a primary index on c_custkey and a secondary index on c_nationkey. The most important thing to note from this output, is the estimated number of different key values, cardinality. For the two indexed columns of the customer table, the cardinality is 150,000 and 47, respectively.

The InnoDB statistics are calculated on-the-fly the first time a table is used after the server has been started. The statistics may be automatically recalculated at various times, and ANALYZE TABLE can be used to force a recalculation. For a description of how InnoDB calculates its statistics, see the MySQL 5.5 Reference Manual. The important thing to note is that there is a system variable, innodb_stats_sample_pages, that controls the accuracy of the statistics. It determines the number of index pages that are sampled in order to calculate the statistics. The default value is 8.

Continuing with the example above, I ran ANALYZE on the customer table, and here is what I got:

mysql> analyze table customer;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| dbt3.customer | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.03 sec)

mysql> show index from customer;
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| customer |          0 | PRIMARY       |            1 | c_custkey   | A         |      150000 |     NULL | NULL   |      | BTREE      |         |               |
| customer |          1 | i_c_nationkey |            1 | c_nationkey | A         |         134 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.01 sec)

As the observant reader has already noticed, the estimated cardinality of the column c_nationkey has changed significantly. Running ANALYZE several times, I saw numbers as low as 5 and as high as 135 for this column.

The question is then whether we can get better and more stable statistics by increasing the setting of innodb_stats_sample_pages. I increased the value to 100, and ran ANALYZE 100 times. This time, the estimated cardinality was between 22 and 84, and 80% of the time it was between 30 and 60.

So how did this affect the execution plan for query 8? Running the query another 100 times with this new setting of innodb_stats_sample_pages, gave only 3 different execution plans, all with the same join ordering. The remaining difference was in which index was used to access one of the tables. I will discuss how to further reduce the number of plans in a future blog post.

Note that increasing innodb_stats_sample_pages will cause the calculation of statistics to take longer time. In my case, sampling 100 pages did not seem to take a noticeable amount of time, and this should normally not cause any problems when ANALYZE TABLE is run explicitly. However, if InnoDB decides to do an automatic recalculation, the increased time may have some unwanted impact. Hence, this variable should be used with care, and its value should not be set higher than necessary to get reasonably accurate estimates.