コンテンツに移動
デベロッパー

MySQL 対応の Cloud SQL Query Insights の一般提供が開始: クエリ負荷の確認、タグ付け、クエリプランの機能を搭載

2022年10月13日
Google Cloud Japan Team

※この投稿は米国時間 2022 年 9 月 28 日に、Google Cloud blog に投稿されたものの抄訳です。

ほぼリアルタイムでデータベースのパフォーマンス分析情報を把握できることは、平均反応時間を短縮し、起こりうる災害を軽減するうえで、あらゆるアプリケーションにとって重要です。現代のフルスタック開発者は、ビジネス要件に基づく開発から、パフォーマンス、応答性、スケーラビリティ、柔軟性、セキュリティを保証する中での運用チームおよび管理者との役割分担まで、多くの役割をこなしています。データベースとアプリケーションのパフォーマンスのモニタリングとトラブルシューティング(およびトラブル予防)も、重要な役割となっています。

すべての役割を挙げていてはきりがないほどです。トラブルシューティングやパフォーマンスの問題の追跡、複雑なクエリプランの分析を行うだけの時間、帯域幅、専門知識がいつもあるとは限らない開発チームの苦境を想像してみてください。そんな場面で開発者、運用チーム、管理者の役に立つのが、アプリケーションとクエリのパフォーマンスの問題を検出、診断、防止、モニタリングできるシンプルで使いやすいツールです。しかしながら、今日のデータベース パフォーマンスの問題解決では、データを相関させるために複数のツールが必要なため、専門知識を要する時間のかかる作業となっています。また、分散型の複雑なアプリケーション スタックでは、実行速度の遅いクエリの発生源がアプリケーションのどの部分にあるかを特定することは非常に困難です。

そこで Google は、Cloud SQL Query Insights for MySQL を一般提供いたします。Query Insights は、Cloud SQL for MySQL データベースのパフォーマンスの問題を検出、診断、防止するためのツール群です。

Query Insights の主な特長

Query Insights は、問題のあるクエリを調査、検出し、問題の根本原因を特定して、発生源を確認するまでを、すべて一連のユーザー ジャーニーとして行えるよう支援します。Cloud SQL Query Insights for MySQL で私が気に入っているのは、トップダウンまたはボトムアップのワークフローとしてトラブルシューティングを行える点です。説明しましょう。つまり、アプリケーション レベルでデータベースのパフォーマンスをモニタリングし、モデル、ビュー、コントローラ、ルート、ユーザー、ホストごとにアプリケーション スタック全体で問題のある SQL クエリの発生源をトップダウンでトレースすることも、または負荷の原因となった特定のデータ部分まで掘り下げ、スタックを上に向かって調査することもできます。また、私が個人的に学び、使用することを楽しみにしている便利な機能がほかにもあります。

  1. Query Insights は、すべての Cloud SQL マシンタイプでサポートされ、すべての Google Cloud リージョンで利用できる

  2. Query Insights の指標は、保存段階で暗号化されており、Cloud SQL ダッシュボードにアクセスできるユーザーは、Query Insights ダッシュボード上で Query Insights の指標にアクセスできる

  3. Query Insights は、オープン標準と API を使用して、既存のアプリケーション モニタリング(APM)ツールや Google Cloud サービスと統合可能

  4. Cloud SQL インスタンスの保存容量を占有せず、パフォーマンスへの影響を最小限に抑え、追加費用なしで常時稼働 - Query Insights ダッシュボードで 1 週間分のデータにアクセス可能

  5. 指標が Cloud Monitoring に保存されるため、Cloud Monitoring と Prometheus の機能を利用して、アプリケーションのログとトレースを統合できる

このブログで説明すること

Query Insights を使用して、動作の遅いペット クリニック アプリをトラブルシューティングする方法を説明します。このために、Cloud SQL for MySQL Instance を作成し、データベースと複数のテーブルを作成し、SQL スクリプトを使用してレコードを挿入します。Jib でコンテナ化され、Cloud Run にデプロイされた Cloud SQL データベースにアクセスするアプリケーションを Spring Boot で構築します。この Codelab の例を使用して、飼い主、ペット、獣医、訪問数情報を含む小規模で簡単なペット クリニック アプリを作成します。新機能として、高いパフォーマンスが要求されるクエリ(コード スニペットに含まれる)を追加します。Cloud SQL Query Insights for MySQL でクエリを使用して、パフォーマンスのモニタリングとトラブルシューティングを行う方法を説明します。

アプリケーション アーキテクチャ

次の図は、以降のセクションでコンポーネントを確認する前に、この例のおおまかな技術的アーキテクチャの概要を示したものです。

https://storage.googleapis.com/gweb-cloudblog-publish/images/1_4.max-1600x1600.JPG

今回は JPA ORM を使用した Spring - Cloud SQL for MySQL アプリケーションを取り上げ、Cloud SQL Query Insights for MySQL を使用したクエリ パフォーマンス解析、トラブルシューティング、モニタリングの自動化と簡略化の方法を紹介します。

データベースとアプリケーションの設定

1. プロジェクトを作成し、課金を有効化し、Cloud Shell と Cloud Run を設定する

  • Google Cloud コンソールの [プロジェクト セレクタ] ページで、Google Cloud プロジェクト選択または作成します。

  • Cloud プロジェクトに対して課金が有効になっていることを確認します。詳しくは、プロジェクトで課金が有効になっているかどうかを確認する方法をご覧ください。

  • Google Cloud はノートパソコンからリモートで操作できますが、ここでは Cloud Shell(Google Cloud 上で動作するコマンドライン環境)を使用します。

  • Cloud Shell をまだアクティブにしていない場合は、次の手順に沿って Cloud Shell をアクティブにし、認証が完了していること、プロジェクト ID が設定されていることを確認してください。

  • なんらかの理由でプロジェクトが設定されていない場合は、コマンド gcloud config set project <PROJECT_ID> を実行します。

  • Cloud Shell から gcloud services enable run.googleapis.com というコマンドを実行して Cloud Run API を有効にします。

2. Cloud SQL for MySQL のインスタンスとデータベースを作成する

Cloud SQL for MySQL は、Google Cloud Platform 上の MySQL リレーショナル データベースの設定、維持、運用、管理を容易にするフルマネージド データベース サービスです。MySQL 固有の情報については、MySQL のドキュメントまたは Cloud SQL for MySQL の詳細をご覧ください。

  • Cloud Shell が立ち上がったら、コマンドラインを使用して、新しい Cloud SQL インスタンスを作成できます。$ gcloud sql instances create <<instance-name>>

  • ここで、Petclinic アプリに使用するデータベースを作成します。$ gcloud sql databases create petclinic --instance <<instance-name>>

  • Cloud コンソールからインスタンスにアクセスし、インスタンスを構成することもできます。

  • 右のコマンドを実行して project-id:zone-id:instance-id の形式でインスタンス接続名を取得します(Spring Boot アプリの構成時にこちらを使用します)。          $ gcloud sql instances describe my-instance | grep connectionName

3. Spring Boot Java アプリケーションを立ち上げる

こちらの Codelab の手順に沿って、プロジェクト内で Spring アプリケーションを立ち上げます。

注:

  • src/main/resources/application-mysql.properties ファイルで、データベース インスタンスの root のユーザーとパスワードを別に作成した場合、またはパスワードを変更した場合は、以下の connection-name の構成を必ず追加してください。

spring.datasource.username=<<user>>

spring.datasource.password=<<password>>

  • アプリケーションをビルドして実行します。

./mvnw package

./mvnw spring-boot:run


4. クエリの複雑性を高めるため、アプリとデータに変更を加える

分析情報をテストするために、データにいくつかの変更を加え、データの量を増やしクエリの複雑さを高めます。

  • Google Cloud コンソールから MySQL Cloud SQL インスタンスに移動し、[概要] ページから [Cloud Shell を開く] リンクをクリックして、Cloud Shell から MySQL に接続します。

https://storage.googleapis.com/gweb-cloudblog-publish/images/2_3.max-1300x1300.jpeg
  • 開いた Cloud Shell のプロンプトで、デフォルトで読み込まれるコマンドを入力します。読み込まれない場合は、以下のコマンドを使用してください。
    gcloud sql connect <<instance-name>> --user=root

  • クエリ プロンプトで、こちらのリンク先にあるファイルの内容を実行します。

  • これにより、Petclinic データベースの「owners」テーブルに 20,000 行が読み込まれます。

  • この時点で、中断してステップ 5 に進むか、もう少しアプリをカスタマイズして複雑なクエリ機能を追加するか、選択できます。

チェックボックスを使用して、ペット抜きで飼い主のみを一覧表示する機能を追加します。

OwnerRepository.java に以下のコード スニペットを追加します。
spring-petclinic/src/main/java/org/springframework/samples/petclinic/owner/OwnerRepository.java
読み込んでいます...

  • findPaginatedForOwnersLastName(int page, String lastname) メソッドを以下の OwnerController.java のコード スニペットで修正します。
    spring-petclinic/src/main/java/org/springframework/samples/petclinic/owner/OwnerController.java
  • メソッド:

読み込んでいます...

  • “lastNameGroup” div に以下のコードを挿入して、findOwners.html ファイルを修正します。

  • spring-petclinic/src/main/resources/templates/owners/findOwners.html

読み込んでいます...

  • アプリケーションを再ビルドして実行します。
    ./mvnw package
    ./mvnw spring-boot:run

5. Jib を使用して、Docker なしでアプリをコンテナ化する

  • Container Registry API をアクティブにしていない場合は、次に進む前にアクティブにする必要があります。

$ gcloud services enable containerregistry.googleapis.com


  • Jib を実行して、Docker イメージをビルドして Container Registry に公開します。

$ ./mvnw com.google.cloud.tools:jib-maven-plugin:3.1.1:build \

-Dimage=gcr.io/$GOOGLE_CLOUD_PROJECT/<<your-container-name>>


6. Cloud Run へデプロイする

次のコマンドを実行して、Cloud Run にコンテナ化されたアプリをデプロイします。

gcloud run deploy <<application>> --image gcr.io/$GOOGLE_CLOUD_PROJECT/<<container>> --platform managed --region us-central1 --allow-unauthenticated --update-env-vars DBHOST=$DB_HOST

  • デプロイが完了したら、コマンドラインにデプロイしたサービスの URL が表示されます。

  • サービスの URL に接続すると、ブラウザにウェブページと、Cloud Logging の [ログ エクスプローラ] ページにログが表示されます。

アプリがクラウドに送られる際の、ログをご確認ください。デプロイが完了したら、アプリに URL が表示されます。これを後で使うために保存します。


7. Query Insights for MySQL を有効化する

このリンク先の指示に従って、Cloud SQL Query Insights for MySQL を有効化します。コンソール、gcloud、REST v1 のうち 1 つを使用できます。有効化したら、[概要] のすぐ下の左側ペインに「Query Insights」オプションが表示されているか確認してください。
https://storage.googleapis.com/gweb-cloudblog-publish/images/3_3.max-1500x1500.jpeg

アプリケーションを使用して Query Insights をモニタリングするのに必要なすべての手順を確認したところで、Query Insights のステップを詳細に見ていきましょう。

Query Insights の使用

Query Insights では、以下の手順で、データベース クエリのパフォーマンスを向上できます。

  1. すべてのクエリのデータベース負荷を表示する

  2. 問題がある可能性のあるクエリまたはタグを特定する(sqlcommenter を使用してクエリにタグを追加する)

  3. クエリまたはタグを調べて問題を特定する

  4. 問題の発生源を特定する

アプリケーションの分析情報を確認するためのこれらの各ステップを見ていきましょう。これを進めるため、まず先ほど保存したアプリケーションの URL をクリックします。

ホームページを読み込みます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/4_3.max-1600x1600.jpeg

[Find Owners] メニュー項目をクリックします。

https://storage.googleapis.com/gweb-cloudblog-publish/images/5_3.max-1600x1600.jpeg

[Only Owners Without Pets] チェックボックスをクリックし、[Find Owner] をクリックします。

https://storage.googleapis.com/gweb-cloudblog-publish/images/6_3.max-1600x1600.jpeg

[Find Owners] を再度クリックして、チェックボックスにチェックを入れずに [Find Owner] をクリックします。ページ下の 804 個のページリンクの一つをクリックして反応を確認します。

https://storage.googleapis.com/gweb-cloudblog-publish/images/7_3.max-1600x1600.jpeg

[Find Owners] をクリックして、[Add Owner] ボタンをクリックします。詳細を入力して、ペットを追加します。

https://storage.googleapis.com/gweb-cloudblog-publish/images/8_1.max-1600x1600.jpeg

1. すべてのクエリのデータベース負荷を表示する

データベースのクエリ負荷は、選択したデータベースで実行されたクエリの作業量の経時的な測定結果(CPU 秒単位)です。トップレベルの Query Insights ダッシュボードには、[データベースの負荷 - すべての上位クエリ] グラフが表示されます。ダッシュボードのプルダウン メニューでは、特定のデータベース、ユーザー、またはクライアント アドレスのグラフがフィルタされます。下のスクリーンショットでは、Petclinic データベースのすべてのクエリについて、負荷の急増が 2 か所確認できます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/9_1.max-1500x1500.jpeg
https://storage.googleapis.com/gweb-cloudblog-publish/images/10_1.max-1500x1500.jpeg

2. 問題がある可能性のあるクエリまたはタグを特定する

クエリまたはタグでデータベース負荷をフィルタリングできます。

クエリでフィルタリング:

[クエリ] テーブルでは、クエリ負荷が最も大きいクエリの概要を確認できます。これは、多くのデータベース ツールで「トップ SQL」と呼ばれています。このテーブルには、Query Insights ダッシュボードで選択した時間枠とオプションについて、正規化されたすべてのクエリ(構成、データ型の互換性、正確さなどが検証されたクエリ)が表示されます。選択した時間枠内の合計実行時間でクエリが並べ替えられます。この例では、最上位のクエリを見ると、最大の負荷がかかったクエリを確認できます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/11_1.max-1100x1100.jpeg

タグでフィルタリング:

タグを使用してアプリケーションの問題を解決するには、まずすべてのタグを SQL クエリに追加する必要があります。クエリ負荷タグは、選択したタグのクエリ負荷の内訳の時間変化を示します。SQL クエリにタグを追加する方法には、次の 2 つがあります。

1. sqlcommenter を使用して ORM で自動的にタグを追加する

2. sqlcommenter を使用して手動でタグを追加する


sqlcommenter の詳細と、ORM フレームワークで sqlcommenter を使用する方法については、sqlcommenter のドキュメントをご覧ください。ORM を使用していない場合は、SQL クエリに対して正しい SQL コメント形式で、sqlcommenter のタグまたはコメントを手動で追加する必要があります。また、各 SQL ステートメントに、シリアル化された Key-Value ペアを含むコメントを追加する必要があります。アクション、コントローラ、フレームワーク、ルート、アプリケーション、DB ドライバのうち 1 つ以上のキーを使用します。


3. 特定のクエリまたはタグを調査する

クエリまたはタグが問題の根本原因であるかどうかを確認するには、[クエリ] タブまたは [タグ] タブで次の操作を行います。

  1. リストを降順で並べ替えるには、[負荷(合計実行時間別)] ヘッダーをクリックします。

  2. リストの一番上にあるクエリまたはタグをクリックします。このクエリまたはタグは、負荷が最も高く、他のインスタンスよりも時間がかかっています。

選択したクエリまたはタグの詳細を示すダッシュボードが開きます。

Google で行った実験では、20,105 行のテーブル スキャンに対し、301,350 件が返されました。これは、クエリ内のデカルト積によって引き起こされた問題です。この where 条件のないコードにおける誤った結合(クロス結合)により、クエリのエラーが起き、クエリが最も大きな負荷をもたらす原因となっています。
https://storage.googleapis.com/gweb-cloudblog-publish/images/12_1.max-1600x1600.jpeg
https://storage.googleapis.com/gweb-cloudblog-publish/images/13_1.max-1500x1500.jpeg

選択したクエリのレイテンシ:

https://storage.googleapis.com/gweb-cloudblog-publish/images/14_1.max-1600x1600.jpeg
https://storage.googleapis.com/gweb-cloudblog-publish/images/15_1.max-1600x1600.jpeg
https://storage.googleapis.com/gweb-cloudblog-publish/images/16_1.max-1600x1600.jpeg
https://storage.googleapis.com/gweb-cloudblog-publish/images/17_1.max-1500x1500.jpeg

選択されたクエリについてサンプリングされたクエリプランでのオペレーションを調査します。

クエリプランは、クエリのサンプルを取得し、それを個々のオペレーションに分割します。クエリ内の各オペレーションが説明され、分析されます。[クエリプランのサンプル] グラフには、特定の時間に実行されているすべてのクエリプランと、各プランの実行にかかった時間が表示されます。

この例では、下の最も負荷の大きいクエリがクエリプランとなります。

https://storage.googleapis.com/gweb-cloudblog-publish/images/18.max-1500x1500.jpeg

4. 問題の発生源を特定する

負荷が最大の領域と時間を見つけたら、トレースを使用してさらに掘り下げて、問題の発生源を特定します。モデル、ビュー、コントローラ、ルート、ホスト、ユーザーなど、問題の発生源を特定できるように、Query Insights では、コンテキストに応じたエンドツーエンドのアプリケーション トレース ビューが表示されます。これにより、特定のリクエストのデータベース レイヤで何が起こっているかを理解し、問題のあるクエリの発生源を見つけることができます。

[クエリの例] 画面の [エンドツー エンド] タブをクリックすると、コンテキスト内トレースが表示されます。選択されたクエリについて、エンドツーエンドのクエリプランのトレースは以下のとおりです。

https://storage.googleapis.com/gweb-cloudblog-publish/images/19.max-1600x1600.jpeg

負荷をもたらしているクライアント、IP、ユーザーに関する情報を確認できます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/20.max-1000x1000.jpeg

クエリプランの各ステップのエンドツーエンドでの追跡状況を確認するには、[Trace で表示] リンクをクリックして、Cloud Trace ツールを開きます。この例では、レイテンシ、HTTP メソッド、URL を確認できます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/21.max-1100x1100.jpeg

詳細については、トレースの検索と表示をご覧ください。

まとめ

この実験を一緒に進める中で、Cloud SQL Query Insights for MySQL のたった 4 つのステップで、最も負荷が高く複雑なクエリを簡単に特定できることに気づかれたことと思います。私は、一番上のクエリ(ステップ 3 そのもの)の間違った結合が負荷の原因であることを特定できました。これは、ステップ 4 のクエリプランでより詳しく説明されています。

Query Insights は Cloud SQL インスタンスですぐに簡単に有効化でき、一度有効化すれば常時稼働し、追加費用なしで連続 7 日間のテレメトリーにアクセスできるようになります。Query Insights の指標は、Cloud Monitoring に保存されます。Cloud Monitoring のグループ機能を使用して、Google Cloud 上の Cloud Run、Cloud Functions など、私が使うその他のサービスと統合し、アプリケーション スタック全体を一括表示できるようになったため、業務が楽になりました。

さらに、役に立つ Cloud SQL Insights for MySQL の一般提供が開始されました。お気軽に Query Insights を使って Cloud SQL for MySQL のクエリ パフォーマンスを向上させる方法を学習、テストして、貴社のアプリケーションやチームの業務を改善してみてください。


- Google、デベロッパー アドボケイト Abirami Sukumaran

投稿先