On InnoDB's Persistent Optimizer Statistics

As I put it in recent Facebook post, one of MySQL features that I truly hate is InnoDB's persistent statistics. I think I should clarify this statement. It's great to have a way to save statistics in between server restarts, to have better control on the way it is estimated (even on a per table basis), set it explicitly, check it with simple SELECT. These all are great additions since MySQL 5.6.2+ that I truly appreciate (even if I may not be happy with some implementation details). They helped to make plans for queries against InnoDB more predictable and allow (with some efforts applied) MySQL query optimizer to really work as "optimizer" instead of "randomizer" or "pessimizer" (as some people called it) for InnoDB tables.

What I hate about it mostly is the way innodb_stats_auto_recalc is implemented, and the fact that it is enabled by default since MySQL 5.6.6+ or so. Here is why:

1. Even if one enables automatic statistics recalculation, she can not be sure that statistics is correct and up to date. One still really has to run ANALYZE TABLE every time after substantial changes of data to be sure, and this comes with a cost (that Percona tried to finally overcome with the fix to lp:1704195 that appeared in their Percona Server 5.7.20-18+). Or enjoy whatever bits of statistics (taken in the process of background recalculation) may be present at the moment and the resulting execution plans...
2. The details on automatic statistics recalculation are not properly documented (if only in some comments to some bug reports). This changes to better with time (thanks to continue pressure from MySQL community, including your truly, in a form of bug reports), but still most of MySQL users are far from understanding why something happens or NOT happens when this feature is involved.
3. Implementation introduced background thread (that does dirty reads) to do recalculation, and separate transactions against InnoDB tables where statistics is stored. This complicates implementation, analysis in gdb etc, and introduced more bugs related to coordination of work performed by this thread and other background and user threads.
4. Recently nobody from Oracle cares much to fix bugs related to this feature.

Let me try to illustrate and prove the points above with some MySQL bug reports (as usual). Many of these bugs are still "Verified" and not fixed as of recent release of recent GA version, MySQL 5.7. The order is somewhat random:

• Bug #70741, "InnoDB background stats thread is not properly documented" - that's one of my requests to improve documentation. Some more details were added and the bug is closed, but make sure to read the entire comment "[26 Nov 2013 13:41] Vasil Dimov" if you want to understand better how it all works.
• Bug #70617, "Default persistent stats can cause unexpected long query times" - this is one of bugs that led me to filing the previous documentation request. Check comments by Vasil Dimov there that he made before closing it as "Not a bug"... His comments are the best documentation of the way feature is implemented that I've seen in public. Make your own conclusions.
• Bug #78289, "Bad execution plan with innodb_stats_persistent enabled" - note that the problem started after pt-osc was applied (to overcome the problems with another feature I hate, "online" ALTER TABLE, most likely). This utility applies RENAME to the table that is altered at the last stage, and as a result statistics just disappears and you have either to wait until it is calculated again in the background, or run ANALYZE... Surely this is "Not a bug".
• Bug #80178 and Bug #78066 are about cases when SHOW INDEXES may still give wrong results while (with persistent statistics automatic recalculation disabled) one expects the same values we see in the tables where statistics is stored, or just correct ones. Both bugs are still "Verified", even though from the comment in the latter one may assume that the problem may be fixed in recent MySQL 5.7.x.
• Bug #75428, "InnoDB persistent statistics not persistent enough". The counter of updated rows since last recalculation does not survive restarts, and 10% threshold is not configurable, so if server restarts often and table is big enough, we may get statistics never updated. Still "Verified".
• Bug #72368, "Empty/zero statistics for imported tablespace until explicit ANALYZE TABLE". Still "Verified", but may be fixed in versions newer than 5.6. Importing tablespace was NOT a reason for automatic statistics recalculation to ever happen for the table...
• Bug #84940, "MySQL Server crash possibly introduced in InnoDB statistics calculation". This regression bug in 5.6.35 and 5.7.17 was quickly fixed in the next releases, but still caused troubles for some time.
• Bug #82184, "Table status inconsistent, requires ANALYZE TABLE executed twice". As Andrii Nikitin stated himself, "Most probably second ANALYZE is needed to give some time to purge thread remove old versions of the rows.", in case the table has huge blobs. The bug is still "Verified".
• Bug #71814, "Persistent stats activity conflicts with mysqldump import of same info". The bug is "Closed" without any reason stated in public (what a surprise...). Note the following comment by Shane Bester (who actually verified and explained the bug):
  

  "Personally, I don't like that mysqldump dumps the content of these tables that should be auto-generated."

  He had also suggested a workaround to disable persistent statistics (SET GLOBAL innodb_stats_auto_recalc=0; SET GLOBAL innodb_stats_persistent=0;) before importing a dump. The problem here is a race condition between the importing of mysql database and the background statistics thread that can insert rows into the table between the CREATE and LOCK TABLE in the dump. See Bug #80705, "Mysqlpump in default configuration does not work", also, with a clear request: "Do not dump innodb_index_stats or innodb_table_stats". Something to think about.
• Bug #84654, "Cardinality reset to 0 with persistent statistics when AUTO_INCREMENT touched".  Still "Verified".
• Bug #84287, "row inserts, statement updates, persistent stats lead to table scans+ lag slaves". It seems automatic recalculation of statistics on slave is not triggered by inserting more rows via row-based replication events. Still "Verified".
• Bug #82969 , "InnoDB statistics update may temporarily cause wrong index cardinalities". This bug (still "Verified") is my all times favorite race condition in the implementation of persistent statistics by Oracle (well, this one and Bug #82968 that is fixed at least in recent 5.7.x and in MariaDB).

 There are also bugs related to other details of InnoDB persistent statistics implementation:

• Bug #78401, "ANALYZE TABLE" may assign temporary values to table stats during its execution". Statistics is not updated atomically, it is first reset and then recaclulated. Still "Verified".
• Bug #86926, "The field table_name (varchar(64)) from mysql.innodb_table_stats can overflow." - this may be not enough for partitioned table, as partition names may be longer. Still "Verified".
• Bug #67179, "mysql system tables innodb_table_stats,slave_master_info not accessible on clean". This was a famous bug during early days of MySQL 5.6 that affected many users who tried to upgrade. You may still need this file from it one day, to re-create missing InnoDB tables in the mysql database.
• Bug #80986, "innodb complains about innodb_table_stats even if persistent stats disabled". Still "Verified".
• Bug #86927, "Renaming a partitioned table does not update mysql.innodb_table_stats.". Fixed recently in MySQL 5.7.21 and 8.0.4.
• Bug #84455 - the topic of this bug report is different and not relevant, but Shane Bester noted the following in the error log uploaded:
  

  [Warning] InnoDB: A transaction id in a record of table `mysql`.`innodb_table_stats` 
  is newer than the system-wide maximum.
  

  This is both suspicious and scary. May be related to the way background thread works.
• Bug #74747, "Failing assertion: index->id == btr_page_get_index_id(page) btr0cur.cc line 899". Yes, this is a debug assertion only provoked explicitly, but note what is written in the error log before it happens:
  

  InnoDB: Cannot save table statistics for table "db1"."t1": Too many concurrent transactions

  It means background thread opens a separate transaction (no surprise, but still worth to note).
• Bug #86702, "please disable persistent stats on the mysql.gtid_executed table". This is a valid and verified request to remove the related overhead for this "system" InnoDB table (as it was correctly done for several others).

So, the implementation of InnoDB's Persistent Optimizer Statistics is far from perfect or well documented. One may ask what I'd suggest instead? I often think and state that only engine-independent persistent statistics (in MariaDB style) should exist, and this should be recalculated only by explicit ANALYZE TABLE statement, maybe with more options to set sample size and other details than we have now. No background threads, nothing automatic until automated by the DBA (for this I'd appreciate a package like Oracle's dbms_stats).

This kind of idea is usually not well accepted. One of recent (valid) complains by Domas here were "No, thanks, don't need more MDL holders." and "I prefer lockless versioned stats, when it gets to what I prefer."

Some of the problems mentioned above may be resolved in MySQL 8 (or not) with its atomic data dictionary operations. Other idea presented in MDEV-15020 is to store statistics with data in the same .ibd file. We shell see what may happen, but current implementation, even though it improved a lot since early MySQL 5.6 days, is hardly long term acceptable.

Recalculating InnoDB Persistent Statistics - a Story of the Bug Report

One of the first posts in this blog was about reporting MySQL bugs "properly", in a way that maximizes chances for it to be processed really soon. I had written the following there:

"Ideally, you should provide a complete test case and/or instructions that any reader can use to reproduce your problem"

Indeed, if one can just copy/paste something to mysql command line client or run some file attached to see the problem, chances are high for the bug to be processed really soon. We all like to get low hanging fruits from time to time, and Oracle engineers who work on bugs are not exceptions. But does this mean that bug without clear test case has no value and is going to be ignored?

It should NOT be the case. Let's review Bug #70617 reported by my colleague some time ago based on the problem we helped our customer to solve. There bug reporter just copied somewhat edited email with problem description we've got, and it was referring to a very complex SQL statement (joining 17 tables or so) that worked slow when executed from some PHP application on MySQL 5.6, but always worked fast when similar SQL was executed in mysql command line client and mostly worked fast in good old MySQL 5.5 in both cases.

The bug report contained this kind of problem description and listed workarounds we found: replacing all INNER JOINs with STRAIGHT_JOIN (trick that is useful more often than I'd like it to be) let us get not ideal, but consistent performance, while recreating the tables with persistent statistics disabled just let us get the same good performance as in 5.5 (take the fact that persistent statistics for InnoDB tables is enabled by default into account every time you see query from older versions performing poorly in MySQL 5.6, by the way).

I let you review that my old post (with the content that was once offered as a talk to MySQL User Conference back in 2006 and recently was submitted for Percona Live London conference, but again was not accepted) and decide for yourself is the bug report good enough to get proper attention. It seems some of my former colleagues in Oracle decided that it is not any good to report something like that, without a simple test case, and they were also offended enough by some statements made there and the fact that bug reporter explicitly does NOT want to spend time on creating small test case not related to customer data... So, bug report was ignored, while I've got few chances to try to defend bug reporter in private chats.

Assuming that without a test case this bug report is going to be ignored longer than I'd like to, I've spent some 30 minutes testing and sending "good" reports:

• Bug #70629 - "innodb_stats_auto_recalc problem for InnoDB tables with persistent statistics"
• Bug #70630 - "Why one can access persistent statistics data while they are changing?"

this time with a simple test case to copy/paste that demonstrated both the reason of the problem (persistent statistics is NOT recalculated immediately) and the way this badly influences join order selection by the optimizer even with just 2 tables. I had not even tried to do anything based on customer data - 2 simple tables and standard enough actions allowed to see the problem immediately.

Was that because I am smart or experienced in bugs processing? Not at all, my actions where really simple. Nice and smart test cases for the original  Bug #70617 were created and added by famous Shane Bester next day, probably as soon as he noted the bug (it was hard NOT to note it after my Facebook posts), along with some insights based on code review. As a result, original bug report was verified even before my detailed one with a simple test case.

To complete this story I've just added this documentation request, Bug #70741. Let's hope the manual will soon clearly describe how the background thread that re-estimates statistics really works and explain why one should run ANALYZE TABLE after any big change in data if he plans to run queries against the table immediately, no matter what the setting of innodb_stats_auto_recalc is.


Looks like I've spent more time arguing about the original bug report (both externally and internally) than any good engineer (Shane or me) needed to understand the problem based on description and create a test case showing something that is a problem and may be related to the original report. Even with time to report 3 more bugs taken into account, arguing and "Facebook escalations" took more...

What is the summary?

First of all, engineers who work on bugs should spend few minutes carefully reading and thinking about the problem described before ignoring it or blaming reporter for wrong attitudes just because he had no time to create a simple test case (or even did not wanted to do this, for whatever reasons). Bug report with clear problem statement and solutions found also presented is already good enough to think about it. Why would one assume that others are wasting time on bug reporting without really good reasons?

Bug reporters, on the other side, are surely able to get their bugs processed faster if, instead of explaining why they can not afford spending any more time on bug report and making various statements not directly related to the problem at hand, they spend some time creating a test case to copy/paste. Chances are high that original bug reporter understands the problem way better already than anybody else in the world, so why not to try to make the world better by contributing some more lines of text?