现象：
- 程序大量SQL运行超时;
- 数据库中大量SQL长时间处于Updating阶段；
初步检查：
- 查看系统状态，CPU有24个核，只有一个核user利用率100%，其余核的idle基本上100%；
- 内存、IO.util都正常；
- 查看processlist，所有UPDATE语句长时间处于Updating阶段，其它类型的SQL没有Block或者运行缓慢现象；
进一步排查：
- 分析运行缓慢的SQL，发现运行缓慢的Update语句操作的都是同一张表；
- 执行缓慢的Update语句总共有两类：一类是通过主键更新一行数据；另一类是根据状态字段批量更新数据；Schema和SQL示例如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<code>-- Schema CREATE TABLE `general_mail` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL DEFAULT '0', `value` text NOT NULL, `status` tinyint(4) unsigned NOT NULL DEFAULT '0', `type` tinyint(4) unsigned NOT NULL DEFAULT '0', ...... PRIMARY KEY (`id`), KEY `idx_status_type` (`status`,`type`), ) ENGINE=InnoDB; -- 根据主键进行更新 UPDATE tbl_A SET status=N, value='.....', user_id=N WHERE id = N; -- 根据状态进行更新 UPDATE tbl_A SET status=N WHERE (status=N or status=N) and user_id=N limit N; </code> |
基本可以推测是根据状态字段更新数据的SQL导致的问题。进一步查看INNODB STATUS，发现如下记录：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<code>------------------ ---TRANSACTION 43DF0254C, ACTIVE 51 sec updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 118879, OS thread handle 0x7f51950b6700, query id 18995841 10.0.0.0 mysql_user Updating UPDATE tbl_A SET status=N, value='.....', user_id=N WHERE id = N ------- TRX HAS BEEN WAITING 51 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2186 page no 188857 n bits 688 index `idx_status_type` of table `db`.`tbl_A` trx id 43DF0254C lock_mode X locks gap before rec insert intention waiting Record lock, heap no 497 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 1; hex 02; asc ;; 1: len 1; hex 01; asc ;; 2: len 8; hex 00000000011c1f0f; asc ;; ------------------ ---TRANSACTION 43DF0216B, ACTIVE 65 sec fetching rows, thread declared inside InnoDB 155 mysql tables in use 1, locked 1 794767 lock struct(s), heap size 71694776, 13900037 row lock(s) MySQL thread id 118812, OS thread handle 0x7f51951dc700, query id 18995134 10.0.0.1 mysql_user Searching rows for update UPDATE tbl_A SET status=N WHERE (status=N or status=N) and user_id=N limit N </code> |
这下可以解释为什么只有一个CPU核在运行了。第二条SQL通过索引加扫表的方式，寻找符合条件的数据，这条SQL消耗了一个CPU核。查找数据的同时，此SQL加了gap锁，导致其它更新同一张表的SQL一直在等待锁的释放，因此其它CPU核基本处于空闲状态。
解决
找到问题的根源，解决起来就比较简单了，分析一下数据，发现索引(user_id, status)的区分度还不错，新增索引(user_id, status)后，问题解决。
3 Comments
博主，您好！跪求运维部的联系方式，我是贵公司2015校招的应聘者，申请岗位是运维部的系统工程师，自笔试通过之后，面试官先后打三次电话面试我，但是由于种种客观因素，没有面试成，由于面试官是用贵公司的公共号打过来的，所以我拨回去也无法联系到面试官，不知道您能不能给个运维部的联系方式，谢谢啦~~~，我的邮箱chrislee8610@foxmail.com
且行且珍惜，年轻错过机会没关系，以后还会有很多机会，要懂得把握。
楼上的发错地了吧，不用“跪求”吧