SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY id DESC LIMIT 10\G;
レプリケーションをしているスレーブの有無確認
SHOW SLAVE HOSTS;
マスター/スレーブの状態確認
SHOW MASTER STATUS \G;
SHOW SLAVE STATUS \G;
インデックス確認
SHOW INDEX FROM mytable;
テーブルの情報取得
SHOW TABLE STATUS LIKE 'mytable'\G
プロセスの各ステータスで要した時間を調査
SHOW PROFILE;
権限一覧取得
SELECT user,host FROM mysql.user ORDER BY user,host;
スロークエリー発行
SELECT SLEEP(@@long_query_time+1);
権限詳細取得
SHOW GRANTS FOR `[user]`@`[host]`;
サイズの大きいテーブルを順番に表示(現在のデータベース)
SELECT
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
floor(data_length+index_length) as Allsize,
floor(data_length) as Dsize,
floor(index_length) as Isize
FROM information_schema.tables
WHERE table_schema=database()
ORDER BY (data_length+index_length) DESC;
SELECT now();
SELECT * FROM information_schema.PROCESSLIST ORDER BY TIME DESC;
SELECT * FROM information_schema.innodb_lock_waits \G
SELECT * FROM information_schema.innodb_trx \G
SELECT * FROM information_schema.innodb_locks\G
SELECT * FROM performance_schema.threads \G
SHOW ENGINE INNODB STATUS \G
コマンドの結果を定期的に取得
$ while true; do \
LANG=C TZ=UTC date ; \
mysql --host xxxxxxxxxxxxx.us-east-1.rds.amazonaws.com \
--port 3306 --user username -ppassword \
--execute 'SHOW FULL PROCESSLIST \G'; sleep 60; done \
| tee show-full-processlist.`TZ=UTC date +%Y%m%d-%H%M%S`.txt
[client]
host = yourdb.ci52xxxxxxx.us-east-1.rds.amazonaws.com
user = youruser
password = XXXXXXXX
$ while true; do \
LANG=C TZ=UTC date ; \
mysql --defaults-file=/home/ec2-user/.my.cnf.caseXXXXX \
--execute 'SHOW FULL PROCESSLIST \G'; sleep 60; done \
| tee show-full-processlist.`TZ=UTC date +%Y%m%d-%H%M%S`.txt
ロングトランザクションがあるかどうか確認するクエリ
SELECT a.trx_id, a.trx_mysql_thread_id, a.trx_state, a.trx_started, TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "running_sec", 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;
history list length を確認するクエリ
select NAME, COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';
待機しているトランザクションと待機させているトランザクションを確認するクエリ
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
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;