MySQL “SHOW ENGINE INNODB STATUS”の読み方 その1

kouです。

何か困ったときに便利なのが各種統計情報出力コマンド。弊社では堅く作るシステムではMySQLを利用することが多く、ストレージエンジンもInnoDBを利用させていただくことが多いです。

というわけで負荷テスト時や運用時に”SHOW ENGINE INNODB STATUS”コマンドをよく使うのですが、各参考サイトを元に自分のメモ代わりに必要そうな項目をまとめてみました。

以下、各セクション毎にポイントをまとめていこうと思います。間違い等があればご指摘いただけるとうれしいです。

出力サンプルのMySQLのバージョンは5.0系です。

ステータスセクション

1  mysql> SHOW ENGINE INNODB STATUS\G
2  *************************** 1. row ***************************
3  Status:
4  =====================================
5  100530  9:10:18 INNODB MONITOR OUTPUT
6  =====================================
7  Per second averages calculated from the last 26 seconds

7行目のPer second …は統計のためにデータを取得した時間です。とはいえ、全てのセクションの情報が同時に採取されるわけではないらしいので、この例では以降のセクションのカウンタ値はだいたい26秒間のもの。秒平均値は26秒間の中のどこかの期間内の平均値であることがわかります。

セマフォセクション

 8 ----------
 9 SEMAPHORES
10 ----------
11 OS WAIT ARRAY INFO: reservation count 359260, signal count 356251
12 Mutex spin waits 0, rounds 6471518, OS waits 83492
13 RW-shared spins 438760, OS waits 214212; RW-excl spins 34083, OS waits 15339

リレーショナルデータベースではそのデータの整合性を担保するために様々な処理でアトミック性を保証しなくてはいけません。MySQLで複数スレッドが動いている中で、クリティカルな処理を行う場合には、そのアトミック性を担保するために同時に1つの操作しかしていないことを保証する仕組み(同期機構)が必要です。LinuxのMySQLでは同期機構にセマフォとMutexを利用しています。

このセクションではそのセマフォ、Mutexに関する統計情報が出力されています。

11行目はスレッドがセマフォの配列に入ったOS待ち(OS wait)の回数です。reservation countが配列に入った回数、signal countが配列に入ってOSからシグナルを受け取った回数です。後述しますが、この回数は少ないほど良いです。

12行目はスレッドのMutex(相互排他ロック)による各種待ち回数を示していて、spin waitsはMutexのスピンロック待ちに入った回数、roundsはスピンロック待ちに入ったロックのスピンラウンド総数を示します。OS waitsはスピンロック待ちでもMutexを取得できなかったため、オペレーティングシステム管理下のセマフォに渡されたロック回数です。

InnoDBはスピンロック待ち→OS待ちの順にロック取得を試みます。スピンロック待ちに入ったロックはinnodb_sync_spin_loopsで設定されたスピンラウンド数(取得待ち回数)を超えるとOS待ちに引き渡されます。OS待ちはスピン待ちに比べて処理コストが大きいので一般的にはOS待ち数が小さい方が好ましいです。OS待ちが多く発生している場合はディスクI/OかInnoDB内部でこれらロックに関する競合が発生している可能性が高いです。OS待ちが毎秒10000以上であればOS待ちを減らす方向でチューニングしましょう。MySQLのスレッド数の変更(innodb_thread_concurrency設定値)が効くと思います。

とはいえ、スピン待ちもCPUリソースを消費するため、あまりspin rounds数が大きいようだとスピン待ちとOS待ちのバランスを取るためにチューニングが必要です(前述のinnodb_sync_spin_loops変数で設定できます。まだ試したことないですが、spin roundが毎秒1000以上であればinnodb_sync_spin_loopsを変更すると良いそうです。もしくはinnodb_thread_concurrency設定値を減らす)。

13行目は読み書きに関する共有ロック(RW-shared)、排他ロック(RW-excl)の各待ち種別カウンタを示していて、それぞれスピン待ち(spins)、OS待ち(OS waits)の回数を示しています。

まとめると、データベースへの並列アクセスが多い場合は同期機構に密接に関わるこのセクションの数字が特に重要で、ほとんどの状況ではOS待ちが多く発生してパフォーマンスが劣化していると思います。その場合はディスクI/OやInnoDB内部の競合が発生している可能性が多いので、MySQLのスレッド数を減らす(innodb_thread_concurrency)と症状が改善されることが多いです。パフォーマンスが出ないからとスレッド数をむやみに増やすと逆に競合が発生しやすくなるため逆効果です。

(もちろん、ボトルネックがどこにあるかによって増やした方がいい場合もありますので、innodb_thread_concurrencyを変更しながらベンチマークをとってチューニングすることが一番望ましいです。)

「その2」へ続く。

参考サイト/書籍:
MySQL Performance Blog – SHOW INNODB STATUS walk through
MySQL 5.0 Reference Manual – 13.2.13.2. SHOW ENGINE INNODB STATUS and the InnoDB Monitors
実践ハイパフォーマンスMySQL 第2版



Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 16271409 bytes) in /home/yumeco/www/prod/wp-includes/wp-db.php on line 1171