mysql慢查詢導(dǎo)致的CPU打滿

異匙浚現(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
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末蟆淀,一起剝皮案震驚了整個濱河市拯啦,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌熔任,老刑警劉巖褒链,帶你破解...
    沈念sama閱讀 222,378評論 6 516
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場離奇詭異疑苔,居然都是意外死亡甫匹,警方通過查閱死者的電腦和手機,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 94,970評論 3 399
  • 文/潘曉璐 我一進店門惦费,熙熙樓的掌柜王于貴愁眉苦臉地迎上來兵迅,“玉大人,你說我怎么就攤上這事薪贫』屑” “怎么了?”我有些...
    開封第一講書人閱讀 168,983評論 0 362
  • 文/不壞的土叔 我叫張陵瞧省,是天一觀的道長扯夭。 經(jīng)常有香客問我,道長鞍匾,這世上最難降的妖魔是什么交洗? 我笑而不...
    開封第一講書人閱讀 59,938評論 1 299
  • 正文 為了忘掉前任,我火速辦了婚禮橡淑,結(jié)果婚禮上构拳,老公的妹妹穿的比我還像新娘。我一直安慰自己梁棠,他們只是感情好置森,可當(dāng)我...
    茶點故事閱讀 68,955評論 6 398
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著掰茶,像睡著了一般暇藏。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上濒蒋,一...
    開封第一講書人閱讀 52,549評論 1 312
  • 那天盐碱,我揣著相機與錄音,去河邊找鬼沪伙。 笑死瓮顽,一個胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的围橡。 我是一名探鬼主播暖混,決...
    沈念sama閱讀 41,063評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼翁授!你這毒婦竟也來了拣播?” 一聲冷哼從身側(cè)響起晾咪,我...
    開封第一講書人閱讀 39,991評論 0 277
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎贮配,沒想到半個月后谍倦,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 46,522評論 1 319
  • 正文 獨居荒郊野嶺守林人離奇死亡泪勒,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,604評論 3 342
  • 正文 我和宋清朗相戀三年昼蛀,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片圆存。...
    茶點故事閱讀 40,742評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡叼旋,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出沦辙,到底是詐尸還是另有隱情夫植,我是刑警寧澤,帶...
    沈念sama閱讀 36,413評論 5 351
  • 正文 年R本政府宣布怕轿,位于F島的核電站偷崩,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏撞羽。R本人自食惡果不足惜阐斜,卻給世界環(huán)境...
    茶點故事閱讀 42,094評論 3 335
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望诀紊。 院中可真熱鬧谒出,春花似錦、人聲如沸邻奠。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,572評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽碌宴。三九已至杀狡,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間贰镣,已是汗流浹背呜象。 一陣腳步聲響...
    開封第一講書人閱讀 33,671評論 1 274
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留碑隆,地道東北人恭陡。 一個月前我還...
    沈念sama閱讀 49,159評論 3 378
  • 正文 我出身青樓,卻偏偏與公主長得像上煤,于是被迫代替她去往敵國和親休玩。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 45,747評論 2 361

推薦閱讀更多精彩內(nèi)容