[MySQL 5.6] Innodb 后台线程之 dict stats 线程 及如何计算索引统计信息

前言

 

在5.6中,引入的一个新参数innodb_stats_auto_recalc用于控制是否进行自动统计信息计算。当表上的记录修改超过10%时,就会对统计信息重新计算;这只对在建表时打开了innodb_stats_persistent或者指定了建表选项STATS_PERSISTEND=1生效,采样page的个数通过参数innodb_stats_persistent_sample_pages来控制(实际读取的page数会大于该值)。

在函数dict_stats_is_persistent_enabled中可以看到,如果没有明确为ON/OFF的话(物理升级?),直接由innodb_stats_persistent来确定
我们也可以根据表的具体负载情况,通过alter语句为表设置是否打开或关闭STATS_PERSISTEND,因为统计信息的重计算,是一个开销很大的过程。
在MySQL 5.6中,完成统计信息自动计算的是一个独立线程用于重新计算表或索引的统计信息.在一个loop中,等待事件dict_stats_event,或者每隔10秒被无条件唤醒。实际工作函数为dict_stats_process_entry_from_recalc_pool()

新对象:recalc_pool

 

1.recalc_pool

 

在内存中维持了这样一个vector,其定义如下:

 57 typedef std::vector<table_id_t> recalc_pool_t;

 58 static recalc_pool_t            recalc_pool;

所有需要被重新计算的表会加入到recalc_pool中,recalc_pool初始化大小为128,随后如果需要再被扩大。recalc_pool通过recalc_pool_mutex来进行互斥操作
2.将表加入到recalc_pool中
dict_stats_recalc_pool_add: 遍历recalc_pool,查看表的table->id是否已经存在,如果存在,直接返回,否则将该表的id加入到recalc_pool中,并发送dict_stats_event事件
在做完DML后,会去调用函数row_update_statistics_if_needed判断是否需要更新统计信息,有以下集中情况:
1)如果表明确指定了STATS_PERSISTEND或者打开了innodb_stats_persistent
     (1)表上明确指定了stats_auto_recalc或者innodb_stats_auto_recalc为TRUE,并且修改的记录数大于总记录数的10分之一时,调用dict_stats_recalc_pool_add将表放到recalc_pool中,并将修改计数器table->stat_modified_counter重置为0.
     (2)不满足(1),直接return,不进行下面的判断
2)这时候,在关闭了PERSISTENT STATS的情况下,当发现修改的记录超过6.25%时,更新统计信息dict_stats_update(table, DICT_STATS_RECALC_TRANSIENT)->dict_stats_update_transient
3.dict stat线程如何处理
dict stat线程接受到dict_stats_event事件后,调用工作函数dict_stats_process_entry_from_recalc_pool()
主要做以下几件事情:
1)从recalc_pool中拿第一个table id
2)持有dict->mutex锁
3)根据表id获取表对象(dict_table_open_on_id(table_id, TRUE, FALSE));如果表被drop掉了,这里返回的是NULL,不做任何处理
4)将表标记为table->stats_bg_flag = BG_STAT_IN_PROGRESS; 这样就无法去DROP 该表了。
5)现在可以安全的释放dict_sys->mutex
6)如果该表在10秒内 已经计算过一次,那么就把该表重新放到recalc_pool尾部,不做任何处理。否则:
调用dict_stats_update(table, DICT_STATS_RECALC_PERSISTENT);
实际上DICT_STATS_RECALC_PERSISTENT类型的状态信息更新,也会由ANALYZE TABLE发起
注意有几个相关的系统表:mysql/innodb_table_stats 和 mysql/innodb_index_stats 。我们甚至可以update这两个系统表,来影响索引统计信息的存储

dict_stats_update()函数,在传参为DICT_STATS_RECALC_PERSISTENT时,做三件事:
(1) 检查dict_stats_persistent_storage_check() 检查相关系统表是否存在,不存在报错
(2)dict_stats_update_persistent(table) 更新表的统计信息
先更新聚集索引,再更新二级索引,均调用函数dict_stats_analyze_index.
(3)dict_stats_save(table) 将统计信息更新到持久化存储系统表中
下面我们主要讨论的是第(2)步,也就是如何去计算索引的统计信息

如何计算索引统计信息

 

入口函数为dict_stats_analyze_index
1.首先从root page获取:
a.获取该索引总的page数:
index->stat_index_size = btr_get_size(index, BTR_TOTAL_SIZE, &mtr) 这些信息只需要从根节点的segment header读取
b.叶子节点page数:
 index->stat_n_leaf_pages = btr_get_size(index, BTR_N_LEAF_PAGES, &mtr);
2.
持有索引的s锁(mtr_s_lock(dict_index_get_lock(index), &mtr);),获取B-TREE的高度,也就是ROOT PAGE所在的level:
root_level = btr_height_get(index, &mtr);
获取决定索引唯一性的key的个数:
n_uniq = dict_index_get_n_unique(index);  例如对于一个典型的sbtest表,索引k上的n_uniq为2,因为要加上聚集索引键
3.
如果该BTREE只有一层(root_level == 0 ),或者采样的page数*n_uniq >叶子节点个数时,直接扫描全部叶子节点:
1817                 dict_stats_analyze_index_level(index,
1818                                                0 /* leaf level */,
1819                                                index->stat_n_diff_key_vals,
1820                                                &total_recs,
1821                                                &total_pages,
1822                                                NULL /* boundaries not needed */,
1823                                                &mtr);
1824
1825                 for (ulint i = 0; i < n_uniq; i++) {
1826                         index->stat_n_sample_sizes[i] = total_pages;
1827                 }
1828
1829                 mtr_commit(&mtr);
1830
1831                 dict_stats_assert_initialized_index(index);
1832                 return;

一般情况下,需要采样的page数 =  N_SAMPLE_PAGES(index) = innodb_stats_persistent_sample_pages ,默认为20个采样page

dict_stats_analyze_index_level函数的功能下面会描述到,这里由于B-TREE只有一层(root_level = 0),也就是一个page,因此可以认为full scan的开销非常小,这里简单的赋值后,就直接返回了。
4.对于稍微大点的表而言,都会走到以下的计算统计值的逻辑。这部分的逻辑就比较复杂了。
为了表述方便,我们以如下表为例:

CREATE TABLE `t1` (

  `a` int(11) NOT NULL DEFAULT ‘0’,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `d` (`d`),
  KEY `b` (`b`,`c`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

假设表t1有3层,我们总是从第3层开始检索。
那么对于索引  KEY `b` (`b`,`c`) 而言,n_unqie = 3,因此需要按照前缀列的个数,从多到少,依次统计前缀索引(b,c,a), (b,c), (b)
a. 首先,对于第一个索引前缀(b,c,a),从根节点开始,找到一个合适的非叶子节点level
for(;;)
<a.1>如果total_recs > N_SAMPLE_PAGES(index) = 20, level++,选择向上的level,并从循环break。对于初始化根节点为root,这显然是不可能发生的,因为total_recs初始化为1。
<a.2>获取当前level的记录信息,执行:
                        dict_stats_analyze_index_level(index,
                                                       level,
                                                       n_diff_on_level,
                                                       &total_recs,
                                                       &total_pages,
                                                       n_diff_boundaries,
                                                       &mtr);
获取:
  • 该层的记录数total_recs
  • 该层的page数total_pages
  • n_diff_on_level[0 ~ n_unique-1] 该层的前缀索引key数目
  • n_diff_boundaries[0~n_unique-1] :该数据每个元素是一个动态数组,当用当前记录和上一个记录比较时,如果从0~N列的key是相同的,则对于N+1~n_unique_1个列的前缀索引,将一个记录记录到该数组中,例如当前记录idx为total_recs-1,前一个记录的idx为total_recs-2,则将total_recs-2记录到n_diff_boundaries[i] (N+1 < n < =n_unique-1)中;该level最后一个记录的idx(total_recs-1)也被记录到每个n_diff_boundaries数组成员中。
简单的讲,n_diff_boundaries每个成员,实际上记录了不同前缀key的范围。n_diff_boundaries[i]中存储的整数的个数 应该和n_diff_on_level[i]是一致的
<a.3>如果当前层次上,对于n_prefix(本例中n_prefix为3)个前缀列,不同键值的个数n_diff_on_level[n_prefix – 1] =n_diff_on_level[2] >=N_DIFF_REQUIRED(index) 或者当前level为1 ,则停止继续查找下一层level,从循环中break。N_DIFF_REQUIRED(index) = N_SAMPLE_PAGES(index) * 10   = 20 * 10  = 200(默认情况下)
我们选择出来的”good enough”的level,必须为非叶子节点。
从函数的逻辑,可以看到,对于同一个level, dict_stats_analyze_index_level函数最多被执行一次。
b.计算统计信息
调用
                dict_stats_analyze_index_for_n_prefix(
                        index, level, total_recs, n_prefix,
                        n_diff_on_level[n_prefix - 1],
                        &n_diff_boundaries[n_prefix - 1], &mtr);
这一步,就会根据选定的层次,选择当前level的N_SAMPLE_PAGES(index)条记录,从该level查询到对应的叶子节点,然后将采样的结果记录到index->stat_n_diff_key_vals[]和index->stat_n_sample_sizes[] 中。这时候会用到上面在函数dict_stats_analyze_index_level中获取的信息
开启一个循环,循环的loop次数为采样page数:
        n_recs_to_dive_below = ut_min(N_SAMPLE_PAGES(index),
                                      n_diff_for_this_prefix);
        因此,如果表很小,或者前缀key的区分度很低的话,可能会小于默认20个page.

 

 

b.1.进行分段

                let n_diff_for_this_prefix=100, n_recs_to_dive_below=4, then:

                segment i=0:  [0, 24]
                segment i=1: [25, 49]
                segment i=2: [50, 74]
                segment i=3: [75, 99] or
                let n_diff_for_this_prefix=1, n_recs_to_dive_below=1, then:
                segment i=0: [0, 0] or
                let n_diff_for_this_prefix=2, n_recs_to_dive_below=2, then:
                segment i=0: [0, 0]
                segment i=1: [1, 1] or
                let n_diff_for_this_prefix=13, n_recs_to_dive_below=7, then:
                segment i=0:  [0,  0]
                segment i=1:  [1,  2]
                segment i=2:  [3,  4]
                segment i=3:  [5,  6]
                segment i=4:  [7,  8]
                segment i=5:  [9, 10]
                segment i=6: [11, 12]
以上摘自注释,解释的也很清楚,从每一段所代表的区间中, 取一个随机数k,再找到n_diff_boundaries[n]对应动态数组中第k个记录,找到当前level对应的第k个用户记录所在的位置(定位pcur).
b.2根据pcur,读取其对应叶子节点上当前前缀索引Key 不重复的记录数:

                n_diff_on_leaf_page = dict_stats_analyze_index_below_cur(btr_pcur_get_btr_cur(&pcur), n_prefix, mtr);

dict_stats_analyze_index_below_cur的逻辑:
1.首先找到叶子节点,在一个for循环中:
根据传递的pcur,获取其子节点,读入page(buf_page_get_gen),如果这个page所在的level不是叶子节点,则调用函数dict_stats_scan_page:从这个page的第一个记录开始读起,由于scan类型为QUIT_ON_FIRST_NON_BORING,因此当它读取到第一个和他的邻居记录的n_prefix个前缀列的值不匹配时,会返回,这意味着dict_stats_scan_page对于QUIT_ON_FIRST_NON_BORING类型的扫描,n_diff值只会为0(空page),1(page上的记录完全相同),或者2(找到两个前缀列不同的记录)。
函数dict_stats_scan_page返回值是第一个和其邻居记录 前缀列不同的记录便宜量,
如果n_diff =1 ,表示该page上的前缀列的key均相同,直接返回1, 不继续找叶子节点
如果n_diff =2, 则根据dict_stats_scan_page返回的记录,获取下一层的page no
2.扫描找到的叶子page,找到其中前缀列不同的记录数,这里我们和上面一样调用的是dict_stats_scan_page,但scan类型是COUNT_ALL_NON_BORING_AND_SKIP_DEL_MARKED
        offsets_rec = dict_stats_scan_page(
                &rec, offsets1, offsets2, index, page, n_prefix,
                COUNT_ALL_NON_BORING_AND_SKIP_DEL_MARKED, &n_diff);
从dict_stats_analyze_index_below_cur返回的是一个叶子page上的前缀列不同的记录数(该值会减1),将其累加到n_diff_sum_of_all_analyzed_pages中,然后继续回到b.1,找下一个分段,直到采样记录数完成
完成默认20个Page的采样后,统计值估算公式为:
        index->stat_n_diff_key_vals[n_prefix - 1]
                = index->stat_n_leaf_pages

                * n_diff_for_this_prefix
                / total_recs_on_level

                * n_diff_sum_of_all_analyzed_pages
                / n_recs_to_dive_below;

        index->stat_n_sample_sizes[n_prefix - 1] = n_recs_to_dive_below;
c.
在完成该索引的前缀列(b,c,a)后,在继续处理(b,c)时,  会先根据上次执行dict_stats_analyze_index_for_n_prefix的结果做判断:
如果n_diff_on_level[1] >= N_DIFF_REQUIRED(index) = 200 或者level =1 ,直接选择上一次选择的level;
否则,从level-1层,也就是下一层开始重复上面的逻辑来找一个合适的level。这种优化可以避免每次从root开始查找,因为很显然,对于有N个列的前缀索引,如果在L层有M条 去重key,那么对于有N-1个列的前缀索引,它在第L层的去重key的数目必然小于等于M;

其他:

 

需要注意的是,在计算统计信息的过程持有索引上的s锁,如果索引统计信息重计算被触发,这可能影响写入负载大的场景(索引分裂、合并等操作)。因此在5.6中要确保后台线程不自动重计算统计信息,需要把参数innodb_stats_auto_recalc手动关闭掉,默认是开启的。这种行为在写负载很大的场景下,可能会引起性能抖动。

原创文章,转载请注明: 转载自Simple Life

本文链接地址: [MySQL 5.6] Innodb 后台线程之 dict stats 线程 及如何计算索引统计信息

Post Footer automatically generated by wp-posturl plugin for wordpress.


Comments

Leave a Reply

Your email address will not be published. Name and email are required


Current month ye@r day *