转载

Mysql 5.7中数据量更改统计数据收集的逻辑

今天一个朋友在问Mysql数据修改后什么时候收集统计数据，我就简单的找了一下源代码，现总结如下。如有错误请指出，因为我只是简单做了一下调试。

一、持久化(PERSISTENT))与非持久化统计数据(TRANSIENT)

Mysql统计数据分为持久化和非持久化

持久化统计数据
存储在mysql.innodb_index_stats和mysql.innodb_table_stats中
非持久化统计数据
存储在information_schema.indexes和information_schema.tables中

前者是innodb表后者是memory表。他们受到参数innodb_stats_persistent的控制，默认为ON。
关于这里还参考官方文档：

14.6.12 Configuring Optimizer Statistics for InnoDB

二、持久化统计数据的更改方式。

我在看更改逻辑的时候，发现非持久化统计数据是直接更新的或者说是同步更新的。但是持久化统计数据应该是异步更新的，看起来是典型的生产者消费者模型。Mysql中有一个专门的后台线程来负责收集统计数据如下：

mysql> select name,thread_id from performance_schema.threads where NAME like '%dict_stats_thread%' /G
*************************** 1. row ***************************
     name: thread/innodb/dict_stats_thread
thread_id: 25

这个线程会从一个vcoter数组中取出需要更新统计数据的表的table_id然后更新统计数据，而生产者就是我们的当前线程他通过逻辑判断将需要更新统计数据表的table_id加入到这个数组，什么逻辑呢？我们稍后在讨论。现在来看一个简单的图：

11.png

三、更改逻辑

其实更改逻辑还是相当简单基本就是两个函数就总结了，他们是row_update_statistics_if_needed函数和dict_stats_process_entry_from_recalc_pool函数，但是我不知道有没有漏掉的。

持久化逻辑(PERSISTENT)

1、数据修改超过10% ，入口函数row_update_statistics_if_needed逻辑如下：

 if (dict_stats_is_persistent_enabled(table)) {
        if (counter > n_rows / 10 /* 10% */
            && dict_stats_auto_recalc_is_enabled(table)) {

            dict_stats_recalc_pool_add(table);
            table->stat_modified_counter = 0;
        }
        return;
    }

2、两次申请统计数据收集超过10S，入口函数dict_stats_process_entry_from_recalc_pool，逻辑如下：

if (ut_difftime(ut_time(), table->stats_last_recalc)
        < MIN_RECALC_INTERVAL) {

        /* Stats were (re)calculated not long ago. To avoid
        too frequent stats updates we put back the table on
        the auto recalc list and do nothing. */

        dict_stats_recalc_pool_add(table);

    } else {

        dict_stats_update(table, DICT_STATS_RECALC_PERSISTENT);
    }

其中MIN_RECALC_INTERVAL 为宏定义

#define MIN_RECALC_INTERVAL 10 /* seconds */

非持久化逻辑(TRANSIENT)

- 修改超过1/16进行收集，入口函数row_update_statistics_if_needed，逻辑如下：

if (counter > 16 + n_rows / 16 /* 6.25% */) {

        ut_ad(!mutex_own(&dict_sys->mutex));
        /* this will reset table->stat_modified_counter to 0 */
        dict_stats_update(table, DICT_STATS_RECALC_TRANSIENT);
    }

四、总结

这里我只是对触发逻辑进行了简单的梳理，但是很多其他参数并没有进行介绍，还是参考官方文档：

14.6.12 Configuring Optimizer Statistics for InnoDB

其次我以前写的一篇文章可以参考如下：

http://blog.itpub.net/7728585/viewspace-1795516/

五、备用栈帧

这是留个我自己调试用的

当前线程栈帧：

#0  dict_stats_recalc_pool_add (table=0x7fff1805a790) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/dict/dict0stats_bg.cc:129
#1  0x0000000001b0f4e3 in row_update_statistics_if_needed (table=0x7fff1805a790) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0mysql.cc:1206
#2  0x0000000001b10c34 in row_insert_for_mysql_using_ins_graph (mysql_rec=0x7fff180479a0 "/375/036", prebuilt=0x7fff18098c10)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0mysql.cc:1831
#3  0x0000000001b10cbf in row_insert_for_mysql (mysql_rec=0x7fff180479a0 "/375/036", prebuilt=0x7fff18098c10)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/row/row0mysql.cc:1857
#4  0x00000000019b04dd in ha_innobase::write_row (this=0x7fff18047460, record=0x7fff180479a0 "/375/036")
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:7923
#5  0x0000000000f732af in handler::ha_write_row (this=0x7fff18047460, buf=0x7fff180479a0 "/375/036") at /root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:8228
#6  0x00000000017d1764 in write_record (thd=0x7fff18000b70, table=0x7fff18036420, info=0x7ffff0255a30, update=0x7ffff02559b0)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:1864
#7  0x00000000017ce909 in Sql_cmd_insert::mysql_insert (this=0x7fff18006b00, thd=0x7fff18000b70, table_list=0x7fff18006570)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:780
#8  0x00000000017d5349 in Sql_cmd_insert::execute (this=0x7fff18006b00, thd=0x7fff18000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_insert.cc:3100
#9  0x00000000015a773e in mysql_execute_command (thd=0x7fff18000b70, first_level=true) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:3719
#10 0x00000000015adcae in mysql_parse (thd=0x7fff18000b70, parser_state=0x7ffff0257600) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:5836
#11 0x00000000015a1b6d in dispatch_command (thd=0x7fff18000b70, com_data=0x7ffff0257d70, command=COM_QUERY)
    at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1447
#12 0x00000000015a099e in do_command (thd=0x7fff18000b70) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/sql_parse.cc:1010
#13 0x00000000016e28f0 in handle_connection (arg=0x68d7220) at /root/mysql5.7.14/percona-server-5.7.14-7/sql/conn_handler/connection_handler_per_thread.cc:312
#14 0x0000000001d7a514 in pfs_spawn_thread (arg=0x37fcbc0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/perfschema/pfs.cc:2188
#15 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0
#16 0x0000003f740e8bcd in clone () from /lib64/libc.so.6

统计数据收集线程栈帧：

#0  dict_stats_update (table=0x7fff1805a790, stats_upd_option=DICT_STATS_RECALC_PERSISTENT)
    at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/dict/dict0stats.cc:3052
#1  0x0000000001cd0362 in dict_stats_process_entry_from_recalc_pool () at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/dict/dict0stats_bg.cc:349
#2  0x0000000001cd05a9 in dict_stats_thread (arg=0x0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/dict/dict0stats_bg.cc:438
#3  0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0
#4  0x0000003f740e8bcd in clone () from /lib64/libc.so.6

作者微信：