标签归档:MySQL

tpcc-mysql安装、使用、结果解读

TPC-C是专门针对联机交易处理系统(OLTP系统)的规范,一般情况下我们也把这类系统称为业务处理系统。
tpcc-mysql是percona基于TPC-C(下面简写成TPCC)衍生出来的产品,专用于MySQL基准测试。其源码放在launchpad上,用bazaar管理,项目地址:https://code.launchpad.net/~percona-dev/perconatools/tpcc-mysql

一、 下载源码包
安装epel包后以便安装bzr客户端:

rpm -Uvh http://dl.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm

然后就可以开始安装bzr客户端了:

yum install bzr

之后,就可以开始用bzr客户端下载tpcc-mysql源码了。

cd /tmp
bzr branch lp:~percona-dev/perconatools/tpcc-mysql

MySQL中文网便捷下载地址:

http://imysql.com/wp-content/uploads/2014/09/tpcc-mysql-src.tgz

下载到本地后,先执行 gunzip 解压缩文件,再执行 tar xf 解包,直接 tar zxf 可能会报告异常。

tpcc-mysql的业务逻辑及其相关的几个表作用如下:

New-Order:新订单,主要对应 new_orders 表
Payment:支付,主要对应 orders、history 表
Order-Status:订单状态,主要对应 orders、order_line 表
Delivery:发货,主要对应 order_line 表
Stock-Level:库存,主要对应 stock 表

其他相关表:
客户:主要对应 customer 表
地区:主要对应 district 表
商品:主要对应 item 表
仓库:主要对应 warehouse 表

二、编译安装
编译非常简单,只需要一个 make 即可。

cd /tmp/tpcc-mysql/src
make
如果 make 没有报错,就会在 /tmp/tpcc-mysql 下生成 tpcc 二进制命令行工具 tpcc_load 、 tpcc_start

三、TPCC测试前准备
初始化测试库环境

cd /tmp/tpcc-mysql
mysqladmin create tpcc1000
mysql -f tpcc1000 < create_table.sql

初始化完毕后,就可以开始加载测试数据了

tpcc_load用法如下:
tpcc_load [server] [DB] [user] [pass] [warehouse]
或者
tpcc_load [server] [DB] [user] [pass] [warehouse] [part] [min_wh] [max_wh]

选项 warehouse 意为指定测试库下的仓库数量。

真实测试场景中,仓库数一般不建议少于100个,视服务器硬件配置而定,如果是配备了SSD或者PCIE SSD这种高IOPS设备的话,建议最少不低于1000个

执行下面的命令,开始灌入测试数据:

cd /tmp/tpcc-mysql
./tpcc_load localhost tpcc1000 tpcc_user "tpcc_password" 1000

在这里,需要注意的是 tpcc 默认会读取 /var/lib/mysql/mysql.sock 这个socket 文件。
因此,如果你的 socket 文件不在相应路径的话,可以做个软连接,或者通过TCP/IP的方式连接测试服务器,例如:

cd /tmp/tpcc-mysql
./tpcc_load 1.2.3.4:3306 tpcc1000 tpcc_user "tpcc_password" 1000

加载测试数据时长视仓库数量而定,若过程比较久需要稍加耐心等待。

四、进行TPCC测试
tpcc_start 工具用于tpcc压测,其用法如下:

tpcc_start -h server_host -P port -d database_name -u mysql_user \
 -p mysql_password -w warehouses -c connections -r warmup_time \
 -l running_time -i report_interval -f report_file

几个选项稍微解释下

-w 指定仓库数量
-c 指定并发连接数
-r 指定开始测试前进行warmup的时间,进行预热后,测试效果更好
-l 指定测试持续时间
-i  指定生成报告间隔时长
-f 指定生成的报告文件名

现在我们来开启一个测试案例:

tpcc_start -hlocalhost -d tpcc1000 -u tpcc_user -p "tpcc_password" \
 -w 1000 -c 32 -r 120 -l 3600 \
 -f tpcc_mysql_20140921.log >> tpcc_caseX_20140921.log 2>&1

即:模拟 1000个仓库规模,并发 16个线程进行测试,热身时间为 60秒, 压测时间为 1小时。

真实测试场景中,建议预热时间不小于5分钟,持续压测时长不小于30分钟,否则测试数据可能不具参考意义。

五、TPCC测试结果解读:

发起测试:

./tpcc_start -h 1.2.3.4 -P 3306 -d tpcc10 -u tpcc -p tpcc \
 -w 10 -c 64 -r 30 -l 120 \
 -f tpcclog_201409211538_64_THREADS.log >> tpcc_noaid_2_20140921_64.log 2>&1

测试结果输出如下:

-- 本轮tpcc压测的一些基本信息
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '1.2.3.4'   -- 主机
option P with value '3306'             -- 端口
option d with value 'tpcc10'         -- 数据库
option u with value 'tpcc'             -- 账号
option p with value 'tpcc'             -- 密码
option w with value '10'                 -- 仓库数
option c with value '64'                 -- 并发线程数
option r with value '30'                 -- 数据预热时长
option l with value '120'               -- 压测时长
option f with value 'tpcclog_20140921_64_THREADS.res'  -- 输出报告日志文件

     [server]: 1.2.3.4
     [port]: 3306
     [DBname]: tpcc10
       [user]: tpcc
       [pass]: tpcc
  [warehouse]: 10
 [connection]: 64
     [rampup]: 30 (sec.)
    [measure]: 120 (sec.)

RAMP-UP TIME.(30 sec.)

-- 预热结束,开始进行压测
MEASURING START.

-- 每10秒钟输出一次压测数据
  10, 8376(0):2.744|3.211, 8374(0):0.523|1.626, 838(0):0.250|0.305, 837(0):3.241|3.518, 839(0):9.086|10.676
  20, 8294(0):2.175|2.327, 8292(0):0.420|0.495, 829(0):0.206|0.243, 827(0):2.489|2.593, 827(0):7.214|7.646
…
 110, 8800(0):2.149|2.458, 8792(0):0.424|0.710, 879(0):0.207|0.244, 878(0):2.461|2.556, 878(0):7.042|7.341
 120, 8819(0):2.147|2.327, 8820(0):0.424|0.568, 882(0):0.208|0.237, 881(0):2.483|2.561, 883(0):7.025|7.405
-- 以逗号分隔,共6列
-- 第一列,第N次10秒
-- 第二列,总成功执行压测的次数(总推迟执行压测的次数):90%事务的响应时间|本轮测试最大响应时间
-- 第三列,新订单业务成功执行次数(推迟执行次数):90%事务的响应时间|本轮测试最大响应时间
-- 第四列,支付业务的结果,后面几个的意义同上
-- 第五列,发货业务的结果,后面几个的意义同上
-- 第六列,库存业务的结果,后面几个的意义同上

-- 压测结束
STOPPING THREADS................................................................

   -- 第一次粗略结果统计
  [0] sc:100589  lt:0  rt:0  fl:0    -- New-Order,新订单业务成功(success,简写sc)次数,延迟(late,简写lt)次数,重试(retry,简写rt)次数,失败(failure,简写fl)次数
  [1] sc:100552  lt:0  rt:0  fl:0    -- Payment,支付业务统计,其他同上
  [2] sc:10059  lt:0  rt:0  fl:0    -- Order-Status,订单状态业务统计,其他同上
  [3] sc:10057  lt:0  rt:0  fl:0    -- Delivery,发货业务统计,其他同上
  [4] sc:10058  lt:0  rt:0  fl:0    -- Stock-Level,库存业务统计,其他同上
 in 120 sec.

    -- 第二次粗略统计结果,其他同上
  [0] sc:100590  lt:0  rt:0  fl:0 
  [1] sc:100582  lt:0  rt:0  fl:0 
  [2] sc:10059  lt:0  rt:0  fl:0 
  [3] sc:10057  lt:0  rt:0  fl:0 
  [4] sc:10059  lt:0  rt:0  fl:0 

 (all must be [OK])       -- 下面所有业务逻辑结果都必须为 OK 才行
 [transaction percentage]
        Payment: 43.47% (>=43.0%) [OK]      -- 支付成功次数(上述统计结果中 sc + lt)必须大于43.0%,否则结果为NG,而不是OK
   Order-Status: 4.35% (>= 4.0%) [OK]       -- 订单状态,其他同上
       Delivery: 4.35% (>= 4.0%) [OK]       -- 发货,其他同上
    Stock-Level: 4.35% (>= 4.0%) [OK]       -- 库存,其他同上
 [response time (at least 90% passed)]      -- 响应耗时指标必须超过90%通过才行
      New-Order: 100.00%  [OK]              -- 下面几个响应耗时指标全部 100% 通过
        Payment: 100.00%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]


                 50294.500 TpmC                      -- TpmC结果值

script目录下的一些脚本主要是一些性能数据采集以及分析的,可以自行摸索下怎么用。

其他推荐:
TPCC-MySQL使用手册

搜狐视频:MySQL DBA成长之路 – tpcc-mysql安装、使用、结果解读

[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率

首先,介绍下关于InnoDB引擎存储格式的几个要点:
1、InnoDB可以选择使用共享表空间或者是独立表空间方式,建议使用独立表空间,便于管理、维护。启用 innodb_file_per_table 选项,5.5以后可以在线动态修改生效,并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间,早于5.5的版本,修改完这个选项后,需要重启才能生效;
2、InnoDB的data page默认16KB,5.6版本以后,新增选项 innodb_page_size 可以修改,在5.6以前的版本,只能修改源码重新编译,但并不推荐修改这个配置,除非你非常清楚它有什么优缺点;
3、InnoDB的data page在有新数据写入时,会预留1/16的空间,预留出来的空间可用于后续的新纪录写入,减少频繁的新增data page的开销;
4、每个data page,至少需要存储2行记录。因此理论上行记录最大长度为8KB,但事实上应该更小,因为还有一些InnoDB内部数据结构要存储;
5、受限于InnoDB存储方式,如果数据是顺序写入的话,最理想的情况下,data page的填充率是15/16,但一般没办法保证完全的顺序写入,因此,data page的填充率一般是1/2到15/16。因此每个InnoDB表都最好要有一个自增列作为主键,使得新纪录写入尽可能是顺序的;
6、当data page填充率不足1/2时,InnoDB会进行收缩,释放空闲空间;
7、MySQL 5.6版本的InnoDB引擎当前支持COMPACT、REDUNDANT、DYNAMIC、COMPRESSED四种格式,默认是COMPACT格式,COMPRESSED用的很少且不推荐(见下一条),如果需要用到压缩特性的话,可以直接考虑TokuDB引擎;
8、COMPACT行格式相比REDUNDANT,大概能节省20%的存储空间,COMPRESSED相比COMPACT大概能节省50%的存储空间,但会导致TPS下降了90%。因此强烈不推荐使用COMPRESSED行格式
9、当行格式为DYNAMIC或COMPRESSED时,TEXT/BLOB之类的长列(long column,也有可能是其他较长的列,不一定只有TEXT/BLOB类型,看具体情况)会完全存储在一个独立的data page里,聚集索引页中只使用20字节的指针指向新的page,这就是所谓的off-page,类似ORACLE的行迁移,磁盘空间浪费较严重,且I/O性能也较差。因此,强烈不建议使用BLOB、TEXT、超过255长度的VARCHAR列类型
10、当InnoDB的文件格式(innodb_file_format)设置为Antelope,并且行格式为COMPACT 或 REDUNDANT 时,BLOB、TEXT或者长VARCHAR列只会将其前768字节存储在聚集索页中(最大768字节的作用是便于创建前缀索引/prefix index),其余更多的内容存储在额外的page里,哪怕只是多了一个字节。因此,所有列长度越短越好
11、在off-page中存储的BLOB、TEXT或者长VARCHAR列的page是独享的,不能共享。因此强烈不建议在一个表中使用多个长列

综上,如果在实际业务中,确实需要在InnoDB表中存储BLOB、TEXT、长VARCHAR列时,有下面几点建议:
1、尽可能将所有数据序列化、压缩之后,存储在同一个列里,避免发生多次off-page;
2、实际最大存储长度低于255的列,转成VARCHAR或者CHAR类型(如果是变长数据二者没区别,如果是定长数据,则使用CHAR类型);
3、如果无法将所有列整合到一个列,可以退而求其次,根据每个列最大长度进行排列组合后拆分成多个子表,尽量是的每个子表的总行长度小于8KB,减少发生off-page的频率;
4、上述建议是在data page为默认的16KB前提下,如果修改成8KB或者其他大小,请自行根据上述理论进行测试,找到最合适的值;
5、字符型列长度小于255时,无论采用CHAR还是VARCHAR来存储,或者把VARCHAR列长度定义为255,都不会导致实际表空间增大;
6、一般在游戏领域会用到比较多的BLOB列类型,游戏界同行可以关注下。

下面是测试验证过程,有耐心的同学可以慢慢看:

#
# 测试案例:InnoDB中长列存储效率
# 测试场景描述:
# 在InnoDB表中存储64KB的数据,对比各种不同存储方式# 每个表写入5000行记录,观察最后表空间文件大小对比
#

#表0:所有数据存储在一个BLOB列中
CREATE TABLE `t_longcol_0` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol` blob NOT NULL COMMENT 'store all data in a blob column',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

#相应的数据写入存储过程:mysp_longcol_0_ins()
CREATE PROCEDURE `mysp_longcol_0_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_0(longcol) select repeat('a',65535);
set @i = @i + 1;
end while;
end;

#表1:将64KB字节平均存储在9个列中
CREATE TABLE `t_longcol_1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` blob NOT NULL COMMENT 'store all data in 9 blob columns',
`longcol2` blob NOT NULL,
`longcol3` blob NOT NULL,
`longcol4` blob NOT NULL,
`longcol5` blob NOT NULL,
`longcol6` blob NOT NULL,
`longcol7` blob NOT NULL,
`longcol8` blob NOT NULL,
`longcol9` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_1_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_1(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9) select
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',5535);
set @i = @i + 1;
end while;
end;

#表2:将64KB数据离散存储在多个BLOB列中
CREATE TABLE `t_longcol_2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol2` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol3` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol4` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol5` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol6` blob NOT NULL COMMENT 'store 255 bytes data',
`longcol7` blob NOT NULL COMMENT 'store 368 bytes data',
`longcol8` blob NOT NULL COMMENT 'store 496 bytes data',
`longcol9` blob NOT NULL COMMENT 'store 512 bytes data',
`longcol10` blob NOT NULL COMMENT 'store 640 bytes data',
`longcol11` blob NOT NULL COMMENT 'store 768 bytes data',
`longcol12` blob NOT NULL COMMENT 'store 912 bytes data',
`longcol13` blob NOT NULL COMMENT 'store 1024 bytes data',
`longcol14` blob NOT NULL COMMENT 'store 2048 bytes data',
`longcol15` blob NOT NULL COMMENT 'store 3082 bytes data',
`longcol16` blob NOT NULL COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` blob NOT NULL COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_1_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_2(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#表3:将64KB数据离散存储在多个CHAR、VARCHAR、BLOB列中
CREATE TABLE `t_longcol_3` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_3_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_3(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#表4:将64KB数据离散存储在多个VARCHAR、BLOB列中,对比t_longcol_3中几个列是CHAR的情况
CREATE TABLE `t_longcol_4` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_4_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_4(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#表5:将64KB数据离散存储在多个VARCHAR、BLOB列中,和t_longcol_4相比,变化在于前面的几个列长度改成了255,但实际存储长度还是100字节
CREATE TABLE `t_longcol_5` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_5_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_5(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#从下面开始,参考第3条建议进行分表,每个表所有列长度总和
#分表1,行最大长度 100 + 100 + 100 + 100 + 100 + 255 + 368 + 496 + 512 + 640 + 768 + 912 + 3082 = 7533 字节
CREATE TABLE `t_longcol_51` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表2,行最大长度 1024 + 2048 + 4096 = 7168 字节
CREATE TABLE `t_longcol_52` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表3,行最大长度 8192 字节
CREATE TABLE `t_longcol_53` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表4,行最大长度 16284 + 20380 = 36664 字节
CREATE TABLE `t_longcol_54` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表5,行最大长度 5977 + 4 = 5981 字节
CREATE TABLE `t_longcol_55` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程:mysp_longcol_51_ins()
CREATE PROCEDURE `mysp_longcol_51_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_51(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol15) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',3082);

insert into t_longcol_52(longcol13,longcol14,longcol16) select
repeat('a',1024),
repeat('a',2048),
repeat('a',4096);

insert into t_longcol_53(longcol17) select repeat('a',8192);

insert into t_longcol_54(longcol18,longcol19) select
repeat('a',16284),
repeat('a',20380);

insert into t_longcol_55(longcol20) select repeat('a',5977);

set @i = @i + 1;
end while;
end;

上述各个测试表都写入5000行记录后,再来对比下其表空间文件大小,以及重整表空间后的大小,观察碎片率。详细对比见下:
mysql-optimization-case-blob-stored-in-innodb-optimization

最后一种分表方式中,5个子表的表空间文件大小总和是 40960 + 40960 + 98304 + 286720 + 40960 = 507904 字节。
可以看到,这种方式的总大小和原始表大小差距最小,其他几种存储方式都比这个来的大。

[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响

本次,我们来看看索引、提交频率对InnoDB表写入速度的影响,了解有哪些需要注意的。

先直接说几个结论吧:

1、关于索引对写入速度的影响:
a、如果有自增列做主键,相对完全没索引的情况,写入速度约提升 3.11%;
b、如果有自增列做主键,并且二级索引,相对完全没索引的情况,写入速度约降低 27.37%;

因此,InnoDB表最好总是有一个自增列做主键。

2、关于提交频率对写入速度的影响(以表中只有自增列做主键的场景,一次写入数据30万行数据为例):

a、等待全部数据写入完成后,最后再执行commit提交的效率最高;
b、每10万行提交一次,相对一次性提交,约慢了1.17%;
c、每1万行提交一次,相对一次性提交,约慢了3.01%;
d、每1千行提交一次,相对一次性提交,约慢了23.38%;
e、每100行提交一次,相对一次性提交,约慢了24.44%;
f、每10行提交一次,相对一次性提交,约慢了92.78%;
g、每行提交一次,相对一次性提交,约慢了546.78%,也就是慢了5倍;

因此,最好是等待所有事务结束后再批量提交,而不是每执行完一个SQL就提交一次。
曾经有一次对比测试mysqldump启用extended-insert和未启用导出的SQL脚本,后者比前者慢了不止5倍。

下面是详细的测试案例过程,有兴趣的同学可以看看:

DROP TABLE IF EXISTS `mytab`;
CREATE TABLE `mytab` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL DEFAULT ‘0’,
`c2` int(11) NOT NULL DEFAULT ‘0’,
`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c4` varchar(200) NOT NULL DEFAULT ”,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DELIMITER $$$
DROP PROCEDURE IF EXISTS `insert_mytab`;

CREATE PROCEDURE `insert_mytab`(in rownum int, in commitrate int)
BEGIN
DECLARE i INT DEFAULT 0;

SET AUTOCOMMIT = 0;

WHILE i < rownum DO INSERT INTO mytab(c1, c2, c3,c4) VALUES( FLOOR(RAND()*rownum),FLOOR(RAND()*rownum),NOW(), REPEAT(CHAR(ROUND(RAND()*255)),200)); SET i = i+1; /* 达到每 COMMITRATE 频率时提交一次 */ IF (commitrate > 0) AND (i % commitrate = 0) THEN
COMMIT;
SELECT CONCAT(‘commitrate: ‘, commitrate, ‘ in ‘, I);
END IF;

END WHILE;

/* 最终再提交一次,确保成功 */
COMMIT;
SELECT ‘ALL COMMIT;’;

END; $$$

#测试调用
call insert_mytab(300000, 1); — 每次一提交
call insert_mytab(300000, 10); — 每10次一提交
call insert_mytab(300000, 100); — 每100次一提交
call insert_mytab(300000, 1000); — 每1千次一提交
call insert_mytab(300000, 10000); — 每1万次提交
call insert_mytab(300000, 100000); — 每10万次一提交
call insert_mytab(300000, 0); — 一次性提交

测试耗时结果对比:
mysql-optimization-case-how-index-and-commit-rate-affect-innodb-insert

[MySQL FAQ]系列 — SAVEPOINT语法错误一例

前几天帮同事解决一个案例,在主从复制环境下,从库上的MySQL版本号是5.5.5,遇到下面的错误:

#其他非相关信息我都隐藏掉了
 [(yejr@imysql.com)]> show slave status \G;
 Slave_IO_Running: Yes
 Slave_SQL_Running: No
 Last_Errno: 1064
 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6e86db84_14847168f19__8000' at line 1' on query. Default database: 'act'. Query: 'SAVEPOINT 6e86db84_14847168f19__8000'
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 1064
 Last_SQL_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '6e86db84_14847168f19__8000' at line 1' on query. Default database: 'act_log'. Query: 'SAVEPOINT 6e86db84_14847168f19__8000'

第一感觉是遇到保留关键字了,不过看到这么长的字符串,不应该是保留关键字才对。
经过尝试,最后发现是字符串中的 “e” 这个字符如果存在就可能会报错,看起来应该是bug才对了。
在MySQL的bug系统里确实找到了这个bug,不过看bug描述,在5.5版本中应该是已经修复了才对,看来太不靠谱了呀~~
关于这个bug:Savepoint identifier is occasionally considered as floating point numbers

其实除了升级版本外,解决方法也很简单,把savepoint后面的 identifier 字符串用反引号(波浪号的下档键,英文叫做 backticks 键)引用起来就行。
例如:

savepoint `6e86db84_14847168f19__8000`;

这样就可以了。

这个案例也提示我们,在写SQL时,涉及到数据库、表、字段、identifier 等名称时,最好是都能用反引号引用,确保可用。
曾经看到线上数据表有个字段名是 check ,这个名字在MySQL里很早就已经是保留关键字,幸好开发同学比较靠谱,都加上了反引号。

关于savepoint的2个bug:
Savepoint Identifier should be enclosed with backticks
Savepoint identifier is occasionally considered as floating point numbers

[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键

我们先了解下InnoDB引擎表的一些关键特征:

  • InnoDB引擎表是基于B+树的索引组织表(IOT);
  • 每个表都需要有一个聚集索引(clustered index);
  • 所有的行记录都存储在B+树的叶子节点(leaf pages of the tree);
  • 基于聚集索引的增、删、改、查的效率相对是最高的;
  • 如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择其作为聚集索引;
  • 如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引;
  • 如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。

综上总结,如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话,这时候存取效率是最高的,也就是下面这几种情况的存取效率最高:

  • 使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的,和B+数叶子节点分裂顺序一致;
  • 该表不指定自增列做主键,同时也没有可以被选为主键的唯一索引(上面的条件),这时候InnoDB会选择内置的ROWID作为主键,写入顺序和ROWID增长顺序一致;
  • 除此以外,如果一个InnoDB表又没有显示主键,又有可以被选择为主键的唯一索引,但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况),该表的存取效率就会比较差。

实际情况是如何呢?经过简单TPCC基准测试,修改为使用自增列作为主键与原始表结构分别进行TPCC测试,前者的TpmC结果比后者高9%倍,足见使用自增列做InnoDB表主键的明显好处,其他更多不同场景下使用自增列的性能提升可以自行对比测试下。

附图:

1、B+树典型结构

B+tree

2、InnoDB主键逻辑结构

Innodb-primary-key

 

延伸阅读:

1、TPCC-MySQL使用手册

2、B+Tree index structures in InnoDB

3、B+Tree Indexes and InnoDB – Percona

4、MySQL官方手册: Clustered and Secondary Indexes

[MySQL FAQ]系列 — 线上环境到底要不要开启query cache

Query Cache(查询缓存,以下简称QC)存储SELECT语句及其产生的数据结果,特别适用于:频繁提交同一个语句,并且该表数据变化不是很频繁的场景,例如一些静态页面,或者页面中的某块不经常发生变化的信息。QC有可能会从InnoDB Buffer Pool或者MyISAM key buffer里读取结果。

由于QC需要缓存最新数据结果,因此表数据发生任何变化(INSERT、UPDATE、DELETE或其他可能产生数据变化的操作),都会导致QC被刷新。

根据MySQL官方的测试,QC的优劣分别是:

1、如果对一个表执行简单的查询,但每次查询都不一样的话,打开QC后,性能反而下降了13%左右。但通常实际业务中,通常不会只有这种请求,因此实际影响应该比这个小一些。

2、如果对一个只有一行数据的表进行查询,则可以提升238%,这个效果还是非常不错的。

因此,如果是在一个更新频率非常低而只读查询频率非常高的场景下,打开QC还是比较有优势的,其他场景下,则不建议使用。而且,QC一般也维持在100MB以内就够了,没必要设置超过数百MB。

QC严格要求2次SQL请求要完全一样,包括SQL语句,连接的数据库、协议版本、字符集等因素都会影响,下面几个例子中的SQL会被认为是完全不一样而不会使用同一个QC内存块:

mysql> set names latin1; SELECT * FROM table_name;
mysql> set names latin1; select * from table_name;
mysql> set names utf8; select * from table_name;

此外,QC也不适用于下面几个场景:

1、子查询或者外层查询;
2、存储过程、存储函数、触发器、event中调用的SQL,或者引用到这些结果的;
3、包含一些特殊函数时,例如:BENCHMARK()、CURDATE()、CURRENT_TIMESTAMP()、NOW()、RAND()、UUID()等等;
4、读取mysql、INFORMATION_SCHEMA、performance_schema 库数据的;
5、类似SELECT…LOCK IN SHARE MODE、SELECT…FOR UPDATE、SELECT..INTO OUTFILE/DUMPFILE、SELECT..WHRE…IS NULL等语句;
6、SELECT执行计划用到临时表(TEMPORARY TABLE);
7、未引用任何表的查询,例如 SELECT 1+1 这种;
8、产生了 warnings 的查询;
9、SELECT语句里加了 SQL_NO_CACHE 关键字;

更加奇葩的是,MySQL在从QC中取回结果前,会先判断执行SQL的用户是否有全部库、表的SELECT权限,如果没有,则也不会使用QC。

相比下面这个,其实上面所说的都不重要。

最为重要的是,在MySQL里QC是由一个全局锁在控制,每次更新QC的内存块都需要进行锁定。
例如,一次查询结果是20KB,当前 query_cache_min_res_unit 值设置为 4KB(默认值就是4KB,可调整),那么么本次查询结果共需要分为5次写入QC,每次都要锁定,可见其成本有多高。

我们可以通过 PROFILING 功能来查看 QC 相关的一些锁竞争,例如像下面这样的:

• Waiting for query cache lock
• Waiting on query cache mutex

或者,也可以通过执行 SHOW PROCESSLIST 来看线程的状态,例如:

• checking privileges on cached query
检查用户是否有权限读取QC中的结果集

• checking query cache for query
检查本次查询结果是否已经存储在QC中

• invalidating query cache entries
由于相关表数据已经修改了,因此将QC中的内存记录被标记为失效

• sending cached result to client
从QC中,将缓存后的结果返回给客户程序

• storing result in query cache
将查询结果缓存到QC中

如果可以频繁看到上述几种状态,那么说明当前QC基本存在比较重的竞争。

说了这么多废话,其实核心要点就一个:
如果线上环境中99%以上都是只读,很少有更新,再考虑开启QC吧,否则,就别开了。
关闭方法很简单,有两种:

1、同时设置选项 query_cache_type = 0 和 query_cache_size = 0;
2、如果用源码编译MySQL的话,编译时增加参数 --without-query-cache 即可;

延伸阅读:
http://www.dbasquare.com/kb/how-query-cache-can-cause-performance-problems/
http://www.percona.com/blog/2012/09/05/write-contentions-on-the-query-cache/
http://dev.mysql.com/doc/refman/5.6/en/query-cache.html

[MySQL FAQ]系列 — 打开general log到底影响多大

我们知道,有时候为了debug或跟踪方便,会临时打开MySQL的general log。如果在线业务请求比较频繁的话,会导致瞬间产生大量的日志,一定程度上会影响IOPS性能。

此外,我们还有一种变通的办法,那就是打开slow query log,然后设置 long_query_time = 0,这样也可以记录所有请求log,而且记录的log比general log还要来的小,他产生的IOPS性能影响可能会比直接打开general log的影响来的小,可事实果真如此吗?我们来对比测试下就知道了。

测试试用MySQL版本:5.5.5-10.0.11-MariaDB-log MariaDB Server
测试工具: tpcc-mysql
测试Warehouse数: 100
warmup time: 60s
run time: 600s
并发线程数: 512

测试结果对比见下:

mysql-faq-impact-of-general-log

在“一般场景”下,我是设置 long_query_time = 1,并且关闭general log。

记录全部general log时的TpmC大约是不打开log时的73.28%,而记录全部slow log时的TpmC大约是不打开log时的59.53%。可见,直接打开general log对TpmC的影响更小一些,而且这种模式下产生的log其实也更小一些。是不是有点毁三观,哈哈O(∩_∩)O~

此外,如果 log-output = TABLE 时结果会怎样,请读者自行测试 :)

备注:MySQL 5.1及以上版本,才支持将long_query_time设置为0秒,在这之前的版本,其最小值是1秒。

[MySQL FAQ]系列 — 不同的binlog_format会导致哪些SQL不会被记录

我们都知道binlog_format有三种可选配置:STATEMENT、ROW、MIXED,相应地,基于这三种模式的Replication分别称为SBR(STATEMENT BASED Replication)、RBR、MBR。 同时,我们也知道,MySQL Replication可以支持比较灵活的binlog规则,可以设置某些库、某些表记录或者忽略不记录。

通常地,我们强烈建议不要设置这些规则,默认都记录就好,在Slave上也是如此,默认所有库都进行Replicate,不要设置DO、IGNORE、REWRITE规则。 如果非要设置这些规则的话,可能会导致某些场景下或者某些特定的SQL无法被记录,就需要特别注意了。

我经过比较简单的测试,不同的binlog_format可能会导致某些SQL不被记录的情况总结如下:

mysql-faq-how-binlog-format-affect-replication
上面的测试区分了两种模式,一种是连接时指定了其他数据库,一种是连接时未指定任何数据库,相当于下面的两种方式:
#假设do/ignore规则中的DB名字叫DoDB/IgnoreDB/RewriteDB的话,OtherDB是规则之外的其他DB

#一种是:连接时指定了do/ignore/rewrite规则之外的其他DB
mysql -h host -u user -p passwd -p port -A OtherDB

#还有一种是:连接时不指定任何DB
mysql -h host -u user -p passwd -p port -A

#tips,加上 -A 是--no-auto-rehash的缩写,其作用是连接后不读取数据库、表、字段信息。与其相反的选项是 --auto-rehash,也就是连接后会读取数据库、表、字段信息,以便自动补齐。
更多情况请读者自行进行测试吧 :)

 

[MySQL FAQ]系列 — MySQL复制中slave延迟监控

在MySQL复制环境中,我们通常只根据 Seconds_Behind_Master 的值来判断SLAVE的延迟。这么做大部分情况下尚可接受,但并不够准确,而应该考虑更多因素。

首先,我们先看下SLAVE的状态:

yejr@imysql.com [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
***
Master_Log_File: mysql-bin.000327
Read_Master_Log_Pos: 668711237
Relay_Log_File: mysql-relay-bin.002999
Relay_Log_Pos: 214736858
Relay_Master_Log_File: mysql-bin.000327
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
***
Skip_Counter: 0
Exec_Master_Log_Pos: 654409041
Relay_Log_Space: 229039311
***
Seconds_Behind_Master: 3296
***

可以看到 Seconds_Behind_Master 的值是 3296,也就是SLAVE至少延迟了 3296 秒。

我们再来看下SLAVE上的2个REPLICATION进程状态:

yejr@imysql.com [(none)]> show full processlist\G
*************************** 1. row ***************************
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 22005006
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 7
User: system user
Host:
db: NULL
Command: Connect
Time: 3293
State: Updating
Info: UPDATE ** SET ** WHERE **

可以看到SQL线程一直在执行UPDATE操作,注意到 Time 的值是 3293,看起来像是这个UPDATE操作执行了3293秒,一个普通的SQL而已,肯定不至于需要这么久。
实际上,在REPLICATION进程中,Time 这列的值可能有几种情况:
1、SQL线程当前执行的binlog(实际上是relay log)中的timestamp和IO线程最新的timestamp的差值,这就是通常大家认为的 Seconds_Behind_Master 值,并不是某个SQL的实际执行耗时;
2、SQL线程当前如果没有活跃SQL在执行的话,Time值就是SQL线程的idle time;

而IO线程的Time值则是该线程自从启动以来的总时长(多少秒),如果系统时间在IO线程启动后发生修改的话,可能会导致该Time值异常,比如变成负数,或者非常大。

来看下面几个状态:

#设置pager,只查看关注的几个status值
yejr@imysql.com [(none)]> pager cat | egrep -i 'system user|Exec_Master_Log_Pos|Seconds_Behind_Master|Read_Master_Log_Pos'

#这是没有活跃SQL的情况,Time值是idle time,并且 Seconds_Behind_Master 为 0
yejr@imysql.com [(none)]> show processlist; show slave status\G
| 6 | system user | | NULL | Connect | 22004245 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 13 | Has read all relay log;**
Read_Master_Log_Pos: 445167889
Exec_Master_Log_Pos: 445167889
Seconds_Behind_Master: 0

#和上面一样
yejr@imysql.com [(none)]> show processlist; show slave status\G
| 6 | system user | | NULL | Connect | 22004248 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 16 | Has read all relay log;**
Read_Master_Log_Pos: 445167889
Exec_Master_Log_Pos: 445167889
Seconds_Behind_Master: 0

#这时有活跃SQL了,Time值是和 Seconds_Behind_Master 一样,即SQL线程比IO线程“慢”了1秒
yejr@imysql.com [(none)]> show processlist; show slave status\G
| 6 | system user | | NULL | Connect | 22004252 | Waiting for master to send event | NULL |
| 7 | system user | | floweradmin | Connect | 1 | Updating | update **
Read_Master_Log_Pos: 445182239
Exec_Master_Log_Pos: 445175263
Seconds_Behind_Master: 1

#和上面一样
yejr@imysql.com [(none)]> show processlist; show slave status\G
| 6 | system user | | NULL | Connect | 22004254 | Waiting for master to send event | NULL |
| 7 | system user | | floweradmin | Connect | 1 | Updating | update **
Read_Master_Log_Pos: 445207174
Exec_Master_Log_Pos: 445196837
Seconds_Behind_Master: 1

好了,最后我们说下如何正确判断SLAVE的延迟情况:
1、首先看 Relay_Master_Log_FileMaster_Log_File 是否有差异;
2、如果Relay_Master_Log_FileMaster_Log_File 是一样的话,再来看Exec_Master_Log_PosRead_Master_Log_Pos 的差异,对比SQL线程比IO线程慢了多少个binlog事件;
3、如果Relay_Master_Log_FileMaster_Log_File 不一样,那说明延迟可能较大,需要从MASTER上取得binlog status,判断当前的binlog和MASTER上的差距;

因此,相对更加严谨的做法是:
在第三方监控节点上,对MASTER和SLAVE同时发起SHOW BINARY LOGSSHOW SLAVE STATUS\G的请求,最后判断二者binlog的差异,以及 Exec_Master_Log_PosRead_Master_Log_Pos 的差异。

例如:
在MASTER上执行SHOW BINARY LOGS 的结果是:

+------------------+--------------+
| Log_name | File_size |
+------------------+--------------+
| mysql-bin.000009 | 1073742063 |
| mysql-bin.000010 | 107374193 |
+------------------+--------------+

而在SLAVE上执行SHOW SLAVE STATUS\G 的结果是:

Master_Log_File: mysql-bin.000009
 Read_Master_Log_Pos: 668711237
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
***
Exec_Master_Log_Pos: 654409041

***
Seconds_Behind_Master: 3296
***

这时候,SLAVE实际的延迟应该是:
mysql-bin.000009 这个binlog中的binlog position 1073742063 和 SLAVE上读取到的binlog position之间的差异延迟,即:

1073742063 - 654409041 = 419333022 个binlog event

并且还要加上 mysql-bin.000010这个binlog已经产生的107374193个binlog event,共

107374193 + 419333022 = 526707215 个binlog event

后记更新:

1、可以在MASTER上维护一个监控表,它只有一个字段,存储这最新最新时间戳(高版本可以采用event_scheduler来更新,低版本可以用cron结合自动循环脚本来更新),在SLAVE上读取该字段的时间,只要MASTER和SLAVE的系统时间一致,即可快速知道SLAVE和MASTER延迟差了多少。不过,在高并发的系统下,这个时间戳可以细化到毫秒,否则哪怕时间一致,也是有可能会延迟数千个binlog event的。
2、网友(李大玉,QQ:407361231)细心支出上面的计算延迟有误,应该是mysql-bin.000009的最大事件数减去已经被执行完的事件数,即1073742063 – 654409041= 419333022个binlog event,再加上mysql-bin.000010这个binlog已经产生的107374193个binlog event,共526707215 个binlog event。

[MySQL FAQ]系列 — 如何安全地关闭MySQL实例

本文分析了mysqld进程关闭的过程,以及如何安全、缓和地关闭MySQL实例,对这个过程不甚清楚的同学可以参考下。

关闭过程:

  • 1、发起shutdown,发出  SIGTERM信号
  • 2、有必要的话,新建一个关闭线程(shutdown thread)

如果是客户端发起的关闭,则会新建一个专用的关闭线程

如果是直接收到 SIGTERM 信号进行关闭的话,专门负责信号处理的线程就会负责关闭工作,或者新建一个独立的线程负责这个事

当无法创建独立的关闭线程时(例如内存不足),MySQL Server会发出类似下面的告警信息:

Error: Can’t create thread to kill server

  • 3、MySQL Server不再响应新的连接请求

关闭TCP/IP网络监听,关闭Unix Socket等渠道

  • 4、逐渐关闭当前的连接、事务

空闲连接,将立刻被终止;

当前还有事务、SQL活动的连接,会将其标识为 killed,并定期检查其状态,以便下次检查时将其关闭;(参考 KILL 语法)

当前有活跃事务的,该事物会被回滚,如果该事务中还修改了非事务表,则已经修改的数据无法回滚,可能只会完成部分变更;

如果是Master/Slave复制场景里的Master,则对复制线程的处理过程和普通线程也是一样的;

如果是Master/Slave复制场景里的Slave,则会依次关闭IO、SQL线程,如果这2个线程当前是活跃的,则也会加上 killed 标识,然后再关闭;

Slave服务器上,SQL线程是允许直接停止当前的SQL操作的(为了避免复制问题),然后再关闭该线程;

在MySQl 5.0.80及以前的版本里,如果SQL线程当时正好执行一个事务到中间,该事务会回滚;从5.0.81开始,则会等待所有的操作结束,除非用户发起KILL操作。

当Slave的SQL线程对非事务表执行操作时被强制 KILL了,可能会导致Master、Slave数据不一致;

  • 5、MySQL Server进程关闭所有线程,关闭所有存储引擎;

刷新所有表cache,关闭所有打开的表;

每个存储引擎各自负责相关的关闭操作,例如MyISAM会刷新所有等待写入的操作;InnoDB会将buffer pool刷新到磁盘中(从MySQL 5.0.5开始,如果innodb_fast_shutdown不设置为 2 的话),把当前的LSN记录到表空间中,然后关闭所有的内部线程。

  • 6、MySQL Server进程退出

关于KILL指令

从5.0开始,KILL 支持指定  CONNECTION | QUERY两种可选项:

  • KILL CONNECTION和原来的一样,停止回滚事务,关闭该线程连接,释放相关资源;
  • KILL QUERY则只停止线程当前提交执行的操作,其他的保持不变;

提交KILL操作后,该线程上会设置一个特殊的 kill标记位。通常需要一段时间后才能真正关闭线程,因为kill标记位只在特定的情况下才检查:

  • 1、执行SELECT查询时,在ORDER BY或GROUP BY循环中,每次读完一些行记录块后会检查 kill标记位,如果发现存在,该语句会终止;
  • 2、执行ALTER TABLE时,在从原始表中每读取一些行记录块后会检查 kill 标记位,如果发现存在,该语句会终止,删除临时表;
  • 3、执行UPDATE和DELETE时,每读取一些行记录块并且更新或删除后会检查 kill 标记位,如果发现存在,该语句会终止,回滚事务,若是在非事务表上的操作,则已发生变更的数据不会回滚;
  • 4、GET_LOCK() 函数返回NULL;
  • 5、INSERT DELAY线程会迅速内存中的新增记录,然后终止;
  • 6、如果当前线程持有表级锁,则会释放,并终止;
  • 7、如果线程的写操作调用在等待释放磁盘空间,则会直接抛出“磁盘空间满”错误,然后终止;
  • 8、当MyISAM表在执行REPAIR TABLE 或 OPTIMIZE TABLE 时被 KILL的话,会导致该表损坏不可用,指导再次修复完成。

安全关闭MySQL几点建议

想要安全关闭 mysqld 服务进程,建议按照下面的步骤来进行:

  • 0、用具有SUPER、ALL等最高权限的账号连接MySQL,最好是用 unix socket 方式连接;
  • 1、在5.0及以上版本,设置innodb_fast_shutdown = 1,允许快速关闭InnoDB(不进行full purge、insert buffer merge),如果是为了升级或者降级MySQL版本,则不要设置;
  • 2、设置innodb_max_dirty_pages_pct = 0,让InnoDB把所有脏页都刷新到磁盘中去;
  • 3、设置max_connections和max_user_connections为1,也就最后除了自己当前的连接外,不允许再有新的连接创建;
  • 4、关闭所有不活跃的线程,也就是状态为Sleep  且 Time 大于 1 的线程ID;
  • 5、执行 SHOW PROCESSLIST  确认是否还有活跃的线程,尤其是会产生表锁的线程,例如有大数据集的SELECT,或者大范围的UPDATE,或者执行DDL,都是要特别谨慎的;
  • 6、执行 SHOW ENGINE INNODB STATUS 确认History list length的值较低(一般要低于500),也就是未PURGE的事务很少,并且确认Log sequence number、Log flushed up to、Last checkpoint at三个状态的值一样,也就是所有的LSN都已经做过检查点了;
  • 7、然后执行FLUSH LOCKAL TABLES 操作,刷新所有 table cache,关闭已打开的表(LOCAL的作用是该操作不记录BINLOG);
  • 8、如果是SLAVE服务器,最好是先关闭 IO_THREAD,等待所有RELAY LOG都应用完后,再关闭 SQL_THREAD,避免 SQL_THREAD 在执行大事务被终止,耐心待其全部应用完毕,如果非要强制关闭的话,最好也等待大事务结束后再关闭SQL_THREAD;
  • 9、最后再执行 mysqladmin shutdown。
  • 10、紧急情况下,可以设置innodb_fast_shutdown = 1,然后直接执行 mysqladmin shutdown 即可,甚至直接在操作系统层调用 kill 或者 kill -9 杀掉 mysqld 进程(在innodb_flush_log_at_trx_commit = 0 的时候可能会丢失部分事务),不过mysqld进程再次启动时,会进行CRASH RECOVERY工作,需要有所权衡。

啰嗦那么多,其实正常情况下执行 mysqladmin shutdown 就够了,如果发生阻塞,再参考上面的内容进行分析和解决吧,哈哈:)