異匙浚現(xiàn)象
大量的慢SQL導(dǎo)致8核CPU全部打滿，解決前后CPU如圖
image.png
實例基礎(chǔ)信息：
- 數(shù)據(jù)庫版本：MariaDB10.0.27
- 硬件信息：8核cpu+16G內(nèi)存+500GSSD
- 數(shù)據(jù)庫中數(shù)據(jù)量> innodb buffer pool配置
- 表結(jié)構(gòu)
CREATE TABLE `tablename` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`gid` varchar(36) NOT NULL COMMENT '全局標(biāo)識',
`create_time` int(11) NOT NULL COMMENT '創(chuàng)建時間',
`update_time` int(11) NOT NULL COMMENT '更新時間',
`biz_type` varchar(16) DEFAULT NULL COMMENT '業(yè)務(wù)號',
`biz_chnl` int(11) NOT NULL DEFAULT '-1' COMMENT '業(yè)務(wù)渠道編號',
`user_gid` char(36) NOT NULL COMMENT '用戶全局唯一標(biāo)識',
`trans_gid` char(36) NOT NULL COMMENT '交易唯一標(biāo)識,消息類型與gid關(guān)系為：0：額度變更記錄gid, 1:借款gid, 2:還款gid',
`msg_type` int(11) NOT NULL COMMENT '消息類型：0：用戶額度變更靡菇，1：借款狀態(tài)變更鸽斟，2：還款狀態(tài)變更',
`msg_status` int(11) NOT NULL COMMENT '消息狀態(tài)：0：處理中挚冤，1：處理成功，2：處理失敗',
`msg_content` varchar(4096) NOT NULL COMMENT '消息內(nèi)容JSON格式',
`return_code` varchar(16) DEFAULT NULL COMMENT '返回狀態(tài)碼',
`return_desc` varchar(4000) DEFAULT NULL COMMENT '狀態(tài)描述',
`trans_return_code` varchar(16) DEFAULT NULL COMMENT '交易狀態(tài)碼',
`trans_return_desc` varchar(255) DEFAULT NULL COMMENT '交易結(jié)果描述',
`retry_next_time` int(11) NOT NULL DEFAULT '0' COMMENT '下次重試時間',
`retry_num` int(11) NOT NULL DEFAULT '0' COMMENT '重試次數(shù)',
`status` int(11) NOT NULL DEFAULT '0' COMMENT '發(fā)送狀態(tài)：0 準(zhǔn)備就緒庶近，1 發(fā)送成功翁脆， 2 發(fā)送失敗，3 結(jié)果未知',
`is_valid` bit(1) NOT NULL DEFAULT b'1' COMMENT '是否有效',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_gid` (`gid`),
KEY `idx_create_time` (`create_time`),
KEY `idx_update_time` (`update_time`),
KEY `idx_status` (`status`),
KEY `idx_bizt_chnl` (`biz_type`,`biz_chnl`),
KEY `idx_return_trans_code` (`return_code`,`trans_return_code`),
KEY `idx_trans_gid` (`trans_gid`) USING BTREE,
KEY `idx_user_gid` (`user_gid`)
) ENGINE=InnoDB AUTO_INCREMENT=9630777 DEFAULT CHARSET=utf8 COMMENT='x x x x'
- 表數(shù)據(jù)量
MariaDB [sailfish]> select count(*) from tablename;
+----------+
| count(*) |
+----------+
| 9630917 |
+----------+
修改SQL
-開發(fā)原始SQL
MariaDB [sailfish]> SELECT id, gid,create_time,update_time,biz_type,biz_chnl,user_gid,trans_gid,msg_type,msg_status,msg_content,return_code,return_desc,trans_return_code,trans_return_desc,retry_next_time,retry_num,status,is_valid FROM tablename WHERE ( status = 2 AND biz_type = 'swqian' AND retry_next_time < 1511333699 ) order by id limit 500;
Empty set (15.40 sec)
MariaDB [sailfish]> explain SELECT id, gid,create_time,update_time,biz_type,biz_chnl,user_gid,trans_gid,msg_type,msg_status,msg_content,return_code,return_desc,trans_return_code,trans_return_desc,retry_next_time,retry_num,status,is_valid FROM tablename WHERE ( status = 2 AND biz_type = 'swqian' AND retry_next_time < 1511333699 ) order by id limit 500;
+------+-------------+--------------------+-------+--------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+-------+--------------------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | tablename | index | idx_status,idx_bizt_chnl | PRIMARY | 4 | NULL | 6578 | Using where |
+------+-------------+--------------------+-------+--------------------------+---------+---------+------+------+-------------+
-修改SQL去掉limit 500
MariaDB [sailfish]> SELECT id, gid,create_time,update_time,biz_type,biz_chnl,user_gid,trans_gid,msg_type,msg_status,msg_content,return_code,return_desc,trans_return_code,trans_return_desc,retry_next_time,retry_num,status,is_valid FROM tablename WHERE ( status = 2 AND biz_type = 'swqian' AND retry_next_time < 1511333699 ) order by id;
Empty set (1.86 sec)
MariaDB [sailfish]> explain SELECT id, gid,create_time,update_time,biz_type,biz_chnl,user_gid,trans_gid,msg_type,msg_status,msg_content,return_code,return_desc,trans_return_code,trans_return_desc,retry_next_time,retry_num,status,is_valid FROM tablename WHERE ( status = 2 AND biz_type = 'swqian' AND retry_next_time < 1511333699 ) order by id;
+------+-------------+--------------------+------+--------------------------+---------------+---------+-------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+------+--------------------------+---------------+---------+-------+--------+----------------------------------------------------+
| 1 | SIMPLE | tablename | ref | idx_status,idx_bizt_chnl | idx_bizt_chnl | 51 | const | 708040 | Using index condition; Using where; Using filesort |
+------+-------------+--------------------+------+--------------------------+---------------+---------+-------+--------+----------------------------------------------------+
- 修改SQL去掉order by limit 500（第一次解決方案鼻种，剛開始有效果反番，一周后查詢時間又逐漸增長，cpu又打滿叉钥，和之前一樣）
MariaDB [sailfish]> SELECT id, gid,create_time,update_time,biz_type,biz_chnl,user_gid,trans_gid,msg_type,msg_status,msg_content,return_code,return_desc,trans_return_code,trans_return_desc,retry_next_time,retry_num,status,is_valid FROM tablename WHERE ( status = 2 AND biz_type = 'swqian' AND retry_next_time < 1511333699 );
Empty set (2.18 sec)
MariaDB [sailfish]> explain SELECT id, gid,create_time,update_time,biz_type,biz_chnl,user_gid,trans_gid,msg_type,msg_status,msg_content,return_code,return_desc,trans_return_code,trans_return_desc,retry_next_time,retry_num,status,is_valid FROM tablename WHERE ( status = 2 AND biz_type = 'swqian' AND retry_next_time < 1511333699 );
+------+-------------+--------------------+------+--------------------------+---------------+---------+-------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+------+--------------------------+---------------+---------+-------+--------+------------------------------------+
| 1 | SIMPLE | tablename | ref | idx_status,idx_bizt_chnl | idx_bizt_chnl | 51 | const | 708040 | Using index condition; Using where |
+------+-------------+--------------------+------+--------------------------+---------------+---------+-------+--------+------------------------------------+
索引更改
- 根據(jù)where條件增加組合索引前
MariaDB [test]> SELECT id, gid, create_time, update_time, biz_type, biz_chnl, user_gid, trans_gid, msg_type, msg_status, msg_content, return_code, return_desc, trans_return_code, trans_return_desc, retry_next_time, retry_num, status, is_valid FROM tablename WHERE ( status = 2 AND biz_type = 'swqian' AND retry_next_time < 1511483534 );
Empty set (13.34 sec)
MariaDB [test]> explain SELECT id, gid, create_time, update_time, biz_type, biz_chnl, user_gid, trans_gid, msg_type, msg_status, msg_content, return_code, return_desc, trans_return_code, trans_return_desc, retry_next_time, retry_num, status, is_valid FROM tablename WHERE ( status = 2 AND biz_type = 'swqian' AND retry_next_time < 1511483534 );
+------+-------------+--------------------+------+----------------------------------------------+---------------+---------+-------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+------+----------------------------------------------+---------------+---------+-------+--------+------------------------------------+
| 1 | SIMPLE | tablename | ref | idx_status,idx_bizt_chnl,idx_retry_next_time | idx_bizt_chnl | 51 | const | 770126 | Using index condition; Using where |
+------+-------------+--------------------+------+----------------------------------------------+---------------+---------+-------+--------+------------------------------------+
1 row in set (0.06 sec)
- 根據(jù)where條件增加組合索引后
pt-online-schema-change -uroot -pxxxxxx --charset=utf8 --alter="add index idx_multi(`status`,`biz_type`)" --dry-run --nocheck-replication-filters --recursion-method=none --print D=sailfish,t= tablename
MariaDB [test]> SELECT id, gid, create_time, update_time, biz_type, biz_chnl, user_gid, trans_gid, msg_type, msg_status, msg_content, return_code, return_desc, trans_return_code, trans_return_desc, retry_next_time, retry_num, status, is_valid FROM tablename WHERE ( status = 2 AND biz_type = 'swqian' AND retry_next_time < 1511483534 );
Empty set (0.06 sec)
MariaDB [test]> explain SELECT id, gid, create_time, update_time, biz_type, biz_chnl, user_gid, trans_gid, msg_type, msg_status, msg_content, return_code, return_desc, trans_return_code, trans_return_desc, retry_next_time, retry_num, status, is_valid FROM tablename WHERE ( status = 2 AND biz_type = 'swqian' AND retry_next_time < 1511483534 );
+------+-------------+--------------------+------+--------------------------------------------------------+-----------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------------------+------+--------------------------------------------------------+-----------+---------+-------------+------+------------------------------------+
| 1 | SIMPLE | tablename | ref | idx_status,idx_bizt_chnl,idx_retry_next_time,idx_multi | idx_multi | 55 | const,const | 1 | Using index condition; Using where |
+------+-------------+--------------------+------+--------------------------------------------------------+-----------+---------+-------------+------+------------------------------------+
1 row in set (0.01 sec)
增加索引時遇到的問題
- 使用pt-online-schema-change更改表結(jié)構(gòu)時罢缸，因為當(dāng)時有40多條慢查詢跑著，pt-online-schema-change被阻塞投队，等待2分鐘效果很差枫疆，中建表只有幾百條數(shù)據(jù)，停止pt-online-schema-change并刪除中間表敷鸦、觸發(fā)器
- 與開發(fā)溝通這40多條慢SQL可以臨時注釋息楔，結(jié)合開發(fā)、運維注釋掉該功能
- 使用pt-online-schema-change更改表結(jié)構(gòu)扒披，用時10分鐘值依，重新恢復(fù)業(yè)務(wù)，cpu趨于穩(wěn)定碟案，如第一張圖
定位問題使用的工具
- pmm監(jiān)控慢SQL鳞滨、用戶cpu、mysql user statics
- mariadb server audit審計日志
- elk顯示每秒慢SQL查詢量
- linux：top
- mysql 信息統(tǒng)計：select * from information_schema.processlist where command='query' order by time\G
