The following query helps you to get more information on the mysql LOCK and the transactions holding the lock and the transactions waiting for the lock to be released – For mysql 5.7 - Find Waiting Transactions and the Locks blocking the resource required SELECT * FROM performance_schema.data_lock_waits SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS Transactions waiting for the locks to be released aka lock waits – Troubleshooting Lock Waits SELECT * FROM performance_schema.data_locks SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS The following query helps you find the locks – mysql 5.7 SELECT `DB`, AVG(`TIME`) FROM information_schema.processlistįind the current transactions and locks and transactions waiting for locks using the following query SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX – display average query time for each database – display processes associated with SELECT queries SELECT * FROM information_schema.processlist SELECT `HOST`, COUNT(*) FROM information_schema.processlist – display number of connections for each host SELECT `USER`, COUNT(*) FROM information_schema.processlist – display number of connections for each user Display the average query time for each database.Find the transactions that run a particular query with LIKE.check the activity of the specific user with where clause.Display the number of connections for each host.Display the number of connections for each user.You can do a lot of filtering and grouping with this command, Here are some examples This is the alternative if you want to apply group by on the PROCESS LIST If you want to use the where clause or sort along with it use the following alternate SELECT * FROM information_schema.processlist The output from the previous command is not sortable or searchable.
0 Comments
Leave a Reply. |