Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use performance_schema for fault diagnosis in mysql. I hope you will get something after reading this article. Let's discuss it together.

Instrunments: producer, which is used to collect event information generated by various operations in mysql. Corresponding to the configuration items in the configuration table, we can call them collection configuration items.

Consumers: consumers. The corresponding consumers are used to store the data collected from instruments. The configuration items in the configuration table can be called consumer storage configuration items.

Enable instruments for all waiting events:

Use performance_schema

Update setup_instruments set enabled='yes',timed='yes' where name like 'wait/%'

Select * from setup_instruments where name like 'wait/%'

Enable consumers for waiting events

Update setup_consumers set enabled='yes' where name like'% wait%'

Select * from setup_consumers where name like'% wait%'

Count the current waiting events in real time:

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

Find out who holds the global read lock:

Use the performance_schema.metadata_locks table to find out who holds the global read lock. In this table, the global read lock usually records the two explicit locks in which the object_type of the same session is global and commit, and lock_type is shared. LOCK_TYPE: INTENTION_EXCLUSIVE intention exclusive lock

Select * from performance_schema.metadata_locks where ownerthread readreadable idols sys.psroomthreadreadloadid (connection_id ())\ G

Find out that OWNER_THREAD_ID is the internal thread id, and you can query the threads table to find the corresponding session id:

Select * from performance_schema.threads where THREAD_ID in (31jue 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 the internal thread holding the lock has an id of 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 the internal thread holding the lock has an id of 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 intention exclusive lock

LOCK_DURATION: STATEMENT

LOCK_STATUS: the status of PENDING is pending, indicating that you are waiting for authorization

SOURCE: sql_base.cc:3190

OWNER_THREAD_ID: 30 blocked

OWNER_EVENT_ID: 2507

3 rows in set (0.04 sec)

Find out who holds the MDL lock:

One shared_write lock is in granted state, the rest are shared_upgradable and execlusive locks, shared_upgradable is in granted state, and exclusive is in pending state.

Select * from performance_schema.metadata_locks where ownerthread readreadable idols sys.psroomthreadreadloadid (connection_id ())\ G

Confirm whether there is an uncommitted transaction in the source blocking thread through the information_schema.innodb_trx table:

Select * from information_schema.innodb_trx\ G

Use performance_schema.events_statements_current to query the event information of a statement that a thread is executing or the last execution has completed:

Select * from performance_schema.events_statements_current where thread_id=11\ G

Find out who holds the table-level lock:

Find out the internal thread id that holds the table READ EXTERNAL table level lock

Select * from performance_schema.table_handles where ownerships thread readable cards 0\ G

Find out what sql the thread is executing:

Select * from performance_schema.events_statements_current where thread_id=30\ G

Find out the processlist_id for kill

Select * from performance_schema.threads where THREAD_ID = 30\ G

Find out who holds the row-level lock:

Mysql8

Select * from performance_schema.data_locks\ G

Mysql 5.7

Select * from sys.innodb_lock_waits\ G

Query the most recent topsql statement:

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 currentless schemas, performing schemas' order by timer_wait desc limit 10\ G

Sql after statistics:

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

View recent failed sql statements:

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 summed errorsreceived 0\ G

View the execution phase and progress information of the sql statement:

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

View the whole process of statement execution and the time cost of each process, etc.

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

View the progress information of sql statement execution:

Select * from sys.session where connexidally connected connectionroomid ()\ G

Query the most recent transaction execution information:

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

Query the error details of multithreaded replication:

Show slave status\ G

Select * from performance_schema.replication_applier_status_by_worker where lastworthy errorships messageworthy errors'\ G

After reading this article, I believe you have a certain understanding of "how to use performance_schema for fault diagnosis in mysql". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.