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

This article mainly explains "how to solve the InnoDB Persistent Statistics problem". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to solve the InnoDB Persistent Statistics problem".

Background:

The optimizer of MySQL chooses the optimal execution plan through the data collected by innodb, but because the data will be recalculated with certain operations, the execution plan will change many times, resulting in imprecision and instability.

The operations that lead to recalculation are:

1. Restart

two。 Access table

3. Data changes in the table (1 DML above 16)

4.show table status and show index for table

5.analyze table

6.and so on

In order to solve this problem, continuous optimization statistics are added in mysql 5.6. continuous statistics are stored in innodb_table_stats and innodb_index_stats as system tables, which were also mentioned in the last share.

How to conduct continuous optimization statistics:

Mysql > show variables like'% innodb_stats%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_stats_auto_recalc | ON |

| | innodb_stats_method | nulls_equal |

| | innodb_stats_on_metadata | OFF |

| | innodb_stats_persistent | ON |

| | innodb_stats_persistent_sample_pages | 20 |

| | innodb_stats_sample_pages | 8 |

| | innodb_stats_transient_sample_pages | 8 |

+-+ +

1. For all innodb tables, you can set global parameters

Global parameters:

Whether innodb_stats_persistent enables statistics

Innodb_stats_auto_recalc automatic recount

Number of pages randomly sampled by innodb_stats_persistent_sample_pages

This parameter of innodb_stats_on_metadata is mainly statistical analysis of metadata index. For example, querying some tables in information_schema, and show table status will also cause innodb to extract data randomly, which can easily lead to significant jitter in query performance. After 5.6, this parameter is already very fishy, and not enabling does not affect the accuracy of data statistics at all.

2. Single table

(1) whether stats_persistent guarantees continuous statistics for innodb tables

ALTER TABLE table_name stats_persistent=1

The default is determined by the innodb_stats_persistent option

(2) whether stats_auto_recalc automatically calculates persistent statistics for innodb tables

The default is determined by the innodb_stats_auto_recalc option, when it is 1

When 10% of the data changes, recalculate, according to my test, probably more than 10%

(3) stats_sample_pages specifies the number of random index pages

Example:

CREATE TABLE `t1` (

`id` int NOT NULL AUTO_INCREMENT

`data`varchar (255) DEFAULT NULL

`date`datetime DEFAULT NULL

PRIMARY KEY (`id`)

KEY `idx_ date` (`date`)

) ENGINE=InnoDB CHARSET=utf8 STATS_PERSISTENT=1 STATS_AUTO_RECALC=1 STATS_SAMPLE_PAGES=25

Example of Innodb statistics:

Mysql > select * from T2

+-- +

| | a | b | c | d | e | |

+-- +

| | 1 | 1 | 1 | 1 | 1 |

| | 2 | 1 | 1 | 2 | 2 |

| | 3 | 1 | 1 | 3 | 3 |

| | 4 | 1 | 1 | 4 | 4 |

| | 5 | 1 | 1 | 5 | 5 |

| | 6 | 1 | 1 | 6 | 6 |

| | 7 | 1 | 1 | 7 | 7 |

| | 8 | 1 | 1 | 8 | 8 |

| | 9 | 1 | 1 | 9 | 9 |

| | 10 | 1 | 1 | 10 | 10 | |

+-- +

10 rows in set (0.01 sec)

Mysql > select * from mysql.innodb_table_stats\ G

* * 1. Row *

Database_name: test

Table_name: t2

Last_update: 2016-02-24 18:58:22

N_rows: 8

Clustered_index_size: 1

Sum_of_other_index_sizes: 2

1 row in set (0.00 sec)

Update statistics immediately using analyze table

Mysql > analyze table T2

+-+

| | Table | Op | Msg_type | Msg_text | |

+-+

| | test.t2 | analyze | status | OK | |

+-+

1 row in set (0.01 sec)

Mysql > select * from mysql.innodb_table_stats\ G

* * 1. Row *

Database_name: test

Table_name: t2

Last_update: 2016-02-24 19:00:23

N_rows: 10

Clustered_index_size: 1

Sum_of_other_index_sizes: 2

1 row in set (0.01 sec)

You can see that the statistics have changed.

The impact of the number of sampled pages

Based on the relative selection of the index, the mysql query optimizer selects the execution plan of the index through key distribution (that is, cardinality) statistics, while using analyze table causes innodb to estimate the index selection by fetching random pages from each index on the table.

To control the accuracy and stability of statistics, you can change the following parameters

The default value of innodb_stats_persistent_sample_pages is 20

Statistics are not accurate, and the optimizer chooses an ideal plan, such as explain

Accurate statistics are made by comparing the actual cardinality of the index with the estimated values in the index statistics table, such as select distinct in the index column

Of course, if automatic updates are turned on, the threshold for line changes up to 10% will be updated in a few seconds.

Innodb_stats_persistent_sample_pages increases this value, although it will make the statistics more accurate, but at the same time, it may require more disk reads, resulting in opening the table or executing show table status, and it is also slow for analyze table, because its complexity calculation is related to this parameter, innodb_stats_sample_pages * index column * number of partitions; but it must not be too small, such as 1 or 2, which will lead to statistical inaccuracy.

Thank you for your reading, the above is the content of "how to solve the InnoDB Persistent Statistics problem", after the study of this article, I believe you have a deeper understanding of how to solve the InnoDB Persistent Statistics problem, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.