【レポート】パフォーマンスチューニングの強い味方！Aurora PostgreSQL Performance Insightsのご紹介 #AWSSummit

DA事業本部の春田です。

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

セッション情報

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

ある日突然データベースのパフォーマンスが悪化した際に皆さんはどのように対処しますか? また、突然悪化させないためにどのような運用をしていますか? 本セッションでは過去から現在にいたるまでデータベースでどのようなワークロードの状況だったかを捕捉し、パフォーマンス悪化の状況や原因を分析します。また、日々の運用でパフォーマンスを適切に管理するのに役立つ Amazon Aurora PostgreSQL の Query Performance Management についても触れます。

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

アジェンダ

1. 今回のシチュエーションとタスク
2. 一般的なチューニングのサイクルと課題
3. チューニングに必要なもの
4. AWSでチューニングを支援する便利なサービス
5. クエリーの実行計画を管理する
6. まとめ

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

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

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

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

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

よくあるパフォーマンストラブルだが、現場ごとにアプローチが異なっている。

一般的なチューニングのサイクルと課題

1. コレクション（データ収集）

• OSレイヤーやDBの特定の統計情報を定期的に取得
  • 独自プログラム
  • 3rdパーティツール
  • CloudWatch、CloudWatch Logs
  • 拡張モニタリング
• 課題
  • 粒度を細かく、長期間に渡りデータを取得していく必要があり、管理・運用コストが高い
  • OSのメトリクスだけではなく、DB特有のメトリクスを対象データベースに負荷なく細かい粒度で取得することが困難

2. アナリシス（分析）

• 複数のメトリクスを組み合わせて、データベースがどのような挙動をしていたかを類推
  • CPU使用率 + キャッシュヒット率
  • 論理読み込み数 + 物理読み込み数
  • トランザクション数 + ロック待ちセッション数
• 課題
  • 様々なメトリクスから多角的に分析することが要求される → 高いスキルを持った人材に属人化
  • どの程度リソースが不足、余剰なのかを見積もることが困難

3. チューニング（最適化）

• トライ&エラーによるチューニングの実施
  • 熟練DBAの経験に基づいたチューニング
  • データ収集できていない項目は、その都度手動で取得
• 課題
  • ボトルネックの分析が曖昧・不十分 → 非効果的・非効率なチューニング
  • どの程度チューニングされたら成功とみなすかの判断が難しい
  • リアルタイムでDB全体のパフォーマンス測定できていないため、チューニング効果の確認に時間がかかる

チューニングに必要なもの

チューニングを最適化するには、「データ収集」「分析」のフェーズをより効果的に行うためのインフラが必要。

• パフォーマンスデータを平時から収集
• 問題が発生したら、シンプルな形で分析してチューニングにつなげる

ただ、このようなインフラを整えるにはかなりのコストがかかる → Amazon RDS Performance Insights

AWSでチューニングを支援する便利なサービス

下記は、Performance Insightsのダッシュボード。特に重要なのが、データベースのロード。

• データベースのロード（Average Active Sessions）
  • シンプルな指標で、どこに問題が発生しているかを調査することができる
• カウンターメトリクス
  • OSのリソース情報や、DBの統計情報といった、従来通りの情報も組み合わせて見ることができる
• ボトルネックの分析軸
  • 「データベースのロード」を補足する、より細かな情報が見れる

• データベースのロード（Average Active Sessions; AAS）
  • 横軸に時間軸
  • 縦軸にSession軸
    • Active or Inactive
  • ActiveなSessionの数 = Average Active Sessions
  • どの時間にいくつアクティブなセッションがあるのか？
  • セッション単位で負荷を外観できる

• RunningかWaitingの内訳
  • 処理が滞っていた時間を色分けで可視化
  • アクティブなセッション数は同じだが、内訳が違うことがわかる

ReadかWriteかでの内訳も見ることが可能。

• データベースロード（AAS）が高ければ高いほど、負荷は大きい
• 何をしきい値にするか？ → 最大vCPU
  • AAS ≒ 0 → データベースに負荷が来ていない → Web層かアプリケーション層で問題が発生？
  • AAS >= CPU数 → これから問題が発生しそう
  • AAS >> CPU数 → 問題が発生している

Performance Insightsの特徴（データ収集、管理）

• データ取得の粒度
  • 毎秒アクティブなセッションの状態を収集
  • 突発的なパフォーマンストラブル時でも、粒度の高いデータでボトルネックの分析が可能
• データの保存期間
  • 過去7日分は無料で収集・保存が可能
  • 追加料金で最大2年分のデータ保存が可能
• ワークロードを端的に示す指標
  • 重要な指標 → アクティブなセッションの総数（データベースロード、AAS）
  • 待機情報も収集されているため、ボトルネックを分析する際にドリルダウンが可能
• フルマネージ型のサービス
  • 収集データの暗号化キー、保存期間の指定などを簡単に設定可能
  • システム側で定期的に保存データを削除

Performance Insightsの特徴（パフォーマンス分析）

• データベースロード
  • シンプルな指標で、DBに負荷がかかっていた時間帯がわかる
  • データベースロード >> vCPU の場合、パフォーマンスに問題が発生している
  • チューニングが必要な時間帯において、何がボトルネックだったのかをドリルダウンできる
• カウンターメトリクス
  • データベースロードを補足するデータを確認
    • OSのリソース情報（CPU、メモリ、I/Oリソースなど）
    • データベースの統計情報（セッション数、トランザクション数）
• ボトルネックのドリルダウン分析
  • パフォーマンスにインパクトを与えた具体的な原因を調査
    • 待機イベント、SQL文、ホスト、DBユーザー
• SQL単位の統計情報など
  • SQL単位の統計情報も取得可能
  • 実行数、実行時間、論理/物理、読み込み量

Performance Insightsの使用イメージ

• データベースロードがCPUを大きく超えているか？
• どんな待機イベントが発生しているか？
• どのSQLが待機イベントを発生させているのか？
• Max vCPUというシンプルな指標で判断可能
• チューニングの結果もリアルタイムにわかる → 問題・原因・結果が一つの時系列グラフで見れる

クエリーの実行計画を管理する

#	メリット	デメリット
SQLヒント	・SQL文毎にヒント句で柔軟に実行プランの制御が可能	・SQL文にヒント句を入れることはSQL文（アプリケーショ）の修正を伴う ・3rdパーティーパッケージなどヒント句を入れることが不可能な場合がある ・より効率の良い実行プランが存在しても選択されない
SQL Plan Stability	・個別にヒント句をSQL文に入れるのではなく、 ヒント句とSQL文を関連づけることができる ・SQL文（アプリケーション）の修正を伴わない	・より効率の良い実行プランが存在しても選択されない
SQL Plan管理	・実行計画のバージョンを管理しどのバージョンを使うかは管理者の承認により制御可能 ・統計情報変更等により効率の良い実行プランが 生成された場合は確認及び使用が可能 ・SQL文（アプリケーション）の修正を伴わない ・個別に実行計画の修正が必要な場合は、ヒント句で実行計画を修正した後に承認が可能	・SQL Plan管理が可能なデータベースエンジンが限られる

SQL Plan管理はSQLヒントとSQL Plan Stabilityの良いとこ取りの機能だが、対応しているDBが少ない。

#	SQLヒント	SQL Plan Stability	SQL Plan管理
RDS Oracle	ヒント句による実行計画の調整	STORED OUTLINEによる実行計画の固定	SQL Plan Managementによる実行計画の管理と固定
RDS SQL Server	ヒント句による実行計画の調整	プランガイドによる実行計画の固定	クエリストアによる実行計画の一部管理と固定
RDS PostgreSQL	pg_hint_plan拡張によるヒント句の使用	pg_hint_plan拡張のヒントテーブルにより実行計画の固定	なし
Aurora PostgreSQL	pg_hint_plan拡張によるヒント句の使用	pg_hint_plan拡張のヒントテーブルにより実行計画の固定	Query Plan Management （QPM）による実行計画の管理と固定
RDS MySQL / MariaDB	index hint、optimizer hintによる実行計画の調整	なし	なし

• Aurora PostgreSQLのPlan管理 = Query Plan Management (QPM)
• 固定化したいプランを、ベースラインの中に入れる（複数可能）
• もっともコストが低いとデータベースによって判断したプランが使用される
• 管理者は、ベースラインに何を入れるかを制御する
• 許可したプランを開発環境から本番環境へと、インポート・エクスポートが可能

まとめ

• チューニングサイクルの理解
• チューニングに必要なデータやインフラの理解
• チューニングの作業効率を高めるPerformance Insights
• 適切にクエリーの実行計画を管理するためのAurora PostgreSQL Query Plan Management

後半の内容は実践編です。