[MySQL FAQ]系列 — EXPLAIN结果中哪些信息要引起关注

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

我们使用EXPLAIN解析SQL执行计划时,如果有下面几种情况,就需要特别关注下了:

首先看下 type 这列的结果,如果有类型是 ALL 时,表示预计会进行全表扫描(full table scan)。通常全表扫描的代价是比较大的,建议创建适当的索引,通过索引检索避免全表扫描。此外,全索引扫描(full index scan)的代价有时候是比全表扫描还要高的,除非是基于InnoDB表的主键索引扫描。

再来看下 Extra 列的结果,如果有出现 Using temporary 或者 Using filesort 则要多加关注:

Using temporary,表示需要创建临时表以满足需求,通常是因为GROUP BY的列没有索引,或者GROUP BY和ORDER BY的列不一样,也需要创建临时表,建议添加适当的索引。

Using filesort,表示无法利用索引完成排序,也有可能是因为多表连接时,排序字段不是驱动表中的字段,因此也没办法利用索引完成排序,建议添加适当的索引。

Using where,通常是因为全表扫描或全索引扫描时(type 列显示为 ALLindex),又加上了WHERE条件,建议添加适当的索引。

暂时想到上面几个,如果有遗漏,以后再补充。

其他状态例如:Using index、Using index condition、Using index for group-by 则都还好,不用紧张。

更多详情请看官方文档:8.8.2 EXPLAIN Output Format

[MySQL FAQ]系列 — processlist中哪些状态要引起关注

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

一般而言,我们在processlist结果中如果经常能看到某些SQL的话,至少可以说明这些SQL的频率很高,通常需要对这些SQL进行进一步优化。

今天我们要说的是,在processlist中,看到哪些运行状态时要引起关注,主要有下面几个:

状态 建议
copy to tmp table 执行ALTER TABLE修改表结构时建议:放在凌晨执行或者采用类似pt-osc工具
Copying to tmp table 拷贝数据到内存中的临时表,常见于GROUP BY操作时建议:创建适当的索引
Copying to tmp table on disk 临时结果集太大,内存中放不下,需要将内存中的临时表拷贝到磁盘上,形成 #sql***.MYD、#sql***.MYI(在5.6及更高的版本,临时表可以改成InnoDB引擎了,可以参考选项default_tmp_storage_engine建议:创建适当的索引,并且适当加大sort_buffer_size/tmp_table_size/max_heap_table_size
Creating sort index 当前的SELECT中需要用到临时表在进行ORDER BY排序建议:创建适当的索引
Creating tmp table 创建基于内存或磁盘的临时表,当从内存转成磁盘的临时表时,状态会变成:Copying to tmp table on disk建议:创建适当的索引,或者少用UNION、视图(VIEW)、子查询(SUBQUERY)之类的,确实需要用到临时表的时候,可以在session级临时适当调大 tmp_table_size/max_heap_table_size 的值
Reading from net 表示server端正通过网络读取客户端发送过来的请求建议:减小客户端发送数据包大小,提高网络带宽/质量
Sending data 从server端发送数据到客户端,也有可能是接收存储引擎层返回的数据,再发送给客户端,数据量很大时尤其经常能看见备注:Sending Data不是网络发送,是从硬盘读取,发送到网络是Writing to net建议:通过索引或加上LIMIT,减少需要扫描并且发送给客户端的数据量
Sorting result 正在对结果进行排序,类似Creating sort index,不过是正常表,而不是在内存表中进行排序建议:创建适当的索引
statistics 进行数据统计以便解析执行计划,如果状态比较经常出现,有可能是磁盘IO性能很差建议:查看当前io性能状态,例如iowait
Waiting for global read lock FLUSH TABLES WITH READ LOCK整等待全局读锁建议:不要对线上业务数据库加上全局读锁,通常是备份引起,可以放在业务低谷期间执行或者放在slave服务器上执行备份
Waiting for tables,Waiting for table flush FLUSH TABLES, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE等需要刷新表结构并重新打开建议:不要对线上业务数据库执行这些操作,可以放在业务低谷期间执行
Waiting for lock_type lock 等待各种类型的锁:• Waiting for event metadata lock• Waiting for global read lock• Waiting for schema metadata lock• Waiting for stored function metadata lock• Waiting for stored procedure metadata lock• Waiting for table level lock• Waiting for table metadata lock

• Waiting for trigger metadata lock

建议:比较常见的是上面提到的global read lock以及table metadata lock,建议不要对线上业务数据库执行这些操作,可以放在业务低谷期间执行。如果是table level lock,通常是因为还在使用MyISAM引擎表,赶紧转投InnoDB引擎吧,别再老顽固了

更多详情可参考官方手册:8.14.2 General Thread States

比较全面的MySQL优化参考(下篇)

本文整理了一些MySQL的通用优化方法,做个简单的总结分享,旨在帮助那些没有专职MySQL DBA的企业做好基本的优化工作,至于具体的SQL优化,大部分通过加适当的索引即可达到效果,更复杂的就需要具体分析了,可以参考本站的一些优化案例或者联系我,下方有我的联系方式。这是下篇。

3、MySQL层相关优化

3.1、关于版本选择

官方版本我们称为ORACLE MySQL,这个没什么好说的,相信绝大多数人会选择它。

我个人强烈建议选择Percona分支版本,它是一个相对比较成熟的、优秀的MySQL分支版本,在性能提升、可靠性、管理型方面做了不少改善。它和官方ORACLE MySQL版本基本完全兼容,并且性能大约有20%以上的提升,因此我优先推荐它,我自己也从2008年一直以它为主。

另一个重要的分支版本是MariaDB,说MariaDB是分支版本其实已经不太合适了,因为它的目标是取代ORACLE MySQL。它主要在原来的MySQL Server层做了大量的源码级改进,也是一个非常可靠的、优秀的分支版本。但也由此产生了以GTID为代表的和官方版本无法兼容的新特性(MySQL 5.7开始,也支持GTID模式在线动态开启或关闭了),也考虑到绝大多数人还是会跟着官方版本走,因此没优先推荐MariaDB。

3.2、关于最重要的参数选项调整建议

建议调整下面几个关键参数以获得较好的性能(可使用本站提供的my.cnf生成器生成配置文件模板):

1、选择Percona或MariaDB版本的话,强烈建议启用thread pool特性,可使得在高并发的情况下,性能不会发生大幅下降。此外,还有extra_port功能,非常实用, 关键时刻能救命的。还有另外一个重要特色是 QUERY_RESPONSE_TIME 功能,也能使我们对整体的SQL响应时间分布有直观感受;

2、设置default-storage-engine=InnoDB,也就是默认采用InnoDB引擎,强烈建议不要再使用MyISAM引擎了,InnoDB引擎绝对可以满足99%以上的业务场景;

3、调整innodb_buffer_pool_size大小,如果是单实例且绝大多数是InnoDB引擎表的话,可考虑设置为物理内存的50% ~ 70%左右;

4、根据实际需要设置innodb_flush_log_at_trx_commit、sync_binlog的值。如果要求数据不能丢失,那么两个都设为1。如果允许丢失一点数据,则可分别设为2和10。而如果完全不用care数据是否丢失的话(例如在slave上,反正大不了重做一次),则可都设为0。这三种设置值导致数据库的性能受到影响程度分别是:高、中、低,也就是第一个会另数据库最慢,最后一个则相反;

5、设置innodb_file_per_table = 1,使用独立表空间,我实在是想不出来用共享表空间有什么好处了;

6、设置innodb_data_file_path = ibdata1:1G:autoextend,千万不要用默认的10M,否则在有高并发事务时,会受到不小的影响;

7、设置innodb_log_file_size=256M,设置innodb_log_files_in_group=2,基本可满足90%以上的场景;

8、设置long_query_time = 1,而在5.5版本以上,已经可以设置为小于1了,建议设置为0.05(50毫秒),记录那些执行较慢的SQL,用于后续的分析排查;

9、根据业务实际需要,适当调整max_connection(最大连接数)、max_connection_error(最大错误数,建议设置为10万以上,而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache这几个参数则可设为约10倍于max_connection的大小;

10、常见的误区是把tmp_table_size和max_heap_table_size设置的比较大,曾经见过设置为1G的,这2个选项是每个连接会话都会分配的,因此不要设置过大,否则容易导致OOM发生;其他的一些连接会话级选项例如:sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等,也需要注意不能设置过大;

11、由于已经建议不再使用MyISAM引擎了,因此可以把key_buffer_size设置为32M左右,并且强烈建议关闭query cache功能;

3.3、关于Schema设计规范及SQL使用建议

下面列举了几个常见有助于提升MySQL效率的Schema设计规范及SQL使用建议:

1、所有的InnoDB表都设计一个无业务用途的自增列做主键,对于绝大多数场景都是如此,真正纯只读用InnoDB表的并不多,真如此的话还不如用TokuDB来得划算;

2、字段长度满足需求前提下,尽可能选择长度小的。此外,字段属性尽量都加上NOT NULL约束,可一定程度提高性能;

3、尽可能不使用TEXT/BLOB类型,确实需要的话,建议拆分到子表中,不要和主表放在一起,避免SELECT * 的时候读性能太差。

4、读取数据时,只选取所需要的列,不要每次都SELECT *,避免产生严重的随机读问题,尤其是读到一些TEXT/BLOB列;

5、对一个VARCHAR(N)列创建索引时,通常取其50%(甚至更小)左右长度创建前缀索引就足以满足80%以上的查询需求了,没必要创建整列的全长度索引;

6、通常情况下,子查询的性能比较差,建议改造成JOIN写法;

7、多表联接查询时,关联字段类型尽量一致,并且都要有索引;

8、多表连接查询时,把结果集小的表(注意,这里是指过滤后的结果集,不一定是全表数据量小的)作为驱动表;

9、多表联接并且有排序时,排序字段必须是驱动表里的,否则排序列无法用到索引;

10、多用复合索引,少用多个独立索引,尤其是一些基数(Cardinality)太小(比如说,该列的唯一值总数少于255)的列就不要创建独立索引了;

11、类似分页功能的SQL,建议先用主键关联,然后返回结果集,效率会高很多;

3.3、其他建议

关于MySQL的管理维护的其他建议有:

1、通常地,单表物理大小不超过10GB,单表行数不超过1亿条,行平均长度不超过8KB,如果机器性能足够,这些数据量MySQL是完全能处理的过来的,不用担心性能问题,这么建议主要是考虑ONLINE DDL的代价较高;

2、不用太担心mysqld进程占用太多内存,只要不发生OOM kill和用到大量的SWAP都还好;

3、在以往,单机上跑多实例的目的是能最大化利用计算资源,如果单实例已经能耗尽大部分计算资源的话,就没必要再跑多实例了;

4、定期使用pt-duplicate-key-checker检查并删除重复的索引。定期使用pt-index-usage工具检查并删除使用频率很低的索引;

5、定期采集slow query log,用pt-query-digest工具进行分析,可结合Anemometer系统进行slow query管理以便分析slow query并进行后续优化工作;

6、可使用pt-kill杀掉超长时间的SQL请求,Percona版本中有个选项 innodb_kill_idle_transaction 也可实现该功能;

7、使用pt-online-schema-change来完成大表的ONLINE DDL需求;

8、定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异;

后记:本文根据个人多年经验总结,个别建议可能有不完善之处,欢迎留言或者加我 微信公众号:MySQL中文网、QQ:4700963 相互探讨交流。

写在最后:这次的优化参考,大部分情况下我都介绍了适用的场景,如果你的应用场景和本文描述的不太一样,那么建议根据实际情况进行调整,而不是生搬硬套。欢迎质疑拍砖,但拒绝不经过大脑的习惯性抵制。

附录:延伸阅读

1、常用PC服务器阵列卡、硬盘健康监控
2、PC服务器阵列卡管理简易手册
3、实测Raid5 VS Raid1+0下的innodb性能
4、SAS vs SSD各种模式下MySQL TPCC OLTP对比测试结果
5、MySQL出了门,Percona在左,MariaDB在右
6、Percona Thread Pool性能基准测试
7、[MySQL优化案例]系列 — 分页优化
8、[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键
9、[MySQL FAQ]系列 — 为什么要关闭query cache,如何关闭

更多相关优化案例,可在 百度谷歌 中根据关键字搜索本站。

比较全面的MySQL优化参考(上篇)

本文整理了一些MySQL的通用优化方法,做个简单的总结分享,旨在帮助那些没有专职MySQL DBA的企业做好基本的优化工作,至于具体的SQL优化,大部分通过加适当的索引即可达到效果,更复杂的就需要具体分析了,可以参考本站的一些优化案例或者联系我,下方有我的联系方式。这是上篇。

1、硬件层相关优化

1.1、CPU相关

在服务器的BIOS设置中,可调整下面的几个配置,目的是发挥CPU最大性能,或者避免经典的NUMA问题:

1、选择Performance Per Watt Optimized(DAPC)模式,发挥CPU最大性能,跑DB这种通常需要高运算量的服务就不要考虑节电了;
2、关闭C1E和C States等选项,目的也是为了提升CPU效率;
3、Memory Frequency(内存频率)选择Maximum Performance(最佳性能);

4、内存设置菜单中,启用Node Interleaving,避免NUMA问题;

1.2、磁盘I/O相关

下面几个是按照IOPS性能提升的幅度排序,对于磁盘I/O可优化的一些措施:

1、使用SSD或者PCIe SSD设备,至少获得数百倍甚至万倍的IOPS提升;
2、购置阵列卡同时配备CACHE及BBU模块,可明显提升IOPS(主要是指机械盘,SSD或PCIe SSD除外。同时需要定期检查CACHE及BBU模块的健康状况,确保意外时不至于丢失数据);

3、有阵列卡时,设置阵列写策略为WB,甚至FORCE WB(若有双电保护,或对数据安全性要求不是特别高的话),严禁使用WT策略。并且闭阵列预读策略,基本上是鸡肋,用处不大;

4、尽可能选用RAID-10,而非RAID-5;

5、使用机械盘的话,尽可能选择高转速的,例如选用15KRPM,而不是7.2KRPM的盘,不差几个钱的;

2、系统层相关优化

2.1、文件系统层优化

在文件系统层,下面几个措施可明显提升IOPS性能:

1、使用deadline/noop这两种I/O调度器,千万别用cfq(它不适合跑DB类服务);
2、使用xfs文件系统,千万别用ext3;ext4勉强可用,但业务量很大的话,则一定要用xfs;

3、文件系统mount参数中增加:noatime, nodiratime, nobarrier几个选项(nobarrier是xfs文件系统特有的);

2.2、其他内核参数优化

针对关键内核参数设定合适的值,目的是为了减少swap的倾向,并且让内存和磁盘I/O不会出现大幅波动,导致瞬间波峰负载:

1、将vm.swappiness设置为5-10左右即可,甚至设置为0(RHEL 7以上则慎重设置为0,除非你允许OOM kill发生),以降低使用SWAP的机会;
2、将vm.dirty_background_ratio设置为5-10,将vm.dirty_ratio设置为它的两倍左右,以确保能持续将脏数据刷新到磁盘,避免瞬间I/O写,产生严重等待(和MySQL中的innodb_max_dirty_pages_pct类似);

3、将net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都设置为1,减少TIME_WAIT,提高TCP效率;

4、至于网传的read_ahead_kb、nr_requests这两个参数,我经过测试后,发现对读写混合为主的OLTP环境影响并不大(应该是对读敏感的场景更有效果),不过没准是我测试方法有问题,可自行斟酌是否调整;

后记:本文根据个人多年经验总结,个别建议可能有不完善之处,欢迎留言或者加我 微信公众号:MySQL中文网、QQ:4700963 相互探讨交流。

附录:延伸阅读

1、常用PC服务器阵列卡、硬盘健康监控
2、PC服务器阵列卡管理简易手册
3、实测Raid5 VS Raid1+0下的innodb性能
4、SAS vs SSD各种模式下MySQL TPCC OLTP对比测试结果
5、MySQL出了门,Percona在左,MariaDB在右
6、Percona Thread Pool性能基准测试
7、[MySQL优化案例]系列 — 分页优化
8、[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键
9、[MySQL FAQ]系列 — 为什么要关闭query cache,如何关闭

更多相关优化案例,可在 百度谷歌 中根据关键字搜索本站。

[MySQL FAQ]系列 — MySQL无法启动例一

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

【场景】某个打算用于slave新搭建的实例启动报错,启动过程中报告InnoDB数据页发生损坏。错误日志像下面这样:

150330 15:37:44 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mytest_3306
2015-03-30 15:37:45 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-30 15:37:45 5884 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
2015-03-30 15:37:45 5884 [Note] Plugin ‘FEDERATED’ is disabled.
2015-03-30 15:37:45 5884 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-03-30 15:37:45 5884 [Note] InnoDB: The InnoDB memory heap is disabled
2015-03-30 15:37:45 5884 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-03-30 15:37:45 5884 [Note] InnoDB: Memory barrier is not used
2015-03-30 15:37:45 5884 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-03-30 15:37:45 5884 [Note] InnoDB: Using Linux native AIO
2015-03-30 15:37:45 5884 [Note] InnoDB: Using CPU crc32 instructions
2015-03-30 15:37:45 5884 [Note] InnoDB: Initializing buffer pool, size = 2.0G
2015-03-30 15:37:46 5884 [Note] InnoDB: Completed initialization of buffer pool
2015-03-30 15:37:47 5884 [Note] InnoDB: Highest supported file format is Barr.
2015-03-30 15:37:48 5884 [Warning] InnoDB: Resizing redo log from 3*32768 to 2*16384 pages, LSN=2740249189
2015-03-30 15:37:48 5884 [Warning] InnoDB: Starting to delete and rewrite log files.
2015-03-30 15:37:48 5884 [Note] InnoDB: Setting log file ./ib_logfile101 size to 256 MB
InnoDB: Progress in MB: 100 200
2015-03-30 15:37:49 5884 [Note] InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Progress in MB: 100 200
2015-03-30 15:37:50 5884 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-03-30 15:37:50 5884 [Warning] InnoDB: New log files created, LSN=2740249612
2015-03-30 15:37:50 5884 [Note] InnoDB: 128 rollback segment(s) are active.
2015-03-30 15:37:50 5884 [Note] InnoDB: Waiting for purge to start
2015-03-30 15:37:50 5884 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.21-rel69.0 started; log sequence number 2740249189
2015-03-30 15:37:50 5884 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ab27d3e4-d6af-11e4-8020-c81f66eeffa6.
150330 15:36:33 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mytest_3306
2015-03-30 15:37:50 5884 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ab27d3e4-d6af-11e4-8020-c81f66eeffa6.
2015-03-30 15:37:50 5884 [Note] RSA private key file not found: /data/mysql/mytest_3306//private_key.pem. Some authentication plugins will not work.
2015-03-30 15:37:50 5884 [Note] RSA public key file not found: /data/mysql/mytest_3306//public_key.pem. Some authentication plugins will not work.
2015-03-30 15:37:50 5884 [Note] Server hostname (bind-address): ’10.x.x.x'; port: 3306
2015-03-30 15:37:50 5884 [Note] – ’10.x.x.x’ resolves to ’10.x.x.x';
2015-03-30 15:37:50 5884 [Note] Server socket created on IP: ’10.x.x.x’.
2015-03-30 15:37:50 7f4ce4d68700 InnoDB: Error: page 32769 log sequence number 2740254202
InnoDB: is in the future! Current system log sequence number 2740249622.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.
2015-03-30 15:37:50 5884 [Note] Event Scheduler: Loaded 0 events
2015-03-30 15:37:50 5884 [Note] /opt/Percona-Server-5.6.21-rel69.0-675.Linux.x86_64/bin/mysqld: ready for connections.
Version: ‘5.6.21-69.0-log’ socket: ‘/data/mysql/mytest_3306/mysql.sock’ port: 3306 Percona Server (GPL), Release 69.0, Revision 675
2015-03-30 15:37:58 7f4ce4d68700 InnoDB: Error: page 6327 log sequence number 2740254445
InnoDB: is in the future! Current system log sequence number 2740251356.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: for more information.

【分析】正常情况下,新部署的实例是不太可能出现InnoDB数据页损坏的。经了解,这个实例是采用xtrabackup工具从master备份过来做恢复的。

细心的同学,通过观察上面的日志,应该能从中发现一些蛛丝马迹。有几个地方需要引起注意:

1、版本是Percona Server 5.6.21;
2、刚启动就把InnoDB的redo log给resize了;
3、刚启动就发现InnoDB的page LSN和redo log中的不匹配;

之所以提醒大家注意上面的三点,并且把版本信息放在了第一条,是因为从5.6版本开始,InnoDB如如果发现当前的redo log文件大小和预设配置的redo log大小不一致的话,就会自动将其删除重建

写到这里,相信聪明的你应该已经想到什么了吧,没错,导致这个启动报错的原因是:从master上xtrabackup备份出来的innodb redo log大小和本地配置参数不一致,被删除重建,结果事务恢复失败,提示数据也损坏错误信息

【解决】修改slave本地配置文件,把下面几个InnoDB配置选项都修改成和在master上的一样,再次执行恢复启动即可。

innodb_data_file_path
innodb_log_file_size
innodb_log_files_in_group
innodb_file_per_table

[MySQL FAQ]系列 — pt-table-checksum工具使用报错一例

Percona Tollkit
图片来自Percona官网

今天同事在用 percona toolkit 工具中的 pt-table-checksum 对主从数据库进行校验,提交命令后,一直提示下面的信息:

Pausing because Threads_running=0

看字面意思是在提示当前活跃线程数为0,但为什么不继续执行呢。这个提示信息有点含糊其辞,该工具是用Perl写的,因此直接打开看脚本跟踪一下,大概就明白怎么回事了,原来是这个工具有负载保护机制,避免运行时对线上数据库产生影响。

和这个机制相关的参数名是: –max-load,其类型是:Array,用法是一个或多个 variables = value 组成的判断条件,然后根据这个规则判断某些条件是否超标。例如,设定 –max-load=”Threads_running=25″,意思是当前活跃线程数如果超过25,就暂停 checksum 工作,直到活跃线程数低于 25。

因此,在我们这个案例中,想要强制让 table-checksum 继续工作的话,可以设定 –max-load 的值,例如:

pt-table-checksum --max-load="Threads_running=25" ...其他选项...

或者

pt-table-checksum --max-load="Threads_connected=25" ...其他选项...

 

前面的选项意思是判断活跃线程数不要超过25个,后面的选项意思是当前打开的线程数不要超过25个。

下面是 pt-table-checksum 帮助手册里的一段话:

–max-load
type: Array; default: Threads_running=25; group: Throttle

Examine SHOW GLOBAL STATUS after every chunk, and pause if any status variables are higher than the threshold. The option accepts a comma-sep-
arated list of MySQL status variables to check for a threshold. An optional “=MAX_VALUE” (or “:MAX_VALUE”) can follow each variable. If not
given, the tool determines a threshold by examining the current value and increasing it by 20%.

For example, if you want the tool to pause when Threads_connected gets too high, you can specify “Threads_connected”, and the tool will check
the current value when it starts working and add 20% to that value. If the current value is 100, then the tool will pause when Threads_con-
nected exceeds 120, and resume working when it is below 120 again. If you want to specify an explicit threshold, such as 110, you can use
either “Threads_connected:110″ or “Threads_connected=110″.

The purpose of this option is to prevent the tool from adding too much load to the server. If the checksum queries are intrusive, or if they
cause lock waits, then other queries on the server will tend to block and queue. This will typically cause Threads_running to increase, and the
tool can detect that by running SHOW GLOBAL STATUS immediately after each checksum query finishes. If you specify a threshold for this vari-
able, then you can instruct the tool to wait until queries are running normally again. This will not prevent queueing, however; it will only
give the server a chance to recover from the queueing. If you notice queueing, it is best to decrease the chunk time.

[MySQL优化案例]系列 — slave延迟很大优化方法

mysql replication
备注:插图来自网络搜索,如果觉得不当还请及时告知 :)

一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发。简单说,在master上是并发模式(以InnoDB引擎为主)完成事务提交的,而在slave上,复制线程只有一个sql thread用于binlog的apply,所以难怪slave在高并发时会远落后master。

ORACLE MySQL 5.6版本开始支持多线程复制,配置选项 slave_parallel_workers 即可实现在slave上多线程并发复制。不过,它只能支持一个实例下多个 database 间的并发复制,并不能真正做到多表并发复制。因此在较大并发负载时,slave还是没有办法及时追上master,需要想办法进行优化。

另一个重要原因是,传统的MySQL复制是异步(asynchronous)的,也就是说在master提交完后,才在slave上再应用一遍,并不是真正意义上的同步。哪怕是后来的Semi-sync Repication(半同步复制),也不是真同步,因为它只保证事务传送到slave,但没要求等到确认事务提交成功。既然是异步,那肯定多少会有延迟。因此,严格意义上讲,MySQL复制不能叫做MySQL同步(处女座的面试官有可能会在面试时把说成MySQL同步的一律刷掉哦)。

另外,不少人的观念里,slave相对没那么重要,因此就不会提供和master相同配置级别的服务器。有的甚至不但使用更差的服务器,而且还在上面跑多实例。

综合这两个主要原因,slave想要尽可能及时跟上master的进度,可以尝试采用以下几种方法:

  1. 采用MariaDB发行版,它实现了相对真正意义上的并行复制,其效果远比ORACLE MySQL好的很多。在我的场景中,采用MariaDB作为slave的实例,几乎总是能及时跟上master。如果不想用这个版本的话,那就老实等待官方5.7大版本发布吧;
    关于MariaDB的Parallel Replication具体请参考:Replication and Binary Log Server System Variables#slave_parallel_threads – MariaDB Knowledge Base
  2. 每个表都要显式指定主键,如果没有指定主键的话,会导致在row模式下,每次修改都要全表扫描,尤其是大表就非常可怕了,延迟会更严重,甚至导致整个slave库都被挂起,可参考案例:mysql主键的缺少导致备库hang
  3. 应用程序端多做些事,让MySQL端少做事,尤其是和IO相关的活动,例如:前端通过内存CACHE或者本地写队列等,合并多次读写为一次,甚至消除一些写请求;
  4. 进行合适的分库、分表策略,减小单库单表复制压力,避免由于单库单表的的压力导致整个实例的复制延迟;
  5. 其他提高IOPS性能的几种方法,根据效果优劣,我做了个简单排序:
    • 更换成SSD,或者PCIe SSD等IO设备,其IOPS能力的提升是普通15K SAS盘的数以百倍、万倍,甚至几十万倍计;
    • 加大物理内存,相应提高InnoDB Buffer Pool大小,让更多热数据放在内存中,降低发生物理IO的频率;
    • 调整文件系统为 XFS 或 ReiserFS,相比ext3可以极大程度提高IOPS能力。在高IOPS压力下,相比ext4有更稳健的IOPS表现(有人认为 XFS 在特别的场景下会有很大的问题,但我们除了剩余磁盘空间少于10%时引发丢数据外,其他的尚未遇到);
    • 调整RAID级别为raid 1+0,它相比raid1、raid5等更能提高IOPS性能。如果已经全部是SSD设备了,可以2块盘做成RAID 1,或者多快盘做成RAID 5(并且可以设置全局热备盘,提高阵列容错性),甚至有些土豪用户直接将多块SSD盘组成RAID 50;
    • 调整RAID的写cache策略为WB或FORCE WB,详情请参考:常用PC服务器阵列卡、硬盘健康监控 以及 PC服务器阵列卡管理简易手册
    • 调整内核的io scheduler,优先使用deadline,如果是SSD,则可以使用noop策略,相比默认的cfq,个别情况下对IOPS的性能提升至少是数倍的。

其他更多方法,欢迎大家帮忙补充 :)

[MySQL优化案例]系列 — discuz!热帖翻页优化

discuz-logo
备注:插图来自discuz!官方LOGO,如果觉得不当还请及时告知 :)

写在前面:discuz!作为首屈一指的社区系统,为广大站长提供了一站式网站解决方案,而且是开源的(虽然部分代码是加密的),它为这个垂直领域的行业发展作出了巨大贡献。尽管如此,discuz!系统源码中,还是或多或少有些坑。其中最著名的就是默认采用MyISAM引擎,以及基于MyISAM引擎的抢楼功能session表采用memory引擎等,可以参考后面几篇历史文章。本次我们要说说discuz!在应对热们帖子翻页逻辑功能中的另一个问题。

在我们的环境中,使用的是 MySQL-5.6.6 版本。

在查看帖子并翻页过程中,会产生类似下面这样的SQL:

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: tid,displayorder,first
 key: displayorder
 key_len: 3
 ref: const
 rows: 593371
 Extra: Using index condition; Using where; Using filesort

这个SQL执行的代价是:

-- 根据索引访问行记录次数,总体而言算是比较好的状态
| Handler_read_key           | 16     |

-- 根据索引顺序访问下一行记录的次数,通常是因为根据索引的范围扫描,或者全索引扫描,总体而言也算是比较好的状态
| Handler_read_next          | 329881 |

-- 按照一定顺序读取行记录的总次数。如果需要对结果进行排序,该值通常会比较大。当发生全表扫描或者多表join无法使用索引时,该值也会比较大
| Handler_read_rnd           | 15     |

而当遇到热帖需要往后翻很多页时,例如:

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline  LIMIT 129860, 15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: displayorder
 key: displayorder
 key_len: 3
 ref: const
 rows: 593371
 Extra: Using where; Using filesort

这个SQL执行的代价则变成了(可以看到Handler_read_key、Handler_read_rnd大了很多):

| Handler_read_key           | 129876 | -- 因为前面需要跳过很多行记录
| Handler_read_next          | 329881 | -- 同上
| Handler_read_rnd           | 129875 | -- 因为需要先对很大一个结果集进行排序

可见,遇到热帖时,这个SQL的代价会非常高。如果该热帖被大量的访问历史回复,或者被搜素引擎一直反复请求并且历史回复页时,很容易把数据库服务器直接压垮。

小结:这个SQL不能利用 `displayorder` 索引排序的原因是,索引的第二个列 `invisible` 采用范围查询(RANGE),导致没办法继续利用联合索引完成对 `dateline` 字段的排序需求(而如果是 WHERE tid =? AND invisible IN(?, ?) AND dateline =? 这种情况下是完全可以用到整个联合索引的,注意下二者的区别)。

知道了这个原因,相应的优化解决办法也就清晰了:
创建一个新的索引 idx_tid_dateline,它只包括 tid、dateline 两个列即可(根据其他索引的统计信息,item_type 和 item_id 的基数太低,所以没包含在联合索引中。当然了,也可以考虑一并加上)。

我们再来看下采用新的索引后的执行计划:

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline  LIMIT 15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: tid,displayorder,first,idx_tid_dateline
 key: idx_tid_dateline
 key_len: 3
 ref: const
 rows: 703892
 Extra: Using where

可以看到,之前存在的 Using filesort 消失了,可以通过索引直接完成排序了。

不过,如果该热帖翻到较旧的历史回复时,相应的SQL还是不能使用新的索引:

mysql> desc SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: tid,displayorder,first,idx_tid_dateline
 key: displayorder
 key_len: 3
 ref: const
 rows: 593371
 Extra: Using where; Using filesort

对比下如果建议优化器使用新索引的话,其执行计划是怎样的:

mysql> desc SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline  LIMIT 129860,15\G
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: pre_forum_post
 type: ref
 possible_keys: idx_tid_dateline
 key: idx_tid_dateline
 key_len: 3
 ref: const
 rows: 703892
 Extra: Using where

可以看到,因为查询优化器认为后者需要扫描的行数远比前者多了11万多,因此认为前者效率更高。

事实上,在这个例子里,排序的代价更高,因此我们要优先消除排序,所以应该强制使用新的索引,也就是采用后面的执行计划,在相应的程序中指定索引。

最后,我们来看下热帖翻到很老的历史回复时,两个执行计划分别的profiling统计信息对比:

1、采用旧索引(displayorder):

mysql> SELECT * FROM pre_forum_post WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15;

​#查看profiling结果
 | starting             | 0.020203 |
 | checking permissions | 0.000026 |
 | Opening tables       | 0.000036 |
 | init                 | 0.000099 |
 | System lock          | 0.000092 |
 | optimizing           | 0.000038 |
 | statistics           | 0.000123 |
 | preparing            | 0.000043 |
 | Sorting result       | 0.000025 |
 | executing            | 0.000023 |
 | Sending data         | 0.000045 |
 | Creating sort index  | 0.941434 |
 | end                  | 0.000077 |
 | query end            | 0.000044 |
 | closing tables       | 0.000038 |
 | freeing items        | 0.000056 |
 | cleaning up          | 0.000040 |

2、如果是采用新索引(idx_tid_dateline):

mysql> SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15;

​#对比查看profiling结果
 | starting             | 0.000151 |
 | checking permissions | 0.000033 |
 | Opening tables       | 0.000040 |
 | init                 | 0.000105 |
 | System lock          | 0.000044 |
 | optimizing           | 0.000038 |
 | statistics           | 0.000188 |
 | preparing            | 0.000044 |
 | Sorting result       | 0.000024 |
 | executing            | 0.000023 |
 | Sending data         | 0.917035 |
 | end                  | 0.000074 |
 | query end            | 0.000030 |
 | closing tables       | 0.000036 |
 | freeing items        | 0.000049 |
 | cleaning up          | 0.000032 |

可以看到,效率有了一定提高,不过不是很明显,因为确实需要扫描的数据量更大,所以 Sending data 阶段耗时更多。

这时候,我们可以再参考之前的一个优化方案:[MySQL优化案例]系列 — 分页优化

然后可以将这个SQL改写成下面这样:

mysql> EXPLAIN SELECT * FROM pre_forum_post t1 INNER JOIN (
 SELECT id FROM pre_forum_post use index(idx_tid_dateline) WHERE
 tid=8201301 AND `invisible` IN('0','-2') ORDER BY
 dateline  LIMIT 129860,15) t2
 USING (id)\G
 *************************** 1. row ***************************
 id: 1
 select_type: PRIMARY
 table: 
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 129875
 Extra: NULL
 *************************** 2. row ***************************
 id: 1
 select_type: PRIMARY
 table: t1
 type: eq_ref
 possible_keys: PRIMARY
 key: PRIMARY
 key_len: 4
 ref: t2.id
 rows: 1
 Extra: NULL
 *************************** 3. row ***************************
 id: 2
 select_type: DERIVED
 table: pre_forum_post
 type: ref
 possible_keys: idx_tid_dateline
 key: idx_tid_dateline
 key_len: 3
 ref: const
 rows: 703892
 Extra: Using where

再看下这个SQL的 profiling 统计信息:

| starting             | 0.000209 |
| checking permissions | 0.000026 |
| checking permissions | 0.000026 |
| Opening tables       | 0.000101 |
| init                 | 0.000062 |
| System lock          | 0.000049 |
| optimizing           | 0.000025 |
| optimizing           | 0.000037 |
| statistics           | 0.000106 |
| preparing            | 0.000059 |
| Sorting result       | 0.000039 |
| statistics           | 0.000048 |
| preparing            | 0.000032 |
| executing            | 0.000036 |
| Sending data         | 0.000045 |
| executing            | 0.000023 |
| Sending data         | 0.225356 |
| end                  | 0.000067 |
| query end            | 0.000028 |
| closing tables       | 0.000023 |
| removing tmp table   | 0.000029 |
| closing tables       | 0.000044 |
| freeing items        | 0.000048 |
| cleaning up          | 0.000037 |

可以看到,效率提升了1倍以上,还是挺不错的。

最后说明下,这个问题只会在热帖翻页时才会出现,一般只有1,2页回复的帖子如果还采用原来的执行计划,也没什么问题。

因此,建议discuz!官方修改或增加下新索引,并且在代码中判断是否热帖翻页,是的话,就强制使用新的索引,以避免性能问题。

扩展阅读:

1、MySQL优化 之 Discuz论坛优化
2、MySQL优化 之 Discuz论坛优化 — 续
3、MySQL优化 之 Discuz论坛MySQL通用优化

最后稍微吐槽一下:最近几天遇到了几起关于MySQL查询优化器的BUG,挺让人摸不着头脑的 :(

[MySQL FAQ]系列 — 为什么要关闭query cache,如何关闭

mysql-query-cache-seems-so-beautiful
备注:插图来自淘宝苏普的博客并保留水印,如果觉得不当还请及时告知 :)

写在前面:MySQL的query cache大部分情况下其实只是鸡肋而已,建议全面禁用。当然了,或许在你的场景下还是挺好的,还能发挥作用,那就继续使用吧,把本文当做参考就好。

不过,可能有的人人为只需要把 query_cache_size 大小调整为 0 就可以了,可以忽略 query_cache_type 参数的值,反正它也是可以在线调整的。

事实果真如此吗?让我们来实际模拟测试下就知道了。

我们模拟了以下几种场景:

1、初始化时,同时设置 query_cache_size 和 query_cache_type 的值为 0;

2、初始化时,设置 query_cache_size = 0,但设置 query_cache_type = 1;

3、初始化时,设置 query_cache_size = 0,query_cache_type = 1,但是启动后立刻 修改 query_cache_type = 0

4、初始化时,设置 query_cache_size = 0,query_cache_type = 0,但是启动后立刻 修改 query_cache_type = 1

5、初始化时,设置 query_cache_size = xMB,query_cache_type = 1,但是启动后立刻 修改 query_cache_type = 0

 

经过测试,可以得到下面几个重要结论(详细测试过程请见最后):

1、想要彻底关闭query cache,务必在一开始就设置 query_cache_type = 0,即便是启动后将 query_cache_type 从 1 改成 0,也不行;

2、即便query_cache_size = 0,但 query_cache_type 非 0 的话,在实际环境中,可能会频繁发生 Waiting for query cache lock;

3、一开始就设置 query_cache_type = 0 的话,没有办法在运行 过程中再次动态启用,反过来则可以。也就是说,一开始是启用 query cache 的, 在运行过程中将其关闭,但事实上仍然会发生  Waiting for query cache lock,并没有真正的关闭;

 

关于query cache的延伸阅读,请见:

1、我的前同事waterbin帅哥的悲惨经历:MySQL Troubleshoting:Waiting on query cache mutex
2、淘宝苏普的旧文:Query Cache,看上去很美

 

详细测试过程:

一、测试方法

采用sysbench模拟并发oltp请求:

sysbench --test=tests/db/oltp.lua --oltp_tables_count=10 --oltp-table-size=100000 --rand-init=on --num-threads=64 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=1800 --max-requests=0 run

二、具体几种测试模式

1、一直关闭QC(query cache的简写,下同),即  query_cache_size = 0, query_cache_type = 0

测试过程中,一直都没有和query cache lock相关的状态出现,结果tps:2295.34

 

2、启用QC,但QC size 设置为 0,即:query_cache_size = 0,query_cache_type = 1

测试过程中,一直有 Waiting for query cache lock 状态出现,结果tps:2272.52

 

3、启用QC,但QC size为0,但启动时立刻关闭QC,即初始化时 query_cache_size = 0,query_cache_type = 1,启动后立刻修改 query_cache_type = 0

测试过程中,也一直有 Waiting for query cache lock 状态出现,结果tps:2311.54

 

4、关闭QC,但启动后立刻启用QC,即初始化时 query_cache_size = 0,query_cache_type = 0,启动后立刻修改 query_cache_type = 1

这时,会提示报错信息:

失败:ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

也就是说,如果一开始就关闭 QC 的话,是没办法在运行过程中动态再启用QC的。

 

5、启用QC,并设置QC size为256M,即 query_cache_size = 256M,query_cache_type = 1

这种情况下,在测试过程中一直有 Waiting for query cache lock 状态出现,并且结果tps也很差,只有 1395.39(几个案例中最差的一种)

 

6、启用QC,设置QC size为256M,但启动后立刻关闭QC,即 query_cache_size = 256M,query_cache_type = 1,启动后立刻修改 query_cache_type = 0

这种情况下,在测试过程中也一直有  Waiting for query cache lock 状态出现,结果tps:2295.79(在这个模式下,如果设置 query_cache_type = 2,效果也不佳)

 

第三种模式下,虽然看起来tps还不错,但毕竟上面只是简单模拟测试,实际情况下如果有频繁的query cache lock的话,tps肯定不会太好看。

因此,总的来说,想要获得较高tps的话,最好还是一开始就关闭QC,不要心存侥幸或者固守陈规。

[MySQL FAQ]系列 — 如何查看当前最新事务ID

InnoDB

写在前面:在个别时候可能需要查看当前最新的事务ID,以便做一些业务逻辑上的判断(例如利用事务ID变化以及前后时差,统计每次事务的响应时长等用途)。

通常地,我们有两种方法可以查看当前的事务ID:

1、执行SHOW ENGINE INNODB STATUS,查看事务相关信息

=====================================
150303 17:16:11 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
...
------------
TRANSACTIONS
Trx id counter 3359877657 -- 当前最大事务ID
Purge done for trx's n:o < 3359877468 undo n:o < 0 state: running
History list length 324
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started -- 该会话中执行SHOW ENGINE INNODB STATUS,不会产生事务,所以事务ID为0
MySQL thread id 4692367, OS thread handle 0x51103940, query id 677284426 xx.173ops.com 10.x.x.x yejr init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 3359877640, not started --非活跃事务,还未开始
mysql tables in use 1, locked 0
MySQL thread id 4678384, OS thread handle 0x41a57940, query id 677284427 xx.173ops.com 10.x.x.x yejr System lock
select polinfo0_.Fid as Fid39_0_, ...

---TRANSACTION 3359877652, not started
MySQL thread id 4678383, OS thread handle 0x50866940, query id 677284420 xx.173ops.com 10.x.x.x yejr cleaning up

---TRANSACTION 3359877635, ACTIVE 1358 sec, thread declared inside InnoDB 5000 --活跃长事务,运行了1358秒还未结束,要引起注意,可能会导致大量锁等待发生
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
MySQL thread id 3120717, OS thread handle 0x529b4940, query id 677284351 xx.173ops.com 10.x.x.x yejr query end
insert into t_live_room ...

 

2、查看INFORMATION_SCHEMA.INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 三个表,通过这些信息能快速发现哪些事务在阻塞其他事务

#先查询 INNODB_TRX 表,看看都有哪些事务

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
 trx_id: 17778 -- 当前事务ID
 trx_state: LOCK WAIT -- 处于锁等待状态,也就是等待其他会话释放锁资源
 trx_started: 2015-03-04 10:40:26
 trx_requested_lock_id: 17778:82:3:6 -- 欲请求的锁
 trx_wait_started: 2015-03-04 10:40:26
 trx_weight: 2 -- 大意是该锁影响了2行记录
 trx_mysql_thread_id: 657 -- processlist中的线程ID
 trx_query: update trx_fee set fee=rand()*1000 where id= 4
 trx_operation_state: starting index read
 trx_tables_in_use: 1
 trx_tables_locked: 1
 trx_lock_structs: 2
 trx_lock_memory_bytes: 360
 trx_rows_locked: 1
 trx_rows_modified: 0
 trx_concurrency_tickets: 0
 trx_isolation_level: REPEATABLE READ
 trx_unique_checks: 1
 trx_foreign_key_checks: 1
 trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
 trx_is_read_only: 0
 trx_autocommit_non_locking: 0
 *************************** 2. row ***************************
 trx_id: 17773
  trx_state: RUNNING
 trx_started: 2015-03-04 10:40:23
 trx_requested_lock_id: NULL
 trx_wait_started: NULL
 trx_weight: 10
 trx_mysql_thread_id: 656
 trx_query: NULL
 trx_operation_state: NULL
 trx_tables_in_use: 0
 trx_tables_locked: 0
 trx_lock_structs: 2
 trx_lock_memory_bytes: 360
 trx_rows_locked: 9
 trx_rows_modified: 8
 trx_concurrency_tickets: 0
 trx_isolation_level: REPEATABLE READ
 trx_unique_checks: 1
 trx_foreign_key_checks: 1
 trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
 trx_is_read_only: 0
 trx_autocommit_non_locking: 0

 

#再看 INNODB_LOCKS 表,看看都有什么锁

mysql> select * from information_schema.INNODB_LOCKS\G
*************************** 1. row ***************************
lock_id: 17778:82:3:6 --当前锁ID
lock_trx_id: 17778 --该锁对应的事务ID
lock_mode: X -- 锁类型,排它锁X
lock_type: RECORD --锁范围,记录锁:record lock,其他锁范围:间隙锁:gap lock,或者next-key lock(记录锁+间隙锁)
lock_table: `test`.`trx_fee`
lock_index: PRIMARY --加载在哪个索引上的锁
lock_space: 82
lock_page: 3
lock_rec: 6
lock_data: 4
*************************** 2. row ***************************
lock_id: 17773:82:3:6
lock_trx_id: 17773
lock_mode: X
lock_type: RECORD
lock_table: `test`.`trx_fee`
lock_index: PRIMARY
lock_space: 82
lock_page: 3
lock_rec: 6
lock_data: 4

 

#最后看 INNODB_LOCK_WAITS 表,看看当前都有哪些锁等待

mysql> select * from information_schema.INNODB_LOCK_WAITS\G
*************************** 1. row ***************************
requesting_trx_id: 17778 --请求锁的事务ID(等待方)
requested_lock_id: 17778:82:3:6 -- 请求锁ID
blocking_trx_id: 17773 -- 阻塞该锁的事务ID(当前持有方,待释放)
blocking_lock_id: 17773:82:3:6 -- 持有的锁ID

关于INFORMATION_SCHEMA中和InnoDB有关的表用途描述,可以查看手册:21.29 INFORMATION_SCHEMA Tables for InnoDB

 

3、利用percona分支的特性,查看当前最新事务ID,该特性从5.6.11-60.3版本开始引入,执行下面的2个命令即可查看

mysqladmin ext | grep Innodb_max_trx_id
或者
mysql> show global status like 'Innodb_max_trx_id';

最后,交代下问题的来源其实是这样的,有位朋友和我讨论问题,说在java连接池中,发现2个事务的事务ID是一样的,测试的SQL代码:

begin;update trx set un=rand() where id=round(rand()*10)+1;select * from information_schema.INNODB_TRX; commit;select sleep(0.01);begin;update trx set un=rand() where id=round(rand()*10)+1;select * from information_schema.INNODB_TRX;commit;

这串代码不能折行,中间的 sleep 停留 不能太大,也就是模拟足够快的情况下,检查2次事务的ID是否有变化。可以发现,时间足够短的话,2次查询到的事务ID是一样的,并没有发生变化。大家也可以在自己的环境下试试。