MySQL 5.6 Why turn off metadata statistics Automatic Update & statistical information collection source code exploration

Source: Internet
Author: User

Problem Description:

MySQL 5.5.15 the original SQL as follows:

Select Constraint_schema,table_name,constraint_name,constraint_type from Information_schema.table_constraints where Table_schema not in (' Information_schema ', ' MySQL ', ' Test ', ' Performance_schema ');

not just the above mentioned table_constraints,information_schema Library under a few tables, access time will trigger this "handy" operation.

Information_schema. TABLES

Information_schema. STATISTICS

Information_schema. Partitions

Information_schema. Key_column_usage

Information_schema. Table_constraints

Information_schema. Referential_constraints

Show Table status. .

Show index from ...

Automatic Update statistics are triggered when innodb_stats_on_metadata=on.

Problem:

5.6 Start default innodb_stats_on_metadata=off,why??? A: To prevent auto-update statistics from causing BP swap when the DB peaks, the query performance is greatly shaken.

Did you update the statistics regularly?? A: Yes, and it can be persistent.


I see the MySQL 5.5.15 this version or the condition is ====>

Counter > 2000000000 | | ((ib_int64_t) counter > + table->stat_n_rows/16)

The following does an extension to MySQL collection statistics:

Let's look at the analysis of the source code for MySQL 5.5.36:

---------------------------------------------------------------------------

#通过更新统计信息stat_modified_counter, each table has this list to maintain:

./storage/innobase/row/row0mysql.c

/*********************************************************************//**updates the table  Modification counter and calculates new estimatesfor table and index  statistics if necessary. */univ_inlinevoidrow_update_statistics_if_needed (/*============= ===============*/        dict_table_t*   table)    /*!< in: table */{        ulint    counter;        counter = table->stat_modified_ Counter;        table->stat_modified_counter = counter  + 1;        if  (Dict_table_changed_too_much (TABLE))  {                dict_ Update_statistics (&NBSP;&NBSP;&Nbsp;                      table,                         false, /* update even if  stats are initialized */                         true /* only  update if stats changed too much */);         }}/*********************************************************************/

rule: Each DML operation results in a 1row update, Stat_modified_counter adds 1 until the update statistics condition is met, and the value of Stat_modified_counter is automatically reset to 0.


* This has a performance problem, if there are multiple threads detected at the same time threshold, that is, concurrent calls will be multiple times, will cause the Dict_update_statistics function multiple calls, wasting system resources.

Workaround : The dict_update_statistics{} function locks the stat_modified_counter to avoid concurrent execution.

#统计新跟更新函数: dict_update_statistics

./storage/innobase/dict/dict0dict.c

/*********************************************************************//**calculates new estimates  for table and index statistics. The statisticsare used in  Query optimization. */univ_internvoiddict_update_statistics (/*===================*/         dict_table_t*   table,           /*!< in/out: table */         ibool           only_calc_if_missing_stats,/*! < in: only                                          update/recalc the stats if they have                                          not  been initialized yet, otherwise                                          do nothing */         ibool            only_calc_if_changed_too_much)/*!< in: only                                          update/recalc the  stats if the table                                          has been changed too much since the                                           last stats update/recalc */{        dict_index_t*    index;        ulint            sum_of_index_sizes      = 0;         dbug_execute_if ("Skip_innodb_statistics", return;);------------ ----------------------------------------------------------------- 

Can be optimized into:

1) plus x lock

2) Index Statistics

3) stat_modified_counter 0

4) Unlock

---------------------------------------------------------------------------

Two. MySQL 5.6 change into:

Can configure persistence and non-persistence of statistics (non-persistent: this is the case before 5.6)

Related parameters:

Persistence:

Innodb_stats_persistent: On(1)

Innodb_stats_persistent_sample_pages:

Non-persistent:

Innodb_stats_sample_pages:8

Related tables:

Mysql.innodb_index_stats

Mysql.innodb_table_stats

From 5.6.6 , the statistics are persisted by default (that is, innodb_stats_persistent=on), using the values of the parameter innodb_stats_persistent_sample_pages to sample , the non-persisted parameter innodb_stats_sample_pages is not valid at this time.

Starting from 5.6.6 , use non-persisted statistics:

1.set innodb_stats_persistent=0;

2.create|alter table stats_persistent=0;

To open a single table:

Create|alter table ... stats_persistent [=] {default|0|1}

DEFAULT : The table's statistics are persisted by the parameter innodb_stats_persistent. \

Summary: Starting from5.6.6 , either turn on the statistics persistence or use the previous non- persisted, and choose one.


Refer to relevant parameters:

Innodb_stats_method:nulls_equalnulls_unequal, andnulls_ignored
Myisam_stats_method:nulls_equalnulls_unequal, andnulls_ignored

--------------------------------------------------------------

基数即value group=N/s (N:表行数 S:average group size)
基数(VG)|值组为不重复的值的个数

nulls_equal:所有的NULL都相等,算作一个值组,这样一旦null值很多的情况下,average group size偏大,导致基数偏小。

nulls_unequal:每一个NULL都相等,算作一个值组,这样一旦null值很多的情况下,如果non-null值组大,而null的值组过多,导致average group size偏小,导致基数偏大,可能导致误走索引

nulls_ignored:所有的null都忽略,不记录索引。

--------------------------------------------------------------

参考:

# Http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_method

This article is from the My DBA life blog, so be sure to keep this source http://huanghualiang.blog.51cto.com/6782683/1596170

MySQL 5.6 Why turn off metadata statistics Automatic Update & statistical information collection source code exploration

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.