MySQL分区表的一个性能BUG

待我称王封你为后i 2022-09-11 13:19 106阅读 0赞

### 水平有限，有误请谅解。最后提交了BUG，并且验证。 ###

### 一、问题描述 ###

最近遇到一个问题，这个问题我和李端峰（微信流轉的時光ゝ）、康壮一起分析，感谢2位的支持。也就是使用分区表进行数据查询/加载的时候比普通表的性能下降了约50%，主要瓶颈出现在CPU，既然是CPU瓶颈理所当然的我们可以采集perf top -a -g和pstack来寻找性能瓶颈所在，同时和普通表进行对比，发现CPU主要耗在函数build\_template\_field上如下图：![40cce99792191f3e07e5f1a2f56ff349.png][]

### 二、使用pt-pmap进行栈分析 ###

为了和perf top -g -a进行相互印证，我们同时获取了当时的pstack，由于线程较多为了方便获取有用的信息我们通过pt-pmap进行了格式化如下：![a045cc2c03c5fec5449760f598071312.png][]

格式化后我们提出掉空闲的等待栈，发现大量的如上，这也和perf top -a -g中的表现进行了相互印证。

### 三、关于本列中瓶颈点的分析 ###

我们看到这里大量的cpu耗在

ha_innobase::build_template
     ->build_template_field
       ->dict_col_get_clust_pos

对于template来讲，其几乎是和特定的一次的查询进行绑定的，也就是普通的语句至少需要一个template。其结构为row\_prebuilt\_t，包含查询元组，查询的表，查询用到的索引，事务相关信息，持久化游标，MySQL层查询行的长度，自增信息，ICP相关信息，mysql\_row\_templ\_t结构等信息。其中mysql\_row\_templ\_t 这个信息就是每个字段一个，主要作用记录的是MySQL层feild信息和Innodb层columns信息的相关属性，用于快速转换一行记录在MySQL层和Innodb层之间转换。为了初始化mysql\_row\_templ\_t 就出现了上面的逻辑，大概逻辑如下：

循环表中每个字段（一层循环）ha_innobase::build_template
      是否为需要访问的字段 build_template_needs_field
      这里包含查询和写入的所有字段，需要访问的字段越多越慢
       如果不是则不作继续循环
      如果需要访问
      build_template_field（mysql_row_templ_t结构体填充）
        循环主键的每个字段（二层循环）
        包含伪列,主键就是表的里面全部字段，表中字段越多越慢)dict_col_get_clust_pos  
          确认本字段在主键的位置 
          pos0 主键 pos1 DB_TRX_ID pos2 DB_ROLL_PTR pos3 开始为用户其他字段
        循环索引的每个字段（二层循环，但是索引字段一般不会太多，因此这里不会慢）dict_index_t::get_col_pos
          确认本字段在索引的位置，如果没有则返回NULL
          返回pos 比如 主键 id1  二级索引 id2 id3 二级索引为 pos0 id2 pos1 id3 pos2 id1
        继续完成其他属性比如mysql null位图，mysql显示长度，mysql字符集等等

这里我们看到这里实际上有2层循环，也就是循环套循环（时间复杂度O(M×N)），而循环影响最大的有2个地方：

*  第一层，表中字段的多少
 *  第二层，需要访问的字段（读和写都算）在主键（也就是全部字段）中循环

这里也就是为什么这里会慢的原因。但是template通常不会一个查询进行多次建立，比如一个普通表的大查询，只有在语句第一次进行数据定位之前会进行建立，这就不得不说这是分区表和普通表的对比中一个特殊的地方了。下面描述一下。

### 四、分区表中多次建立template的情况 ###

假设我们有如下的分区表：

create table t(
        id1 int,
        id2 int,
        primary key(id1),
        key(id2)
    )engine=innodb
    partition by range(id1)(
        partition p0 values less than(100),
        partition p1 values less than(200),
        partition p2 values less than(300));    
    
    insert into t values(1,1);
    insert into t values(101,1);
    insert into t values(201,1);
    insert into t values(2,2);
    insert into t values(3,2);
    insert into t values(4,2);
    insert into t values(7,2);
    insert into t values(8,2);
    insert into t values(9,2);
    insert into t values(10,2);

我们使用语句"select \* from t where id2=1"，显然id2是二级索引，由于MySQL全部都是local分区的二级索引，因此这里值分别分布在3个分区中，对于这样一个语句在本该是普通表通过上次定位后的位置继续访问（next\_same）的时候，通过封装分区表的方法，将其改为了index read再次定位，而我们可以清楚的看到这里是scan next partition，其part=1这是第二个分区了，也就是我们的p1（第一个为0）![eedb97fd825b0bd50e55752b5a534bfa.png][]这样template需要每个分区（scan next partition）都进行重建，这样就出现了我们上面的问题。这个其实也可以理解，新的分区是新的innodb文件，这样上次定位的持久化游标实际已经没有什么用了，就相当于一次新的表访问。这里在是否进行template建立还有一个判断如下：

if (m_prebuilt->sql_stat_start) {
        build_template(false);
      }

而m\_prebuilt->sql\_stat\_start除了在语句开始的时候设置为true，每次更换分区依旧会设置为true如下：

ha_innopart::set_partition:
    m_prebuilt->sql_stat_start = m_sql_stat_start_parts.test(part_id);

### 五、关于一个特殊的流程 ###

在我们的故障pstack中还有一个栈如下：![506940d4c88a83af4069d0e4f23f996f.png][]

这个栈实际并不完整，但是其中出现了Partition\_helper::handle\_ordered\_index\_scan，这个函数实际上和分区表的排序有关，如果我们考虑这样一种情况，对于二级索引select max(id2) from t，那么需要首先访问每个分区获取其中的最大值然后对比每个分区的最大值，得到最终的结果，而MySQL则采用优先队列进行处理，这应该是就是本函数完成的部分功能（没仔细去看）。其次我们先出现了QUICK\_RANGE\_SELECT这是范围查询会用到的，那么我们构造如下：

select * from t where id2<2 order by id2;

栈：![558f332a66f388deac71c26163166829.png][]

这里就是因为id2这个字段只保证在分区内部是按照大小排列的但是在整个表来讲，它是无序的，需要额外的处理。

### 六、问题模拟 ###

有了这些准备，我们可以构造一个300个字段和25个分区的分区表。测试版本最新8.0.26

create table tpar300col(
        id1 int,
        id2 int,
        id3 int,
        id4 int,
    ...
        id299 varchar(20),
        id300 varchar(20),
        primary key(id1),
        key(id2)
    )engine=innodb
    partition by range(id1)(
        partition p0 values less than(100),
        partition p1 values less than(200),
        partition p3 values less than(300),
     ...
        partition p25 values less than(2500));  
    
    insert into tpar300col values(1  ,1,1,
    ....每个分区一条数据
    insert into tpar300col values(2401,1,1
    然后构造一些其他数据id2不要为1，建立存储过程：
    delimiter //
    
    CREATE PROCEDURE test300col()
    begin 
      declare num int;
      set num = 1; 
    while num <= 1000000 do
      select * from tpar300col where id2=1;
      set num = num+1;
    end while;
    end //
    执行：
     /opt/mysql/mysql3340/install/mysql8/bin/mysql -S--socket=/opt/mysql/mgr3315/data/mgr3315.sock -e'use test;call test300col();' > log.log

然后perf top 观察如下：![c543f5939cbb16efd8a60a97b66166c6.png][]

这样问题就得到了确认。

### 七、总结 ###

这个问题实际上和二级索引相对于分区键的数据离散度有关，但是我们无法控制二级索引的数据，并且索引也是必须使用的。只能通过一些方面尽量避免，当然我也提交了一个BUG，如下：

https://bugs.mysql.com/bug.php?id=104576

不知道是否有办法修复这个问题，比如对于分区表来讲实际上每个分区的字段都是一样的，是否需要每次都重建mysql\_row\_templ\_t.clust\_rec\_field\_no？如果不需要那么问题自解，官方目前已经验证了这个问题确实存在。如下是一些避免的方式，

*  分区表字段不宜过多
 *  访问的字段不应该一味的使用select \*
 *  避免使用hash分区，hash分区会增加这种问题

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

**文章推荐：**

*  [MGR最佳实践(MGR Best Practice)][MGR_MGR Best Practice]
 *  [万里数据库MGR Bug修复之路][MGR Bug]
 *  [《叶问》36期，MySQL最多只能用到128个逻辑CPU，是真的吗][36_MySQL_128_CPU]
 *  [GreatSQL重磅特性，InnoDB并行并行查询优化测试][GreatSQL_InnoDB]
 *  [一周碎碎念，2021.8.8，正在筹备MGR专题课程][2021.8.8_MGR]
 *  [金融应用场景下跨数据中心的MGR架构方案][MGR]  
    
 *  [InnoDB Cluster+GreatSQL快速部署MGR集群，并实现读写分离和故障自动转移][InnoDB Cluster_GreatSQL_MGR]
 *  [利用GreatSQL部署MGR集群，并完成添加新节点 、滚动升级、切主][GreatSQL_MGR_]
 *  [《叶问》33期，MGR最佳配置参考，PFS里的监测指标要全开吗，mysqld进程占用内存过高怎么排查][33_MGR_PFS_mysqld]

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

扫码加入GreatSQL/MGR交流**QQ群**

![9cdbaca4d8188f729793316b8327d121.png][]

点击文末“**阅读原文**”直达老叶专栏

[40cce99792191f3e07e5f1a2f56ff349.png]: /images/20220828/b77cc94270874aaabd8620bd0f30587d.png
[a045cc2c03c5fec5449760f598071312.png]: /images/20220828/fe6aa731f5e04722b8a2f0102871d83d.png
[eedb97fd825b0bd50e55752b5a534bfa.png]: /images/20220828/2a2d25335ad943f38407aa69a11f31be.png
[506940d4c88a83af4069d0e4f23f996f.png]: /images/20220828/9e1899f84e41497ca8d195969cf23f6f.png
[558f332a66f388deac71c26163166829.png]: /images/20220828/40d7c96cab7641749eae6b3f3d787011.png
[c543f5939cbb16efd8a60a97b66166c6.png]: /images/20220828/5bc29e8305934118ac7e2400ddd99f72.png
[MGR_MGR Best Practice]: http://mp.weixin.qq.com/s?__biz=MzkzMTIzMDgwMg==&mid=2247484174&idx=1&sn=4f795e889e3778876b09143c4ffdc770&chksm=c26f6633f518ef25f19309e4b01ce5d4527d945a2a030b66a9d9bbaa598cb668eca30cb91a0f&scene=21#wechat_redirect
[MGR Bug]: http://mp.weixin.qq.com/s?__biz=MzkzMTIzMDgwMg==&mid=2247483798&idx=1&sn=21fc1b1797074e002e1c684263b731e8&chksm=c26f64abf518edbdb278f1c21e98e39dc73b78397d01c2d583450a1da6d72a4ff32a0ec6db9c&scene=21#wechat_redirect
[36_MySQL_128_CPU]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653936499&idx=1&sn=f2efa2f3ae920b74039b6a9afa820238&chksm=bd3b41198a4cc80f18e90a0ad1b5550efbf20791e9512cd6c43dec9a0689da0d54fab5754749&scene=21#wechat_redirect
[GreatSQL_InnoDB]: https://blog.csdn.net/n88Lpo/article/details/120386624
[2021.8.8_MGR]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653936226&idx=1&sn=023012d2d287607c8fc8813c42b7ec73&chksm=bd3b40088a4cc91eff534623ce915f646e5dc3a904ae79e5df5ff303b59390e36e39268344ef&scene=21#wechat_redirect
[MGR]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653935994&idx=1&sn=91c596c81944aabd396d33f4924aac26&chksm=bd3b43108a4cca06841d2adf3a63dfe6af99df09b09164e8676d99cb08fc5e569bfc543a0ccf&scene=21#wechat_redirect
[InnoDB Cluster_GreatSQL_MGR]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653935962&idx=1&sn=38f2acfb147f16ae8daaa6a48a3102ae&chksm=bd3b43308a4cca26c2db9912a9ce5e2d73df1a10e7415f83538742b92e5475a0b11b88e39f4c&scene=21#wechat_redirect
[GreatSQL_MGR_]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653935951&idx=1&sn=ec8794371cd6f7677a5355b7e7ac39ea&chksm=bd3b43258a4cca336b0260c6b9cfc363f10596c775bdd0408d89dca855f3c7949911aa50ec8f&scene=21#wechat_redirect
[33_MGR_PFS_mysqld]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=2653935873&idx=1&sn=7511853d63145b7d72ae533586b6ad81&chksm=bd3b436b8a4cca7ddf138db1d2342aa19f0452bff97beb2f5015cf0eb377a7d2a6899d1c3c05&scene=21#wechat_redirect
[9cdbaca4d8188f729793316b8327d121.png]: /images/20220828/851fe9c65e2e46d28295e8587340eb77.png