【レポート】パフォーマンスチューニングの強い味方!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

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