MySQL, PostgreSQL トラブルシューティング コマンド実行例 (Work In Progress)

2021-06-02 Last updated : 2021-06-08 hayashier

トラブルシューティング

編集途中ですが、一旦公開します。

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

My Twitter & RSS

Categories
AWS, Cloud, General, MySQL, PostgreSQL, and RDBMS

Leave a Reply

Your email address will not be published. Required fields are marked *

Previous article
10分で作るお手軽Markdownで書く独自ドメインのブログサイト構築
2021-06-02
Next article
MySQL, PostgreSQL ベンチマーク(sysbench, pgbench)
2021-06-02