Debug MySQL performance Script.sql

-- run as root
TEE mysql_output.txt; 
SELECT VERSION(); 
SELECT NOW(); 
SHOW GLOBAL VARIABLES; 
SHOW MASTER STATUS; 
SHOW SLAVE STATUS\G 
SHOW GLOBAL STATUS; 
SHOW FULL PROCESSLIST; 
SHOW ENGINE INNODB STATUS\G 
SHOW FULL PROCESSLIST; 
-- Info on transactions and locks 
SELECT r.trx_wait_started AS wait_started, TIMEDIFF(NOW(), r.trx_wait_started) AS wait_age, 
rl.lock_table AS locked_table, rl.lock_index AS locked_index, rl.lock_type AS locked_type, 
r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_pid, r.trx_query AS waiting_query, 
rl.lock_id AS waiting_lock_id, rl.lock_mode AS waiting_lock_mode, b.trx_id AS blocking_trx_id, 
b.trx_mysql_thread_id AS blocking_pid, b.trx_query AS blocking_query, bl.lock_id AS blocking_lock_id, 
bl.lock_mode AS blocking_lock_mode 
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 
INNER JOIN information_schema.INNODB_LOCKS bl ON bl.lock_id = w.blocking_lock_id 
INNER JOIN information_schema.INNODB_LOCKS rl ON rl.lock_id = w.requested_lock_id 
ORDER BY r.trx_wait_started\G 
SHOW FULL PROCESSLIST; 
SELECT SLEEP(300); 
SHOW MASTER STATUS; 
SHOW SLAVE STATUS\G 
SHOW GLOBAL STATUS; 
SHOW FULL PROCESSLIST; 
SHOW ENGINE INNODB STATUS\G 
SHOW FULL PROCESSLIST; 
-- Info on transactions and locks 
SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, 
b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query, 
bl.lock_id blocking_lock_id, bl.lock_mode blocking_lock_mode, bl.lock_type blocking_lock_type, 
bl.lock_table blocking_lock_table, bl.lock_index blocking_lock_index, 
rl.lock_id waiting_lock_id, rl.lock_mode waiting_lock_mode, rl.lock_type waiting_lock_type, 
rl.lock_table waiting_lock_table, rl.lock_index waiting_lock_index 
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 
INNER JOIN information_schema.INNODB_LOCKS bl ON bl.lock_id = w.blocking_lock_id 
INNER JOIN information_schema.INNODB_LOCKS rl ON rl.lock_id = w.requested_lock_id\G 
SHOW FULL PROCESSLIST; 
SELECT ENGINE, COUNT(*), SUM(DATA_LENGTH), SUM(INDEX_LENGTH) 
FROM information_schema.TABLES 
GROUP BY ENGINE; 
STATUS; 
SELECT benchmark(50000000,(1234*5678/37485-1298+8596^2)); #should take less than 20 seconds 
SELECT * FROM performance_schema.setup_instruments WHERE name LIKE 'wait/sync%' AND (enabled='yes' OR timed='yes'); ##if you see any results, it means performance overhead is likely. 
SHOW ENGINE performance_schema STATUS; ##checking memory usage (last line). 
XA RECOVER; # if you had a crash, and these trx lurk around, innodb purge is prevented and ibdata will explode 
NOTEE; 


See also:

Popular posts from this blog

How to get Active Transactions and Locks from MySQL

Example of a PAC file