トラブルシューティング

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
  • エンジン内部のクエリー状況等調査
SELECT now();SELECT * FROM information_schema.PROCESSLIST ORDER BY TIME DESC;
SELECT now();SELECT * FROM information_schema.innodb_lock_waits \G
SELECT now();SELECT * FROM information_schema.innodb_trx \G
SELECT now();SELECT * FROM information_schema.innodb_locks\G
SELECT now();SELECT * FROM performance_schema.threads \G
SELECT now();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();

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です