MySQL 5.6, InnoDB statistics, and testcases for optimizer

One of the new features in MySQL 5.6 is persistent table statistics. The word “persistent” makes you think that table statistics will be very stable from now on.
This is generally true, but there are some exceptions. There is a particular case where InnoDB statistics may change with no warning, even when there is no user activity on the server. Consider the following pattern (which nearly all bug or feature testcases follow):

CREATE TABLE t1 (...) ENGINE=INNODB;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
EXPLAIN SELECT * FROM t1;
EXPLAIN SELECT * FROM t1;

With MySQL 5.6, the following is possible

• the first EXPLAIN will show that the optimizer expects table t1 to have 6 rows
• the second EXPLAIN will show that the optimizer expects table t1 to have 5 rows

For me, this was a big surprise. After all, table t1 never had 6 rows. If there was some rounding which rounded up 5 to 6, why did the number go back down to 5 again?

I debugged it, and discovered that

1. Multi-row INSERT will increment #records-in-table statistics as many times as many rows it has inserted
2. There is also dict_stats_thread() in storage/innobase/dict/dict0stats_bg.cc. which re-calculates table statistics in the background.

And what happens is:

1. INSERT inserts a record
2. dict_stats_thread() comes and re-calculates the statistics (the new record is counted)
3. INSERT increments the statistics by one (because it has inserted a record on step#1)

This way, right after INSERT has finished, the table statistics will report more records. The number will go back down within a second (because dict_stats_thread() will re-calculate statistics again). This slight and temporary inaccuracy in statistics should not be a problem for production environments. This is a big deal for test cases in mysql-test suite, though. Test cases are special:

1. Test datasets are often small. Even an off-by-one difference may cause a different query plan to be chosen. For optimizer bugs, this means that the testcase might be no longer hitting the bug it was made for.
2. Test cases often include EXPLAIN output in .result file. EXPLAIN output has “rows” column, and mysql-test-run framwork will interpret off-by-one difference as a test failure

I’ll repeat myself: the above two properties mean that with MySQL 5.6, it is not possible to write optimizer+InnoDB testcases the way we used to write them before. I’ve observed it in practice – testcases will fail sporadically, depending on how quickly your system is running.

But wait, what about Oracle? How did they manage to develop MySQL 5.6 with this? I was really interested, too, so I dug in the revision history. Oracle developers’ approach was to run ANALYZE TABLE after filling the table. It took them 33 patches to put ANALYZE TABLE everywhere where it was needed (examples: 1, 2, 3, 4, …) but they did it.

The take-away is: if you’re working on a testcase for an optimizer bug in MySQL 5.6 – put an ANALYZE TABLE statement after all the INSERTs but before the SELECTs. This will make your testcase stable.

For the record, we in MariaDB 10.0 did not take the 33 patches with ANALYZE TABLE statements. For now, we have decided to run mysql-test testsuite with --skip-innodb-stats-persistent option. Judging from the EXPLAIN outputs in the tests, that has achieved the same goal (same query plans), and the goal was achieved with just one patch. 🙂