MySQL道普請便り

第101回　InnoDBバッファプールの状態を確認するさまざまな方法

MySQL, InnoDB, バッファプール, LRUアルゴリズム

この記事を読むのに必要な時間：およそ 6 分

InnoDBをチューニングする際に，真っ先に確認するものといえばInnoDBバッファプールがあります。これは頻繁にアクセスされたテーブルデータやインデックスデータをキャッシュし，リクエストを高速に処理するための重要な機構です。基本的にはバッファプールは大きな値を設定するようにガイドされています。データサイズがすべてバッファプールサイズに収まるように設定すると安定したサービスの提供が可能です。

バッファプールサイズよりもデータサイズが大きい場合は，ディスクへのアクセスが頻発して運用しているサービスに影響があることもあります。しかし，サービスが頻繁にアクセスするデータは決まっていて（過去のデータにはほとんどアクセスしない⁠）⁠，そのデータがすべてバッファプール上にあるために問題なくサービスを運用できることもあります。このように，サービスの特性によるワーキングセットが重要になります。

今回は，バッファプールの状態を確認するためのさまざまな方法を紹介したいと思います。MySQLのバージョンは，MySQL 8.0.16を使用しています。

SHOW GLOBAL STATUS

まずは，SHOW GLOBAL STATUS構文を使って確認してみます。

mysql> SHOW GLOBAL STATUS LIKE 'InnoDB\_buffer\_pool%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 190508 18:19:37 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 263342                                           |
| Innodb_buffer_pool_bytes_data         | 4314595328                                       |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 268171                                           |
| Innodb_buffer_pool_pages_free         | 392018                                           |
| Innodb_buffer_pool_pages_misc         | 0                                                |
| Innodb_buffer_pool_pages_total        | 655360                                           |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 244304743                                        |
| Innodb_buffer_pool_reads              | 1345                                             |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 114563219                                        |
+---------------------------------------+--------------------------------------------------+

mysql> SHOW GLOBAL STATUS LIKE 'Innodb\_pages%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_pages_created | 712   |
| Innodb_pages_read    | 21284 |
| Innodb_pages_written | 13246 |
+----------------------+-------+

LIKE 'InnoDB\_buffer\_pool%'とLIKE 'Innodb\_pages%'をつけて実行することで，InnoDBバッファプールに関する情報を取得しています。この中からいくつか重要な項目を紹介します。

バッファプールの現在の状態

Innodb_buffer_pool_pages_total
バッファプールに割り振られているページの合計
Innodb_buffer_pool_pages_data
バッファプールにキャッシュされているデータのページの合計
Innodb_buffer_pool_pages_dirty
バッファプール内で変更されているページの合計（ダーティページ）
Innodb_buffer_pool_pages_free
バッファプールの空きリストのページの合計
Innodb_buffer_pool_pages_misc
バッファプールのadaptive hash indexなどで確保されたその他のページの合計

これらの値を確認することで，現在のバッファプールの状態がわかります。また，Innodb_buffer_pool_bytes_xxなどもありますが，これは単位がバイト数になります。

バッファプールへのアクセス情報

Innodb_buffer_pool_pages_flushed
バッファプールからディスクへフラッシュした総数
Innodb_buffer_pool_write_requests
バッファプールに書いたページ総数
Innodb_buffer_pool_read_requests
バッファプールから読んだページ総数
Innodb_buffer_pool_reads
ディスクから読んでバッファプールにロードしたページ総数
Innodb_pages_created
バッファプールに作成されたページの総数
Innodb_pages_written
バッファプールからディスクに書き込まれたページの総数

これらの値は累積値で，差分を出すことでその時間にどれだけディスクから読んだか，またはディスクへ書いたかがわかります。

たとえば，バッファプール上にロードされていないデータへのリクエストがあったとします。その場合はディスクを読み込み，バッファプールにロードします。そうすると，Innodb_buffer_pool_readsがカウントされます。ロードされた後，バッファプールから読み，リクエストを返すので，Innodb_buffer_pool_read_requestsもカウントされます。また，バッファプールにロードされたデータへのリクエストがあった場合は，Innodb_buffer_pool_read_requestsのみカウントされます。

よって，バッファプールのヒット率は以下の式で計算することができます。

パッファープールヒット率 =
(1 - ( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests )) * 100

また，これらの値を定期的の取得して可視化していることが多いかと思います。単発で見たい場合はmysqladminコマンドのextended-statusオプションを使用しても可能です。以下の例は秒間でのInnodb_buffer_pool_read_requestsとInnodb_buffer_pool_readsの差分を表示しています。

mysqladmin -p extended-status -i 1 -r | grep -e Innodb_buffer_pool_read_requests -e Innodb_buffer_pool_reads
| Innodb_buffer_pool_read_requests                      | 81861|
| Innodb_buffer_pool_reads                              | 66724|
| Innodb_buffer_pool_read_requests                      | 7|
| Innodb_buffer_pool_reads                              | 0|
| Innodb_buffer_pool_read_requests                      | 7|
| Innodb_buffer_pool_reads                              | 0|

MySQL, InnoDB, バッファプール, LRUアルゴリズム

著者プロフィール

北川健太郎（きたがわけんたろう）

LINE株式会社所属のデータベースエンジニア。担当はMySQLとOracle Database。好きなMySQLの機能はレプリケーションで，好きなOracleDatabaseの機能はログオントリガー。

Twitter：@keny_lala