关于MySQL分区表的一个性能BUG

目录
  • 二、使用pt-pmap进行栈分析
  • 三、关于本列中瓶颈点的分析
  • 四、分区表中多次建立template的情况
  • 五、关于一个特殊的流程
  • 六、问题模拟
  • 七、总结

一、问题描述

最近遇到一个问题,也就是使用分区表进行数据查询/加载的时候比普通表的性能下降了约50%,主要瓶颈出现在CPU,既然是CPU瓶颈理所当然的我们可以采集perf top -a -gpstack来寻找性能瓶颈所在,同时和普通表进行对比,发现CPU主要耗在函数build_template_field上如下图:

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

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

格式化后我们提出掉空闲的等待栈,发现大量的如上,这也和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)

这样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中还有一个栈如下:

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

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

栈:

这里就是因为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 观察如下:

这样问题就得到了确认。

七、总结

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

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

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

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

到此这篇关于讲诉MySQL分区表的一个性能BUG的文章就介绍到这了,更多相关MySQL分区表的一个性能BUG内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 解决mysql删除用户 bug的问题

    作者在使用mysql添加用户的时候,发现用户名给我写错了 强迫症的我,必须要改过来,但是发现删除用户的时候,命令是成功的 但是在此创建同名用户的时候,会报错,网上查了很多,说这是mysql的官方bug,不知道是作者水平不够没能理解到那个bug,还是真的解决了这个问题,下面和大家分享,欢迎大家一起讨论 删除了user的用户之后 无法再次创造相同的用户名 在mysql 数据库中有一张user表,可以查询到所有的用户和用户信息,删除里面的用户信息以及你赋予那个用户的什么权限就能完全的删除那个用户了 解

  • Mysql临时表及分区表区别详解

    临时表与内存表 内存表,指的是使用Memory引擎的表,建表语法是create table - engine=memory.这种 表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在.除了这两个特性看 上去比较"奇怪"外,从其他的特征上看,它就是一个正常的表 临时表,可以使用各种引擎类型 .如果是使用InnoDB引擎或者MyISAM引擎的临时表,写 数据的时候是写到磁盘上的.当然,临时表也可以使用Memory引擎. 临时表特性 建表语法是create temporary ta

  • MySQL分区表的基本入门教程

    前言 在最近的项目中,我们需要保存大量的数据,而且这些数据是有有效期的,为了提供查询效率以及快速删除过期数据,我们选择了MySQL的分区机制.把数据按照时间进行分区. 分区类型 Range分区:最为常用,基于属于一个给定连续区间的列值,把多行分配给分区.最常见的是基于时间字段. 基于分区的列最好是整型,如果日期型的可以使用函数转换为整型. List分区:LIST分区和RANGE分区类似,区别在于LIST是枚举值列表的集合,RANGE是连续的区间值的集合. Hash分区:基于给定的分区个数,将数据

  • MySQL对window函数执行sum函数可能出现的一个Bug

    使用MySql的窗口函数统计数据时,发现一个小的问题,与大家一起探讨下. 环境配置: mysql-installer-community-8.0.20.0 问题点:在sum对window函数执行时,如果有重复数据,会直接把相同的数据相加,并不是逐步相加. 问题描述 数据:在一个成绩表中,有三个个字段:学生s_id,课程c_id,成绩s_score. 查询条件查询每个课程的学生成绩排名和成绩汇总. 查询结果:发现如果同一个课程有相同成绩是,汇总成绩不是累加的,而是一次全部加上去. 创建数据表 CR

  • MySQL最佳实践之分区表基本类型

    MySQL分区表概述 随着MySQL越来越流行,Mysql里面的保存的数据也越来越大.在日常的工作中,我们经常遇到一张表里面保存了上亿甚至过十亿的记录.这些表里面保存了大量的历史记录. 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里.所以只能是启用一个或多个带where条件的delete语句去删除(一般where条件是时间). 这对数据库的造成了很大压力.即使我们把这些删除了,但底层的数据文件并没有变小.面对这类问题,最有效的方法就是在使用分区表.最常见的分区方法就是按

  • CentOS 安装 PHP5.5+Redis+XDebug+Nginx+MySQL全纪录

    启动ssh服务 service sshd start yum -y update 查看centos版本 centos 5 执行: 复制代码 代码如下: rpm -Uvh http://mirror.webtatic.com/yum/el5/latest.rpm centos 6 执行: 复制代码 代码如下: rpm -Uvh http://mirror.webtatic.com/yum/el6/latest.rpm yum安装php 复制代码 代码如下: yum install php55w 

  • MySQL关于exists的一个bug

    今天碰到一个关于exists很奇怪的问题 第一个语句如下: SELECT count(1) FROM APPLY t WHERE EXISTS ( SELECT r.APPLY_ID FROM RECORD r WHERE t.APPLY_ID = r.APPLY_ID ); 产生的结果是:89584 第二个语句如下: SELECT count(1) FROM APPLY t WHERE EXISTS ( SELECT max(r.FINISH_TIME) FROM RECORD r WHERE

  • MySQL优化之分区表

    当数据库数据量涨到一定数量时,性能就成为我们不能不关注的问题,如何优化呢? 常用的方式不外乎那么几种: 1.分表,即把一个很大的表达数据分到几个表中,这样每个表数据都不多. 优点:提高并发量,减小锁的粒度 缺点:代码维护成本高,相关sql都需要改动 2.分区,所有的数据还在一个表中,但物理存储数据根据一定的规则存放在不同的文件中,文件也可以放到另外磁盘上 优点:代码维护量小,基本不用改动,提高IO吞吐量 缺点:表的并发程度没有增加 3.拆分业务,这个本质还是分表. 优点:长期支持更好 缺点:代码

  • 关于MySQL分区表的一个性能BUG

    目录 二.使用pt-pmap进行栈分析 三.关于本列中瓶颈点的分析 四.分区表中多次建立template的情况 五.关于一个特殊的流程 六.问题模拟 七.总结 一.问题描述 最近遇到一个问题,也就是使用分区表进行数据查询/加载的时候比普通表的性能下降了约50%,主要瓶颈出现在CPU,既然是CPU瓶颈理所当然的我们可以采集perf top -a -g和pstack来寻找性能瓶颈所在,同时和普通表进行对比,发现CPU主要耗在函数build_template_field上如下图: 二.使用pt-pma

  • Mysql分区表的管理与维护

    改变一个表的分区方案只需使用alter table 加 partition_options 子句就可以了.和创建分区表时的create table语句很像. 创建表 CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THA

  • MySQL分区表的最佳实践指南

    前言: 分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表.但是对于应用程序来讲,分区的表和没有分区的表是一样的.换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理.本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助. 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据.在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这

  • 详解MySQL分区表

    前言: 分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表.但是对于应用程序来讲,分区的表和没有分区的表是一样的.换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理.本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助. 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据.在执行查询的时候,优化器根据分区定义过滤那些没有我们需要的数据的分区,这

  • MySQL分区表实现按月份归类

    目录 建表 查看数据库文件: 插入 查询 删除 补充:Mysql自动按月表分区 MySQL单表数据量,建议不要超过2000W行,否则会对性能有较大影响.最近接手了一个项目,单表数据超7000W行,一条简单的查询语句等了50多分钟都没出结果,实在是难受,最终,我们决定用分区表. 建表 一般的表(innodb)创建后只有一个 idb 文件: create table normal_table(id int primary key, no int) 查看数据库文件: normal_table.ibd

  • MySQL 分区表中分区键为什么必须是主键的一部分

    目录 水平拆分VS垂直拆分 分区表 MySQL8.0中分区表的变化 为什么分区键必须是主键的一部分? 本地分区索引VS全局索引 总结 前言: 分区是一种表的设计模式,通俗地讲表分区是将一大表,根据条件分割成若干个小表.但是对于应用程序来讲,分区的表和没有分区的表是一样的.换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理 随着业务的不断发展,数据库中的数据会越来越多,相应地,单表的数据量也会越到越大,大到一个临界值,单表的查询性能就会下降. 这个临界值,并不能一概而论,它与硬件能力.

  • 利用MySQL系统数据库做性能负载诊断的方法

    某大师曾说过,像了解自己的老婆 一样了解自己管理的数据库,个人认为包含了两个方面的了解: 1,在稳定性层面来说,更多的是关注高可用.读写分离.负载均衡,灾备管理等等high level层面的措施(就好比要保证生活的稳定性) 2,在实例级别的来说,需要关注内存.IO.网络,热点表,热点索引,top sql,死锁,阻塞,历史上执行异常的SQL(好比生活品质细节)MySQL的performance_data库和sys库提供了非常丰富的系统日志数据,可以帮助我们更好地了解非常细节的,这里简单地列举出来了

  • MySQL分区表的正确使用方法

    MySQL分区表概述 我们经常遇到一张表里面保存了上亿甚至过十亿的记录,这些表里面保存了大量的历史记录. 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里.所以只能是启用一个或多个带where条件的delete语句去删除(一般where条件是时间). 这对数据库的造成了很大压力.即使我们把这些删除了,但底层的数据文件并没有变小.面对这类问题,最有效的方法就是在使用分区表.最常见的分区方法就是按照时间进行分区. 分区一个最大的优点就是可以非常高效的进行历史数据的清理. 1.

  • 聊聊MySQL的COUNT(*)的性能

    前言 基本职场上的程序员用来统计数据库表的行数都会使用count(*),count(1)或者count(主键),那么它们之间的区别和性能你又是否了解呢? 其实程序员在开发的过程中,在一张大表上统计总行数是非常耗时的一个操作,那么我们应该用哪个方法统计会更快呢? 接下来我们就来聊一聊MySQL中统计总行数的方法和性能. count(*),count(1),count(主键)哪个更快? 1.建表并且插入1000万条数据进行实验测试: # 创建测试表 CREATE TABLE `t6` ( `id`

随机推荐