Cloud SQL におけるデータベース オブザーバビリティの決定版ガイド: パート 1
Google Cloud Japan Team
※この投稿は米国時間 2023 年 8 月 3 日に、Google Cloud blog に投稿されたものの抄訳です。
はじめに
データベースのパフォーマンスは、日常業務の効率と効果に影響を及ぼすため、どのようなビジネスにおいても重要です。低速なデータベースはトランザクションの処理を遅延させ、顧客満足度や収益性に悪影響をもたらす可能性があります。
Cloud SQL for PostgreSQL は、Cloud SQL Insights と SQLcommenter によるデータベース オブザーバビリティを備えており、デベロッパー ファーストのアプローチを使用してデータベースのパフォーマンスの問題を診断、検出、予防できます。
Cloud SQL for PostgreSQL は、データベースの接続と接続解除およびクエリ実行に関するメタデータを記録できる追加のデータベース ログをサポートしています。これらのログはすべて、データベース フラグを使用して構成できます。Cloud SQL は、広く使用されている PostgreSQL 拡張機能（pg_stat_statements など）もサポートしています。Cloud SQL Insights とネイティブ オプションをいずれも有効にすれば、両方のメリットが得られます。
このブログ投稿では、Cloud SQL for PostgreSQL に移行したお客様が、データベース オブザーバビリティを確保するために、Cloud SQL Insights に加えて pgBadger や pg_stat_statements などの使い慣れた PostgreSQL ツールを引き続き使用する方法について説明します。Cloud SQL Insights とは異なり、pgBadger を使用してレポートを生成するには、必要なすべてのロギングを有効にするステップが追加で必要になります。ロギングが有効になっていないと、部分的なレポートが生成されます。さらに、pgBadger には、ログのダウンロードとレポートの作成用のサーバーまたは GCE インスタンスをセットアップするという運用上の追加ステップも必要です。
この記事では、ロギングを構成し、pgBadger を使用して HTML レポートを生成する方法を説明します。また、プロシージャ コール内のネストされた呼び出しを収集するために pg_stat_statement を使用することの価値にも焦点を当てます。
データベース フラグの構成
Cloud SQL for PostgreSQL には、データベース ログで収集する情報を制御するためのデータベース フラグが用意されています。まず、低速なクエリとすべての接続および接続解除のロギングを有効にするフラグを構成します。以下の値を使用して一連のデータベース フラグを有効にします。これらの値は単なる例であり、推奨値ではありません。
上記のパラメータを有効にしたり、またはロギング用に高い値を設定したりすると、データベースの負荷が高くなる場合があります。本番環境でこの変更を行う前には、データベースのパフォーマンスに対する影響を評価する必要があります。Cloud SQL for PostgreSQL の構成に使用可能なフラグの一覧については、こちらをご覧ください。
データベース フラグを更新するコマンドラインの例を次に示します。
次に、クライアントが発行した最上位のステートメントに含まれるすべてのネスト呼び出しを記録するために、新しい設定を有効にします。これにより、問題のあるネスト呼び出し（プロシージャ コード内で呼び出されたステートメント）を pg_stat_statements ビューで特定できます。
pg_settings ビューをクエリすることで、設定したパラメータをデータベースから確認できます。
pgbench データベースのセットアップ
今回は、pgbench という PostgreSQL データベースのベンチマーク用ツールを使用して、Cloud SQL データベースでのパフォーマンス ベースラインの実行をシミュレートします。この模擬ベンチマークの実行中に発生した負荷を基に、pgBadger を使用してデータベース ログからレポートを生成します。
データベースを初期化するため、新しく作成したデータベース「pgbench」に対して、PostgreSQL クライアント ライブラリに含まれる pgbench コマンドラインを使用します。
pgBadger レポートの生成
pgBadger は、データベースのログファイルを処理して、接続、セッション、バキューム、一時ファイル、上位クエリの情報を含む HTML レポートを生成するツールです。関連するデータベース フラグは前のステップですでに構成済みであり、各データベース接続と実行時間が 300 ミリ秒を超えるクエリに関する情報がログに記録されるようになっています。
Cloud SQL for PostgreSQL のログが Cloud Storage バケットに保存されるように構成した後、それらのログを Cloud Shell にダウンロードし、pgBadger で処理して HTML レポートを生成します。
最初のステップとして、pgBadger を Cloud Shell にインストールしてセットアップします。
Cloud Shell または Compute インスタンスで pgBadger をセットアップする
Cloud Storage への Cloud SQL ログ ルーティング シンクを構成する
Cloud SQL は、Cloud Logging を使用してすべてのインスタンス ログを保存します。Cloud Logging は、データベース インスタンス ログの表示やクエリに使用できます。必要なすべての Cloud SQL インスタンス ログを Google Cloud Storage（GCS）の宛先に送信するログ ルーティング シンクを作成します。ログルーターは Cloud Logging の一部です。シンクの宛先は Google Cloud Storage バケットとして定義します。
Cloud SQL ログの宛先となる GCS バケットをセットアップし、指定の ID ラベルを持つ Cloud SQL データベースからのログのみが送信されるようにログをフィルタリングします。
これで必要なシンク情報が設定され、1 時間ごとのバッチプロセスとしてすべてのログが転送される宛先の Cloud Storage バケットが新しくセットアップされました。また、追加のフィルタを指定し、シンクに含まれるログのうち特定の Cloud SQL インスタンスのログのみを送信することもできます。
今回のサンプル インスタンスの包含フィルタは次のとおりです。
pgBadger でレポートを生成するための基となる Cloud SQL の実行ログは、1 時間ごとのバッチで提供されます。Cloud SQL Insights は常時使用可能で、リアルタイムの分析情報から負荷と SQL の詳細を把握できます。
pgbench を使用して模擬ベンチマーク テストを行う
pgbench サンプル データベースの初期化はすでに行いました。次に、ベンチマーク テストとして 5 つのスレッドで 50 のクライアント接続をシミュレートし、Cloud SQL データベースで負荷を発生させます。
pgBadger を実行して HTML レポートを生成する
Cloud SQL for PostgreSQL のログを Cloud Storage から Cloud Shell 環境にダウンロードし、pgBadger を使用して模擬ベンチマークの実行に関する HTML レポートを生成します。Cloud Storage からログをダウンロードして pgBadger でレポートを生成するプロセスは自動化することもでき、毎時から毎週までのさまざまな間隔でこのプロセスを実行できます。
Cloud Storage からログをダウンロードするには、Compute Engine サービス アカウントに必要なロールを付与する必要があります。
Cloud SQL のログがダウンロードされたら、pgBadger ツールを使用して JSON ベースのログから HTML レポートを生成できます。
HTML レポートの初期の外観は次のスクリーンショットのようになります。
HTML レポートには、接続、一時ファイルの使用、SQL の実行に関する統計などの情報が含まれます。
たとえば、[Top] セクションで最も低速なクエリや最もリソース消費量の多いクエリを確認できます。
pg_stat_statements 拡張機能
pg_stat_statements 拡張機能は、クエリの実行に関する累積的な統計情報をデータベース ビューとして表示できる、便利な機能です。共有ライブラリにプリロードされており、create extension コマンドを実行することでデータベースで使用可能になります。pg_stat_statements 拡張機能は、PostgreSQL の公式ドキュメントで詳しく説明されています。このブログ投稿では、pg_stat_statements.track フラグを使用してプロシージャ コールに含まれるネスト呼び出しを追跡する方法について説明します。
パフォーマンス調査中に、あるプロシージャ コールが「問題あり」と診断されたり、相当な実行時間を消費していることが判明したりする場合があります。プロシージャ コード内の問題のあるステートメントまたはネストされたステートメントがパフォーマンスの問題を引き起こしている場合、または全体的なパフォーマンスに影響を及ぼしている場合に、それをデバッグするのは難題です。
pg_stat_statements.track を all に設定すると、プロシージャ ステートメント内で実行されたネストされたクエリを pg_stat_statements ビュー自体の一部として収集できます。
不良プロシージャ コールを作成し、SQL の一部として繰り返し実行してみましょう。
このレコードの存在をチェックするプロシージャは、インデックスが付いていないフィルタ列（bid）に対してカウント集計を使用しています。このプロシージャを複数回呼び出すと、プロシージャの全体的なパフォーマンスにオーバーヘッドが追加されます。
クリーンな結果を得るために pg_stat_statements をリセットし、すべての SQL を新規の実行として収集します。
作成した不良プロシージャ ブロックを繰り返し実行した後、pg_stat_statements 拡張機能をクエリして実行時間を取得してみましょう。
これは、不良プロシージャ コール内のネストされた SQL 呼び出しが問題のあるステートメントであることを示しています。これで問題のあるネスト呼び出しがわかったので、次に推奨事項を提示し、プロシージャの全体的な実行を修正できます。
pg_stat_statements.track の設定を変更するのは、品質保証フェーズでテストを実施して問題のあるネスト呼び出しを見つける場合のみにしてください。ワークロード負荷の高い環境でこの設定を変更すると、オーバーヘッドが増加する場合があります。
次のステップ
pgBadger や pg_stat_statements 拡張機能などの PostgreSQL ネイティブのオブザーバビリティ オプションにより、データベース開発者や管理者は、Cloud SQL for PostgreSQL でも使い慣れたツールを引き続き利用できます。
Cloud SQL Insights とは異なり、pgBadger で完全なレポートを生成するには、必要なすべてのロギングを有効にする必要があります。そうしない場合、部分的なレポートが生成されます。また、ログをダウンロードして pgBadger でレポートを生成するためにサーバーまたは GCE インスタンスが必要になります。
Cloud SQL for PostgreSQL は、ネイティブの PostgreSQL 機能と Cloud SQL Insights の両方の長所を兼ね備えています。Cloud SQL Insights は、パフォーマンスの問題の解決に役立つ直感的なモニタリングと根本原因分析を提供します。Cloud SQL Insights については、このデータベース オブザーバビリティ シリーズの次のパートで取り上げます。このトピックについて詳しく説明したこちらのドキュメントもぜひご覧ください。
- Google Cloud、データ移行コンサルタント Deepak Mahto
- Google Cloud、データベース リード Kiran Shenoy
