MySQL: Script to find Long Running Queries and Transactions

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.

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;