1、查看監控，發現整點時間有寫IO過高情況 2、iotop 分析確認io高峰是由mysql導致的 3、開啟general log，分析SQL set global general_log = on ; 4、觀察binlog 與 general log 發現 文件增長量不大，懷疑不是有Insert 與 ...
1、查看監控，發現整點時間有寫IO過高情況
2、iotop 分析確認io高峰是由mysql導致的
3、開啟general log，分析SQL set global general_log = on ; 4、觀察binlog 與 general log 發現 文件增長量不大，懷疑不是有Insert 與 update 與 delete 導致的 寫IO過高 5、show full processlist ;發現有慢SQL *************************** 6. row *************************** Id: 337153 User: user_car_bill Host: 192.168.3.10:63018 db: yoolifin Command: Query Time: 295 State: Creating sort index Info: SELECT 1 AS `Number of Records`, `自定義 SQL 查詢`.`實還金額` AS `實還金額`, `自定義 SQL 查詢`.`應還客戶數` AS `應還客戶數`, `自定義 SQL 查詢`.`應還金額` AS `應還金額`, `自定義 SQL 查詢`.`累計發過代扣客戶數` AS `累計發過代扣客戶數`, `自定義 SQL 查詢`.`累計客戶覆蓋率` AS `累計客戶覆蓋率`, `自定義 SQL 查詢`.`累計還款收回率` AS `累計還款收回率`, `自定義 SQL 查詢`.`還款日發過代扣客戶數` AS `還款日發過代扣客戶數`, `自定義 SQL 查詢`.`還款日客戶覆蓋率` AS `還款日客戶覆蓋率`, `自定義 SQL 查詢`.`還款日期` AS `還款日期`, `自定義 SQL 查詢`.`還款日還款收回率` AS `還款日還款收回率` FROM ( select a.dt 還款日期,a.c 應還客戶數,a.totle_repayment 應還金額,a.fact_repayment 實還金額, a.fact_repayment/a.totle_repayment 累計還款收回率, a.very_fact_repayment/a.totle_repayment 還款日還款收回率,t.c 還款日發過代扣客戶數,t.c/a.c 還款日客戶覆蓋率,tt.c 累計發過代扣客戶數,tt.c/a.c 累計客戶覆蓋率 FROM( select date(payment_date) dt,count(DISTINCT APP_NO) c,sum(totle_repayment) totle_repayment,sum(fact_repayment) fact_repayment ,sum(if(payment_date=fact_payment_date,fact_repayment,0)) very_fact_repayment from fin_repayment m where date(payment_date)>'2017-06-01' and date(payment_date)<=date(curdate() + INTERVAL 20 DAY) and DATE_FORMAT(payment_date,'%d') in ('05','20') and m.`status` in ('1','2','5') and ifnull(cut_chanl,0)<>3 group by date(payment_date) ) a left join ( select date(n.SUBMIT_TIME) dt,count(DISTINCT APP_NO) c from fin_back_info n where date(n.SUBMIT_TIME)>'2017-06-01' and DATE_FORMAT(n.SUBMIT_TIME,'%d') in ('05','20') and n.PAYMENT_DATE=DATE(n.SUBMIT_TIME) group by date(n.SUBMIT_TIME) ) t on a.dt=t.dt left join ( select date(n.PAYMENT_DATE) dt,count(DISTINCT APP_NO) c from fin_back_info n where date(n.SUBMIT_TIME)>'2017-06-01' group by date(n.PAYMENT_DATE) ) tt on a.dt=tt.dt ) `自定義 SQL 查詢` *************************** 6. row *************************** 6、分析慢SQL，產生大量的派生表，可能是由於記憶體不夠，寫入磁碟導致的IO過高
7、執行該SQL，觀看監控，產生IO高峰，確認為該SQL導致的IO瓶頸過高
