mysql中怎么利用performance_schema進(jìn)行故障診斷

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

instrunments:生產(chǎn)者，用于采集mysql中各種操作產(chǎn)生的事件信息，對(duì)應(yīng)配置表中的配置項(xiàng)，我們可以稱(chēng)之為采集配置項(xiàng)。

consumers:消費(fèi)者，對(duì)應(yīng)的消費(fèi)者用于存儲(chǔ)來(lái)自instruments采集的數(shù)據(jù)，對(duì)應(yīng)配置表的配置項(xiàng)，我們可以稱(chēng)之為消費(fèi)存儲(chǔ)配置項(xiàng)。

啟用所有等待事件的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%';

實(shí)時(shí)統(tǒng)計(jì)當(dāng)前等待事件：

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;

找出誰(shuí)持有全局讀鎖：

通過(guò)performance_schema.metadata_locks 表來(lái)排查誰(shuí)持有全局讀鎖，全局讀鎖在該表中通常記錄著同一個(gè)會(huì)話的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為內(nèi)部線程id,可以查詢(xún)threads表找出對(duì)應(yīng)會(huì)話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 持有鎖的內(nèi)部線程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 持有鎖的內(nèi)部線程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  狀態(tài)為pending，表示正在等待被授權(quán)

               SOURCE: sql_base.cc:3190

      OWNER_THREAD_ID: 30 被阻塞

       OWNER_EVENT_ID: 2507

3 rows in set (0.04 sec)

找出誰(shuí)持有MDL鎖：

一個(gè)shared_write鎖處于granted狀態(tài)，其余是shared_upgradable、execlusive鎖，shared_upgradable處于granted狀態(tài)，exclusive處于pending狀態(tài)

select * from performance_schema.metadata_locks where owner_thread_id!=sys.ps_thread_id(connection_id())\G;

通過(guò)information_schema.innodb_trx表確認(rèn)源阻塞線程是否存在一個(gè)沒(méi)有提交的事務(wù)：

select * from information_schema.innodb_trx\G;

通過(guò)performance_schema.events_statements_current來(lái)查詢(xún)某個(gè)線程正在執(zhí)行或者最后一次執(zhí)行完成的語(yǔ)句事件信息：

select * from performance_schema.events_statements_current where thread_id=11\G;

找出誰(shuí)持有表級(jí)鎖：

找出持有表READ EXTERNAL表級(jí)鎖的內(nèi)部線程id

select * from performance_schema.table_handles where owner_thread_id!=0\G;

找出線程正在執(zhí)行什么sql:

select * from performance_schema.events_statements_current where thread_id=30\G;

找出processlist_id來(lái)進(jìn)行kill

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

找出誰(shuí)持有行級(jí)鎖：

mysql8

select * from performance_schema.data_locks\G;

mysql 5.7

select * from sys.innodb_lock_waits\G;

查詢(xún)最近的topsql語(yǔ)句：

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;

統(tǒng)計(jì)后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;

查看最近執(zhí)行失敗的sql語(yǔ)句：

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語(yǔ)句執(zhí)行階段和進(jìn)度信息：

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%';

查看語(yǔ)句執(zhí)行全過(guò)程以及每個(gè)過(guò)程時(shí)間開(kāi)銷(xiāo)等。

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語(yǔ)句執(zhí)行進(jìn)度信息：

select  * from sys.session where conn_id!=connection_id()\G;

查詢(xún)最近的事務(wù)執(zhí)行信息：

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;

查詢(xún)多線程復(fù)制報(bào)錯(cuò)詳情：

show slave status\G;

select * from performance_schema.replication_applier_status_by_worker where last_error_message!=''\G

看完了這篇文章，相信你對(duì)“mysql中怎么利用performance_schema進(jìn)行故障診斷”有了一定的了解，如果想了解更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！