MySQL, PostgreSQL トラブルシューティング コマンド実行例 まとめ
トラブルシューティング
MySQL
- 設定確認
SHOW GLOBAL VARIABLES;
- 状態確認
SHOW GLOBAL STATUS;
- システム設定値確認(グローバル)
SHOW GLOBAL VARIABLES
- システム設定値確認(セッション)
SHOW (SESSION) VARIABLES
- Innodb 詳細確認
SHOW ENGINE INNODB STATUS\G;
- 実行しているクエリー
SHOW FULL PROCESSLIST \G;
- 実行したクエリー
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 CONCAT(table_schema, '.', table_name),
CONCAT(ROUND(table_rows / 1000000, 2), 'M') rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') idx,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
ROUND(index_length / data_length, 2) idxfrac
FROM information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;
ベンチマーク
- 指定したSQLの実行時間測定
SELECT BENCHMARK(1000000, SELECT * FROM users);
- テーブルDDL
SHOW CREATE TABLE mytable
- エンジン内部のクエリー状況等調査
MySQL 8.0未満
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
historyにパスワード等を残したくない場合 .my.cnf.XXXXXを以下の内容で作成してchmod 600を設定。
[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
インポート/エクスポート
$ mysqldump -u root -p -h xxxxxxxxxx.us-east-1.rds.amazonaws.com devdb > dump.sql
$ cat dump.sql | mysql -u root -p -h xxxxxxxxxx.us-east-1.rds.amazonaws.com proddb
各種メモリサイズ調査
- メモリ使用量分析
SELECT
@@key_buffer_size AS KEY_BUFFER_SIZE_Bytes,
@@query_cache_size AS QUERY_CACHE_SIZE_Bytes,
@@innodb_buffer_pool_size AS INNODB_BUFFER_POOL_SIZE_Bytes,
@@innodb_additional_mem_pool_size AS INNODB_ADDITIONAL_MEM_POOL_SIZE_Bytes,
@@innodb_log_buffer_size AS INNODB_LOG_BUFFER_SIZE_Bytes,
@@max_heap_table_size AS MAX_HEAP_TABLE_SIZE_Bytes,
@@read_buffer_size AS READ_BUFFER_SIZE_Bytes,
@@read_rnd_buffer_size AS READ_RND_BUFFER_SIZE_Bytes,
@@sort_buffer_size AS SORT_BUFFER_SIZE_Bytes,
@@join_buffer_size AS JOIN_BUFFER_SIZE_Bytes,
@@binlog_cache_size AS BINLOG_CACHE_SIZE_Bytes,
@@thread_stack AS THREAD_STACK_Bytes,
@@tmp_table_size AS TMP_TABLE_SIZE_Bytes,
@@max_allowed_packet AS MAX_ALLOWED_PACKET_Bytes,
@@max_connections AS MAX_CONNECTIONS,
(
@@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size
+ @@max_allowed_packet
) / (1024 * 1024 * 1024) AS PER_THREAD_MEMORY_GB,
(@@max_connections * (
@@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size
+ @@max_allowed_packet
)) / (1024 * 1024 * 1024) AS MAX_TOTAL_THREAD_MEMORY_GB,
(
@@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
+ @@max_heap_table_size) / (1024 * 1024 * 1024) AS MAX_GLOBAL_MEMORY_GB,
(
@@key_buffer_size
+ @@query_cache_size
+ @@innodb_buffer_pool_size
+ @@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
+ @@max_heap_table_size
+ @@max_connections * (
@@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size
+ @@max_allowed_packet
)
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB \G;
Others
MySQL
- DDL取得
SHOW CREATE TABLE <テーブル名>;
PostgreSQL
- DDL取得
$ pg_dump -st <テーブル名> -U <ユーザー名> -h <エンドポイント> <テータベース名>
参考
- MySQLインスタンスのメモリ領域
- グローバルメモリ領域
- query_cache
- thread_cache
- table_cache
- InnoDBテーブル用のメモリ領域
- innodb_buffer_pool
- innodb_log_buffer
- innodb_additional_mem_pool
- MyISAMテーブル用のメモリ領域
- key_buffer
- HEAPテーブル用のメモリ領域
- スレッド専用メモリ領域
- join_buffer
- read_buffer
- sort_buffer
- thread_stack
- binlog_cache
- max_allowed_packet
- OS管理領域
- OSバッファキャッシュ
- データベース領域
- MYIファイル
- MYDファイル
- InnoDBログファイル
- frmファイル
- バイナリログファイル
- InnoDBデータファイル
参考
- https://dev.mysql.com/doc/refman/5.6/ja/sql-syntax.html
- https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html
- https://qiita.com/yoheiW@github/items/23c5791b34da6f814056
- https://www.qoosky.io/techs/3a369dd466
- https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/
- https://open-groove.net/mysql/seek-mysql-memory/
- http://nippondanji.blogspot.jp/2011/12/mysql.html
- http://nippondanji.blogspot.jp/2009/03/mysql10.html
個人ブログ
- Yakst: https://yakst.com/ja/tags/mysql
- MySQLのメモリー使用量を最適化する設定のベストプラクティス: https://yakst.com/ja/posts/3983
- 漢(オトコ)のコンピュータ道
- http://nippondanji.blogspot.jp/search/label/mysql
- http://nippondanji.blogspot.jp/search/label/performance%20tuning
- MySQLを高速化する10の方法: http://nippondanji.blogspot.jp/2009/02/mysql10.html
- さらにMySQLを高速化する7つの方法: http://nippondanji.blogspot.jp/2009/03/mysql7.html
- InnoDBのログとテーブルスペースの関係: http://nippondanji.blogspot.jp/2009/01/innodb.html
- MySQLのEXPLAINを徹底解説!!: http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
- Using filesort: http://nippondanji.blogspot.jp/2009/03/using-filesort.html
- オトコのソートテクニック2008: http://nippondanji.blogspot.jp/2008/12/2008.html
- 限界までMySQLを使い尽くす!!: http://nippondanji.blogspot.jp/2009/05/mysql.html
- MySQLにおけるレプリケーション遅延の傾向と対策: http://nippondanji.blogspot.jp/2011/12/mysql.html
- 大人のためのInnoDBテーブルとの正しい付き合い方。: http://nippondanji.blogspot.jp/2010/09/innodb.html
- やってはいけない!!MySQLに悲鳴をあげさせる10の方法: http://nippondanji.blogspot.jp/2009/04/mysql10.html
- なぜMySQLのサブクエリは遅いのか。: http://nippondanji.blogspot.jp/2009/03/mysql_25.html
- 知って得するInnoDBセカンダリインデックス活用術!: http://nippondanji.blogspot.jp/2010/10/innodb.html
- InnoDBでCOUNT()を扱う際の注意事項あれこれ。: http://nippondanji.blogspot.jp/2010/03/innodbcount.html
- InnoDBのREPEATABLE READにおけるLocking Readについての注意点: http://nippondanji.blogspot.jp/2013/12/innodbrepeatable-readlocking-read.html
- Sergey Petrunia’s blog: http://s.petrunia.net/blog/?p=24
- MySQLのmetricに関する話: http://labs.gree.jp/blog/2018/06/16962/
- 誰も教えてくれなかったMySQLの障害解析方法: https://qiita.com/muran001/items/14f19959d4723ffc29cc
サンプルデータ
- https://downloads.mysql.com/docs/world.sql.gz
- https://github.com/awslabs/aws-database-migration-samples
Others
グローバルやスレッド変数を元にしたメモリ使用量推定用クエリー
https://dev.mysql.com/doc/refman/5.6/ja/memory-use.html
DELIMITER $$
DROP PROCEDURE IF EXISTS `my_memory` $$
CREATE PROCEDURE `my_memory` ()
BEGIN
DECLARE var VARCHAR(100);
DECLARE val VARCHAR(100);
DECLARE done INT;
DECLARE GLOBAL_SUM DOUBLE;
DECLARE PER_THREAD_SUM DOUBLE;
DECLARE MAX_CONN DOUBLE;
DECLARE HEAP_TABLE DOUBLE;
DECLARE TEMP_TABLE DOUBLE;
DECLARE CUR_GBLVAR CURSOR FOR SELECT * FROM information_schema.GLOBAL_VARIABLES;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SET GLOBAL_SUM=0;
SET PER_THREAD_SUM=0;
SET MAX_CONN=0;
SET HEAP_TABLE=0;
SET TEMP_TABLE=0;
OPEN CUR_GBLVAR;
mylp:LOOP
FETCH CUR_GBLVAR INTO var,val;
IF done=1 THEN
LEAVE mylp;
END IF;
IF var in ('key_buffer_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size','query_cache_size') THEN
SET GLOBAL_SUM=GLOBAL_SUM+val;
ELSEIF var in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','thread_stack','max_allowed_packet','net_buffer_length') THEN
SET PER_THREAD_SUM=PER_THREAD_SUM+val;
ELSEIF var in ('max_connections') THEN
SET MAX_CONN=val;
ELSEIF var in ('max_heap_table_size') THEN
SET HEAP_TABLE=val;
ELSEIF var in ('tmp_table_size','max_heap_table_size') THEN
SET TEMP_TABLE=if((TEMP_TABLE>val),TEMP_TABLE,val);
END IF;
END LOOP;
CLOSE CUR_GBLVAR;
select Global Buffers as Parameter,CONCAT(GLOBAL_SUM/(1024*1024),' M') as Value union
select Per Thread,CONCAT(PER_THREAD_SUM/(1024*1024),' M') union
select Maximum Connections,MAX_CONN union
select Total Memory Usage,CONCAT((GLOBAL_SUM + (MAX_CONN * PER_THREAD_SUM))/(1024*1024),' M') union
select + Per Heap Table,CONCAT(HEAP_TABLE / (1024*1024),' M') union
select + Per Temp Table,CONCAT(TEMP_TABLE / (1024*1024),' M') ;
END $$
DELIMITER ;
CALL my_memory();