7.?鎖的監(jiān)控及處理
7.1?鎖等待模擬
##?tx1:
USE?test
UPDATE?t100w?SET?k1='av'?WHERE?id=10;
##?tx2:
USE?test
UPDATE??t100w?SET?k1='az'?WHERE?id=10;
監(jiān)控鎖狀態(tài):
##?1.?看有沒有鎖等待
SHOW??STATUS?LIKE?'innodb_row_lock%';
##?2.?查看哪個(gè)事務(wù)在等待(被阻塞了)
USE?information_schema
SELECT?*?FROM?information_schema.INNODB_TRX?WHERE?trx_state='LOCK?WAIT';
trx_id?:?事務(wù)ID號(hào)
trx_state?:?當(dāng)前事務(wù)的狀態(tài)
trx_mysql_thread_id:連接層的,連接線程ID(SHOW?PROCESSLIST?===>Id或trx_id?)
trx_query?:?當(dāng)前被阻塞的操作(一般是要丟給開發(fā)的)
7.3.查看鎖源,誰(shuí)鎖的我!
SELECT?*?FROM?sys.innodb_lock_waits;?????##?====>被鎖的和鎖定它的之間關(guān)系
locked_table?:?哪張表出現(xiàn)的等待
waiting_trx_id:?等待的事務(wù)(與上個(gè)視圖trx_id?對(duì)應(yīng))
waiting_pid???:?等待的線程號(hào)(與上個(gè)視圖trx_mysql_thread_id)
blocking_trx_id?:?鎖源的事務(wù)ID
blocking_pid????:?鎖源的線程號(hào)
7.4.?找到鎖源的thread_id
SELECT?*?FROM?performance_schema.threads?WHERE?processlist_id=6;
====>?28
7.5.?找到鎖源的SQL語(yǔ)句
--?當(dāng)前在執(zhí)行的語(yǔ)句
SELECT?*?FROM?performance_schema.`events_statements_current`?WHERE?thread_id=28;
--?執(zhí)行語(yǔ)句的歷史
SELECT?*?FROM?performance_schema.`events_statements_history`?WHERE?thread_id=31;
得出結(jié)果,丟給開發(fā)
表信息
被阻塞的
鎖源SQL
處理方案：
1.?經(jīng)過以上步驟，
a.?被鎖的語(yǔ)句掰派，表信息榄檬，鎖的類型旺坠，PID
b.?鎖源的相關(guān)信息，PID砰蠢，SQL_THREAD_ID杭棵，SQL_TEXT
c.?和開發(fā)進(jìn)行溝通：1.?可不可以kill。2.?業(yè)務(wù)邏輯有沒有問題屡谐。
d.?DBA:?1.索引合不合理（explain）?。?2.?輔助判斷語(yǔ)句的邏輯問題蝌数。RC級(jí)別愕掏。
練習(xí):
一鍵獲得以上信息,請(qǐng)寫出具體的SQL語(yǔ)句
7.6?優(yōu)化項(xiàng)目:鎖的監(jiān)控及處理
1.?背景:
硬件環(huán)境:?DELL?R720,E系列16核,48G?MEM,SAS*900G*6,RAID10
在例行巡檢時(shí),發(fā)現(xiàn)9-11點(diǎn)時(shí)間段的CPU壓力非常高(80-90%)
2.?項(xiàng)目的職責(zé)
2.1?通過top詳細(xì)排查,發(fā)現(xiàn)mysqld進(jìn)程占比達(dá)到了700-800%
2.2?其中有量的CPU是被用作的SYS和WAIT,us處于正常
2.3?懷疑是MySQL?鎖?或者SQL語(yǔ)句出了問題
2.4?經(jīng)過排查slowlog及鎖等待情況,發(fā)現(xiàn)有大量鎖等待及少量慢語(yǔ)句
(1)?pt-query-diagest?查看慢日志
(2)?鎖等待有沒有?
db03?[(none)]>show?status?like?'innodb_row_lock%';
+-------------------------------+-------+
|?Variable_name?????????????????|?Value?|
+-------------------------------+-------+
|?Innodb_row_lock_current_waits?|?0?????|
|?Innodb_row_lock_time??????????|?0?????|
|?Innodb_row_lock_time_avg??????|?0?????|
|?Innodb_row_lock_time_max??????|?0?????|
|?Innodb_row_lock_waits?????????|?0?????|
+-------------------------------+-------+
情況一:
有100多個(gè)current_waits,說明當(dāng)前很多鎖等待情況
情況二:
1000多個(gè)lock_waits,說明歷史上發(fā)生過的鎖等待很多
2.5?查看那個(gè)事務(wù)在等待(被阻塞了)
2.6?查看鎖源事務(wù)信息(誰(shuí)鎖的我)
2.7?找到鎖源的thread_id
2.8?找到鎖源的SQL語(yǔ)句
3.?找到語(yǔ)句之后,和應(yīng)用開發(fā)人員進(jìn)行協(xié)商
(1)
開發(fā)人員描述,此語(yǔ)句是事務(wù)掛起導(dǎo)致
我們提出建議是臨時(shí)kill?會(huì)話,最終解決問題
(2)
開發(fā)人員查看后,發(fā)現(xiàn)是業(yè)務(wù)邏輯問題導(dǎo)致的死鎖,產(chǎn)生了大量鎖等待
臨時(shí)解決方案,將阻塞事務(wù)的會(huì)話kill掉.
最終解決方案,修改代碼中的業(yè)務(wù)邏輯
項(xiàng)目結(jié)果:
經(jīng)過排查處理,鎖等待的個(gè)數(shù)減少80%.解決了CPU持續(xù)峰值的問題.
鎖監(jiān)控設(shè)計(jì)到的命令:
show?status?like?'innodb_rows_lock%'
select?*?from?information_schema.innodb_trx;
select?*?from?sys.innodb_lock_waits;
select?*?from?performance_schema.threads;
select?*?from?performance_schema.events_statements_current;
select?*?from?performance_schema.events_statements_history;
MySQL總結(jié)
