この記事では MySQL 5.6 の InnoDB ストレージエンジンでテーブルデータを SSD に保存する環境むけの最適化について解説します。
SSDとHDDでは全く異なる性能特性になります。MySQL のデフォルト設定では HDD向けの設定になっているためにSSDの性能を活かすには デフォルト設定を変更する必要があります。
設定内容
設定を行うには SSD という物理デバイスに対する最適化になりますので、使用している SSD の性能を調査しておく必要があります。
以下のパラメータを設定します。
innodb_flush_neighbors = 0 innodb_page_size = 4K innodb_io_capacity = 2000
- innodb_flush_neighbors
MySQL 5.6 で追加されたパラメータで近隣ページの書き出しを無効化できるようになりました。バッファプール上で変更されたデータをディスクに書き出す場合に、近隣のページをまとめて1回のI/Oで書き出す仕組みはランダムアクセスで書き込み性能が大幅に劣化するHDD向けに最適化された機能ですが、テーブルデータをSSDに保存する環境ではランダムアクセスで性能が劣化しないためにこの機能を無効化した方が性能が高くなります。
- innodb_page_size
HDDでは読み書きする際にデータの記録位置にヘッドを動かしプラッタを回転させるのHDDは必要な複数のデータが1つのブロック内に保存されていれば、ヘッドを動かしプラッタを回転させる時間、すなわちシークタイムが減少します。SSDでは可動部が無く電気的に情報を読み書きしますので、必要な複数のデータが離れたブロックに保存されていても性能影響を受けません。
単一のページに多くの行が含まれている場合に競合が問題となることが多くなりやすいため、小さいページサイズは小さな書き込みを伴うOLTPワークロードで効率的になると考えられます。SSDのブロックサイズに近いページサイズにすることでディスクに書き込まれるデータの量を最小限に抑えることが出来ると考えられます。
この設定とあわせてファイルシステムのブロックサイズも調整すべきです。
- innodb_io_capacity, innodb_io_capacity_max
-
バックグラウンド·フラッシング速度の制御を行うための変数です。大きな値を指定しすぎるとI/Oの帯域を占有していまうので、動作環境にあわせて設定する必要があります。実際の稼働環境を想定した負荷テストで適切な値を探します。
| パラメタ | デフォルト | 説明 |
|---|---|---|
| innodb_flush_neighbors | 1 |
InnoDBのバッファプールからページをフラッシュすることも同程度の他のダーティ·ページをフラッシュするかどうかを指定します。 0: オフ1: バッファプールから同じ程度での連続したダーティ·ページをフラッシュします 2: 設定は、バッファプールから同じ程度でダーティページをフラッシュします。 |
| innodb_page_size | 16k | ページサイズ 4k, 8k, 16k |
| innodb_io_capacity | 200 | 1秒間にページのI/Oを行えるかの閾値になります。 |
| innodb_io_capacity_max | innodb_io_capacity * 2 | デフォルト値は innodb_io_capacityの２倍となります。 |