> For the complete documentation index, see [llms.txt](https://hayashier.gitbook.io/article/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://hayashier.gitbook.io/article/rdbms/troubleshooting-rdbms.md). # RDBMS Engine Inspection for Troubleshooting ## 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データファイル ### 参考 * * * * * * * * ### 個人ブログ * Yakst: * MySQLのメモリー使用量を最適化する設定のベストプラクティス: * 漢(オトコ)のコンピュータ道 * * * MySQLを高速化する10の方法: * さらにMySQLを高速化する7つの方法: * InnoDBのログとテーブルスペースの関係: * MySQLのEXPLAINを徹底解説!!: * Using filesort: * オトコのソートテクニック2008: * 限界までMySQLを使い尽くす!!: * MySQLにおけるレプリケーション遅延の傾向と対策: * 大人のためのInnoDBテーブルとの正しい付き合い方。: * やってはいけない!!MySQLに悲鳴をあげさせる10の方法: * なぜMySQLのサブクエリは遅いのか。: * 知って得するInnoDBセカンダリインデックス活用術！: * InnoDBでCOUNT()を扱う際の注意事項あれこれ。: * InnoDBのREPEATABLE READにおけるLocking Readについての注意点: * Sergey Petrunia’s blog: * MySQLのmetricに関する話: * 誰も教えてくれなかったMySQLの障害解析方法: ## サンプルデータ * *