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

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|

SHOW ENGINE INNODB STATUS

続いて、SHOW ENGINE INNODB STATUS構文から確認します。BUFFER POOL AND MEMORYセクションを確認します。前述で紹介したものより詳細な情報を確認できます。この中から行に番号を振った部分（①～⑬）を説明します。

mysql> show engine innodb status\G
〈一部割愛〉
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 10989076480 ①
Dictionary memory allocated 406634  ②
Buffer pool size   655360  ③
Free buffers       588325  ④
Database pages     67035   ⑤
Old database pages 24844   ⑥
Modified db pages  6  ⑦
Pending reads      0 
Pending writes: LRU 0, flush list 0, single page 0 
Pages made young 0, not young 0 ⑧
0.00 youngs/s, 0.00 non-youngs/s
Pages read 66731, created 304, written 2225  ⑨
0.00 reads/s, 0.00 creates/s, 1.04 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000  ⑩
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s  ⑪
LRU len: 67035, unzip_LRU len: 0  ⑫
I/O sum[0]:cur[0], unzip sum[0]:cur[0]  ⑬
〈一部割愛〉

① Total large memory allocated: バッファプールに割り当てられた合計メモリ（バイト）
② Dictionary memory allocated: InnoDBデータディクショナリのキャッシュに割り当てられている合計メモリ（バイト）
③ Buffer pool size: バッファプールに割り振られているページの合計
④ Free buffers: バッファプールの空きリストのページの合計
⑤ Database pages: バッファプールにキャッシュされているデータのページの合計
⑥ Old database pages: バッファプールのold LRUサブリストのページ単位の合計
⑦ Modified db pages: バッファプール内で変更されているページの合計（ダーティページ）
⑧ Pages made: young：バッファプールLRUリスト内でyoungページの合計数; not young：バッファプールLRUリスト内でoldページの合計数
⑨ Pages: read：ディスクから読んでバッファプールにロードしたページ総数; created ：バッファプールに作成されたページの総数; written：バッファプールからディスクに書き込まれたページの総数
⑩: Buffer pool hit rate：バッファプールページヒット率; young-making rate：ページアクセスによってyoungに移動された平均ヒット率; not：innodb_old_blocks_timeオプションなどによる影響のため、ページアクセスによってyoungに移動されなかった平均ヒット率
⑪: Pages read ahead：1秒あたりのディスクからの先読みページ数; evicted without access：1秒あたりの先読み操作でバッファプールにロードされたが、アクセスされずに削除されたページ数; Random read ahead：1秒あたりのランダム先読み操作のページ数
⑫: LRU len：バッファプールLRUリストの合計ページ数; unzip_LRU len：バッファプールのunzip_LRUリストの合計ページ数（unzipは圧縮機能を利用している際の解凍済みページを意味します）
⑬: I/O sum：過去50秒間にアクセスされたバッファプールLRUリストページの総数; I/O cur：アクセスされたバッファプールLRUリスト・ページの総数; unzip sum：アクセスされたバッファプールunzip_LRUリスト・ページの総数; unzip cur：アクセスされたバッファプールunzip_LRUリスト・ページの総数

BUFFER POOL AND MEMORYセクションのあとにINDIVIDUAL BUFFER POOL INFOセクションがあります。これは個別のパッファプールの情報になります。複数のスレッドからのページへの競合を減らし、並列性を向上するために、バッファプールを個別のインスタンスに分割しています。innodb_buffer_pool_instancesオプションで設定します。デフォルトではバッファプールサイズが1GB未満の場合は1、それ以上の場合は8となっています。BUFFER POOL AND MEMORYセクションの情報は個別のインスタンスを統合した情報になります。

バッファプールはLRUアルゴリズムでリストとして管理されています。そのリストは最近アクセスされた新しいデータ（young）のサブリストと、直近アクセスされたことのない古いデータ（old）のサブリストと、いうように2つのサブリストとして扱います。それらの詳細な内容をSHOW ENGINE INNODB STATUS構文から確認することができます。バッファプールのアルゴリズムやアーキテクチャを知りたい方は 15.5.1 Buffer Pool をご確認ください。

また、MySQL8.0からはINDIVIDUAL BUFFER POOL INFOセクションの情報をinformation_schema.INNODB_BUFFER_POOL_STATSテーブルから確認することができます。バッファプールのインスタンスごとに行が存在します。下の例のPOOL_ID: 0は---BUFFER POOL 0のバッファプールインスタンスを表します。

SHOW ENGINE INNODB STATUS構文のBUFFER POOL 0

----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   81920
Free buffers       79210
Database pages     2698
Old database pages 975
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 41, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2659, created 39, written 1497
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2698, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

information_schema.INNODB_BUFFER_POOL_STATSのPOOL_ID=0

mysql> SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS\G
*************************** 1. row ***************************
                         POOL_ID: 0
                       POOL_SIZE: 81920
                    FREE_BUFFERS: 79210
                  DATABASE_PAGES: 2698
              OLD_DATABASE_PAGES: 975
         MODIFIED_DATABASE_PAGES: 0
              PENDING_DECOMPRESS: 0
                   PENDING_READS: 0
               PENDING_FLUSH_LRU: 0
              PENDING_FLUSH_LIST: 0
                PAGES_MADE_YOUNG: 41
            PAGES_NOT_MADE_YOUNG: 0
           PAGES_MADE_YOUNG_RATE: 0
       PAGES_MADE_NOT_YOUNG_RATE: 0
               NUMBER_PAGES_READ: 2659
            NUMBER_PAGES_CREATED: 39
            NUMBER_PAGES_WRITTEN: 1497
                 PAGES_READ_RATE: 0
               PAGES_CREATE_RATE: 0
              PAGES_WRITTEN_RATE: 0
                NUMBER_PAGES_GET: 1279964
                        HIT_RATE: 0
    YOUNG_MAKE_PER_THOUSAND_GETS: 0
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0
         NUMBER_PAGES_READ_AHEAD: 0
       NUMBER_READ_AHEAD_EVICTED: 0
                 READ_AHEAD_RATE: 0
         READ_AHEAD_EVICTED_RATE: 0
                    LRU_IO_TOTAL: 0
                  LRU_IO_CURRENT: 0
                UNCOMPRESS_TOTAL: 0
              UNCOMPRESS_CURRENT: 0

詳しくは 25.39.3 The INFORMATION_SCHEMA INNODB_BUFFER_POOL_STATS Table をご確認ください。

まとめ

InnoDBバッファプールのさまざまな確認方法を紹介しました。今回は説明しませんでしたが、information_schema.INNODB_METRICSテーブルからも同様の情報が取得できます。このテーブルはさまざまなInnoDBのパフォーマンス情報をより詳しく提供します。それぞれ同じ意味を表すものもあり、それは以下の通りとなります。

SHOW GLOBAL STATUS	information_schema.INNODB_METRICS	SHOW ENGINE INNODB STAUTS(BUFFER POOL AND MEMORY)
Innodb_buffer_pool_pages_total	buffer_pool_pages_total	Buffer pool size
Innodb_buffer_pool_pages_data	buffer_pool_pages_data	Database pages
Innodb_buffer_pool_pages_free	buffer_pool_pages_free	Free buffers
Innodb_buffer_pool_pages_dirty	buffer_pool_pages_dirty	Modified db pages
Innodb_buffer_pool_pages_misc	buffer_pool_pages_misc	-
Innodb_buffer_pool_read_requests	buffer_pool_read_requests	-
Innodb_buffer_pool_write_requests	buffer_pool_write_requests	-
Innodb_buffer_pool_reads	buffer_pool_reads	Pages read
Innodb_pages_created	buffer_pages_created	Pages created
Innodb_pages_written	buffer_pages_written	Pages written