MySQL メモ3 InnoDB最低限の設定(バッファープールサイズ)

バッファープール

InnoDBでは登録されているデータとIndexをメモリ上にキャッシュすることで、ディスクへのI/Oを抑える仕組みがある。データとIndexをバッファープールと呼ばれる領域にキャッシュさせることが、チューニングの第一歩となる。

理想的には、バッファープールのサイズをできるだけ大きな値に設定して、サーバー上のほかのプロセスが過剰なページングなく実行するように、十分なメモリーを残します。バッファープールが大きいほど、InnoDB はさらにインメモリーデータベースのように動作し、ディスクから 1 回データを読み取り、後続の読み取り時に、メモリーからデータにアクセスします。
出典:MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.9.1 InnoDB バッファープール

バッファープールサイズは、登録済みデータとIndexの合計以上の値を設定した場合に、
すべてのデータとIndexがキャッシュされるためパフォーマンスが向上する。

MySQLの公式に記載されている通り、バッファープールサイズの値は大きければいいのだが、
使えるメモリには制限があるので、適切な値を算出する必要がある。

下記の記事で記載されているSQLで算出が可能。

https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

測定ツールを使える場合は、"MySQL Tuner"が便利です。
下記のような測定値が計測可能です。

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 896.0M/446.5M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 100.0M * 2/896.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 7 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: -33.57% (-466 hits/ 1388 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)

データ量は日々増えていくものなので、定期的にバッファープールサイズに不足がないことは確認が必要。

お金はたくさんあるけど、バッファープールサイズの適正値を調べる時間がない人は、
DB専用のサーバを用意してメモリを最大限積んで、メモリの80%の値を設定すればOK。