バッファプールは十分か: ヒット率、wait_free、オンラインでのサイズ変更

MySQLメモリ

コマンド

SELECT ROUND(@@innodb_buffer_pool_size / 1024 / 1024) AS bp_mb,
       @@innodb_buffer_pool_chunk_size / 1024 / 1024 AS chunk_mb,
       @@innodb_buffer_pool_instances AS instances;
SELECT
  ROUND(100 * (1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
                 / NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'), 0)), 3) AS hit_pct,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_free') AS pages_free,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty') AS pages_dirty,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_wait_free') AS wait_free;

-- Online resize and status tracking
SET PERSIST innodb_buffer_pool_size = 8589934592;
SELECT VARIABLE_NAME, VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'Innodb_buffer_pool_resize_status%';

概要

バッファプールのサイズ、キャッシュヒット率、空き / ダーティページ数、空きページ待機 (wait_free) カウンターを表示する。再起動なしでサイズを変更し、進捗を追跡する。

使用する場面

読み取り IO が高い場合、RAM の増設 / 削減後、またはキャパシティプランニング時。

注意

ヒット率だけでは誤解を招く。99% でも毎秒数千回のディスク読み取りを意味することがある。Innodb_buffer_pool_reads の毎秒の増加量を確認する。wait_free > 0 はバッファプールが小さすぎるか、ページクリーナーが追いついていないことを意味する。新しいサイズは innodb_buffer_pool_chunk_size × instances の倍数に丸められる。サイズ変更中は短時間のストールが起こりうる (縮小時は特に)。ピーク時間帯は避ける。専用サーバーでは RAM の 60〜75% から開始する。接続ごとのメモリ、OS キャッシュ、その他のプロセス用の余裕を残す。スワップは小さめのバッファプールより常に悪い。

DBAtlas で開く →