mysql中怎么利用performance

164次閱讀

沒有評論

共計 6066 個字符，預計需要花費 16 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 mysql 中怎么利用 performance_schema 進行故障診斷，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

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

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

看完了這篇文章，相信你對“mysql 中怎么利用 performance_schema 進行故障診斷”有了一定的了解，如果想了解更多相關知識，歡迎關注丸趣 TV 行業資訊頻道，感謝各位的閱讀！

正文完