エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

チューニング一般

• innodb_flush_loag_at_trx_commit

  • ログ書込またずにコミット完了するか
  • マスタで1, スレーブで0,2がよく使われる

• innodb_flush_method

  • O_DIRECT がシステムバッファを防げておすすめ
  • ext3を用いている場合はgrubでelevator=noopを設定する

• mysql proxy

  • LBしたり、クエリをluaで解析したりできる
  • connector/J以外を使う場合のスレーブ負荷分散として使うとよい

• バイナリログ

  • log_bin=接頭語を設定すると有効になる
  • 形式は SBR, RBR, MBR
  • sync_binlog=1は絶対にロストしないが遅い
  • expire_logs_days=XXは必ず設定すること

• クエリキャッシュ

  • 無効で良い
  • query_cache_type=2とかにして、必要なものだけ有効にするのがよい

• ディスク

  • テーブル別innodbにしておく
  • RAWデバイスを利用するのも手

SHOW STATUS

• Aborted_clients
  • 異常終了クライアント数。プログラムクラッシュや、NW不調を疑う。
• Created_tmp_disk_tables
  • インメモリのTempテーブルに収まらず、MyISAMテーブル(Disk)が発生した数
• Handler_read_first
  • インデックススキャン数
• Handler_read_rnd
  • テーブルスキャン数
• Opend_table_definitions
  • 急増はテーブル定義キャッシュが少なすぎる兆候
• Opend_tables
  • 急増はテーブルキャッシュが少なすぎる兆候
• Select_full_join
  • インデックスなしJOIN数
• Select_full_range_join
  • インデックスなし範囲検索JOIN数
• Select_range_check
  • EXPLAINのExtraがrange checked for each record (index map: N)になるクエリ数
  • Select_full_joinよりは高速だが要改善
• Select_scan
  • 先頭JOINテーブル、単一テーブルで全件スキャンが発生した数
• Slow_queries
  • スロークエリ数
• Sort_maerge_passess
  • ソートバッファが足りてないと増える
• クエリキャッシュヒット率
  • Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached)
  • 7-8割ヒットしていないと意味が無い
• FLUSH STATUSを使ったあとに特定のクエリを流せば、そのクエリだけの統計情報がわかる

レプリケーション関連コマンド

• SHOW BINLOG EVENTS [IN log_file] [FROM 開始位置] LIMIT 1

  • レプリケーションに失敗したときに、その前のクエリを調べるときに使う

• SHOW MASTER STATUS\G

• SHOW SLAVE STATUS\G

  • Slave_IO_State
  • FailedやReconnectiongだったら問題あり
  • Slave_IO_Running, Slave_SQL_Running
  • どちらがNoならば即刻メンテすべし
  • Seconds_Behind_Master
  • SQLスレッドのみの遅延時間だが、遅すぎるならinnodb_flush_loag_at_trx_commit=2にすべし

便利なコマンド

• SHOW FULL PROCESSLIST
  • 現在の実行クエリがわかるので、そのクエリをEXPLAINすれば簡単な性能調査ができる
• SHOW TABLE STATUS WHERE tbl_name;
  • 行数、index数、データサイズ等わかる
• DESC tbl_name;
  • カラム定義がわかる
• SHOW INDEX IN tbl_name;
  • UNIQUE以外はカーディナリティが多くないとインデックス使う意味が無い
  • カーディナリティが低いカラムで分けたい場合は、パーティショニングをする

INFORMATION_SCHEMAのみの情報

• PARTITIONS
• REFERENTAL_CONSTRAINTS
  • 外部キー制約を一覧できる
• TABLE_CONSTRAINTS
  • UNIQUEキー制約を一覧できる
• *_PRIVILEGES
  • 権限一覧

EXPLAIN

idとSelect Type

• MySQLクエリは JOIN、UNION、サブクエリに分類できる

JOIN

• 1つもJOINがないクエリでもこれが該当する
• select_typeはSIMPLE
• idは1
  • 1回の処理でデータを取得できることを意味する

UNION

• それぞれのSELECTが単独実行後、結果が統合される
• idはSELECTの数だけ存在する
• select_typeは、最初がPRIMARY、続いてUNION、最後にUNION RESULTになる
• UNION RESULTはidがつかない

サブクエリ

• 実行の順番で考えると、FROM句サブクエリ と それ以外に分けられる
• FROM句サブクエリ
  • select_typeにPRIMARYとDERIVEDが現れる
  • idとは裏腹に2のDERIVEDから実行される
• それ以外
  • FROM句以外ではWHERE,HAVING,SELECTで利用される
  • select_typeには下記の4つが現れる
  • PRIMARY 最外部クエリ
  • SUBQUERY 外部クエリと相関関係なしサブクエリ
  • DEPENDENT SUBQUERY 外部クエリと相関関係ありサブクエリ
  • UNCACHEABLE SUBQUERY 実行毎に結果変わるサブクエリ
  • SUBQUERYは1度だけ取得されるが、DEPENDENT SUBQUERYは最外部クエリ1行ごとに実行される
  • 相関サブクエリにしないためには下記を用いないこと
  • IN (SELECT…)
  • NOT IN (SELECT…)
  • = ANY (SELECT…) 不等号はOK
  • <> ANY (SELECT…) 不等号はOK

Record Access Type

• const
  • PKEYやUNIQUEキーで等価比較されたため、定数とみなしている
• ALL
  • テーブルスキャン(インデックス無しアクセス)、要注意
    
• index
  • 該当のインデックス内全て読み込む重い処理
  • ORDER BYとLIMITがあれば問題ない
• eq_ref
  • JOIN時にPKEYかUNIQUEキーが利用された
  • 片方のテーブルにはrefが表示される
• ref
  • JOIN時に普通のindexが利用された
  • インデックスがUNIQUEでなければ単一SELECTでもこちらになる
• ref_or_null
  • refの時かつ、OR条件で同じindexに対しIS NULLが指定された場合
• range
  • インデックスを用いた範囲検索
  • 範囲が大きくても全てrangeになるので、意外と重いことがあるので注意
• fulltext
  • フルテキストインデックス使用
• index_merge
  • 2種類のインデックスを個別に利用して取得ｓｈた
  • const, ref, rangeのいずれでも可
• unique_subquery
  • DEPENDENT SUBQUERYにおいて、PKEYやUNIQUEキーが利用された
  • このサブクエリは高速
• index_subquery
  • DEPENDENT SUBQUERYにおいて、通常indexが利用された
  • このサブクエリはそこそこ高速

possible_keys, key, key_len, ref

いくらpossible_keysがたくさんあっても、keyがNULLだと意味が無い

• possible_keys
  • オプティマイザが利用可能と判断したキー
• key
  • 実際に利用されたキー
• key_len
  • 実際に利用されたキーの長さ
• ref
  • 検索条件ひ比較されている値やカラムが表示される
  • const 定数が指定されている、select_type=SUBQUERY
  • JOINの時は結合する相手テーブルカラム

rows

• 取得される行数の見積もり
• DERIVERDだけは実際に発行されるので正確な行数になる

Extra

• Using where
  • インデックスが使われない場合、使ったがさらにWHERE句があり絞込みがある
  • マルチカラムインデックスで、左端のみWhereが指定されている
  • インデックスで適切な行数まで絞っていれば悪くないクエリ
  • 注意しなければいけないのは下記の3つ
  • Record Access TypeがALLまたはindex
  • rowsが多く、大半がindexでないWHEREで弾かれる
  • JOINの後から結合される(内部)表で表示されている
• Using index
  • 1つのインデックスだけで解決できるため高速
  • ただしRecord Access Typeがindexでも表示されるので早とちりしないように
• Using filesort
  • indexを用いず、取得してからソートしている
  • 行数が少なければ問題ない

• Using tempory

  • JOINの結果をソートする場合（下記）
  • ORDER BYとDISTINCTを併用した場合
  • 集計関数を使う場合（SUM, GROUP BY）

• MySQLのJOINありソート3つ(速い順)

  1. 最初のテーブルでindexを用いてソート（Using index）
  2. 最初のテーブルでファイルソートを用いたあとにJOIN（Using filesort）
  3. JOINを全て実行してからソート（Using temporary; Using filesort）

• 複数のテーブルに跨ってソートする場合はこれを使うしかないが、頻出する場合は設計を変えるべき

• Using index for group by

  • MINやMAXをindexだけで算出できるケース
  • INDEX(colA, colB)
  • colA: GROUP BY
  • colB: MIN() or MAX()

• Range checked for each record (index map: N)

  • 値の範囲によって実行計画がかわるもの
  • 直積よりはマシだがクエリを書き換えたほうが良い
  • index map: Nは使われるかもしれないインデックスの一覧の16進数

• Not Exists

  • LEFT JOINで右側にレコードがない場合

• Using join buffer

  • BNLやBKAなどのJOIN最適化で使うbufferを割当てた
  • 正しい用途で利用されているか見極める必要がある

• Full scan on NULL key

  • INサブクエリでフルスキャンが走る場合があることを示す
  • SELECT col1, col1 IN (SELECT key1 FROM tbl2) FROM tbl1;
  • col1がNULLの時フルスキャンになるので、NOT NULLか確認する

• index merge最適化

  1. Using intersect: 2つ以上のインデックスを用いた検索がANDで実行。PKEYは範囲、それ以外は等価を利用可
  2. Using union: 2つ以上のインデックスを用いた検索がORで実行。PKEYは範囲、それ以外は等価を利用可
  3. Using sort_union: 2つ以上のインデックスを用いた範囲検索がORで実行。MySQLが内部的にROWIDでソートするのでこう呼ばれている

EXPLAIN PARTITIONS

• パーティションの刈り込みができているか確認する必須機能

チューニングの心得

• クエリを書き換えても同じ結果になること
• 実データを用いてEXPLAINする。カーディナリティが異なると実行計画も異なる
• EXPLAINが良くなっても、実測値を必ず確認すること
• テーブルに大量のデータ追加、削除、変更を行ったら、ANALYZE TABLEしたりOPTIMIZE TABLEすること
• サブクエリは極力避けること。DISTINCTで代用可。
• 必要なカラムだけをSELECTすること
• マルチカラムインデックスを適切に貼ること
  • サイズが大きくなり、更新性能も落ちるので適切に
• FORCE INDEX, STRAIGHT_JOIN ヒントを活用する
• innodbはPKEYとそれ以外のindexに性能差が大きいので、できるだけPKEYを活用する

プロファイリング

• SET profiling=1; SELECT …; SHOW PROFILE オプション;
• 現在のセッションのみ有効
• CPUリソース等の情報は、プロセス単位で計測してしまう
• オプションのおすすめ
  1. BLOCK IO ディスクブロックのR/W
  2. SOURCE ソースコード情報
• SHOW PRODILESでデフォルト15件まで過去分を見れる
• Status 勘所
  • copying to tmp tableが長い
  • テンポラリテーブルが巨大で、MyISAMテーブルに変換するのに時間がかかっている(converting HEAP to MyISAM)
  • Sorting〜が長い
  • Sending dataが長い (JOINの効率が悪い)
  • executingが何度も現れる（サブクエリが何度も発行）

innoDBモニタ

• innodb-status-file=1を設定しておくと、SHOW ENGINE INNODB STATUSの結果が15秒ごとに書き込まれる

レプリケーション

• SBRを使うと転送速いが、下記のような結果が不定の処理で不具合が生じる
  1. ユーザー定義関数
  2. UUID(), USER(), LOAD_FILE()など
  3. ORDER BY無しのLIMIT
  4. 不定のストアド
• READ-COMMITEDの場合は、RBRしか使えない

手順1. マスターからmysqldump

• すでにマスターがあり、マスターを停止しないでスレーブを用意できるが時間がかかる

• マスターで必要な設定を行う

[mysqld]
server_id=1
log_bin=mysql-bin
binlog_format=MIXED
max_binlog_size=100M
expire_logs_days=30
sycn_binlog=1
innodb_support_xa=1
innodb_flush_loag_at_trx_commit=1

• マスターにレプリ用ユーザーを作成

GRANT REPLICATION SLAVE ON *.* TO 'ユーザー名'@'スレーブのホスト名' IDENTIFIED BY 'パスワード';

• マスターデータをダンプする

mysqldump -u ユーザー名 -pパスワード --all-databases --master-data=2 --single-transaction --dump-slave --flush-logs > dumpfile.sql

• スレーブのオプションファイルを設定する
  • 下記以外は全てマスターと同じ設定にする

[mysqld]
server_id=1000
report_host=slave1000
sync_binlog=0 # バイナリログを同期しない
innodb_flush_log_at_trx_commit=0 # ログをコミット毎にフラッシュしない
#skip_innodb_doblewrite # ダブルライトバッファの停止

• スレーブサーバーを起動

  • --skip-slaveをつける

• mysqldumpデータをリストア

• スレーブでレプリケーション設定を行う

  • head -100 dumpfile.sql | grep CHANGE ログファイル名と開始位置を取得
  • CHANGE MASTER TO …

• レプリケーション開始

  • START SLAVE;
  • 確認 SHOW SLAVE STATUS;

手順2 他のスレーブから直接コピー

• すでにレプリケーション済みで、マスター停止せずに行う方法
• 1台停止させて、ディレクトリ毎scpする
• 手順1と同様

手順3 スナップショットを利用

• データが大きく、マスター止めず。素早くセットアップする方法

• スナップショットをとる

• スナップショットをマウントし、スレーブサーバーにscp

• 手順1と同様

よく起きる問題

• SQLスレッドの停止 (Slave_SQL_Running: No)
  • メモリ不足
  • スレーブ上のテーブルを更新してしまった
  • バイナリログ欠損
  • バグ

• IOスレッドの停止 (Slave_IO_Running: No)

  • ネットワークエラー
  • 設定の問題
  • max_allowed_packetがスレーブで小さい
  • レプリケーションユーザーがマスターにログイン出来ない

• SQLスレッド遅延 (Seconds_Behind_Master)

• IOスレッド遅延 (Master_Log_FileとRead_Master_Log_Posが違う)

  • 長時間かかるクエリはないか
  • マスタークエリ完了後にスレーブに転送されるため、実行時間がそのままスレーブ遅延になる
  • スレーブとマスターのスペックに差が大きい
  • スレーブリソース不足
  • 参照系が多いのでCPUが不足しがち
  • ネットワーク帯域不足

• マスターの更新を止められるなら、pt-table-syncを使って同期するのがよい

堅牢にするには

• マルチマスターレプリケーションにしない
  • 互いに更新しあわない
  • 同一行更新なりかねない
  • ホストの台数が増えるので逆効果
• スレーブを更新しない
  • read_onlyを有効にしておく
  • SUPER権限があるrootでないと更新できなくなる
• バイナリログを同期する (sync_binlog=1)
  • ディスクの負荷が高くなるので、IOPSが高いディスクのみ
• HWや設定をあわせる
  • 特にInnodbバッファプール等のバッファ設定
  • 理想はserver_id以外は全て同じ
  • 性能のためにスレーブだけinnodb_flush_loag_at_trx_commit=0もあり
  • ただし復旧しなくてならない可能性が上がる
• スレーブを複数用意する
  • ファイルコピーで復旧できるので
• 一度に大量の更新をしない
• 堅牢なNWを使う
  • バイナリログ破損のまま転送を防ぐために、SSLやSSHポートフォワーディングでマスタと接続する
• テンポラリテーブルを使わない
• スレーブ監視する