【レポート】実践！Average Active SessionsとQuery Plan Managementを駆使したAurora PostgreSQLチューニングテクニック #AWSSummit

DA事業本部の春田です。

AWS Summit Online絶賛開催中！ということで、本記事では「Architecting and Building - 突然データベースのパフォーマンスが悪化、あなたならどうする？【後半】」の内容についてまとめていきます。

前半の内容はこちらです。

セッション情報

• アマゾン ウェブ サービス ジャパン株式会社 技術統括本部 ソリューションアーキテクト 新久保 浩二

前半で確認したチューニングのサイクルを元に Performance Insights を使って、実際に蓄積した過去データからボトルネックの分析の流れや、突然のパフォーマンストラブルに向けたリアルタイムでの性能状況の確認、ボトルネック分析の流れを見ていきます。また、SQL の実行計画を柔軟に制御、管理ができる Amazon Aurora PostgreSQL の Query Performance Management (QPM) を使ったチューニングも実施していきます。

※セッション動画と資料は以下リンク

アジェンダ

1. 今回のシチュエーションとタスクの振り返り
2. Performance Insightsの使い方を確認する
3. ケース1の原因を分析して、SQLチューニングを実施する
4. ケース2のパフォーマンスダウンの原因を分析する
5. ケース2でパフォーマンスを安定化させるクエリー計画管理(QPM)を使用する
6. まとめ

今回のシチュエーションとタスクの振り返り

ケース1: 新たにバッチ処理(大量データの更新)をリリースしたが、全く終わらない

• バッチ処理がエラー終了した場合、この前日のバックアップ + 当日の更新データを元に再実行が可能
• ボトルネックを特定して、チューニングすることで排除したい
• アプリケーションのロジックを変更することも可能だが、可能な限り修正したくない

ケース2: 今まで全くパフォーマンス的に問題がなかったクエリが、ある日突然パフォーマンスダウンした

• 今回のシステムはとあるベンダーのパッケージ製品のため、すぐにSQLの書き換えを行うことができない
• 緊急対応のため、原因特定よりも現状のパフォーマンスアップを優先させる
• アプリケーション(SQL)に修正を加えることが非常に難しいので、直接的な修正以外で対応したい

Performance Insightsの使い方を確認する

RDSのPerformance Insightsは、3つの入口あります。

データベース一覧の「現在のアクティビティ」のリンク。

インスタンスの中の「モニタリングタブ」 → 「パフォーマンスインサイト」。

メニューバー内の「パフォーマンスインサイト」。今回は1つのインスタンスのみ立ち上げています。インスタンス名「online」をクリック。

データベースロードのグラフが立ち上がります。デフォルトでは過去1時間のデータがサマライズされており、最大2年前までの情報が取得可能。

Performance Insightsで最重要のデータベースロードのグラフは、以下のように見たい時間帯を直接ドラッグすることで、ドリルダウンが可能。

GUI上から時間帯を絞り込んだ上で、待機の原因まで知ることができます。下記では、行ロックが待機の原因となっていました。

待たされていたSQLやクライアント、DBユーザーの情報も取得できます。

データベースロードの上部にあるカウンターメトリクスでは、OSやDB内の基本的なメトリクス情報を追加することができます。

「View past」タブの5分を指定すると、Performance Insightsが毎秒取得したデータをサマライズすることなくリアルタイムで表示されます。自動更新をONにすれば、モニターとして使うことができます。

ケース1の原因を分析して、SQLチューニングを実施する

画面の見方を確認したところで、実際にケース１「バッチ処理のWrite性能の悪化」の状況を発生させます。今回は16セッションを使用して負荷をかけるクエリを流していきます。

実行後、データベースロードのグラフでは待機となっているオレンジのセッション数の割合が増加しました。IO待機名IO:XactSyncをマウスオーバーすると、詳細が表示されます。

IO:XactSyncは、データベースへのコミット数が増大しているため、Write待機が発生してしまっている状況を示しています。カウンターメトリクスでCPU使用率を確認してみると、負荷をかけるタイミングを境目にCPU使用率が大幅に上昇していることがわかりますね。

実行されていたSQL文を確認し、チューニングの方針を立てていきます。

チューニングの方針

1. 現状、1件ずつコミットが実行され、WAL書き込み待機が発生している
2. バッチ終了時にトランザクションの整合性が取れていれば問題ない。（1件づつ確実にトランザクションの整合性を取る必要はない）
3. 1件づつではなく、ある程度のまとまりでCOMMITを実行
4. 今回は、アプリケーションロジックを修正

修正したSQLで負荷をかける後のデータベースロードとカウンターメトリクスです。一気に色が変わりましたね。CPUを100%近く使うことができ、待機なく動くようになったことがわかります。

チューニング後のSQLでは、LOOPの後にCOMMITを打つよう変更しています。

このように、Performance Insightsでは、チューニング前と後のパフォーマンスをリアルタイムに可視化することができて、非常に便利ですね！

ケース2のパフォーマンスダウンの原因を分析する

ケース2では、16セッションを使用してReadの負荷をかけていきます。パフォーマンス悪化前のデータベースロードは以下のグラフです。Max CPUをかなり下回っているので、それほど負荷はかかっていない状況です。

カウンターメトリクスでは、CPU使用率とディスク使用率を見ていきます。現在はディスク使用率がほぼ0%ですね。

流しているSQL文を確認します。JOINとWHEREを使ったSELECT文で負荷がかかっています。

このSQLの実行計画を取得すると、JOINの結合方法にNested Loopが採用されていることがわかりました。

ここから、人為的にパフォーマンスを悪化させます。データベースロードでは青いグラフIO:BufFileWriteの割合が増え、添付ファイルをデータベースの内部に書き出さないといけない状況になっていて、そのWrite処理で待機が発生していることが読み取れます。

ただし、SQL文は変わっていません。現在の実行計画を確認すると、結合方法がHash Joinに変わっていることが判明しました。

チューニングの方向性としては、以下の通りです。

1. 今のHash Joinを、なんとかしてNested loopに戻したい
2. SQL文は書き換えられない

今回は、Aurora PostgreSQLのQuery Plan Managementを活用してチューニングしていきます、

ケース2でパフォーマンスを安定化させるクエリー計画管理(QPM)を使用する

Query Plan Managementを使用する流れ

1. クラスターのパラメータグループ "rds.enable_plan_management" = 1
2. インスタンス/セッション単位でのパラメータを設定
   1. プランのキャプチャー設定 "apg_plan_mgmt.capture_plan_baselines" = manual (or auto)
   2. プランベースラインの使用有無の設定 "apg_plan_mgmt.use_plan_baselines" = false (or true)
3. 拡張のインストール
   1. > create extension apg_plan_mgmt;
4. QPMで提供されるツール（関数）で定期的にベースラインをメンテナンス
   1. 既存のベースライン内のプランとは異なるプランを使いたい場合は、pg_hint_plan拡張を使って新しいプランをキャプチャーすることが可能
   2. ベースライン外のプランを検査し、必要に応じてプランのステータスを変更（Approved、Rejected、Preferred）
      1. apg_plan_mgmt.set_plan_status関数
   3. 各プランのクエリーの実行時間を実測し、高速になるものだけ承認、遅いものは拒否などが可能
      1. apg_plan_mgmt.evolve_plan_baselines関数

チューニングの方針

1. 通常時はNested Loop結合が使われていたが、パフォーマンス劣化時はHash結合が使われるようになっている
2. pg_hint_planのヒント句で実行計画を修正（SQL文の修正）
3. 今回はアプリケーション(SQL文)の書き換えが不可能 ↓
4. Aurora PostgreSQLのQuery Plan Management(QPM)を使って、SQL文の修正なしに実行計画を修正（Hash結合からNested Loop結合に戻す）
5. pg_hint_planでプランを修正し、QPMで修正後のプランをキャプチャー
6. 修正後のプランを許可（Approved）し、従来のプランを不許可（Rejected）にする

SQLを叩いてQPMを設定していきます。

SET apg_plan_mgmt.use_plan_baselines = false;
SET apg_plan_mgmt.capture_plan_baselines = manual;
EXPLAIN SELECT * FROM sample_table_a a JOIN sample_table_2 b on a.id2 = b.id WHERE a.id > 0 and a.id < 210000;
-- nested loopの実行計画が作られ、QPMに記憶される

-- 現在のApprovedとUnapprovedを入れ替える
SELECT apg_plan_mgmt.set_plan_status('<sql_hash A>', '<plan_hash A>', 'Approved');
SELECT apg_plan_mgmt.set_plan_status('<sql_hash B>', '<plan_hash B>', 'Unapproved');

プラン変更後、データベースロードのグラフでIO:BufFileWriteのグラフがなくなっていることが確認できました。もちろん、SQL文は変更が加わっていません。

まとめ

• チューニングの作業効率を高めるPerformance Insights
  • 過去のパフォーマンス状況の確認と問題のドリルダウン
  • リアルタイムでのパフォーマンスモニタリング
• 適切にクエリの実行計画を管理するためのAurora PostgreSQL Query Plan Management
  • クエリやアプリケーションの変更なしに適切な実行計画に変更するなどの管理が可能
  • 定期的にベースライン内を分析することで効率の高い実行計画を承認可能