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:
Comments
Post a Comment