一、问题描述
最近遇到一个问题,也就是使用分区表进行数据查询/加载的时候比普通表的性能下降了约50%,主要瓶颈出现在CPU,既然是CPU瓶颈理所当然的我们可以采集perf top -a -g和pstack来寻找性能瓶颈所在,同时和普通表进行对比,发现CPU主要耗在函数build_template_field上如下图:
二、使用pt-pmap进行栈分析
为了和perf top -g -a进行相互印证,我们同时获取了当时的pstack,由于线程较多为了方便获取有用的信息我们通过pt-pmap进行了格式化如下:
格式化后我们提出掉空闲的等待栈,发现大量的如上,这也和perf top -a -g中的表现进行了相互印证。
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)
这样template需要每个分区(scan next partition)都进行重建,这样就出现了我们上面的问题。这个其实也可以理解,新的分区是新的innodb文件,这样上次定位的持久化游标实际已经没有什么用了,就相当于一次新的表访问。这里在是否进行template建立还有一个判断如下:
if (m_prebuilt->sql_stat_start) {
build_template(false);
}
这个栈实际并不完整,但是其中出现了Partition_helper::handle_ordered_index_scan,这个函数实际上和分区表的排序有关,如果我们考虑这样一种情况,对于二级索引select max(id2) from t,那么需要首先访问每个分区获取其中的最大值然后对比每个分区的最大值,得到最终的结果,而MySQL则采用优先队列进行处理,这应该是就是本函数完成的部分功能(没仔细去看)。其次我们先出现了QUICK_RANGE_SELECT这是范围查询会用到的,那么我们构造如下:
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