MySQL道普請便り

第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_requestsInnodb_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

おすすめ記事

記事・ニュース一覧