ネイティブツールと外部ツールに基づいた Amazon RDS PostgreSQL のクエリの最適化とチューニング

PostgreSQL は最も人気のあるオープンソースのリレーショナルデータベースシステムの 1 つです。30年以上の開発作業の成果である PostgreSQL は、多数の複雑なデータワークロードを処理できる、信頼性が高く堅牢なデータベースであることが証明されています。Oracle などの商用データベースから移行する場合、PostgreSQL はオープンソースデータベースの主要な選択肢と見なされています。

AWS は、PostgreSQL データベースのデプロイを、コスト効率の良い方法でクラウドに簡単にセットアップ、管理、およびスケールできるサービスを提供しています。これらのサービスは、Amazon RDS for PostgreSQL および PostgreSQL と互換性のある Amazon Aurora です。

データベースのパフォーマンスは、アプリケーションレベルの多くの要因、および CPU やメモリなどのハードウェアに依存しています。この記事では、主要なパフォーマンス要因の 1 つであるクエリパフォーマンスを取り扱います。クエリの遅延は、ほとんどの環境で一般的に見られる問題です。この記事では以下について説明します。

ネイティブデータベースツールを使用して、どのクエリが遅いかを見つける方法。
Amazon RDS Performance Insights を使用してパフォーマンスの問題を見つける方法。
遅いクエリを修正する方法。

RDS および Amazon Aurora PostgreSQL 環境を管理する開発者と DBA にとって、遅いクエリを特定し、パフォーマンスを向上させるためのチューニングは重要なタスクです。

PostgreSQL には、人気のある pgBadger など、遅いクエリを識別するためのツールが多数用意されています。pgBadger は、PostgreSQL ログファイルからの完全なレポートを使用して速度を上げるために構築された PostgreSQL ログアナライザーです。これは、他の PostgreSQL ログアナライザーよりも優れた小さな Perl スクリプトです。このツールを使用してレポートを生成するには、それに応じてログレベルを設定する必要があります。

次の論理図は、pgBadger の使用方法を示しています。PostgreSQL ログをダウンロードして pgBadger レポートを生成するには、Amazon EC2 インスタンスが必要です。

次のチュートリアルは、pgBadger を使用して低速クエリを識別する方法を示しています。

前提条件

pgBadger のロギングパラメータを使用して新しいパラメータグループを作成します。

pgBadger はログファイルで機能し、特定の情報を解析してレポートを構築する必要があります。データベースは、pgBadger が必要とする情報をキャプチャするように設定する必要があります。

まず、次のパラメータを設定します。これらのパラメータの詳細については、「PostgreSQL パラメータの操作」および「エラー報告とロギング」を参照してください。

 log_checkpoints = on (Only for RDS)
 log_connections = on
 log_disconnections = on
 log_lock_waits = on
 log_temp_files = 0
 log_autovacuum_min_duration = 0
 log_error_verbosity = default

これらのパラメータを有効にすると、ログファイルに書き込む負荷が生まれ、ログファイルのスペースが使用される可能性があります。これらのパラメータを永続的に設定する必要はありません。データベースの速度を分析する場合など、必要な場合にのみ行うことができます。問題を修正した後、設定をロールバックします。

次に、「PostgreSQL データベースの作成と接続の手順」に従って、RDS または Aurora PostgreSQL インスタンスを作成します。インスタンスを作成するときに、前の手順で作成したパラメータグループを選択してください。

ログをダウンロードしてレポートを生成する

次のスクリーンショットに示すように、AWS マネジメントコンソールで RDS または Aurora インスタンスを選択すると、[ログとイベント] タブで Postgres ログを確認できます。

ログファイルをローカルマシンまたは Amazon EC2 インスタンスにダウンロードして、レポートを生成します。
1. [ログとイベント] を選択します。
2. [ログ] で、ダウンロードするログファイルを選択します。
3. [ダウンロード] を選択します。

AWS CLI コマンド download-db-log-file-portion を使用してログファイルをダウンロードできます。例:

aws rds download-db-log-file-portion \ --db-instance-identifier myexampledb \ --starting-token 0 --output text \ --log-file-name log/ERROR.4 > errorlog.txt

PostgreSQL と互換性のある Amazon Aurora のデータベースインスタンスから、Amazon RDS の Amazon CloudWatch Logs にログを発行できるようになりました。詳細については、「Aurora PostgreSQL Logs の Amazon CloudWatch Logs への発行」を参照してください。

レポートを生成する

この例では、従業員情報を含むサンプルデータベースを作成します。

ダウンロードしたログファイルに関するレポートを生成するには、次の手順に従ってください。

pgbadger リポジトリをダウンロードします。
pgBadger ファイルを解凍し、解凍されたディレクトリ内で pgbadger バイナリを見つけます。
次のコマンドを使用して、ダウンロードしたログファイルに関する pgBadger レポートを生成します。
```
./pgbadger -p "%t:%r:%u@%d:[%p]:" postgresql.log.2019-06-20-12 -o pgbadger_rdsinstance.html
```

-p は、カスタム log_line_prefix パラメータを表します (RDS には、%t:%r:%u@%d:[%p]: として固定値があります)。
-o は出力 HTML ファイルを表します。

HTML レポートを開くと、次のスクリーンショットの例のようになります。

HTML レポートには、クエリと一意のクエリの数、合計クエリ実行時間、1 秒あたりのクエリ数などの SQL トラフィックに関する情報などが含まれています。

次のスクリーンショットに示すように、実行速度の遅いクエリは、Top セクションの Time Consuming query (N) の下に表示されます。そこから、遅いクエリのトラブルシューティングを開始できます。

pgBadger は、最も頻繁に待機するクエリ、最も頻繁に実行されるクエリ、クエリとセッション時間のヒストグラム、上位クエリに関係するユーザーなど、SQL クエリに関する情報をレポートします。

また、ロック統計の分布に関する円グラフやタイプ (選択/挿入/更新/削除) ごとのクエリなど、グラフィック形式の情報もあります。

すべてのチャートはズーム可能で、PNG 画像として保存できます。報告された SQL クエリは強調表示され、自動的に美化されます。

遅さの診断

レポートから遅いクエリを抽出して分析します。

なぜクエリが遅いのか?
クエリは調整できるか? もしできるなら、その方法は?

遅延の原因を見つける

前のスクリーンショットに示すように、ほとんどの開発者は WHERE 句で結合することを好みます。

SELECT e.emp_no,
       e.first_name,
       e.last_name,
       d.dept_no
FROM   employees e,
       dept_emp d
WHERE  e.emp_no = d.emp_no;

このタイプの結合は、デカルト積またはクロス結合とも呼ばれるデカルト結合を作成します。デカルト結合は、変数のすべての可能な組み合わせを作成します。

デカルト結合の作成を防ぐには、代わりに内部結合を使用します。

SELECT e.emp_no,
       e.first_name,
       e.last_name,
       d.dept_no
FROM   employees e
       INNER JOIN dept_emp d
               ON e.emp_no = d.emp_no;

AWS Performance Insights の使用:

RDS および Aurora データベースインスタンスのチューニングとモニタリングのために、AWS は最近、負荷に関するパフォーマンスの問題を検出するためのわかりやすいダッシュボードを提供する Amazon RDS Performance Insights と呼ばれる機能をリリースしました。

インスタンスを作成しているとき、またはインスタンスを変更しているときに、Performance Insights を有効にできます。

Performance Insights メトリクスを確認するには、Amazon RDS データベースの左パネルで [Performance Insights] を選択します。

Performance Insights ダッシュボードは、次のスクリーンショットに示されています。

負荷引き起こした SQL クエリ、I/O 待機時間、およびクエリを実行したユーザーとホストを監視できます。

前のスクリーンショットを見るとわかるように、同じ SQL クエリが最も CPU を消費していると報告されています。

Performance Insights は、RDS PostgreSQL のカウンターメトリクスをサポートしています。カウンターメトリクスを使用すると、Performance Insights ダッシュボードをカスタマイズして、使用可能なオペレーティングシステムとデータベースのメトリクスから最大 10 個の追加グラフを含めることができます。負荷チャートを相関させることにより、パフォーマンスの問題を特定して分析すると役立ちます。

パフォーマンスカウンターメトリクスは、ネイティブおよび非ネイティブです。

次のスクリーンショットを見てわかるように、カウンターメトリクスは、取得したタプル、返されたタプル、レイテンシーのブロック、読み取りのブロックで更新されます。

まとめ

この記事では、データベース中の遅いクエリを見つけるためのネイティブツールおよび AWS ツールについて説明し、それを修正する方法を示しました。pgBadger ツールは PostgreSQL ログファイルに基づいているため、pgBadger のレポートの解析および作成のためにログを有効にする必要があります。Performance Insights は、(I/O、負荷、または CPU が原因で) クエリが遅い場所を特定するデータベースおよび OS 情報を表示する AWS の機能です。Performance Insights には、クエリの実行中にバックグラウンドで何が起こっているかに関する追加情報を提供するカウンターメトリクスがあります。

Performance Insights とは異なり、pgBadger では、レポートを取得するためにすべてのロギング (前提条件セクションで説明) を有効にする必要があります。ロギングが欠落すると、レポートが部分的にしか生成されません。また、pgBadger では、ダウンロードしてレポートを生成するためにサーバーまたは EC2 インスタンスが必要です。Performance Insights カウンターメトリクスを使用すると、CPU、負荷、I/O 待機時間の統計、およびデータベースの統計を十分に把握できます。

著者について

Baji Shaik は、インドの AWS ProServe チームのアソシエイトコンサルタントです。彼のバックグラウンドは、SQL/NoSQL データベーステクノロジーの幅広い専門知識と経験に及びます。彼は、困難なビジネス要件に対応する多くのデータベースソリューションのアーキテクチャと設計を成功させてきました。レポート、ビジネスインテリジェンス、データウェアハウジング、アプリケーション、および開発サポートに PostgreSQL を使用したソリューションを提供しています。また、クラウド環境でのオートメーション、オーケストレーション、DevOps についても十分な知識を持っています。

Amazon Web Services ブログ