When should run Analyze table statement in mysql
I just implemented a service at my company to run ANALYZE TABLE regularly on InnoDB tables.
The reason I did this is that we were affected by this bug: InnoDB persistent statistics not persistent enough
.
When using InnoDB persistent statistics, automatic table statistics are refreshed after about 10% of the rows are modified. There's a counter that keeps track of this, but the counter itself is not persistent; it starts over at 0 if the MySQL Server restarts.
We have some tables that are very large, with hundreds of millions of rows. We also restart MySQL Server regularly, because we stay up to date faithfully on MySQL updates and OS updates. The combination of these two things causes us to do restarts before the 10% of rows are modified, and therefore the InnoDB table stats never update.
So I created a service that visits every database replicaset every day. It checks the table mysql.innodb_table_stats and specifically the last_update column. This will tell me how long it has been since the table stats were updated.
If that value is more than 90 days old for a given table, I definitely need to run ANALYZE TABLE.
Otherwise I do a random sampling and pick 10% of the tables to analyze. This should result in every table getting analyzed on average every ten days.
Naturally by checking mysql.innodb_table_stats, only InnoDB tables are considered, but we made a policy that we only support InnoDB tables in our company.