MySQL Scripts

scripts.mysql

/* Get currently running transactions */
select * from information_schema.innodb_trx

/* Get currently running transactions where tables in use */
select * from information_schema.innodb_trx where trx_tables_in_use = 1

/* show locked tables */
show open tables where in_use > 0 ;

SHOW ENGINE INNODB STATUS;

/* indicates the operations currently being performed by the set of threads */
show full processlist 

/* indicates the operations that are executing a query */
select * from information_schema.processlist where COMMAND = 'Query';

/* show long running transactions */
SELECT ps.id as processlist_id,
             trx_started, trx_isolation_level,
             esh.EVENT_ID,
             esh.TIMER_WAIT,
             esh.event_name as EVENT_NAME,
             esh.sql_text as 'SQL',
             esh.RETURNED_SQLSTATE, 
             esh.MYSQL_ERRNO, 
             esh.MESSAGE_TEXT, 
             esh.ERRORS, 
             esh.WARNINGS   
FROM information_schema.innodb_trx trx
JOIN information_schema.processlist ps ON trx.trx_mysql_thread_id = ps.id
LEFT JOIN performance_schema.threads th ON th.processlist_id = trx.trx_mysql_thread_id
LEFT JOIN performance_schema.events_statements_history esh ON esh.thread_id = th.thread_id
WHERE trx.trx_started < CURRENT_TIME - INTERVAL 300 SECOND
  AND ps.USER != 'SYSTEM_USER'
  
/* show long running transactions which are insert statements and have locked the tables */
SELECT ps.id as processlist_id,
             trx_started, trx_isolation_level,
             esh.EVENT_ID,
             esh.TIMER_WAIT,
             esh.event_name as EVENT_NAME,
             esh.sql_text as 'SQL',
             esh.RETURNED_SQLSTATE, 
             esh.MYSQL_ERRNO, 
             esh.MESSAGE_TEXT, 
             esh.ERRORS, 
             esh.WARNINGS   
FROM information_schema.innodb_trx trx
JOIN information_schema.processlist ps ON trx.trx_mysql_thread_id = ps.id
LEFT JOIN performance_schema.threads th ON th.processlist_id = trx.trx_mysql_thread_id
LEFT JOIN performance_schema.events_statements_history esh ON esh.thread_id = th.thread_id
WHERE trx.trx_started < CURRENT_TIME - INTERVAL 300 SECOND
	AND trx.trx_tables_in_use = 1
	AND esh.event_name = 'statement/sql/insert'
  	AND ps.USER != 'SYSTEM_USER'
  
/* Generate kill commands for process ids */
SELECT distinct(concat('KILL ', ps.id, ';'))
FROM information_schema.innodb_trx trx
JOIN information_schema.processlist ps ON trx.trx_mysql_thread_id = ps.id
LEFT JOIN performance_schema.threads th ON th.processlist_id = trx.trx_mysql_thread_id
LEFT JOIN performance_schema.events_statements_history esh ON esh.thread_id = th.thread_id
WHERE trx.trx_started < CURRENT_TIME - INTERVAL 300 SECOND
	AND trx.trx_tables_in_use = 1
	AND esh.event_name = 'statement/sql/insert'
  	AND ps.USER != 'SYSTEM_USER'
	
SELECT a.trx_id, a.trx_state, a.trx_started, 
      TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open", 
      a.trx_rows_modified, b.USER, b.host, b.db, b.command, b.time, b.state 
      from information_schema.innodb_trx a, information_schema.processlist b 
      where a.trx_mysql_thread_id=b.id 
      order by trx_started;

/* show global status values for innodb engine */
SHOW GLOBAL STATUS LIKE 'innodb_%';

/* show aurora replica lag */
SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec,
       oldest_read_view_trx_id , oldest_read_view_lsn
       from mysql.ro_replica_status;

/* check if event statement is enabled */
select * from performance_schema.setup_consumers where name like 'events%statement%';