Lately we experienced huge lags on one of our replica. Therefore I had to check queries and locks on tables.
- 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
THREAD_ID, SOURCE, EVENT_NAME,
timer_wait/1e12 as duration_sec,
lock_time/1e12 as lock_time_sec,
order by timer_wait desc;
Will show you longest queries and number of rows examined by them. This is very useful for investigation.
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 ;