利用performance_schema進行故障診斷(mysql金字塔法則讀書筆記)

darren__chan發表於2019-11-24
ORMMySql筆記
instrunments:生產者，用於採集mysql中各種操作產生的事件資訊，對應配置表中的配置項，我們可以稱之為採集配置項。
consumers:消費者，對應的消費者用於儲存來自instruments採集的資料，對應配置表的配置項，我們可以稱之為消費儲存配置項。
啟用所有等待事件的instruments:
use performance_schema
update  setup_instruments set enabled='yes',timed='yes' where name like 'wait/%';
select * from setup_instruments where name like 'wait/%';

啟用等待事件的consumers
update setup_consumers set enabled='yes' where name like '%wait%';
select * from setup_consumers where name like '%wait%';

實時統計當前等待事件：
create view sys.test_waits as select sum(timer_wait) as timer_wait ,sum(number_of_bytes) as number_of_bytes,event_name,operation from events_waits_current where event_name !='idle' group by event_name,operation;
select sys.format_time(timer_wait),sys.format_bytes(number_of_bytes),event_name,operation from sys.test_waits where sys.format_time(timer_wait) not regexp 'ns|us' order by timer_wait desc;

select thread_id,event_name,sys.format_time(timer_wait),index_name,nesting_event_type,operation,number_of_bytes from events_waits_current where event_name !='idle' order by timer_wait desc;

找出誰持有全域性讀鎖：
透過performance_schema.metadata_locks 表來排查誰持有全域性讀鎖，全域性讀鎖在該表中通常記錄著同一個會話的object_type為global和commit、lock_type都為shared的兩把顯式鎖,  LOCK_TYPE: INTENTION_EXCLUSIVE 意向排他鎖
select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connection_id())\G;

找出 OWNER_THREAD_ID為內部執行緒id,可以查詢threads表找出對應會話id：

select * from performance_schema.threads where THREAD_ID in (31,30)\G;


*************************** 1. row ***************************

          OBJECT_TYPE: GLOBAL

        OBJECT_SCHEMA: NULL

          OBJECT_NAME: NULL

OBJECT_INSTANCE_BEGIN: 140514110025712

            LOCK_TYPE: SHARED

        LOCK_DURATION: EXPLICIT

          LOCK_STATUS: GRANTED

               SOURCE: lock.cc:1110

      OWNER_THREAD_ID: 31 持有鎖的內部執行緒id為31

       OWNER_EVENT_ID: 43

*************************** 2. row ***************************

          OBJECT_TYPE: COMMIT

        OBJECT_SCHEMA: NULL

          OBJECT_NAME: NULL

OBJECT_INSTANCE_BEGIN: 140514110303360

            LOCK_TYPE: SHARED

        LOCK_DURATION: EXPLICIT

          LOCK_STATUS: GRANTED

               SOURCE: lock.cc:1194

      OWNER_THREAD_ID: 31  持有鎖的內部執行緒id為31

       OWNER_EVENT_ID: 97

*************************** 3. row ***************************

          OBJECT_TYPE: GLOBAL

        OBJECT_SCHEMA: NULL

          OBJECT_NAME: NULL

OBJECT_INSTANCE_BEGIN: 140514110036384

            LOCK_TYPE: INTENTION_EXCLUSIVE n意向排他鎖

        LOCK_DURATION: STATEMENT

          LOCK_STATUS: PENDING  狀態為pending，表示正在等待被授權

               SOURCE: sql_base.cc:3190

      OWNER_THREAD_ID: 30 被阻塞

       OWNER_EVENT_ID: 2507

3 rows in set (0.04 sec)



找出誰持有MDL鎖：
一個shared_write鎖處於granted狀態，其餘是shared_upgradable、execlusive鎖，shared_upgradable處於granted狀態，exclusive處於pending狀態
select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connection_id())\G;
透過information_schema.innodb_trx表確認源阻塞執行緒是否存在一個沒有提交的事務：
select * from information_schema.innodb_trx\G;

透過performance_schema.events_statements_current來查詢某個執行緒正在執行或者最後一次執行完成的語句事件資訊：
select * from performance_schema.events_statements_current where thread_id=11\G;

找出誰持有表級鎖：
找出持有表 READ EXTERNAL表級鎖的內部執行緒id
select * from performance_schema.table_handles where owner_thread_id!=0\G;
找出執行緒正在執行什麼sql:
select * from performance_schema.events_statements_current where thread_id= 30\G;
找出processlist_id來進行kill

select * from performance_schema.threads where THREAD_ID =30\G;


找出誰持有行級鎖：

mysql8

select * from  performance_schema.data_locks\G;

mysql 5.7

select * from sys.innodb_lock_waits\G;


查詢最近的topsql語句：

select thread_id,event_name,source,sys.format_time(timer_wait),sys.format_time(lock_time),sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined from  performance_schema.events_statements_history where current_schema!='performance_schema' order by timer_wait desc limit 10\G;


統計後sql:

select schema_name,digest_text,COUNT_STAR,sys.format_time(sum_timer_wait) as sum_time,sys.format_time(min_timer_wait) as min_time, sys.format_time(avg_timer_wait) as avg_time,sys.format_time(max_timer_wait) as max_time,sys.format_time(sum_lock_time) as sum_lock_time,sum_rows_affected,sum_rows_sent,sum_rows_examined from performance_schema.events_statements_summary_by_digest where schema_name is not null order by count_star desc limit 10\G;


檢視最近執行失敗的sql語句：

select thread_id,event_name,source,sys.format_time(timer_wait) as exec_time, sys.format_time(lock_time) as lock_time,sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined ,mysql_errno from  performance_schema.events_statements_history where  mysql_errno=1064\G;


select thread_id,event_name,source,sys.format_time(timer_wait) as exec_time, sys.format_time(lock_time) as lock_time,sql_text,current_schema,message_text,rows_affected,rows_sent,rows_examined ,mysql_errno ,errors from performance_schema.events_statements_history where errors>0\G;


select schema_name,digest_text, COUNT_STAR ,sys.format_time(sum_timer_wait) as sum_time,sys.format_time(min_timer_wait) as min_time,sys.format_time(avg_timer_wait) as avg_time,sys.format_time(max_timer_wait) as max_time,sys.format_time(sum_lock_time) as sum_lock_time,sum_errors,first_seen,last_seen from performance_schema.events_statements_summary_by_digest where sum_errors!=0\G;

檢視sql語句執行階段和進度資訊：


use performance_schema
update  setup_instruments set enabled='yes',timed='yes' where name like 'stage/%';
update setup_consumers set enabled='yes' where name like '%stage%';

檢視語句執行全過程以及每個過程時間開銷等。
select thread_id,event_name,source,sys.format_time(timer_wait) as exec_time,work_completed,work_estimated from performance_schema.events_stages_history_long;

檢視sql語句執行進度資訊：
select  * from sys.session where conn_id!=connection_id()\G;

查詢最近的事務執行資訊：
use performance_schema
update  setup_instruments set enabled='yes',timed='yes' where name like 'transaction%';
update setup_consumers set enabled='yes' where name like '%transaction%';
select thread_id,event_name,state,trx_id,gtid,source,timer_wait,access_mode,isolation_level,autocommit,nesting_event_id,nesting_event_type from performance_schema.events_transactions_current\G;

select thread_id,event_name,state,trx_id,gtid,source,timer_wait,access_mode,isolation_level,autocommit,nesting_event_id,nesting_event_type from performance_schema.events_transactions_history_long\G;

查詢多執行緒複製報錯詳情：
show slave status\G;
select * from performance_schema.replication_applier_status_by_worker where last_error_message!=''\G







來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29863023/viewspace-2665462/，如需轉載，請註明出處，否則將追究法律責任。

相關文章