MySQL8.0 innodb_buffer_pool_sizeのオンライン縮小

Jun 18, 2024 · 5 min read · MySQL InnoDBCluster チューニング ·

Share on:

はじめまして！インフラエンジニアのぺんぺんです。今回は、直近で私の担当する案件で行った検証をノウハウとして残すという意味で以下についてお話させていただきたいと思います！

『MySQL8.0でinnodb_buffer_pool_sizeをオンラインで縮小するときってどんな影響があるのでしょうか』

まず初めにこのパラメータを設定することによる利点ですが、innodb_buffer_pool_sizeを設定することで、InnoDBがアクセス時にテーブルやインデックスデータをメモリ内でキャッシュするための領域を確保できます。これにより、ディスクI/Oの回数を減らしてデータベースのパフォーマンスを向上させることができます。
MySQLを使っている方は多いと思いますし、innodb_buffer_pool_sizeはチューニングの代表的なパラメータの1つだと認識しています。とはいえ、オンラインで拡張することはあっても縮小するのは私も初めての機会でした。(縮小することでスロークエリの発生が増えてサービス影響が出る可能性があるため、メモリが足りないならスペックアップの判断をしがち)
今回はこのinnodb_buffer_pool_sizeの値が推奨値※の50%～75%を大きく超えて割り当てを行っていたことにより、メモリ不足を起こしてしまったので、推奨値に収まるように縮小するにあたり、影響を確認するために今回の検証を実施することとなりました。 ※推奨される innodb_buffer_pool_size 値は、システムメモリーの 50 から 75%
検証前の想定では、縮小に伴いdirty pagesのフラッシュが発生することでディスクI/Oが発生して一時的にパフォーマンスが低下するのでは？というくらいに考えていました。

検証詳細

検証環境

MySQLバージョン：8.0.34

構成：InnoDBCluster(シングルプライマリ)

実メモリ：256GB

事前準備

自作のwarmupスクリプトを実行し、事前に本番環境とpages_dataを揃えておきます。

検証方法

検証環境に対してsysbenchを使って700qpsの負荷をかけ続けている状態でdirty pagesが本番環境と同等になったことを確認したのち、「set global innodb_buffer_pool_size = 167,772,160」を実行(220Gb→160GB)し、縮小時の影響を確認しました。

検証結果

【19:28:32】にset globalを実行し、7つの確認ポイントとその前後の各種値を以下に記載します。

[1] innodb_buffer_pool_sizeの変更にかかった時間

変更自体は1秒未満で完了しています。

[19:28:30]
Variable_name	Value
innodb_buffer_pool_size	236223201280
[19:28:31]
Variable_name	Value
innodb_buffer_pool_size	236223201280
[19:28:32] ★set global実行、リサイズ開始
Variable_name	Value
innodb_buffer_pool_size	171798691840
[19:28:33]
Variable_name	Value
innodb_buffer_pool_size	171798691840
[19:28:34]
Variable_name	Value
innodb_buffer_pool_size	171798691840

[2] innodb_buffer_pool_pages_dataの変更にかかった時間

innodb_buffer_pool_sizeと同様に、変更自体は1秒未満で完了しています。

[19:28:30]
Innodb_buffer_pool_pages_data	14409408
Innodb_buffer_pool_bytes_data	236083740672
[19:28:31]
Innodb_buffer_pool_pages_data	14409408
Innodb_buffer_pool_bytes_data	236083740672
[19:28:32] ★set global実行、リサイズ開始
Innodb_buffer_pool_pages_data	14365499
Innodb_buffer_pool_bytes_data	235364335616
[19:28:33]
Innodb_buffer_pool_pages_data	10687130
Innodb_buffer_pool_bytes_data	175097937920
[19:28:34]
Innodb_buffer_pool_pages_data	10498607
Innodb_buffer_pool_bytes_data	172009177088

[3] innodb_buffer_poolのリサイズにかかる時間

リサイズがCompletedになるまで約20秒程かかっています。「Innodb_buffer_pool_resize_status」なんて値があるなんて初めて知りました。 ※オンラインバッファプールのサイズ変更の進行状況の監視

[19:28:30]
Variable_name	Value
Innodb_buffer_pool_resize_status	Completed resizing buffer pool at 240411 18:31:37.
[19:28:31]
Variable_name	Value
Innodb_buffer_pool_resize_status	Completed resizing buffer pool at 240411 18:31:37.
[19:28:32] ★set global実行、リサイズ開始
Variable_name	Value
Innodb_buffer_pool_resize_status	buffer pool 7 : withdrawing blocks. (1024/491509)
[19:28:33]
Variable_name	Value
Innodb_buffer_pool_resize_status	buffer pool 7 : withdrawing blocks. (1024/491509)
～～～ 省略 ～～～
[19:28:49]
Variable_name	Value
Innodb_buffer_pool_resize_status	buffer pool 7 : withdrawing blocks. (491509/491509)
[19:28:50]
Variable_name	Value
Innodb_buffer_pool_resize_status	buffer pool 3 : resizing with chunks 55 to 40.
[19:28:51] ★リサイズ完了
Variable_name	Value
Innodb_buffer_pool_resize_status	Completed resizing buffer pool at 240411 19:28:51.

[4] 実行中のクエリへの影響

リサイズが完了するまでの20秒間の間で2度(63s、79s)0qpsになるタイミングが見られ、その後65sのタイミングでqpsや、err/sが増大していることがわかります。(クエリが実行されずに待機され、その後一気に実行されているような挙動)

[ 60s ] thds: 16 tps: 36.00 qps: 719.94 (r/w/o: 503.96/143.99/71.99) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00
[ 60s ] queue length: 0, concurrency: 0
[ 61s ] thds: 16 tps: 18.00 qps: 360.03 (r/w/o: 252.02/72.01/36.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 61s ] queue length: 0, concurrency: 0
[ 62s ] thds: 16 tps: 12.00 qps: 308.99 (r/w/o: 221.00/48.00/40.00) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 62s ] queue length: 6, concurrency: 16
↓★ここで0qpsになっています↓
[ 63s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 63s ] queue length: 43, concurrency: 16
[ 64s ] thds: 16 tps: 0.00 qps: 3.00 (r/w/o: 3.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 64s ] queue length: 85, concurrency: 16
↓★その後qps、err/sが増大しています↓
[ 65s ] thds: 16 tps: 58.00 qps: 1378.98 (r/w/o: 976.98/286.00/116.00) lat (ms,95%): 2828.87 err/s: 0.00 reconn/s: 0.00
～～～ 省略 ～～～
[ 75s ] queue length: 0, concurrency: 0
[ 76s ] thds: 16 tps: 49.00 qps: 979.99 (r/w/o: 685.99/196.00/98.00) lat (ms,95%): 7.17 err/s: 0.00 reconn/s: 0.00
[ 76s ] queue length: 0, concurrency: 0
[ 77s ] thds: 16 tps: 31.00 qps: 630.99 (r/w/o: 443.99/124.00/63.00) lat (ms,95%): 7.43 err/s: 0.00 reconn/s: 0.00
[ 77s ] queue length: 0, concurrency: 1
[ 78s ] thds: 16 tps: 2.00 qps: 45.00 (r/w/o: 18.00/8.00/19.00) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00
[ 78s ] queue length: 12, concurrency: 16
↓★ここで0qpsになっています↓
[ 79s ] thds: 16 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 79s ] queue length: 52, concurrency: 16
↓★その後qps、err/sが増大しています↓
[ 80s ] thds: 16 tps: 114.00 qps: 2264.04 (r/w/o: 1596.03/456.01/212.00) lat (ms,95%): 1708.63 err/s: 0.00 reconn/s: 0.00
[ 80s ] queue length: 0, concurrency: 0
[ 81s ] thds: 16 tps: 31.00 qps: 620.01 (r/w/o: 434.01/124.00/62.00) lat (ms,95%): 7.43 err/s: 0.00 reconn/s: 0.00
[ 81s ] queue length: 0, concurrency: 0

[5] スロークエリの発生状況

buffer_poolのリサイズが完了するまでの約20秒間で229件、最大で3秒程度のスロークエリが発生しました。

[6] dirty pages

今回の検証の目的ともなっていたdirty pagesのフラッシュについては特に数値上の変化は見受けられませんでした。(恐らく、負荷に応じて常にdirty pagesは増減していたので、フラッシュ自体は発生していたものの数値上はほぼ変化が見られなかったと考えられます)

[19:28:30]
Innodb_buffer_pool_pages_dirty	2456
Innodb_buffer_pool_bytes_dirty	40239104
[19:28:31]
Innodb_buffer_pool_pages_dirty	2461
Innodb_buffer_pool_bytes_dirty	40321024
[19:28:32] ★set global実行、リサイズ開始
Innodb_buffer_pool_pages_dirty	2464
Innodb_buffer_pool_bytes_dirty	40370176
[19:28:33]
Innodb_buffer_pool_pages_dirty	2462
Innodb_buffer_pool_bytes_dirty	40337408
[19:28:34]
Innodb_buffer_pool_pages_dirty	2461
Innodb_buffer_pool_bytes_dirty	40321024

[7] I/O負荷が上がるか

I/Oの負荷としては上がりませんでした。([4]でも記載した、クエリが待機して実行できない状態になっていることもあり、むしろ下がっていました)

[07:28:32 PM] ★set global実行、リサイズ開始
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.57    0.00    1.06    0.56    0.00   91.80
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdc               0.00   161.00    0.00  384.00     0.00  2280.00    11.88     0.10    0.26    0.00    0.26   0.26   9.90
[07:28:33 PM]
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          22.86    0.00    1.20    0.44    0.00   75.50
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdc               0.00   158.00    0.00  378.00     0.00  2236.00    11.83     0.09    0.24    0.00    0.24   0.24   9.10
[07:28:34 PM]
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          15.82    0.00    1.07    0.06    0.00   83.05
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdc               0.00    12.00    0.00   27.00     0.00   160.00    11.85     0.01    0.30    0.00    0.30   0.30   0.80
[07:28:35 PM]
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.68    0.00    0.69    0.00    0.00   88.63
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdc               0.00     1.00    0.00    3.00     0.00    16.00    10.67     0.00    0.33    0.00    0.33   0.33   0.10
～～～ 省略 ～～～
[07:28:49 PM]
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           7.90    0.00    5.27    0.44    0.00   86.39
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdc               0.00   131.00    0.00  288.00     0.00  1772.00    12.31     0.07    0.24    0.00    0.24   0.23   6.70
[07:28:50 PM]
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.19    0.00    4.07    0.00    0.00   92.75
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdc               0.00     1.00    0.00    3.00     0.00    16.00    10.67     0.00    0.00    0.00    0.00   0.00   0.00
[07:28:51 PM] ★リサイズ完了
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           4.95    0.00    2.82    0.69    0.00   91.54
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdc               0.00   279.00    0.00  495.00     0.00  3484.00    14.08     0.14    0.28    0.00    0.28   0.25  12.30

まとめ

今回の検証で得られた結果で分かったこととしては以下となります。

60GB程度のInnodb_buffer_pool_sizeの変更に約20秒かかる。
サイズ変更が開始されると終了するまでの間でqpsのバラつきが発生し、タイミングによっては0qpsになり、その後qpsやerr/sが増大するといった挙動が見られた。
変更にかかる約20秒程の間でslow queryが229件発生し、最大で3秒程のクエリタイムだった。
当初影響が想定されていた dirty buffer については、disk flush して 0 になるような挙動なかった。

検証前にイメージしていた結果とは異なっていたこともあり改めてドキュメントを確認したところ、オンラインでinnodb_buffer_pool_sizeを変更する際の注意点が書いてありました。(今回欲しかった情報はドキュメントに記載があったのでやっぱりちゃんとドキュメントを読むことは大事だと改めて実感)

MySQL 8.0 リファレンスマニュアルによると、

バッファプールのサイズを変更する前に、InnoDB API を介して実行されるアクティブなトランザクションおよび操作を完了する必要があります。サイズ変更操作を開始しても、すべてのアクティブなトランザクションが完了するまで操作は開始されません。サイズ変更操作が進行中になると、バッファプールへのアクセスを必要とする新しいトランザクションおよび操作は、サイズ変更操作が終了するまで待機する必要があります。バッファプールのサイズ変更操作の開始後に開始された場合、ネストされたトランザクションは失敗する可能性があります。

とのことなので、今回のinnodb_buffer_pool_sizeをオンラインで縮小する際に気にするべきポイントは、I/O負荷よりも上記MySQLの仕様に対する影響がアプリ側で許容できるのか？という点が重要でした。

あくまで今回の結果は私の担当案件の場合なのでこの内容は参考レベルに見ていただき、実際に同様のことをすることになった際は皆さんの環境でしっかり検証をお願いします！(その時にこのブログが何かの役に立てば幸いです)