MySQL Bugs: #20058: Query Internal ANALYZE counter in show status (or similar)

Bug #20058	Query Internal ANALYZE counter in show status (or similar)
Submitted:	24 May 2006 21:31	Modified:	13 May 2010 16:04
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.1+	OS:	Any (ALL)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Internally InnoDB will automatically ANALYZE a table when 1/16th of the table has changed, or after 2 000 000 000 operations.  This automatic statistics update after 2 billion operations leads to two problems;

* The ANALYZE has some performance impact.
* The ANALYZE is random in nature, and the statistics produced *may* lead to worse results.

From innobase/row/row0mysql.c:869

/* Calculate new statistics if 1 / 16 of table has been modified
since the last time a statistics batch was run, or if
stat_modified_counter > 2 000 000 000 (to avoid wrap-around).
We calculate statistics at most every 16th round, since we may have
a counter table which is very small and updated very often. */

My request is:
If there is a counter, can the current value of it be queried?  This will assist in planning around its (slight) unpredictability.

How to repeat:
Create a table, run 2 billion operations on it.

Suggested fix:
A show status/information_schema variable of some sort.

Hmm... I believe MySQL still does not use the new index statistics while the mysqld server is running. It only asks for the index statistics once, when the table is opened.

But also the row count affects optimization, and that counter IS queried by MySQL in certain situations other than the first opening of the table. I believe also MyISAM keeps an up-to-date value of row count for query optimization.

This whole area should be overhauled. InnoDB should collect more accurate statistics in ANALYZE, and there should be a clear policy when MySQL updates the index stats and row count it uses in query optimization. The DBA could configure if he wants dynamic updating of stats every day or when 1 / 16 of the table has changed.

Thank you for a reasonable feature request.