现象:
- 程序大量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
且行且珍惜,年轻错过机会没关系,以后还会有很多机会,要懂得把握。
楼上的发错地了吧,不用“跪求”吧