MySQL のパフォーマンス最適化のヒント
パフォーマンスの最適化は、データベース管理の重要な要素です。パフォーマンスの最適化は、データベース サーバーをホストするハードウェア コンポーネントとソフトウェア コンポーネントの選択から、データモデルの設計とスキーマ構成まで、データベース管理のすべてのステップで行うことができます。このドキュメントでは、クラウド内の MySQL データベース（特に Cloud SQL for MySQL）のパフォーマンス最適化のヒント、および新しいデータベースのインスタンス化と既存のデータベースの最適化のためのベスト プラクティスについて説明します。
ハードウェアに関する考慮事項
ハードウェア構成は、データベースのパフォーマンスに関する重要な考慮事項です。ハードウェア構成を定義する前に、アプリケーションのアクティブ ユーザー数と同時実行ユーザー数、データベースとインデックスのサイズ、アプリケーションまたはサービスの予想レイテンシを十分に把握することが重要です。ハードウェアに関する重要な考慮事項は次のとおりです。
中央処理装置（CPU）
処理能力は、パフォーマンスの高いデータベース システムで最も重要な要素の一つです。同時接続数 / ユーザー数 / スレッド数によって、データベース リクエストの処理に必要なコア数が決まります。データベースに割り当てられた CPU は、アプリケーションが最適なレベルで動作するために、通常のワークロードとピーク（エクストリーム）ワークロードを処理できる必要があります。
Cloud SQL の場合、Google Cloud のフルマネージド MySQL サービスである CPU は、仮想 CPU（vCPU）の形式で割り当てられます。 データベースに割り当てられた vCPU の数により、データベース インスタンスのメモリ容量とネットワーク スループットが最終的に決定されます。これは、各 vCPU には最大メモリ割り当て量があり、ネットワーク スループットも、vCPU の数に基づいて変動するためです。Cloud SQL では、インスタンスの vCPU の数を柔軟に調整できるため、アプリケーションのメモリとネットワーク スループットの要件を満たすことが容易になります。
メモリ
データベースに割り当てるメモリ量を決定する際の重要な考慮事項は、ワーキング セットがバッファプールに収まるようにすることです。ワーキング セットは、データベースでアクティブに使用されているデータです。割り当てられたメモリは、このワーキング セットまたは頻繁にアクセスされるデータを保持するのに十分なものである必要があります。通常、このデータはデータベース データ、インデックス、セッション バッファ、辞書キャッシュ、ハッシュ テーブルで構成されます。十分なメモリが割り当てられているかどうかを確認する 1 つの方法は、データベース内のディスク読み取りステータスを確認することです。 通常のワークロード条件下では、ディスクの読み取り量は最小限に抑えることをおすすめします。
インスタンスに対するメモリ割り当てが不十分な場合、インスタンスにメモリ不足問題が発生する可能性があります。これにより、データベース インスタンスが再起動され、データベースまたはアプリケーションのダウンタイムが発生します。
ストレージ
データベース ストレージもパフォーマンスの最適化に重要な役割を果たしているコンポーネントです。Cloud SQL では 2 種類のストレージを提供
- SSD（デフォルト）
- HDD
SSD は HDD よりもパフォーマンスとスループットが大幅に向上します。 特に本番環境のワークロードには、パフォーマンス向上のために SSD を選択します。
インスタンスに割り当てられた 1 秒あたりの入出力オペレーション（IOP）は、インスタンスの作成中に割り当てられたストレージの容量によって異なります。ディスクサイズが大きいほど、読み取りと書き込みの IOP は大きくなります。したがって、IOP のパフォーマンスを改善するために、データサイズを大きくしたインスタンスを作成することをおすすめします。次の Google Cloud コンソールのスクリーンショットは、作成時にデータベース インスタンスに割り当てられたリソースの概要（最大容量を含む）を示しています。これは、データベースをインスタンス化した後にどのように構成されるかについて、ユーザーが確認し、正しく理解するのに役立ちます。
リージョン
ネットワーク レイテンシを短縮する方法の 1 つは、アプリケーションまたはサービスに最も近いインスタンス リージョンを選択することです。Cloud SQL for MySQL はすべての Google Cloud リージョンで利用できるため、エンドユーザーができるだけ近くにデータベースを簡単にインスタンス化できます。
柔軟なスケーリング
CloudSQL では、データベース インスタンスに割り当てられたリソース（CPU、メモリ、ストレージ）を簡単にスケールアップまたはスケールダウンできます。これは、リソース要件が異なるワークロードで役立ちます。たとえば、ユーザーは、ワークロード要件が増加している間にリソースを増やし（スケールアップ）、ワークロードがピークに達した時点でリソースをスケールダウンできます。
MySQL の構成
このセクションでは、パフォーマンスを改善するための MySQL データベース構成のベスト プラクティスについて説明します。
バージョン
新しいデータベースを作成するときに、MySQL の最新バージョンを選択します。最新バージョンにはバグの修正と最適化が行われており、古いバージョンと比較してパフォーマンスが向上しています。Cloud SQL は、市販されている最新バージョンの MySQL を提供しており、これが新しいデータベース作成時のデフォルト バージョンになります。詳細については、Cloud SQL でサポートされている MySQL バージョンをご覧ください。
InnoDB のバッファプール サイズ
MySQL インスタンスの場合、サポートされているストレージ エンジンは InnoDB のみです。Innodb のバッファプール サイズは、最適なパフォーマンスのためにユーザーが定義する最初のパラメータです。バッファプールは、テーブル キャッシュ、インデックス キャッシュ、フラッシュされた変更済みデータ、アダプティブ ハッシュ インデックス（AHI）などの内部構造に割り当てられたメモリ領域です。
Cloud SQL では、インスタンス サイズに基づいて、InnoDB バッファプールに割り当てられるインスタンス メモリの約（約 72%）のデフォルト値を定義します（デフォルト値はインスタンスのサイズによって異なります）。詳細については、さまざまなインスタンス サイズのバッファプール設定をご覧ください。 Cloud SQL では、データベース フラグを使用して、アプリケーションのニーズに応じてバッファプール サイズを柔軟に変更できます。
バッファプールのサイズを、InnoDB バッファプールとは別に、セッション バッファ、ディクショナリ キャッシュ、performance_schema テーブル（有効にした場合）に使用できる十分な空き領域を確保する必要があります。
ユーザーは、インスタンスから発生したディスク読み取りをチェックして、ディスクから読み取られているデータの量と、バッファプールから読み取られた読み取り量を特定できます。ディスク読み取りが多い場合、バッファプール サイズとインスタンス メモリを増やすと、読み取りクエリのパフォーマンスが向上します。
redo ログ / InnoDB ログファイルのサイズ
InnoDB のログファイルまたは redo ログは、テーブルデータに対するデータ変更を記録します。InnoDB のログファイルのサイズは、単一の redo ログファイルのサイズを定義します。
redo ログサイズが高い書き込み負荷の高いワークロードの場合、チェックポイントのフラッシュ アクティビティを頻繁に行う必要がなく、書き込みのスペースが広がるため、ディスク I/O を節約できるため、書き込みパフォーマンスが向上します。redo ログの合計サイズは、innodb_log_file_size × innodb_log_files_in_group で計算できます。これは、データベース アクセスのビジー状態の間に、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 のログバッファ サイズは、InnoDB がログファイルの書き込みに使用するバッファ（redo ログ）の量です。
データベース内のトランザクション（挿入、更新、削除）が大きく、使用されているバッファが 16 MB を超える場合は、InnoDB がトランザクションを commit する前にディスク IO を実行する必要があります。これにより、パフォーマンスが影響を受けます。ディスク IO を回避するには、innodb_log_buffer_size の値を増やします。
CloudSQL では、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 容量
InnoDB の IO 容量は、バックグラウンド タスク（バッファプールからのページのフラッシュ、変更バッファのデータの統合など）に使用できる IOP の数を定義します。
Cloud SQL では、innodb_io_capacity のデフォルト値 5,000 と innodb_io_capacity_max の値として 10,000 が定義されています。
このデフォルトは、ほとんどのワークロードで最適に機能しますが、ワークロードが書き込みが多い場合や、インスタンスに適用されていない変更が多く、インスタンスに対して十分な IOP がある場合は、innodb_io_capacity と innodb_io_capacity_max を増やすことを検討します。適用された変更の値は、MySQL シェルで次のクエリを使用して確認できます。
mysql -e 'show engine InnoDB status \G;' | grep Ibuf
セッション バッファ
セッション バッファは、個々のセッションに割り当てられたメモリです。アプリケーションやクエリに多くの挿入、更新、並べ替え、結合が含まれ、より高いバッファが必要な場合は、特定のセッションでクエリを実行するときに高バッファ値を定義すると、パフォーマンスのオーバーヘッドを回避できます。ユーザーは、グローバル レベルでの過度なバッファ割り当てを防止できるため、すべての接続で値が増加し、インスタンスの総メモリ使用量が増える可能性があります。次のバッファのデフォルト値を変更すると、クエリのパフォーマンスを改善できます。これらの値は、データベース フラグを使用して変更できます。
これらはセッション バッファ値であるため、上限を増やすと、すべての接続に影響があり、最終的には全体的なメモリ使用量が増加する可能性があります。
Table_open_cache と Table_Definition_cache
データベース インスタンス内（単一または複数のデータベース）にテーブルが多すぎる（数千を超える）場合は、table_open_cache と table_definition_cache の値を増やして、テーブルを開く速度を改善できます。
Table_defined_cache を使用すると、テーブルをすばやく開くことができ、テーブルごとにエントリが 1 つだけになります。テーブル定義のキャッシュはスペースが小さく、ファイル記述子を使用しません。辞書オブジェクト キャッシュ内のテーブル インスタンスの数が table_Definition_cache の上限を超えると、LRU メカニズムがテーブル インスタンスをエビクションとしてマークし、最終的に辞書オブジェクト キャッシュからテーブル インスタンスを削除して、新しいテーブル定義用のスペースを確保します。このプロセスは、新しいテーブルスペースが開かれるたびに実行されます。 無効なテーブルスペースのみが閉じられます。このエビクション プロセスでは、テーブルの表示が遅くなります。
Table_open_cache は、すべてのスレッドのオープン テーブルの数を定義します。テーブル キャッシュを増やす必要があるかどうかは、 Opened_tables ステータス変数で確認できます。 Open_tables の値が大きく、 FLUSH TABLE を頻繁に使用しない場合、table_open_cache 変数の値を増やすことを検討します。
Table_open_cache と table_defined_cache は、インスタンス内の実際のテーブル数に設定できます。詳しくは、Cloud SQL のオープンテーブル数に関する Recommender の概要をご覧ください。
注: Cloud SQL では、これらの値を柔軟に変更できます。
スキーマの推奨事項
常に主キーを定義する
テーブルに主キーを定義すると、データが物理的に整理され、レコードの検索、取得、並べ替えが速くなり、パフォーマンスが向上します。
OLTP システムでは、整数値を自動的にインクリメントする主キーをおすすめします。
主キーがないことは、行ベースのレプリケーション シナリオでレプリケーションの遅延またはレプリケーション ラグの主な原因の 1 つになります。
インデックスを作成する
インデックスの作成により、データの取得が速くなり、読み取りクエリのパフォーマンスが向上します。クエリの WHERE、ORDER BY、GROUP BY 句で使用される列のインデックスを作成します。
注: インデックスが多すぎるか未使用の場合は、データベースのパフォーマンスが低下する可能性があります。
パフォーマンスの最適化に関するベスト プラクティス
ベンチマークを実行する
パフォーマンス テストまたはベンチマークを実行して、構成が最適かどうかを確認します。あるいは、ハードウェア、MySQL データベース、またはスキーマ設計の構成を調整することで、さらに改善できるかどうかを確認します。一度に 1 つのパラメータを変更し、ベンチマーク結果に対して改善の有無を確認します。
接続プーリング
接続プーリングは、必要なプロセスで使用できる接続のプールを作成、管理する手法です。接続プーリングにより、全体的なリソース使用量を削減しながら、アプリケーションのパフォーマンスを大幅に向上できます。接続数やタイムアウトなど、アプリケーションから接続を管理する方法の詳細を確認します。
リードレプリカへリードワークロードを分散させる
リードレプリカは（ゾーン全体で複数）を使用して、プライマリ インスタンスからリード ワークロードを開放できます。これにより、プライマリ インスタンスのオーバーヘッドと負荷が減少し、プライマリ インスタンスのパフォーマンスが向上します。また、リードレプリカに対する読み取りクエリに使用できるリソースも増えます。
データベース クエリのルーティングが可能なオープンソースの高性能の MySQL プロキシである ProxySQL を使用すると、Cloud SQL for MySQL データベースを水平方向にスケーリングできます。
長時間実行されるクエリを回避する
数分または数時間実行されるクエリは、パフォーマンスの低下を引き起こすことがわかっています。
- undo ログは、トランザクションをロールバックするために変更した行の古いバージョンを保存するため、また、トランザクションで一貫した読み取り（データのスナップショット）を行うために使用されます。これらの undo ログは、最新バージョンが古いバージョンを指すリンクされたリストの形式で保存され、さらに古いバージョンを指すように保存されます。長時間実行されるトランザクションは、undo ログのパージを遅らせる傾向があるため、undo ログのリストが増えます。InnoDB は、大量の大量の undo ログや長いリンクリストを走査する必要があるため、パフォーマンスが低下します。
- 長時間実行されるクエリもリソース（メモリ、バッファ、ロックなど）を消費します。これらは長時間解放されず、リソース不足のために他のクエリに影響します。
大規模なトランザクションを回避する
1 つのトランザクションでレコードの変更（更新、削除、挿入）が多すぎると、多数のレコード用にリソース（ロック、バッファ）が保持されます。これによりログバッファがオーバーフローし、ディスク IO が発生する可能性があります。残りのクエリは、リソースまたはロックが解放されるまで待つ必要があります。その結果、バッファプールに大量のデータが入り、バッファプールをそれ以上使用できなくなります。このような大規模なトランザクションをロールバックも、データベースのパフォーマンスを低下させます。これを解決するには、大規模なトランザクションを高速かつ小さなトランザクションに分割することをおすすめします。
クエリを最適化する
クエリを常に最適化して、最善の結果（リソースの削減と実行の高速化）を実現します。MySQL クエリの調整に関する推奨事項を確認します。
パフォーマンス調整用のツール
モニタリング
Cloud SQL には、デフォルトの Cloud SQL モニタリング ダッシュボードなど、いくつかの Google Cloud プロダクト用に事前定義されたダッシュボードが用意されています。このダッシュボードを使用すると、ユーザーはプライマリ インスタンスとレプリカ インスタンスの全般的な状態をモニタリングできます。ユーザーは独自のカスタム ダッシュボードを作成して、関心のある指標を表示することもできます。これらのダッシュボードと指標を使用すると、前述の推奨事項を使用して、CPU 使用率やメモリ使用量が多いなどのさまざまなパフォーマンスのボトルネックを識別し、対処できます。 これらの指標に基づいてアラートを構成することもできます。
低速クエリフラグ
Cloud SQL for MySQL インスタンスで低速クエリフラグを有効にすると、実行に long_query_time より時間がかかっているクエリを特定できます。こうした低速なクエリをさらに分析して調整することで、パフォーマンスを改善できます。Cloud SQL インスタンスに低速クエリを有効にして確認する方法をご覧ください。
パフォーマンス スキーマ
パフォーマンス スキーマにより、MySQL インスタンスの低レベルのモニタリングを行うことができます。 パフォーマンス スキーマは、メモリが 15 GB を超える Cloud SQL for MySQL インスタンスで有効にできます。 システム スキーマのレポートは、ボトルネック、待機、インデックスの欠落、メモリ使用量などを特定するためのさまざまなレポートを提供します。
クエリ分析情報
Query Insights は Cloud SQL のネイティブ機能であり、クエリのプロファイリングと分析を行ってクエリのパフォーマンスを改善できます。Query Insights は、直感的なモニタリングをサポートし、検出するだけでなくパフォーマンスの問題の根本原因の特定に役立つ診断情報を提供します。
関連プロダクトとサービス
Google Cloud は、オンプレミスのデータセンターの廃止から SaaS アプリケーションの実行、基幹業務システムの移行まで、お客様のビジネスニーズに合わせて構築されたマネージド MySQL データベースを提供します。
