Configure & update the Optimizer Stats gather in MySQL
In MySQL, two type of stats gather you can configure:
1. Persistent : Means after restart the MySQL Server your stats remain persistent for InnonDB Tables offers more plan stability and consistent query performance.
2. Non-persistent : Means after restart the MySQL Server your stats lost and recomputed during table access in memory.
Note: Persistent optimizer statistics were introduced in MySQL 5.6.2 and were made the default in MySQL 5.6.6 (innodb_stats_persistent=ON).
Manually update the statistics in MySQL
ANALYZE TABLE t1;
Enable the Persistent optimizer setting
Configure the following parameter for enable the persistent optimizer setting:
innodb_stats_persistent
-- Enable the Persistent setting: (Default is enabled)
innodb_stats_persistent=ON
-- Disable the Persistent Setting:
innodb_stats_persistent=OFF
innodb_stats_auto_recalc
It's used to control whether statistics are updated automatically after DML changes to a table happens(10% DML operation happens in table).
innodb_stats_auto_recalc is enabled by default.
-- Enable the auto stats gather
innodb_stats_auto_recalc=ON
-- Disable the auto stats gather
innodb_stats_auto_recalc=OFF
innodb_stats_persistent_sample_pages
It is used for number of index pages to sample when estimating cardinality and other statistics for an indexed column.
-- Default value is 20
innodb_stats_persistent_sample_pages=20
Note: Increase the value improves the accuracy of index statistics, which improve the query execution plan, at the cost of increased I/O during the execution.
Enable the Non-Persistent Optimizer Setting
It will stored stats in memory and go away when MySQL is rebooted.
-- Disable the Persistent Setting:
innodb_stats_persistent=OFF
Update stats in non persistent stage:
1. Running ANALYZE TABLE.
2. Running SHOW TABLE STATUS, SHOW INDEX, or querying the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables with the innodb_stats_on_metadata option enabled.
Configure Persistent setting at table level
You can define the setting while creating table in Create statement. Following clauses used in syntax: STATS_PERSISTENT, STATS_AUTO_RECALC, STATS_SAMPLE_PAGES.
STATS_PERSISTENT: enable persistent statistics for an InnoDB table. Value 1 means enabled and 0 means disabled. (innodb_stats_persistent parameter for global setting).
STATS_AUTO_RECALC: used for automatically recalculate persistent statistics for an InnoDB table. Value 1 means enabled and 0 means disabled. (innodb_stats_auto_recalc parameter)
STATS_SAMPLE_PAGES: specifies the number of index pages to sample when estimating cardinality and other statistics for an indexed column.
CREATE TABLE t1 (
id int(8) NOT NULL auto_increment,
data varchar(255),
date datetime,
PRIMARY KEY (id),
INDEX DATE_IX ('date')
) ENGINE=InnoDB,
STATS_PERSISTENT=1,
STATS_AUTO_RECALC=1,
STATS_SAMPLE_PAGES=25;
Check the Status of Statistics from Persistent Setting
Check status of Statistics from mysql.innodb_table_stats and mysql.innodb_index_stats
-- Table stats
mysql> select * from mysql.innodb_table_stats;
+---------------+---------------+---------------------+--------+----------------------+--------------------------+ | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | +---------------+---------------+---------------------+--------+----------------------+--------------------------+ | mysql | component | 2019-06-18 15:44:51 | 0 | 1 | 0 | | mysql | gtid_executed | 2019-06-11 16:16:02 | 0 | 1 | 0 | | sys | sys_config | 2019-06-11 16:16:10 | 6 | 1 | 0 | | test | t | 2019-06-18 15:45:23 | 0 | 1 | 0 | | test5 | t | 2019-06-19 21:24:54 | 26 | 1 | 0 | | test6 | t | 2019-06-19 21:20:44 | 5 | 1 | 0 | +---------------+---------------+---------------------+--------+----------------------+--------------------------+
-- Index stats
mysql> select * from mysql.innodb_index_stats
+--------------+------------+------------+-------------------- +--------------+------------+-------------+-----------------------------------+ | database_name| table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description | +--------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+ | test | test | test_idx | 2019-06-27 14:25:38 | n_diff_pfx01 | 10 | 1 | id | | test | test | test_idx | 2019-06-27 14:25:38 | n_diff_pfx02 | 10 | 1 | id,DB_ROW_ID | | test | test | test_idx | 2019-06-27 14:25:38 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index | | test | test | test_idx | 2019-06-27 14:25:38 | size | 1 | NULL | Number of pages in the index | +--------------+------------+------------+---------------------+--------------+------------+-------------+-----------------------------------+