MySQL index statistics update related parameters and mysql statistics

MySQL index statistics update related parameters and mysql statistics

 

MySQL statistics parameters:

 

1. innodb_stats_on_metadata (whether to automatically update statistics), which is disabled by default in MySQL 5.7

This takes effect only when the statistical information configuration is non-persistent.
That is to say, when innodb_stats_persistent is set to OFF, the setting of innodb_stats_on_metadata takes effect for non-persistent storage of statistics.
When innodb_stats_on_metadata is set to ON,
When InnoDB executes show table status or accesses the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS system TABLE, it updates the persistent statistics (similar to ANALYZE table)

For the Update Time of statistical information of an index, see the mysql. innodb_index_stats system table.
Select *
From mysql. innodb_index_stats
Where table_name = 'testatistics ';

2. innodb_stats_auto_recalc

Whether to automatically trigger the update of statistical information. Only the tables that affect the statistics of persistent storage are affected. The threshold value is that the changed data exceeds 10% of the number of rows in the table.
That is to say, the index statistics of a table are stored persistently, and the table data changes by more than 10%,
If innodb_stats_auto_recalc is ON, statistics are automatically updated. Otherwise

3. innodb_stats_persistent (non-persistent statistics switch), which is enabled by default in MySQL 5.7, stores statistics persistently.

When this option is set to ON, the statistics will be stored persistently in the disk instead of in the memory,
On the contrary, if it is non-persistent storage (in memory), the corresponding statistics will be lost as the server is shut down.

4. innodb_stats_persistent_sample_pages (number of samples on the index page when statistics are updated persistently)

The default value is 20 pages. If the setting is too high, the execution time of analyze table will be increased when statistics are updated.

5. innodb_stats_transient_sample_pages (number of samples on the index page when statistic information is temporarily updated)

The default value is 8. If innodb_stats_persistent is set to disable, innodb_stats_transient_sample_pages takes effect,
When the index statistics are not stored persistently, innodb_stats_transient_sample_pages indicates the number of samples for updating the statistics.

6. innodb_stats_sample_pages

Obsolete. Replaced by innodb_stats_transient_sample_pages.
Why should I replace innodb_stats_transient_sample_pages?
My personal guess is that the initial parameter name is not standardized. Since it is the statistical information of temporary rows, it is not known by name. It is different from innodb_stats_persistent_sample_pages,
In the beginning, MySQL only had temporary ROW statistics and no persistent statistics.