【MySQL】innodb_buffer_pool_sizeと各種バッファの役割・確認方法

MySQLのメモリも、PostgreSQLと同様に全接続で共有する領域と、接続ごとに確保する領域へ大別できる。
innodb_buffer_pool_sizeは前者、sort_buffer_size・join_buffer_sizeは後者を制御するパラメータである。

graph TD
  C1[クライアント接続1] --> B1[スレッド1]
  C2[クライアント接続2] --> B2[スレッド2]
  B1 --> W1["sort_buffer_size / join_buffer_size<br/>接続ごとに確保"]
  B2 --> W2["sort_buffer_size / join_buffer_size<br/>接続ごとに確保"]
  B1 --> S["innodb_buffer_pool_size<br/>全接続で共有"]
  B2 --> S
  W1 -.超過時.-> T[一時ファイル]
  W2 -.超過時.-> T
  S --> D[(ディスク)]

参考: 【PostgreSQL】shared_buffersとwork_memの役割と確認方法

innodb_buffer_pool_sizeの役割

innodb_buffer_pool_sizeは、InnoDBがテーブルやインデックスのページをキャッシュする共有メモリのサイズである。
クエリが必要とするページがバッファプール上にあればディスクI/Oが発生しない。サイズが小さいとキャッシュから追い出されるページが増え、ディスク読み込みが増加する。

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

デフォルト値は128MBであり、SHOW VARIABLESの値はバイト単位のため読みにくい。
@@構文でシステム変数を直接参照し、ROUND関数と組み合わせるとMB単位に変換できる。

mysql> SELECT ROUND(@@innodb_buffer_pool_size / 1024 / 1024, 0) AS innodb_buffer_pool_size_mb;
+-----------------------------+
| innodb_buffer_pool_size_mb  |
+-----------------------------+
| 128                         |
+-----------------------------+

sort_buffer_size・join_buffer_sizeの役割

sort_buffer_sizeはソート処理1つあたりに使用できる作業メモリの上限であり、ORDER BYやGROUP BYで使われる。
join_buffer_sizeはテーブル結合時に使用できる作業メモリの上限であり、インデックスを使わない結合で使われる。
どちらも接続（スレッド）ごとに確保され、上限を超えるとディスク上の一時ファイルを使うため処理が遅くなる。

mysql> SHOW VARIABLES LIKE 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+

mysql> SHOW VARIABLES LIKE 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+

どちらもデフォルト値は256KBである。
PostgreSQLのwork_memは1つのパラメータでソート・結合の両方を制御するが、MySQLはソート用と結合用でパラメータが分かれている。

innodb_buffer_pool_sizeが足りているかを確認する

Innodb_buffer_pool_read_requests（論理読み込み数）とInnodb_buffer_pool_reads（バッファプールになくディスクから読んだ回数）からヒット率を計算する。

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Innodb_buffer_pool_read_requests  | 15350 |
+-----------------------------------+-------+

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Innodb_buffer_pool_reads  | 1015  |
+---------------------------+-------+

SQLでまとめて計算する場合はperformance_schema.global_statusを参照する。

mysql> SELECT
    ->   ROUND(100.0 * (1 - r.Value / q.Value), 2) AS hit_ratio
    -> FROM
    ->   (SELECT VARIABLE_VALUE AS Value FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
    ->   (SELECT VARIABLE_VALUE AS Value FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') q;
+-----------+
| hit_ratio |
+-----------+
| 99.75     |
+-----------+

値はサーバー起動（またはFLUSH STATUS）以降の累積であり、PostgreSQLのpg_stat_database.blks_hit・blks_readから求めるヒット率と同じ考え方である。
99%を大きく下回る場合はinnodb_buffer_pool_sizeの不足を疑う。

innodb_buffer_pool_pages_total・innodb_buffer_pool_pages_freeを確認すると、バッファプールの空きページ数が分かる。

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| Innodb_buffer_pool_pages_total  | 8192  |
+---------------------------------+-------+

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_pages_free';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_free  | 4986  |
+--------------------------------+-------+

空きページの少ない状態が続く場合、データの増加にバッファプールが追いついていない可能性がある。

sort_buffer_sizeが足りているかを確認する

以降の例では、名前用の文字列カラムと500バイトのパディング用カラムを持つt1・t2という2つのテーブルへ、それぞれ50,000行を投入した状態を使う。

CREATE TABLE t1 (id INT PRIMARY KEY, val INT, name VARCHAR(100));
CREATE TABLE t2 (id INT PRIMARY KEY, val INT, padding VARCHAR(500));

sort_buffer_sizeの不足は、Sort_merge_passesステータス変数で判断する。
ソートするデータがsort_buffer_sizeに収まらない場合、一時ファイルを使ったマージソートに切り替わり、Sort_merge_passesが加算される。

sort_buffer_sizeを32KBまで下げてソートを実行すると、マージパスが発生する。

mysql> SET SESSION sort_buffer_size = 32768;
mysql> FLUSH STATUS;
mysql> SELECT id FROM t2 ORDER BY padding, val;
mysql> SHOW SESSION STATUS LIKE 'Sort_merge_passes';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Sort_merge_passes  | 397   |
+--------------------+-------+

sort_buffer_sizeを8MBまで増やすと、同じソートでもマージパスがほぼ発生しなくなる。

mysql> SET SESSION sort_buffer_size = 8*1024*1024;
mysql> FLUSH STATUS;
mysql> SELECT id FROM t2 ORDER BY padding, val;
mysql> SHOW SESSION STATUS LIKE 'Sort_merge_passes';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Sort_merge_passes  | 1     |
+--------------------+-------+

EXPLAINのExtra列にUsing filesortが表示されている場合、ソート処理が発生していることを示す。
Using filesortがあるだけでは一時ファイルを使ったとは限らないため、実際に一時ファイルを使ったかどうかはSort_merge_passesの増加で確認する。

mysql> EXPLAIN SELECT * FROM t1 ORDER BY name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 50085
     filtered: 100.00
        Extra: Using filesort

join_buffer_sizeが足りているかを確認する

インデックスを使わない結合では、EXPLAINのExtra列にUsing join bufferが表示される。

mysql> EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.val = t2.val\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 48249
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 50085
     filtered: 10.00
        Extra: Using where; Using join buffer (hash join)

(hash join)は、MySQL 8.0.18以降で使われるハッシュ結合アルゴリズムを示す。
結合対象のテーブルが大きくjoin_buffer_sizeに収まらない場合、ハッシュテーブルの一部をディスクに書き出す処理が発生する。
結合列にインデックスを張るとUsing join bufferが消え、join_buffer_sizeに依存しないインデックス経由の結合に切り替わる。

sort_buffer_size・join_buffer_sizeはクエリ全体の上限ではない

PostgreSQLのwork_memと同様に、sort_buffer_size・join_buffer_sizeはクエリ単位ではなく、ソートや結合の操作1つあたりの上限である。
1つのクエリに複数のソートや結合があれば、それぞれが上限までメモリを消費する。
さらに接続数が多いほど、接続ごとに確保される分の合計も増える。
最悪ケースのメモリ使用量は「同時接続数 × クエリ内の操作数 × 各バッファのサイズ」まで膨らむため、sort_buffer_size・join_buffer_sizeを大きくしすぎるとメモリ枯渇を招く。

設定値の目安と変更方法

innodb_buffer_pool_sizeは、専用のデータベースサーバーであれば物理メモリの最大80%程度まで割り当てられる。
ただしOSやMySQLの他のプロセスが過度なページングを起こさない範囲で、余裕を残しておく必要がある。

sort_buffer_size・join_buffer_sizeはデフォルトの256KBのまま運用されがちだが、集計処理や結合が多いシステムでは不足しやすい。
全体を引き上げる前に、一時ファイルを多用する特定のセッションへSET SESSIONで設定する方が安全である。

SET SESSION sort_buffer_size = 4*1024*1024;
[対象のクエリ];

サーバー全体の設定を変更する場合は、my.cnfまたはSET PERSISTを使用する。

SET PERSIST innodb_buffer_pool_size = 4*1024*1024*1024;
SET PERSIST sort_buffer_size = 1024*1024;

innodb_buffer_pool_sizeは動的変数であり、SET GLOBALで実行中のサーバーに対しても変更できる。
拡大時は他のスレッドからのバッファプールへのアクセスをブロックする一方、縮小時はデフラグとページ解放を並行アクセスを許可しながら行う。
このため縮小時には一時的にページ不足に陥ることがある。

参考: 【MySQL】my.cnfの場所を探す
参考: 【MySQL】再起動が必要な設定を見分ける