RDBMS Engine Inspection for Troubleshooting

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

ロングトランザクションがあるかどうか確認するクエリ

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;

インポート/エクスポート

$ 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;

PostgreSQL

基本

SELECT CURRENT_TIMESTAMP;
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_locks;
SELECT * FROM pg_stat_database;
SELECT * FROM pg_stat_replication;

INSERT INTO pg_stat_activity_log SELECT now(), * from pg_stat_activity;のようにログテーブルに蓄積しておくのも手

DDL取得

$ pg_dump -st <スキーマ名>.<テーブル名> -U <ユーザー名> -h <エンドポイント> <テータベース名>

Oracle

DDL取得

set pages 0
set longchunksize 3000
set long 2000000000
select dbms_metadata.get_ddl('TABLE','テーブル名','スキーマ名(オプション)') from dual;

参考

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

PreviousRDS PostgreSQL Extensions Get Started NextDive Deep Redis ~ 入門から実装の確認まで

Last updated 7 months ago