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

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_equal, nulls_unequal, andnulls_ignored
Myisam_stats_method:nulls_equal, nulls_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