Persistent Optimizer Statistics for InnoDB Tables
Plan stability is a desirable goal for your biggest and most important queries. InnoDB has always computed statistics for each InnoDB table to help the optimizer find the most efficient query execution plan. Now you can make these statistics persistent, so that the index usage and join order for a particular query is less likely to change.
The persistent statistics feature relies on the internally managed tables in the MariaDB database, named innodb_table_stats and innodb_index_stats. These tables are set up automatically in all install, upgrade, and build-from-source procedures.
You turn on this feature by enabling the configuration option innodb_analyze_is_persistent, and then running the ANALYZE TABLE statement for each applicable table. You control how much sampling is done to collect the statistics by setting the configuration options innodb-stats-persistent-sample-pages and innodb_stats_transient_sample_pages.
With this feature enabled, statistics are only gathered for a table by the ANALYZE TABLE statement, not the first time the table is accessed after each server restart. 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.Caution
Because statistics are not automatically gathered when a new index is created, always run ANALYZE TABLE after creating a new index when the persistent statistics mode is enabled.
Formerly, these statistics were cleared on each server restart and after some other operations, and recomputed when the table was next accessed. The statistics are computed using a random sampling technique that could produce different estimates the next time, leading to different choices in the execution plan and thus variations in query performance.
Whenever statistics for a table or index are present in the InnoDB system tables, they are used by the optimizer. To revert to the previous method of collecting statistics that are periodically erased, turn off the innodb_analyze_is_persistent setting, then remove the contents of the innodb_table_stats andinnodb_index_stats tables in the MariaDB database.