This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Don’t you think so that finding Long Running Queries are a very common requirement for a Database Administrator? ‘
Yes, Database Administrators are always looking for a list of Long Running Queries.
In this post, I am sharing scripts to find Long Running Queries or Transactions of MySQL Database Server.
MySQL DBA can use this script to take necessary steps against the bad and long running queries which increase the overall performance of MySQL Database Server.
In below script, I set 59 seconds internal to find long running query since last 59 seconds.
As per your requirement, you can change this value.
Here, You can also visit few related articles.
MySQL: How to Log General and Long Running Queries into Log Table
MySQL: Using SQL Query Profiler finds total execution time and CPU information of the Queries
Script without using Performance_schema:
|
1 2 3 4 5 6 7 8 9 |
SELECT trx.trx_id ,trx.trx_started ,trx.trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX AS trx INNER JOIN INFORMATION_SCHEMA.PROCESSLIST AS pl ON trx.trx_mysql_thread_id = pl.id WHERE trx.trx_started < CURRENT_TIMESTAMP - INTERVAL 59 SECOND AND pl.user <> 'system_user'; |
Script using Performance_schema:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT pl.id 'PROCESS ID' ,trx.trx_started ,esh.event_name 'EVENT NAME' ,esh.sql_text 'SQL' FROM information_schema.innodb_trx AS trx INNER JOIN information_schema.processlist pl ON trx.trx_mysql_thread_id = pl.id INNER JOIN performance_schema.threads th ON th.processlist_id = trx.trx_mysql_thread_id INNER JOIN performance_schema.events_statements_history esh ON esh.thread_id = th.thread_id WHERE trx.trx_started < CURRENT_TIME - INTERVAL 59 SECOND AND pl.user <> 'system_user' ORDER BY esh.EVENT_ID; |
