MySQL Bugs: #70741: InnoDB background stats thread is not properly documented

Bug #70741	InnoDB background stats thread is not properly documented
Submitted:	27 Oct 2013 13:25	Modified:	9 Nov 2016 10:26
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any
Tags:	innodb, innodb_stats_auto_recalc, MIN_RECALC_INTERVAL

Description:
It seems that when innodb_stats_auto_recalc is enabled separate background thread is re-estimating statistics once in a while. I do not see any details on how often it performs checks at http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html.

How to repeat:
Check http://bugs.mysql.com/bug.php?id=70617 and http://bugs.mysql.com/bug.php?id=70629 for some relevant details.

Suggested fix:
Please, describe the way this thread works on the page above or elsewhere. Note that because check happens once in a while this paragraph:

"The configuration option innodb_stats_auto_recalc determines whether the statistics are calculated automatically whenever a table undergoes substantial changes (to more than 10% of the rows). If that setting is disabled, ensure the accuracy of optimizer statistics by issuing the ANALYZE TABLE statement for each applicable table after creating an index or making substantial changes to indexed columns. You might run this statement in your setup scripts after representative data has been loaded into the table, and run it periodically after DML operations significantly change the contents of indexed columns, or on a schedule at times of low activity."

and caution below it are not entirely true. One has to run ANALYZE TABLE also in case new statistics is needed faster than usual interval between checks.

Hello Valeriy,

Thank you for the report.

Thanks,
Umesh

Hello,

this can be added to the user documentation wrt persistent statistics:

--- cut ---
Because of the asynchronous nature of the automatic stats recalculation (which happens in the background), the stats may not be recalculated instantly after a DML has completed (a DML that affects more than 10% of the table). In some cases the stats recalculation may be delayed by a few seconds. Thus if an up to date stats are needed immediately after changing significant portions of the table, then ANALYZE TABLE should be run to do a synchronous/foreground recalculation of the statistics.
--- cut ---

What I will write below does not belong to the user documentation and should be considered as a technical description of the way it works internally. It may be changed at any time and users should not rely on it working the way it does today.

Whenever InnoDB executes INSERT/UPDATE/DELETE of a row it increments a counter to designate that one row in the table has been modified. When this counter reaches 10% of the total number of rows in the table, then the table id is scheduled into a queue for background stats gathering (for transient stats (old) the stats recalculation is done inside the user thread that is executing the DML and the thread returns to the user only then the stats recalc has completed). If the table id is already in the queue then it is not put there for a second time (no duplicates in the queue). A signal is sent to the background stats thread to wake it up. This is what is done in the user thread, it is very fast and does not affect the query response time.

The background stats thread waits to be woken up and when that happens it processes one table from the queue - recalculating its statistics. Then goes to sleep again. This thread tries not to be too aggressive in order to avoid hogging the server with IO which can easily happen if it keeps recalculating statistics constantly. In this aspect, it will not recalculate a table's statistics more often than once per 10 seconds. I.e. if it has recalculated a table's stats at some point in time, say t0 and >10% of this table is changed in e.g. t0+3sec then the stats will only be recalculated at t0+10sec.

I hope this helps, feel free to ask further questions of something is unclear.

The following documentation pages have been updated:

http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-persistent-stats

http://dev.mysql.com/doc/refman/5.7/en/innodb-performance.html#innodb-persistent-stats

The changes will appear in the next published documentation, in the next 24 hours or so. 

Thank you for the bug report.

The page that contains the text suggested is:

https://dev.mysql.com/doc/refman/5.6/en/innodb-persistent-stats.html

in case of 5.6. Links presented in the previous comment now lead to section content page, not any specific page.