Lately we experienced huge lags on one of our replica. Therefore I had to check queries and locks on tables.

Sources:

Activate performance_schema.metadata_locks:

  • activate without restart:
UPDATE performance_schema.setup_instruments
SET enabled = 'YES'
WHERE name = 'wait/lock/metadata/sql/mdl';
  • in config file: performance_schema_instrument = 'wait/lock/metadata/sql/mdl=ON'
  • see locks: SELECT * FROM performance_schema.metadata_locks

Queries statistics:

select
THREAD_ID, SOURCE, EVENT_NAME,
timer_wait/1e12 as duration_sec,
lock_time/1e12 as lock_time_sec,
CURRENT_SCHEMA,
SQL_TEXT,
ROWS_EXAMINED
from performance_schema.events_statements_history
order by timer_wait desc;

Will show you longest queries and number of rows examined by them. This is very useful for investigation.

Blocking logs:

SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_trx_id;

Show locked tables:

show open tables where In_Use > 0 ;