DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

MySQL InnoDBバッファプールを最高のパフォーマンスに調整する

InnoDBバッファプールをマスターしてMySQLの最高のパフォーマンスを引き出しましょう。このガイドでは、バッファプールがデータとインデックスをキャッシュする仕組み、システムのRAMとワークロードに基づいた最適なサイズの計算方法、主要なステータス変数を使用した重要な監視戦略について詳しく説明します。`innodb_buffer_pool_size`、`innodb_buffer_pool_instances`、およびその他のパラメータを調整して、ディスクI/Oを削減し、クエリ実行を高速化する方法を学びます。

MySQL InnoDBバッファプールを最高のパフォーマンスに調整する

InnoDBバッファプールは、MySQLのパフォーマンス作業が実を結ぶか、希望的観測に終わるかの分かれ目となる場所です。データとインデックスページをメモリにキャッシュするため、クエリはディスクにアクセスせずにホットページを読み取ることができます。バッファプールが小さすぎると、MySQLはストレージの待機に多くの時間を費やします。大きすぎると、オペレーティングシステムがスワップを開始し、サーバーのパフォーマンスが低下します。

私は通常、バッファプールのチューニングを単一の魔法の設定ではなく、測定の演習として扱います。適切なサイズから始め、実際のトラフィック下でのサーバーの動作を監視し、ゆっくりと調整します。

InnoDBバッファプールとは？

InnoDBバッファプールは、InnoDBストレージエンジンがデータとインデックスページをキャッシュするために使用する共有メモリ領域です。MySQLがデータを読み取る必要がある場合、まず必要なページがすでにバッファプールにあるかどうかを確認します。ある場合（キャッシュヒット）、データはメモリから直接取得され、ディスクからの読み取りよりも桁違いに高速です。ページがバッファプールにない場合（キャッシュミス）、InnoDBはディスクから読み取り、バッファプールにロードしてから提供します。バッファプールは、変更されたページ（ダーティページ）をディスクにフラッシュする前にメモリに保持することで、書き込み操作にも役割を果たします。

バッファプールのチューニングが重要な理由

MySQLデータベースのパフォーマンスは、バッファプールがどれだけ効果的に利用されているかに大きく影響されます。チューニングの主な理由は次のとおりです。

  • ディスクI/Oの削減： 主な目標は、可能な限り多くの読み取り要求をメモリから処理し、低速なディスク読み取りを最小限に抑えることです。これは、特に読み取り負荷の高いワークロードで重要です。
  • クエリレイテンシの改善： データ取得の高速化は、クエリ実行時間の短縮に直接つながり、アプリケーションの応答性が向上します。
  • スループットの向上： ディスクI/Oに関連するボトルネックを減らすことで、サーバーはより多くの同時操作を処理できます。
  • 効率的な書き込み操作： 主に読み取りキャッシュですが、バッファプールはディスクにフラッシュされる前に変更をステージングすることで、書き込みパフォーマンスにも影響を与えます。

最適なバッファプールサイズの決定

InnoDBの最も影響力のあるチューニングパラメータの1つはinnodb_buffer_pool_sizeです。これを正しく設定することが最も重要です。最適なサイズはいくつかの要因に依存するため、万能の答えはありません。

  • システムの総RAM： バッファプールは、オペレーティングシステム、MySQL接続メモリ、バックアップツール、監視エージェント、その他のローカルプロセスを圧迫するほどメモリを消費してはいけません。専用データベースサーバーでは、一般的な開始範囲は**RAMの50％から75％**です。一部の専用サーバーはより高い値で実行できますが、スワップとメモリプレッシャーを確認した後に限ります。
  • ワークロードの特性： 読み取り負荷の高いワークロードは、書き込み負荷の高いワークロードよりも大きなバッファプールの恩恵を受けます。
  • データベースのサイズ： アクティブデータセット（頻繁にアクセスされるデータ）がデータベース全体のサイズよりも大幅に小さい場合、より小さなバッファプールで十分な場合があります。ただし、アクティブデータセットが大きい場合は、それを収容できる十分な大きさのバッファプールが必要です。

注意： innodb_buffer_pool_sizeを高く設定しすぎないでください。オペレーティングシステムによる過度のスワップが発生し、パフォーマンスが著しく低下する可能性があります。OSおよび他のMySQLスレッドに十分なメモリを常に残してください。

設定パラメータ：innodb_buffer_pool_size

これはバッファプールサイズを設定するための主要なパラメータです。バイト、キロバイト、メガバイト、またはギガバイトで指定します。

例： バッファプールサイズを8GBに設定するには：

[mysqld]
innodb_buffer_pool_size = 8G

注： 大規模な専用サーバーでは、多くのチームがRAMの約70％から開始し、監視します。接続数、一時テーブルの使用状況、バックアップ動作、OSページキャッシュを確認せずに、別の環境からのパーセンテージをコピーしないでください。

InnoDBバッファプールのパフォーマンス監視

innodb_buffer_pool_sizeを設定したら、その有効性を評価し、潜在的な問題を特定するために継続的な監視が不可欠です。いくつかの主要なメトリクスがバッファプールのパフォーマンスを評価するのに役立ちます。

1. Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests

これらの統計は、SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';で利用可能で、バッファプールの効率を示します。

  • Innodb_buffer_pool_read_requests：バッファプールに対して発行された論理読み取り要求の総数。
  • Innodb_buffer_pool_reads：ディスクから読み取らなければならなかった論理読み取りの数（バッファプールになかったため）。

計算：

  • バッファプールヒット率 = (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100

読み方： 非常に高いヒット率は健全なOLTPシステムでは一般的ですが、その数値は誤解を招く可能性があります。サーバーは高いヒット率を示す一方で、1つの不良なレポートクエリが数百万行をスキャンしている可能性があります。ヒット率が低い場合は、バッファプールが小さすぎるか、ワークロードがメモリが合理的に保持できる以上のデータを読み取っていることを意味する場合があります。

コマンド例：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

2. Innodb_buffer_pool_wait_free

このステータス変数は、バッファプール操作が空きページを待たなければならなかった回数をカウントします。この数値が一貫して増加している場合、バッファプールが空きページを見つけるのに苦労していることを示し、小さすぎるか、フラッシュが必要なダーティページの割合が高いことを示唆しています。

コマンド例：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';

3. Innodb_buffer_pool_pages_dirty

これは、現在バッファプール内にあるダーティページの数を示します。ダーティページの数が多いということは、多くの変更がディスクにフラッシュされるのを待っていることを意味します。ある程度のダーティページは正常ですが、一貫して高い数値は、I/Oボトルネックまたはバッファプールが書き込みアクティビティに対応するには小さすぎることを示している可能性があります。

コマンド例：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

高度なバッファプールチューニングパラメータ

innodb_buffer_pool_sizeが最も重要ですが、他のパラメータもバッファプールの動作に影響を与える可能性があります。

  • innodb_buffer_pool_instances： バッファプールを複数のインスタンスに分割し、マルチコアシステムでの競合を減らすのに役立ちます。デフォルトと動作はMySQLのバージョンによって異なり、最近のMySQLバージョンでは内部の並行性が改善されています。習慣的にCPU数に設定しないでください。大きなバッファプールの場合は、4や8などの控えめな値をテストし、競合メトリクスを比較してください。

    [mysqld]
innodb_buffer_pool_instances = 8

    ヒント： innodb_buffer_pool_sizeがinnodb_buffer_pool_instancesで割り切れることを確認してください。

  • innodb_flush_method： InnoDBがデータとログファイルをディスクにフラッシュする方法を制御します。O_DIRECT（Linux上）などのオプションは、OSファイルシステムキャッシュをバイパスし、二重バッファリングを防ぎ、特にバッファプールが大きい場合にパフォーマンスを向上させる可能性があります。

    [mysqld]
innodb_flush_method = O_DIRECT

    警告： 特定のOSとハードウェアでO_DIRECTを徹底的にテストしてください。常に最良の選択とは限りません。

  • innodb_log_file_sizeとinnodb_log_files_in_group： バッファプールの直接の一部ではありませんが、リドゥログのサイズは書き込みパフォーマンスに影響を与えます。ログが大きいと、チェックポイント（ダーティページのフラッシュ）の頻度が減り、書き込み負荷の高いワークロードのパフォーマンスが向上する可能性がありますが、リカバリ時間も長くなります。

実践的なチューニング戦略

  1. 控えめに開始する： 適切なinnodb_buffer_pool_size（例：専用サーバーではRAMの50〜75％）から始め、パフォーマンスを監視します。
  2. 主要メトリクスを監視する： SHOW GLOBAL STATUSを使用して、バッファプールのヒット率、Innodb_buffer_pool_wait_free、Innodb_buffer_pool_pages_dirtyを定期的に確認します。
  3. 段階的に増やす： ヒット率が一貫して高く、Innodb_buffer_pool_wait_freeが低い場合は、innodb_buffer_pool_sizeを段階的に増やし、その影響を観察することを検討してください。
  4. クエリをプロファイリングする： バッファプールのヒット率が低い場合、原因はバッファプールのサイズだけではない可能性があります。EXPLAINとslow_query_logを使用してスロークエリを調査し、欠落しているインデックスや非効率的なクエリパターンを特定します。
  5. 専用サーバー： 最適なパフォーマンスを得るには、サーバーをMySQL専用にします。これにより、他のサービスに影響を与えることなく、RAMのより大きな割合をバッファプールに割り当てることができます。
  6. innodb_buffer_pool_instancesを検討する： 大きなバッファプールを持つマルチコアシステムでは、innodb_buffer_pool_instancesを増やすことを試してみてください。

実践的なチューニングの手順

32 GBのRAMを搭載した専用MySQLサーバーをチューニングする現実的な方法を次に示します。まず、マシンで他に何が実行されているかを確認します。MySQLと軽量な監視のみが実行されている場合、20 GBから22 GBのバッファプールから開始するのが妥当です。アプリケーションコード、ログ配信、バックアップ、または負荷の高いエンドポイントツールも実行されている場合は、より低い値から開始します。目標は、Linuxが1日のうちで最も負荷の高い時間帯にスワップしないように、十分なメモリを残すことです。

[mysqld]
innodb_buffer_pool_size = 20G

再起動後、通常の負荷でサーバーを監視します。

free -m
vmstat 1
iostat -xz 1

MySQL内で、数分間隔でステータスを2回取得し、差分を比較します。

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';
SHOW GLOBAL STATUS LIKE 'Innodb_pages_read';
SHOW GLOBAL STATUS LIKE 'Innodb_pages_written';

通常のトラフィック中にInnodb_buffer_pool_readsが急速に増加し続け、ストレージの読み取りレイテンシが高い場合、サーバーはより多くのバッファプールメモリを必要としている可能性があります。Linuxがスワップしている場合は、バッファプールを減らします。ディスク書き込みが問題である場合、バッファプールを増やしても問題が一時的に隠れるだけかもしれません。リドゥログのサイズ、チェックポイントプレッシャー、または低速な書き込みクエリを調査する必要があるかもしれません。

ダーティページとチェックポイントプレッシャー

書き込み負荷の高いシステムでは、バッファプールが大きくても遅く感じられることがあります。多くのダーティページが蓄積されると、最終的にInnoDBはそれらをフラッシュする必要があります。ストレージが追いつかない場合、ユーザーはストールを経験する可能性があります。

役立つチェックには次のものがあります。

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW GLOBAL VARIABLES LIKE 'innodb_max_dirty_pages_pct';
SHOW ENGINE INNODB STATUS\G

ダーティページは正常です。警告サインはパターンです：ダーティページの上昇、チェックポイントエイジの増加、ディスク書き込みレイテンシの上昇、フォアグラウンドクエリの待機。

再起動後のウォームアップ

MySQLの再起動後、バッファプールのダンプとロードが有効になっていない限り、バッファプールはコールド状態で開始されます。コールドサーバーは、ホットページをストレージから再度読み取る必要があるため、最初の数分間は遅く見えることがよくあります。

メンテナンスウィンドウ中に再起動する本番システムの場合は、以下を検討してください。

[mysqld]
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

これにより、バッファプール全体が保存されるわけではありません。有用なページに関するメタデータが保存され、MySQLがそれらを再ロードできるようになります。これにより、特に予測可能なホットデータを持つシステムでは、再起動の影響を軽減できます。

バッファプールのチューニングで修正できないもの

適切なインデックスがないためにクエリが200 GBのテーブルをスキャンする場合、バッファプールを大きくしても、最初の数回の実行が多少マシになるだけかもしれません。アプリケーションが数千の接続を開き、各接続がソートや一時テーブル用にメモリを割り当てる場合、バッファプールだけがメモリコンシューマではありません。レポートジョブが5分ごとに昨日の全イベントストリームを読み取る場合、アクティブデータセットが単にメモリよりも大きい可能性があります。

そのため、バッファプールのチューニングは、クエリレビュー、インデックスレビュー、ワークロードレビューと並行して行う必要があります。メモリは、MySQLが同じ有用なページに繰り返しアクセスする場合に最も効果を発揮します。

悪いチューニングを防ぐいくつかの本番環境の習慣

バッファプールを変更するたびに、古い値、新しい値、理由、日付、改善が期待されるメトリクスをメモしておきます。これは、2年前にサーバーがなぜ26Gに設定されたのか誰かが尋ねるまでは退屈に思えます。そのメモがなければ、将来の運用担当者はダッシュボードとメモリプレッシャーからその決定をリバースエンジニアリングする必要があります。

通常のトラフィックだけでなく、バックアップやメンテナンスジョブも監視します。論理ダンプ、オンラインスキーマ変更、チェックサムジョブ、または負荷の高い分析エクスポートは、メモリとI/Oの動作を数時間にわたって変化させる可能性があります。営業時間中は問題ないように見えるバッファプールサイズも、夜間のバックアップが開始されると過剰になる可能性があります。

また、レプリカは個別に確認してください。レプリカは多くの場合、プライマリとは異なるワークロード（読み取りトラフィック、レポート、遅延ジョブ、バックアッププロセス）を実行します。プライマリのバッファプール設定をすべてのレプリカにコピーするのは便利ですが、それらのマシンの使用方法と一致しない場合があります。

一度に1つの主要な設定を変更し、古い値を書き留め、変更前後の同じメトリクスを監視します。サーバーが改善された場合は、変更を維持します。ボトルネックが読み取りから書き込みに移動しただけの場合は、さらに調査を続けます。バッファプールは重要ですが、データベースが何を求められているかを理解することの代わりにはなりません。