そーだいなるらくがき帳

そーだいが自由気侭に更新します。

InnoDBの監視 ~ mackerel-plugin-mysqlを読み解く その2

この記事は Mackerel プラグインアドベントカレンダー(全部CRE） の20日目です。

qiita.com

soudai.hatenablog.com

それでは20日目は mackerel-plugin-mysql 第二弾、InnoDBの監視です。

mackerel-plugin-mysqlはRDBMSとして広く使われているMySQL専用のプラグインです。 第一弾はこちら。

soudai.hatenablog.com

インストール方法や使い方、MySQLのデータ取得で使っているSQLは前回説明したので割愛します。

前回はMySQL全般に言える監視の内容でした。 今回はその中でもInnoDBに特化した内容でお送りします。

見れるメトリック

それでは各グラフ定義ごとに説明します。 また表に出てくるdiffとはプラグイン上で差分値計算をするかどうかです。 ◯ となっている項目はプラグインで前回の実行時の値と差分値計算して出力しています。

MySQL innodb Rows

メトリック名(ラベル) プラグインの出力名 diff 説明
Read mysql.innodb_rows.Innodb_rows_read ◯ 1分間あたりの読み込まれた行数
Inserted mysql.innodb_rows.Innodb_rows_inserted ◯ 1分間あたりの追加された行数
Updated mysql.innodb_rows.Innodb_rows_updated ◯ 1分間あたりの更新された行数
Deleted mysql.innodb_rows.Innodb_rows_deleted ◯ 1分間あたりの削除された行数

MySQL Commandと違うところは行数なので1回のクエリで大量のINSERTが走ったときなどに違いが出てきます。 リリース直後やメンテ作業直後などは自分の意図通りの量になっているか確認すると良いでしょう。 また MySQL Command と併せて確認することで全体の傾向が見えてくるので定期的に一緒に見るようにしましょう。

MySQL innodb Row Lock Time

メトリック名(ラベル) プラグインの出力名 diff 説明
Lock Time mysql.innodb_row_lock_time.Innodb_row_lock_time ◯ 1分間あたりの行ロックを獲得するまでの所要総時間(ミリ秒)

この値が増えるということは行ロック待ちが増えていることを意味します。 通常時は常に0に限りなく近くなることを目標にしましょう。

MySQL innodb Row Lock Waits

メトリック名(ラベル) プラグインの出力名 diff 説明
Lock Time mysql.innodb_row_lock_waits.Innodb_row_lock_waits ◯ 1分間あたりの行ロックを待機した回数

前述の MySQL innodb Row Lock Time が時間に対し、こちらは回数です。 同じく通常時は常に0に限りなく近くなることを目標にしましょう。 また 時間と回数 をセットで比べるのはとても大切です。 これにより、長い時間行ロックをとっているのか短いけど行ロック待ちが頻発してるのかが分かるからです。 ロックはデータの整合性を守るためにとても大切な仕組みですがパフォーマンスに直結するところですので定期的に確認して増える傾向が見えたら早目に対応するようにしましょう。 個人的にはInnodb_row_lock_time_avgで代用出来るわけだけど 5.0.37未満 だと正しい値ではないっていうバグがあったのでInnodb_row_lock_time_avgを使わず、敢えてこの値を残してると思っています。 なのでMySQL innodb Row Lock Timeと一緒に見る項目といった感じです。

MySQL innodb Adaptive Hash Index

メトリック名(ラベル) プラグインの出力名 diff 説明
Hash Index Cells Total mysql.innodb_adaptive_hash_index.hash_index_cells_total ー Hash tableの全体の大きさ
Hash Index Cells Used mysql.innodb_adaptive_hash_index.hash_index_cells_used ー 実際に使っているAHIの領域

MySQLはテーブルデータが全てメモリ上にある場合、InnoDBがハッシュインデックスを作ってくれます。 これがAdaptive Hash Index(以下AHI)です。 AHIは等価比較(主に=での検索）の際に使われ、INDEXの走査すら行わずにレコードの内容をメモリ上で1回で取ってこれるので非常に高速です。 そんなAHIを如何に効率良く使えているかを知るためのグラフになります。

https://dev.mysql.com/doc/refman/5.6/ja/innodb-adaptive-hash.html

MySQL innodb Buffer Pool Read (/sec)

メトリック名(ラベル) プラグインの出力名 diff 説明
Pages Read Ahead mysql.innodb_buffer_pool_read.read_ahead ー InnoDB バッファープールに読み取られたページ数/60秒
Evicted Without Access mysql.innodb_buffer_pool_read.read_evicted ー 先読みはしたが使われなかったため消去されたInnoDB バッファープールに読み取られたページ数/60秒
Random Read Ahead mysql.innodb_buffer_pool_read.read_random_ahead ー InnoDBによって開始された「ランダムな」先読みの数/60秒

ページ数/60秒 は SHOW ENGINE INNODB STATUS では毎秒の値が表示されているため、SHOW STATUSの同名の値をその仕様に合わせています。 下位互換のためにこの仕様になっているので素直にページ数にすればいいと思うけど下位互換担保するってのは難しい… またRandom Read Aheadはクエリがテーブルの大部分をランダムにスキャンする場合に発生します。 増えているときはパフォーマンス遅延の原因ですので要注意です。

MySQL innodb Buffer Pool Activity (Pages)

メトリック名(ラベル) プラグインの出力名 diff 説明
Created mysql.innodb_buffer_pool_activity.pages_created ◯ 1分間あたりのInnoDが作成したページ数
Read mysql.innodb_buffer_pool_activity.pages_read ◯ 1分間あたりのInnoDBが読み取りしたページ数
Written mysql.innodb_buffer_pool_activity.pages_written ◯ 1分間あたりのInnoDBが書き込みしたページ数

InnoDBが読み取り、書き込みなどをInnoDB バッファープールとディスクの間で行ったベージ数を表しています。 CreatedはInnoDBがデータファイルを読み取らずにバッファープールで割り当てるページ数です。 またInnoDBはページの中身を知りません。 そのため例えばDROPしたtableのデータファイルのページなども含まれています。

MySQL innodb Buffer Pool Efficiency

メトリック名(ラベル) プラグインの出力名 diff 説明
Reads mysql.innodb_buffer_pool_efficiency.Innodb_buffer_pool_reads ◯ 1分間あたりのInnoDB バッファープールを利用できなかった読み込み回数
Read Requests mysql.innodb_buffer_pool_efficiency.Innodb_buffer_pool_read_requests ◯ 1分間あたりの読み込み要求回数

全体のバッファープールに対する読み込みとバッファープールに対象のデータが無く、ディスクから直接読み取る必要があった時の回数ですので、どれくらい有効にバッファープールを活用できているかが分かります。 MySQLは兎にも角にもバッファープールでデータを全て解決できることがパフォーマンスの肝ですからReadsは常に0に限りなく近くなることを目標にしましょう。

MySQL innodb Buffer Pool (Pages)

メトリック名(ラベル) プラグインの出力名 diff 説明
Pool Size mysql.innodb_buffer_pool.pool_size ー 合計サイズ
Used mysql.innodb_buffer_pool.database_pages ー データを格納しているページ数
Free mysql.innodb_buffer_pool.free_pages ー 空きページ数
Modified mysql.innodb_buffer_pool.modified_pages ー 書き換えしたダーティーページ数

バッファープール全体の利用状況を表しています。 そのため MySQL innodb Buffer Pool Efficiency と併せて確認し、バッファープールを有効活用しているかどうか確認しましょう。 データが大きくなってパフォーマンスが劣化してきたり、メモリに乗らないと判断する際に重要な要素です。 定期的に確認しましょう。

MySQL innodb Checkpoint Age

メトリック名(ラベル) プラグインの出力名 diff 説明
Uncheckpointed mysql.innodb_checkpoint_age.uncheckpointed_bytes ー チェックポイントで書き込みされいないデータ量

Uncheckpointedの単位はバイトです。Uncheckpointedが innodb_log_file_size * innodb_log_files_in_group の60%超えたら注意するようにしましょう。 ちなみに innodb_log_file_size * innodb_log_files_in_group の指定できる最大は5.6からは512GB、それ未満は4GBです。

MySQL innodb Current Lock Waits (secs)

メトリック名(ラベル) プラグインの出力名 diff 説明
Innodb Lock Wait mysql.innodb_current_lock_waits.innodb_lock_wait_secs ー ロック開放待ち合計時間

今のロック開放待ちの合計時間です。 右肩上がりに増えてくればロック待ち時間が伸びているので長時間のロックがいるかクエリが詰まっています。 こちらも通常時は常に0に限りなく近くなることを目標にしましょう。

MySQL innodb I/O

メトリック名(ラベル) プラグインの出力名 diff 説明
File Reads mysql.innodb_io.file_reads ◯ 1分間あたりのOSの読み込みI/Oの実行回数
File Writes mysql.innodb_io.file_writes ◯ 1分間あたりのOSの書き込みI/Oの実行回数
File fsyncs mysql.innodb_io.file_fsyncs ◯ 1分間あたりのOSのfsyncsの実行回数
Log Writes mysql.innodb_io.log_writes ◯ 1分間あたりのログの書き込みI/Oの実行回数

ディスクI/Oに関する値です。 MySQLにかぎらずRDBMSはディスクI/Oは重要な指標です。 特にI/Oのどの種類が多いかでI/Oチューニングが代わってきますので定期的に確認するようにしましょう。 OSの読み込みがボトルネックになっているのにログを減らしても効果がないありませんよね？ システムメトリックと併せて活用しましょう。

MySQL innodb I/O Pending

メトリック名(ラベル) プラグインの出力名 diff 説明
Normal AIO Reads mysql.innodb_io_pending.pending_normal_aio_reads ー 通常の読み込み非同期I/Oでの待ち数
Normal AIO Writes mysql.innodb_io_pending.pending_normal_aio_writes ー 通常の書き込み非同期I/Oでの待ち数
InnoDB Buffer AIO Reads mysql.innodb_io_pending.pending_ibuf_aio_reads ー Insert Bufferの非同期ログ読み込みでの待ち数
AIO Log Ios mysql.innodb_io_pending.pending_aio_log_ios ー Insert Bufferの非同期ログでの待ちI/O数
AIO Sync Ios mysql.innodb_io_pending.pending_aio_sync_ios ー Insert Bufferの非同期syncでの待ちI/O数
Log Flushes mysql.innodb_io_pending.pending_log_flushes ー ログフラッシュでの待ち数
Buffer Pool Flushes mysql.innodb_io_pending.pending_buf_pool_flushes ー バッファープールフラッシュでの待ち数
Log Writes mysql.innodb_io_pending.pending_log_writes ー ログ書き込みでの待ち数
Checkpoint Writes mysql.innodb_io_pending.pending_chkp_writes ー チェックポイントでの待ち数

この値のいずれかが多いときはディスクI/Oがボトルネックになっていることを表しています。 そのためMySQL innodb I/Oと併せて確認しましょう。 また通常時は常に0に限りなく近くなることを目標にしましょう。

MySQL innodb Insert Buffer

メトリック名(ラベル) プラグインの出力名 diff 説明
Inserts mysql.innodb_insert_buffer.ibuf_inserts ー 実行した書き込み要求数
Merges mysql.innodb_insert_buffer.ibuf_merges ー マージされたI/O要求数
Merged mysql.innodb_insert_buffer.ibuf_merged ー マージされたI/O要求数処理回数

Insertの度合いを表しています。 Insertの多いシステムの場合は特に注意して確認しましょう。 N+1なInsertが居た場合などで跳ねる事があります。

MySQL innodb Insert Buffer Usage (Cells)

メトリック名(ラベル) プラグインの出力名 diff 説明
Cell Count mysql.innodb_insert_buffer_usage.ibuf_cell_count ー 総セル数
Used mysql.innodb_insert_buffer_usage.ibuf_used_cells ー 利用中のセル数
Free mysql.innodb_insert_buffer_usage.ibuf_free_cells ー 空きセル数

MySQLのINSERTの勘所として Change buffering があります。 INSERTは平たく言うとバッファープールに乗っていないセカンダリーインデックスの更新の時にバッファリングしてバックグラウンドでページを更新してくれます。 このバッファリングしておくためのbufferがInsert Bufferなので枯渇しないように注意しましょう。 ちなみにページの更新は以下のタイミングで行われます。

  • 該当するインデックスのページがバッファプールに読まれた時
  • バックグラウンド実行によるマージ

そのためMySQL innodb Insert Bufferと併せて確認すると良いでしょう。

MySQL innodb Lock Structures

メトリック名(ラベル) プラグインの出力名 diff 説明
Structures mysql.innodb_lock_structures.innodb_lock_structs ー 開放待ちlock structの数

lock structが増えれば当然パフォーマンスに影響が出ます。 通常時は常に0に限りなく近くなることを目標にしましょう。

MySQL innodb Log

メトリック名(ラベル) プラグインの出力名 diff 説明
Written mysql.innodb_log.log_bytes_written ◯ 1分間あたりのログに書き込まれたデータ量
Flushed mysql.innodb_log.log_bytes_flushed ◯ 1分間あたりのログから書き出されたデータ量
Unflushed mysql.innodb_log.unflushed_log ー ログから書き出されていないデータ量
Buffer Size mysql.innodb_log.innodb_log_buffer_size ー ログバッファのサイズ

ログの滞留状況を表しています。 ログがディスクI/Oのボトルネックになっている場合は確認してみましょう。

MySQL innodb Memory Allocation

メトリック名(ラベル) プラグインの出力名 diff 説明
Additional Pool Allocated mysql.innodb_memory_allocation.additional_pool_alloc ー Additional Poolのメモリ割当量
Total Memory Allocated mysql.innodb_memory_allocation.total_mem_alloc ー 総メモリ割当量

意図した状態にメモリが割当られてるか確認しましょう。

MySQL innodb Semaphores

メトリック名(ラベル) プラグインの出力名 diff 説明
Spin Waits mysql.innodb_semaphores.spin_waits ◯ スピンロック獲得待ち数
Spin Rounds mysql.innodb_semaphores.spin_rounds ◯ スピンロック獲得のためのラウンド数
OS Waits mysql.innodb_semaphores.os_waits ◯ OSロック獲得待ち数

スピンロック < OSロック の方が重いのでOSロックが増えていないか注意しましょう。 勿論スピンロックも多い状態はよくありません。 システムメトリックと併せて見ると良いです。

MySQL innodb Tables In Use

メトリック名(ラベル) プラグインの出力名 diff 説明
Table in Use mysql.innodb_tables_in_use ー 実行中のトランザクションが利用しているtable数の述べ合計
Locked Tables mysql.innodb_tables_in_use.innodb_tables_in_use ー 実行中のトランザクションがロックしているtable数の述べ合計

普段から眺めておき、リリース後に意図していない量になっていないかなど確認しましょう。 勿論、量が増えてくるとパフォーマンスの問題にも直結します。 特にロックは死活問題ですので意図してない状態になっていないか定期的に確認しましょう。

MySQL innodb Transactions Active/Locked

メトリック名(ラベル) プラグインの出力名 diff 説明
Current mysql.innodb_transactions_active_locked.current_transactions ー 実行中のトランザクション数
Active mysql.innodb_transactions_active_locked.active_transactions ー 実行中のトランザクションのうち、Activeな数
Locked mysql.innodb_transactions_active_locked.locked_transactions ー 実行中のトランザクションのうち、Lockedな数
Read Views mysql.innodb_transactions_active_locked.read_views ー 実行中のトランザクションのうち、Read Viewsな数

トランザクション内容を表しています。 特にLockedは通常時は常に0に限りなく近くなることを目標にしましょう。

MySQL innodb Transactions

メトリック名(ラベル) プラグインの出力名 diff 説明
History List mysql.innodb_transactions.history_list ー undo領域にある未破棄のトランザクション数
InnoDB Transactions mysql.innodb_transactions.innodb_transactions ◯ 1分間あたりの生成されたトランザクション数

トランザクションの内容です。 意図していない状態になっていないか定期的に確認しましょう。 バッチや時間のかかるトランザクションを実行していない時間帯なら多くても3000未満が一つの指標になるでしょう。

以上です！！ これで皆さんもInnoDBはバッチリですね。 僕も流石に疲れました。 そしてここで唐突にmackerel-plugin-mysqlのオプション一覧を見てみましょう。

# mackerel-plugin-mysql -h
Usage of mackerel-plugin-mysql:
  -disable_innodb
        Disable InnoDB metrics
  -enable_extended
        Enable Extended metrics
  -host string
        Hostname (default "localhost")
  -metric-key-prefix string
        metric key prefix (default "mysql")
  -password string
        Password
  -port string
        Port (default "3306")
  -socket string
        Port
  -tempfile string
        Temp file name
  -username string
        Username (default "root")

Enable Extended metrics ...だと！？

…はい、まだもう1段階MySQLプラグインは変身を残しています。 この続きはまたどこかでやります。 ここではInnoDBにフォーカスした監視についてでした。 次回はAWSでも人気のソリューション、CloudFrontの監視です。 明日もお楽しみに！！

21日目 mackerel-plugin-aws-cloudfront