MySQL のパフォーマンス最適化のヒント

ハードウェア構成は、データベースのパフォーマンスに関する重要な考慮事項です。ハードウェア構成を定義する前に、アプリケーションのアクティブ ユーザー数と同時ユーザー数、データベースとインデックスのサイズ、アプリケーションまたはサービスで予想されるレイテンシを十分に理解することが重要です。ハードウェアに関する重要な考慮事項は次のとおりです。

処理能力は、パフォーマンスの高いデータベース システムで最も重要な要素の一つです。同時接続数、ユーザー数、スレッド数に基づいて、データベース リクエストの処理に必要なコア数が決まります。アプリケーションが最適なレベルで実行されるには、データベースに割り当てられる CPU で、通常のワークロードとピーク時の（極端な）ワークロードを処理できる必要があります。

Google Cloud のフルマネージド MySQL サービスである Cloud SQL の場合、CPU は仮想 CPU（vCPU）の形式で割り当てられます。最終的には、データベースに割り当てられる vCPU の数に基づいて、データベース インスタンスのメモリ容量とネットワーク スループットが決まります。各 vCPU に最大メモリ容量が割り当てられており、ネットワーク スループットも vCPU の数に基づいて変化するためです。Cloud SQL ではインスタンスの vCPU 数を柔軟にスケーリングできるため、アプリケーションのメモリとネットワーク スループットの要件を簡単に満たすことができます。

データベースに割り当てるメモリの容量を決定するための重要な考慮事項として、ワーキング セットがバッファプールに収まるようにすることが挙げられます。ワーキング セットは、データベースによってある時点でアクティブに使用されるデータです。このワーキング セットや頻繁にアクセスされるデータ（通常はデータベース データ、インデックス、セッション バッファ、ディクショナリ キャッシュ、ハッシュ テーブルなど）を保持するのに、十分なメモリが割り当てられる必要があります。十分なメモリが割り当てられているかどうかを確認する 1 つの方法は、データベースでディスク読み取りステータスを確認することです。通常のワークロード条件下では、ディスクの読み取りを少なく、または最小限に抑えるのが理想的です。

インスタンスに対するメモリ割り当てが不十分な場合、インスタンスに「メモリ不足」の問題が発生する可能性があります。これにより、データベース インスタンスが再起動され、データベースまたはアプリケーションのダウンタイムが発生します。

データベース ストレージも、パフォーマンスの最適化で重要な役割を果たすコンポーネントです。Cloud SQL で提供される 2 種類のストレージ

• SSD（デフォルト）
• HDD

SSD は HDD よりもパフォーマンスとスループットが大幅に向上します。特に本番環境のワークロードには、パフォーマンス向上のために SSD を選択します。

インスタンスに割り当てられる、読み取りと書き込みの 1 秒あたりの入出力オペレーション（IOP）は、インスタンスの作成中に割り当てられたストレージの量によって異なります。ディスクサイズが大きいほど、読み取りと書き込みの IOP は大きくなります。そのため、IOP のパフォーマンスを向上させるには、データサイズの大きなインスタンスを作成することをおすすめします。次の Google Cloud コンソールのスクリーンショットは、作成時にデータベース インスタンスに割り当てられたリソース（最大容量を含む）の概要を示しています。これは、ユーザーがデータベースをインスタンス化した後のデータベースの構成を正確に確認、理解するうえで役立ちます。

ネットワーク レイテンシを短縮する方法の一つは、アプリケーションやサービスに最も近いインスタンス リージョンを選択することです。Cloud SQL for MySQL はすべての Google Cloud リージョンで利用できるため、エンドユーザーに可能な限り近い場所でデータベースをインスタンス化しやすくなります。

Cloud SQL では、データベース インスタンスに割り当てられたリソース（CPU、メモリ、ストレージ）を簡単にスケールアップまたはスケールダウンできます。これは、リソース要件が異なるワークロードに対して有効です。たとえば、ユーザーは、ワークロード要件が増加する期間にリソースを増やし（スケールアップ）、ワークロードのピークが終了したらリソースをスケールダウンできます。

このセクションでは、パフォーマンスを向上させるための MySQL データベース構成のベスト プラクティスについて説明します。

新しいデータベースを作成するときには、最新バージョンの MySQL を選択してください。最新バージョンは、バグの修正と最適化により、古いバージョンよりもパフォーマンスが向上しています。Cloud SQL は、市場で入手可能な MySQL の最新バージョンを提供しており、それが新しいデータベースを作成する際のデフォルト バージョンになっています。詳しくは、Cloud SQL でサポートされている MySQL のバージョンをご覧ください。

MySQL インスタンスの場合、サポートされているストレージ エンジンは InnoDB のみです。Innodb バッファプール サイズは、最適なパフォーマンスのために定義する必要のある最初のパラメータです。バッファプールは、テーブル キャッシュ、インデックス キャッシュ、フラッシュ前の変更されたデータ、アダプティブ ハッシュ インデックス（AHI）などの他の内部構造を格納するために割り当てられるメモリ領域です。

Cloud SQL では、インスタンス サイズに基づいて InnoDB バッファプールに割り当てられるインスタンス メモリのデフォルト値（最大 72%）が定義されます（デフォルト値はインスタンス サイズによって異なります）。詳細はインスタンス サイズごとのバッファプール設定をご覧ください。Cloud SQL では、データベース フラグを使用して、アプリケーションのニーズに応じてバッファプールのサイズを柔軟に変更できます。

バッファプールのサイズは、InnoDB バッファプールとは別に、セッション バッファ、辞書キャッシュ、performance_schema テーブル（有効な場合）用にインスタンスで十分な空きメモリを使用できるようにする必要があります。

ユーザーは、インスタンスから行われるディスク読み取りを確認して、ディスクから読み取られているデータの量と、バッファプールから満たされた読み取りの量を特定できます。ディスク読み取り量が多い場合は、バッファプール サイズとインスタンスのメモリを増やすと、読み取りクエリのパフォーマンスが向上します。

InnoDB のログファイルまたは redo ログは、テーブルデータに対するデータ変更を記録します。InnoDB のログファイルのサイズは、単一の redo ログファイルのサイズを定義します。

redo ログサイズが大きい、書き込み負荷の高いワークロードの場合、チェックポイントのフラッシュ アクティビティを頻繁に行う必要がなく、書き込みのスペースが広がり、ディスク I/O を節約できるため、書き込みパフォーマンスが向上します。redo ログの合計サイズは、innodb_log_file_size × innodb_log_file_size で計算できます。これは、データベース アクセスのビジー状態の間に、1 〜 2 時間程度のデータ書き込みを行うのに十分です。

Cloud SQL のデフォルト値は 512 MB です。Cloud SQL では、データベース フラグを使用して InnoDB ログファイルのサイズを柔軟に増やすこともできます。

注: InnoDB ログファイルのサイズを大きくすると、障害復旧にかかる時間が長くなります。

innodb_flush_log_at_trx_commit フラグは、ログデータがディスクにフラッシュされる頻度と、トランザクションが commit されるたびにフラッシュするかどうかを制御します。

innodb_flush_log_at_trx_commit の値を 0 または 2 に変更することで、リードレプリカの書き込みパフォーマンスが向上します。

Cloud SQL は、Cloud SQL プライマリの耐久性設定の変更をサポートしていません。ただし、Cloud SQL ではリードレプリカのフラグの変更が許可されます。リードレプリカの耐久性を下げると、レプリカの書き込みパフォーマンスが向上します。これは、レプリカでのレプリケーション ラグへの対処に役立ちます。詳しくは、innodb_flush_log_at_trx_commit をご覧ください。

InnoDB のログバッファ サイズは、InnoDB がログファイルの書き込みに使用するバッファ（redo ログ）の量です。

データベース内のトランザクション（挿入、更新、削除）が大きく、使用されているバッファが 16 MB を超える場合は、InnoDB がトランザクションを commit する前にディスク IO を実行する必要があります。これにより、パフォーマンスが影響を受けます。ディスク IO を回避するには、innodb_log_buffer_size の値を増やします。

Cloud SQL では、InnoDB ログのバッファサイズのデフォルト値として 16 MB が定義されています。MySQL のステータス変数 innodb_log_waits は、innodb_log_buffer_size が小さかった回数と、トランザクションを commit する前に InnoDB がフラッシュの発生を待機する必要があった回数を示します。innodb_log_waits の値が 0 より大きく、増加している場合は、パフォーマンスを向上させるためにデータベース フラグを使用して innodb_log_buffer_size の値を増やします。innodb_log_buffer_size と innodb_log_waits の値は、MySQL シェル（CLI）で次のクエリを実行することで特定できます。これらのクエリは、MySQL でのステータス変数とグローバル 変数の値を表示します。

SHOW GLOBAL VARIABLES LIKE 'innodb_log_buffer_size';

SHOW GLOBAL STATUS LIKE 'innodb_log_waits';

InnoDB IO 容量により、バックグラウンド タスク（バッファプールからのページ フラッシュ、変更バッファからのデータのマージなど）に使用できる IOP の数が定義されます。  

Cloud SQL では、innodb_io_capacity で 5,000、innodb_io_capacity で 10,000 のデフォルト値が定義されています。

このデフォルト設定はほとんどのワークロードに最適ですが、ワークロードでの書き込みが多い場合や、インスタンスで適用されていない変更が多く、インスタンスで十分な IOP がある場合は、innodb_io_capacity と innodb_io_capacity を増やすことを検討してください。適用された変更の値は、MySQL シェルで次のクエリを使用して確認できます。

mysql -e 'show engine InnoDB status \G;' | grep Ibuf

セッション バッファは、個々のセッションに割り当てられるメモリです。多数の挿入、更新、並べ替え、結合が含まれるアプリケーションやクエリで、より大きなバッファが必要な場合は、特定のセッションでクエリを実行するときに大きなバッファ値を定義することで、パフォーマンスのオーバーヘッドを回避できます。ユーザーはグローバル レベルで過剰なバッファ割り当てを防止できます。これにより、すべての接続の値が増加し、その結果インスタンスの合計メモリ使用量が増加します。次のバッファのデフォルト値を変更すると、クエリのパフォーマンスが向上します。これらの値は、データベース フラグを使用して変更できます。

sort_buffer_size

join_buffer_size

tmp_table_size

max_heap_table_size

これらはセッションごとのバッファ値であるため、上限を増やすと、すべての接続に影響があり、最終的には全体的なメモリ使用量が増加する可能性があります。

データベース インスタンス内（単一または複数のデータベース）にテーブルが多すぎる（数千を超える）場合は、table_open_cache と table_open_cache の値を増やして、テーブルを開く速度を改善できます。

Table_definition_cache は、テーブルを開く時間を短縮します。エントリはテーブルごとに 1 つだけです。テーブル定義キャッシュは容量が少なく、ファイル記述子を使用しません。ディクショナリ オブジェクト キャッシュ内のテーブル インスタンスの数が table_definition_cache の上限を超えると、LRU メカニズムによってテーブル インスタンスがエビクションの対象としてマークされ、最終的にはディクショナリ オブジェクト キャッシュから削除されて、新しいテーブル定義用のスペースが確保されます。このプロセスは、新しいテーブルスペースが開かれるたびに実行されます。アクティブでないテーブルスペースのみが閉じられます。このエビクション プロセスにより、テーブルを開く速度が低下します。

Table_open_cache は、すべてのスレッドで開いているテーブルの数を定義します。Opened_tables ステータス変数を確認することで、テーブルのキャッシュを増やす必要があるかどうかを確認できます。Opened_tables の値が大きく、FLUSH TABLES を頻繁に使用しない場合は、table_open_cache 変数の値を増やすことを検討してください。

Table_open_cache と Table_open_cache は、インスタンス内の実際のテーブル数に設定できます。詳しくは、Cloud SQL の高オープンテーブル数 Recommender についてをご覧ください。

注: Cloud SQL では、これらの値を柔軟に変更できます。

テーブルに対する主キーを定義すると、レコードの検索、取得、並べ替えを高速化するためにデータが物理的に整理され、パフォーマンスが向上します。

OLTP システムでは、整数値が自動的にインクリメントされる主キーが理想的です。

主キーがないことは、行ベースのレプリケーション シナリオでレプリケーションの遅延やレプリケーション ラグが発生する主な原因の一つにもなります。

インデックスを作成すると、データの取得が速くなり、読み取りクエリのパフォーマンスが向上します。クエリの WHERE、ORDER BY、GROUP BY 句で使用する列のインデックスを作成します。

注: インデックスが多すぎるか、使用されていない場合も、データベースのパフォーマンスが低下する可能性があります。

パフォーマンス テストまたはベンチマークを実行して、構成が最適であるかどうか、ハードウェア、MySQL データベース、スキーマ設計の構成を調整してさらに改善できるかどうかを確認します。パラメータを 1 つずつ変更し、ベンチマーク結果と比較して改善の余地があるかどうかを確認します。

接続プーリングは、接続を必要とするすべてのプロセスで使用できる接続のプールを作成して管理する手法です。接続プールを使用すると、全体的なリソース使用量を削減しながら、アプリケーションのパフォーマンスを大幅に向上させることができます。接続数やタイムアウトなど、アプリケーションからの接続を管理する方法の詳細を確認します。

リードレプリカは（ゾーン全体で複数）を使用して、プライマリ インスタンスからリード ワークロードを開放できます。これにより、プライマリ インスタンスのオーバーヘッドと負荷が減少し、プライマリ インスタンスのパフォーマンスが向上します。また、リードレプリカに対する読み取りクエリに使用できるリソースも増えます。 

データベース クエリのルーティングが可能なオープンソースの高性能の MySQL プロキシである ProxySQL を使用すると、Cloud SQL for MySQL データベースを水平方向にスケーリングできます。

クエリを数分から数時間実行すると、パフォーマンスの低下につながることがわかっています。

• undo ログは、トランザクションをロールバックするために変更した行の古いバージョンを保存するため、また、トランザクションで一貫した読み取り（データのスナップショット）を行うために使用されます。これらの undo ログは、最新バージョンが古いバージョンを指すリンクされたリストの形式で保存され、さらに古いバージョンを指すように保存されます。長時間実行されるトランザクションは、undo ログのパージを遅らせる傾向があるため、undo ログのリストが増えます。InnoDB は、大量の大量の undo ログや長いリンクリストを走査する必要があるため、パフォーマンスが低下します。
• 長時間実行されるクエリもリソース（メモリ、バッファ、ロックなど）を消費します。これらは長時間解放されず、リソース不足のために他のクエリに影響します。

1 回のトランザクションでのレコード変更（更新、削除、挿入）が多すぎると、多くのレコードでリソース（ロック、バッファ）を保持することになります。ログバッファがオーバーフローし、ディスク IO が発生する可能性があります。残りのクエリは、リソースまたはロックが解放されるまで待つ必要があります。その結果、バッファプールに取り込まれるデータが多くなりすぎて、バッファプールを使用できなくなります。このような大規模なトランザクションをロールバックすると、データベースのパフォーマンスも低下します。これを解決するには、大規模なトランザクションを小規模で高速なトランザクションに分割することをおすすめします。

クエリを常に最適化して、最善の結果（リソースの削減と実行の高速化）を実現します。MySQL クエリの調整に関する推奨事項を確認します。