Posted inMySQL

MySQL再起動後の激重問題を解消！Buffer Poolを常に「ホット」に保つ秘訣

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

メンテナンス後の恐怖、「コールドキャッシュ」現象

このシナリオは、SysAdminの方々にとって決して珍しいものではないでしょう。設定を更新するために systemctl restart mysql を実行したとします。データベースは数秒で active (running) になりますが、本当の悪夢はここから始まります。続く15〜30分間、CPU使用率は10%から80〜90%まで急上昇し、ディスクI/Oは跳ね上がり、アプリケーションではタイムアウトエラーが多発します。

私が以前管理していたeコマースシステム（MySQL 8.0で動作する約100GBのDB）の実例では、事前の準備なしに再起動を行うたびに、クエリのレイテンシが5msから500msまで増大しました。これがまさにコールドキャッシュ（Cold Cache）現象です。この時、キャッシュ領域であるバッファプール（Buffer Pool）は空の状態です。MySQLはRAMから直接データを取り出す代わりに、ハードディスクからデータブロックを一つずつ読み取らざるを得なくなります。

再起動後にMySQLの動作が重くなる理由

解決策を理解するには、InnoDB Buffer Poolに注目する必要があります。これは最も重要なメモリ領域であり、頻繁にアクセスされるデータページやインデックスが格納されています。SELECTクエリが発行されると、MySQLはまずBuffer Pool内を探します（キャッシュヒット）。データが見つかれば、光速に近い速さで結果を返します。

しかし、RAMは一時的なメモリです。MySQLを停止すると、これらの「ホット」なデータはすべて消えてしまいます。再起動後、Buffer Poolは完全に空の状態になります。システムが実際のユーザーリクエストに基づいて重要なデータを再び読み込むには、非常に長い時間が必要となります。

手動での対策：低速かつリスクが高い

多くのエンジニアは、起動直後に大きなテーブルに対して SELECT COUNT(*) を実行したり、全データをクロールするスクリプトを走らせたりする「強制的な」手法を取ることがあります。しかし、この方法には3つの致命的な欠点があります。

  • システムが最も不安定な時期に、ディスクI/O帯域を使い果たしてしまう。
  • 不要な「ゴミ」データまでRAMに読み込んでしまう。
  • 制御が難しく、スクリプトのメンテナンスに手間がかかる。

推奨される解決策：InnoDB Buffer PoolのDump and Load

MySQL 5.6以降、非常に優れた機能が標準搭載されています。それは、シャットダウン前にRAM内にあるデータページのリストを記録し、起動時にそれらを自動的にリロードする機能です。

この機能の賢い点は、数十GBのデータそのものをファイルに保存するわけではないということです。保存されるのはSpace IDとPage ID（データページの識別子）のみです。そのため、ダンプファイルは通常数MB程度と非常に軽く、読み書きは一瞬で完了します。

1. 機能のステータスを確認する

まず、サーバーでこの機能が有効になっているか、以下のSQLコマンドで確認してください。

SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_at_shutdown';
SHOW VARIABLES LIKE 'innodb_buffer_pool_load_at_startup';

もし結果が OFF であれば、非常に強力な最適化ツールを無駄にしていることになります。

2. my.cnfファイルでの最適化設定

この設定を永続化するには、my.cnfファイル（通常は /etc/mysql/my.cnf）を開き、[mysqld] セクションに以下の行を追加します。

[mysqld]
# シャットダウン時にページリストを自動保存する
innodb_buffer_pool_dump_at_shutdown = ON

# 起動時にページを自動的にリロードする
innodb_buffer_pool_load_at_startup = ON

# デフォルトの25%ではなく、ホットなページを100%ダンプする
innodb_buffer_pool_dump_pct = 100

# 保存ファイル名（デフォルトはデータディレクトリ内）
innodb_buffer_pool_filename = ib_buffer_pool

保存後、MySQLを再起動してください。初回はまだ遅いかもしれませんが、次回の再起動からはシステムが素早く「本調子」になるのを実感できるはずです。

3. 再起動を待たずに即座にDump/Loadを実行する

サーバーを停止せずに、重いメンテナンス作業を行う直前に手動でリストをダンプしたい場合は、以下のコマンドを使用します。

-- 現在のデータを即座にファイルにダンプする
SET GLOBAL innodb_buffer_pool_dump_now = ON;

-- ファイルからRAMにデータを即座にロードする
SET GLOBAL innodb_buffer_pool_load_now = ON;

4. データロードの進捗を監視する

ロード処理はバックグラウンドで行われるため、MySQLの起動プロセスをブロックすることはありません。完全に「温まった」かどうかを確認するには、以下のコマンドを使用します。

SHOW STATUS LIKE 'innodb_buffer_pool_load_status';

Buffer pool(s) load completed という行が表示されれば、データベースは最大パフォーマンスでサービスを提供する準備が整ったことを意味します。

現場の経験から得た注意点

この機能は非常に有用ですが、以下の2点に注意してください。

  • ディスクI/O: バックグラウンドでのデータロードは依然としてディスクリソースを消費します。古いHDDを使用している場合、このプロセスが長引き、実際のクエリに影響を与える可能性があります。NVMe SSDであれば、全く心配ありません。
  • dump_pctの値: MySQL 8.0のデフォルトでは、ページの25%のみをダンプします。RAMに余裕がある場合は、システムの状態を完全に保持するために、思い切って100%に設定することをお勧めします。

おわりに

設定ファイルを修正するわずか2分の作業で、メンテナンス後のシステム遅延の悩みを完全に解消できます。これは、すべてのデータベース管理者が適用すべき「小さいながらも非常に効果的な」最適化ステップです。今すぐあなたのサーバーをチェックしてみてください！

Share:
Tags: