Shulou(Shulou.com)05/31 Report--

This article mainly introduces Percona MySQL 5.6 how to configure InnoDB optimizer permanent statistics, the article is very detailed, has a certain reference value, interested friends must read it!

Optimizer permanent statistics make MySQL choose a relatively consistent execution plan when selecting a statement execution plan by storing the statistics on disk, which improves the stability of the SQL execution plan.

When the parameter innodb_stats_persistent=ON is enabled or when the table is created with the STATS_PERSISTENT=1 parameter, the optimizer's statistics are permanently saved to disk. In previous versions, the optimizer statistics were cleared whenever the MySQL service was restarted or certain operations were performed.

The next time the table is accessed, MySQL re-collects optimizer statistics, which results in a change in statistics, which results in a change in MySQL's execution plan when parsing the statement, which in turn affects query performance.

The optimizer permanent statistics are stored in the mysql.innodb_table_stats and mysql.innodb_index_stats tables.

Mysql > select @ @ version

+-+

| | @ @ version |

+-+

| | 5.6.31-77.0-log |

+-+

1 row in set (0.01 sec)

Mysql > show variables like 'innodb_stats_persistent'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_stats_persistent | ON |

+-+ +

1 row in set (0.00 sec)

Mysql > desc mysql.innodb_table_stats

+-+

| | Field | Type | Null | Key | Default | Extra | |

+-+

| | database_name | varchar (64) | NO | PRI | NULL |

| | table_name | varchar (64) | NO | PRI | NULL |

| | last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | |

| | n_rows | bigint (20) unsigned | NO | | NULL |

| | clustered_index_size | bigint (20) unsigned | NO | | NULL |

| | sum_of_other_index_sizes | bigint (20) unsigned | NO | | NULL |

+-+

6 rows in set (0.00 sec)

Mysql > select * from mysql.innodb_table_stats

+-+

| | database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes | |

+-+

| | fire | T1 | 2016-06-11 23:12:34 | 392945 | 392945 | 481 |

| | fire | T2 | 2016-06-11 23:15:12 | 2080004 | 4070 | 2341 | |

| | fire | test | 2016-06-09 01:23:06 | 0 | 1 | 0 |

| | mysql | gtid_executed | 2016-06-07 01:28:28 | 0 | 1 | 0 |

| | sys | sys_config | 2016-06-07 01:28:30 | 2 | 1 | 0 |

+-+

5 rows in set (0.08 sec)

Mysql > desc mysql.innodb_index_stats

+-- +

| | Field | Type | Null | Key | Default | Extra | |

+-- +

| | database_name | varchar (64) | NO | PRI | NULL |

| | table_name | varchar (64) | NO | PRI | NULL |

| | index_name | varchar (64) | NO | PRI | NULL |

| | last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | |

| | stat_name | varchar (64) | NO | PRI | NULL |

| | stat_value | bigint (20) unsigned | NO | | NULL |

| | sample_size | bigint (20) unsigned | YES | | NULL |

| | stat_description | varchar (1024) | NO | | NULL |

+-- +

8 rows in set (0.00 sec)

Mysql > select * from mysql.innodb_index_stats

+- -- +

| | database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |

+- -- +

| | fire | T1 | GEN_CLUST_INDEX | 2016-06-11 23:12:34 | n_diff_pfx01 | 392945 | 20 | DB_ROW_ID |

| | fire | T1 | GEN_CLUST_INDEX | 2016-06-11 23:12:34 | n_leaf_pages | 763 | NULL | Number of leaf pages in the index |

| | fire | T1 | GEN_CLUST_INDEX | 2016-06-11 23:12:34 | size | 801 | NULL | Number of pages in the index |

| | fire | T1 | idx_t1_a | 2016-06-11 23:12:34 | n_diff_pfx01 | 2 | 4 | a |

| | fire | T1 | idx_t1_a | 2016-06-11 23:12:34 | n_diff_pfx02 | 395866 | 20 | aMagneDB ROWIDs | |

| | fire | T1 | idx_t1_a | 2016-06-11 23:12:34 | n_leaf_pages | 403 | NULL | Number of leaf pages in the index |

| | fire | T1 | idx_t1_a | 2016-06-11 23:12:34 | size | 481 | NULL | Number of pages in the index |

| | fire | T2 | GEN_CLUST_INDEX | 2016-06-11 23:15:12 | n_diff_pfx01 | 2079570 | 20 | DB_ROW_ID |

| | fire | T2 | GEN_CLUST_INDEX | 2016-06-11 23:15:12 | n_leaf_pages | 4038 | NULL | Number of leaf pages in the index |

| | fire | T2 | GEN_CLUST_INDEX | 2016-06-11 23:15:12 | size | 4070 | NULL | Number of pages in the index |

| | fire | T2 | idx_t2_a | 2016-06-11 23:15:12 | n_diff_pfx01 | 3 | 5 | a |

| | fire | T2 | idx_t2_a | 2016-06-11 23:15:12 | n_diff_pfx02 | 2084334 | 20 | aMagneDB ROWIDs | |

| | fire | T2 | idx_t2_a | 2016-06-11 23:15:12 | n_leaf_pages | 2122 | NULL | Number of leaf pages in the index |

| | fire | T2 | idx_t2_a | 2016-06-11 23:15:12 | size | 2341 | NULL | Number of pages in the index |

| | fire | test | GEN_CLUST_INDEX | 2016-06-09 01:23:06 | n_diff_pfx01 | 0 | 1 | DB_ROW_ID |

| | fire | test | GEN_CLUST_INDEX | 2016-06-09 01:23:06 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| | fire | test | GEN_CLUST_INDEX | 2016-06-09 01:23:06 | size | 1 | NULL | Number of pages in the index |

| | mysql | gtid_executed | PRIMARY | 2016-06-07 01:28:28 | n_diff_pfx01 | 0 | 1 | source_uuid |

| | mysql | gtid_executed | PRIMARY | 2016-06-07 01:28:28 | n_diff_pfx02 | 0 | 1 | source_uuid,interval_start |

| | mysql | gtid_executed | PRIMARY | 2016-06-07 01:28:28 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| | mysql | gtid_executed | PRIMARY | 2016-06-07 01:28:28 | size | 1 | NULL | Number of pages in the index |

| | sys | sys_config | PRIMARY | 2016-06-07 01:28:30 | n_diff_pfx01 | 2 | 1 | variable |

| | sys | sys_config | PRIMARY | 2016-06-07 01:28:30 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |

| | sys | sys_config | PRIMARY | 2016-06-07 01:28:30 | size | 1 | NULL | Number of pages in the index |

+- -- +

24 rows in set (0.00 sec)

-- automatic collection of permanent statistics for configuration optimizer

When the entries in the table change (more than 10% of the rows change), the innodb_stats_auto_recalc parameter determines whether to re-collect statistics. This parameter is on by default. You can add STATS_AUTO_RECALC option to the CREATE TABLE and ALTER TABLE statements to enable automatic collection of statistics for the specified table.

The automatic collection of statistics is carried out asynchronously in the background. When a DML operation is performed on a table that affects the number of 10% rows in the table, the statistics may not be re-collected immediately when the innodb_stats_auto_recalc parameter is turned on, and the collection may be delayed by tens of seconds. If you need the latest statistics, you can execute the ANALYZE TABLE statement to collect statistics at the foreground.

Mysql > show variables like 'innodb_stats_auto_recalc'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_stats_auto_recalc | ON |

+-+ +

1 row in set (0.21 sec)

If the innodb_stats_auto_recalc parameter is not enabled, when there is a big change in the index field data in the table, such as a large amount of data is imported into the table, or the DML operation of the index field is changed periodically, the ANALYZE TABLE statement needs to be executed in time to ensure the accuracy of the optimizer statistics. When you create an index on an existing table, the statistics for the index are automatically collected and saved in the innodb_index_ stats table, regardless of whether the innodb_stats_auto_recalc parameter is turned on or not.

In the execution plan, the MySQL query optimizer uses index distribution statistics to select the index to use based on the selectivity of the index. When performing an ANALYZE TABLE operation, InnoDB samples each index to estimate the cardinality (the number of non-repeating values in a field), a technique known as random dives.

1. In the EXPLAIN output, the statistical information is not accurate, and the optimizer chooses a non-optimal execution plan. You can see the accuracy of the actual cardinality of the index by comparing the cardinality of the index in the SELECT DISTINCT index field with the index in the mysql.innodb_index_ stats table.

If the statistics are not accurate, increase the value of the parameter innodb_stats_persistent_sample_pages until the statistics are accurate enough. Increasing the value of this parameter too much will cause the ANALYZE TABLE operation to run slowly.

2. ANALYZE TABLE operation is too slow. At this point, you can consider reducing the value of the parameter innodb_stats_persistent_sample_pages until the execution time of ANALYZE TABLE is within an acceptable range. However, setting the value of this parameter too small may lead to inaccurate statistical information, which in turn affects the quality of the execution plan.

3. If you cannot strike a balance between the accuracy of statistics and the execution time of ANALYZE TABLE, consider reducing the number of index fields in the table or the number of partitions analyzed by ANALYZE TABLE.

These are all the contents of the article "how to configure InnoDB Optimizer permanent Statistics in Percona MySQL 5.6". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.