SQLパフォーマンスチューニングのはじめかた with Aurora PostgreSQL ～年初の私に捧ぐ～

はじめに

フューチャー Advent Calendar 2024の23日目です。

2024年を振り返ると、今年はがっつりPostgreSQLと向き合う１年だったなと思います。私がいままで経験してきたデータベースはDynamoDBやBigQueryなど非RDBなものが多く、またIoTの文脈でシステムに携わってきたことが多かったこともあってかあまり「RDBにおけるSQLパフォーマンスチューニング」というものに触れる機会がありませんでした。

2024年を通してSQLチューニングに取り組んできた私が、2024年初頭の私に届けたいチューニングの勘所や進め方をこの記事に書き記したい...それが本記事のモチベーションです。

※私が取り組んだSQLチューニングは、Amazon Aurora PostgreSQL(ver14.6)を前提としてます。

※パフォーマンスメトリクスが充実している環境の場合、後述する私の心得は各所不要なパートもあるのではと思います。そういった箇所は「ウチはこのへんいい感じにやってるから不要だなー」と読み飛ばしちゃってください。

パフォーマンスチューニングの心得

1.前提情報編

まずはチューニング対象システムの設定パラメータを確認するところから始めましょう。特にタイムアウト周りの設定を確認しておくことが重要です。

• LBタイムアウト
• SQLクエリタイムアウト
• etc...

システムは各所でタイムアウトが仕込まれていると思います。基本的にはV字型に設計されてるとは思いますが、そうでない場合も踏まえて確認しておくのが好ましいです。パフォーマンスチューニングを依頼される際には、「◯◯でタイムアウトエラーが発生した。調べて欲しい。」といったざっくり粒度で初報が飛んでくるケースも少なくないので、システム全体にてどういったケースでタイムアウトが起こりうるのかを頭に入れておくと切り分けがスムーズに進みます。

スロークエリログの閾値も確認しておきましょう。具体的な設定項目はlog_min_duration_statementですが、この値を超えたクエリ実行について実行計画がログ出力されます。閾値を下げすぎるとなんでもかんでもログ出力されてしまい、例えばAWS利用の場合ではログの金額増加に繋がってしまいます。あまり小さな値は設定しすぎず、必要なタイミングでのみ閾値を下げる運用がおすすめです。

2.事前準備編

チューニングに突入する際は、以下の情報を揃えるところから始めしょう。

1. チューニング対象の機能はどのサービスで動いているか　
2. 対象機能の総動作時間
3. 対象機能内の各SQLの動作時間
4. チューニング対象となるSQLの実行計画
5. DBのパフォーマンス情報

2-1.チューニング対象の機能はどのサービスで動いているか

これはプロジェクトで作成されている構成図などを元に確認すれば概ね情報が充足するはずです。後続作業にてログの取得を進めていくことになりますが、どのログを見ればよいのかが分からないと収集のしようがありません。例えばCloudWatchのどのロググループを見ればよいのか、などをまずは確認しましょう。

2-2.対象機能の総動作時間

もしかしたらこのプロセスは不要な方もいるかもしれません。私はイベント駆動で動作するバックエンドプロセスのパフォーマンスチューニングに携わる機会が多かったので、まずは一連の機能全体がどの程度の時間で動作していたのかを確認することが重要でした。

ログなどから機能の動作時間を取得しておきましょう。

2-3.対象機能内の各SQLの動作時間

各機能は数本のSQL発行を通じて必要な情報を取得、クライアントへレスポンスを返却することが多いと思います。「◯◯機能が遅いから見て欲しい」と言われた際、その機能にて実際に遅い箇所はどこなのかを特定していくことが重要です。

SQLごとの実行時間は詳細なログに出力される内容なので取得しておきましょう。また、その際にはSQLにおける処理件数も合わせて取得できることが望ましいです。もしSQL個別の動作時間および処理ボリュームを把握するすべがない場合、ログ出力項目などの見直しを行ってもいいのではと思います。

※SQL以外のアプリケーションソースコードに性能課題があるケースについては本記事では割愛しますが、可能性はもちろんゼロではないので念頭に置いておきましょう。

2-4.チューニング対象となるSQLの実行計画

前項にて「遅い」と判断されたSQLを対象に実行計画を取得しましょう。該当SQLの実行時間がスロークエリログの出力閾値を超えている場合にはログから実行計画を取得可能です。

Tips
私は当初CloudWatch Logsにてログを探す際、該当ロググループを選び、たくさんあるログストリームをひとつずつ選択して検索してました...イケてないですね。「すべてのログストリームを検索」を押下すると一括で検索できるので忘れずに活用しましょう。（自戒）

また、実行計画を探す際は「duration "ms plan"」（msとplanの間は半角スペース2個です）と検索ボックスにいれると実行計画のみが表示されるので非常に便利です。

SQL実行時間がスロークエリログ閾値未満の場合

スロークエリログは出ないため、自分でSQLを組み立てて実行し改めて実行計画を取得する必要があります。API内でのSQL本数やその複雑性にもよりますが、チューニング対象SQL実行直前のデータ断面を準備することが難しい場合はスロークエリログ閾値を一時的に下げて実行計画を取得した方が良いケースもあると思います。

私が本年取り組んだチューニングにおいては、画面APIのSQLチューニング時などは自身で実行計画を取得する手法をとることが多かったです。例えば画面検索APIなどは1秒未満でのレスポンス要求が求められることもあるはず。検索APIはSQL実行直前断面を比較的容易に作り出すことができる（できた）ので、閾値を極小に下げることはせずに自身で実行計画を取得していました。

自身で実行計画を取得する場合は、チューニング対象のSQLを組み立てた上でexplain analyzeを付けて実行しましょう。この「SQLを組み立てる」部分は取り組んでいる環境によってやり方は様々かと思います。私の触っていた環境ではSQL文とバインドパラメータがログに出力されていた（検証環境のため）ので、ログを元に実際に発行されていたSQL文を作成、explain analyzeを付けて実際に発行することで実行計画を取得していました。

2-5.DBレイヤのパフォーマンス情報

チューニング対象機能が動作していたタイミングのDBパフォーマンス状況を確認しておきましょう。例えば、AWSコンソールにてPerformanceInsightsを確認するというのが具体的なアクションです。該当時間のDB負荷は普段通りだったのか、なにか予定外の別機能が動作していてそれがチューニング対象機能に影響を及ぼしていたのか、などが確認可能です。

3.実行編

さて、情報収集が完了したら具体的な動作を紐解いて行きましょう。

3-1.本当にその機能が遅いのか切り分ける

まずは外的要因の有無を確認し、「遅い」となっている機能が本当に遅いのか、あるいは外的要因により「遅く見えている」のか切り分けます。

• 普段動かないはずのジョブが同時に動いており、ロック待ちが発生していた
• 他機能のSQLが長走し、ロック待ちが発生していた

私が今年出会ったあるあるの外的要因は上記のように「他要因起点でのロック待ち」というパターンが多かったです。SQLクエリタイムアウト（例えば10分）に達しているが、10分の内訳を紐解くとほとんどがロック待ちの時間だったというケースもありました。

個人的に外的要因の特定に役立ったのが2-5で取得したPerformanceInsightsの情報です。DBLoadメトリクスには様々な情報が表示されるのですが、「待機イベント」についての表示がありこれにより該当機能動作タイミングでロック待ちが発生していたかなど確認することが可能です。

3-2.本当に遅い場合

外的要因の影響を受けてないことが分かったら、やっと「本当に遅い」ことが確定します。以下ステップで進めていきましょう。

1. SQLの処理件数を確認し、業務仕様踏まえた処理量の確からしさを確認する
2. SQLの確からしさを確認する
3. 実行計画を分析する

3-2-1.SQLの処理件数を確認し、業務仕様踏まえた処理量の確からしさを確認する

さて、該当のSQLは本来の想定処理量で動作しているのでしょうか。

アプリの処理フロー設計なども大きく関わりますが、該当SQLが本来はありえないデータボリュームを処理しようとして結果遅くなってしまっているケースもありえます。想定外のアプリ操作、あるいはバグ起因などで処理対象件数が大幅に上振れしてしまってる場合、戦うべき処理件数をしっかりと確認した上で後続ステップに進まないと不毛かつ不必要な戦いに突入してしまいかねません。

2-3で取得した情報をもとにSQLごとの処理件数を整理し業務仕様の分かるメンバーと会話し、直面している処理ボリュームが「ありえる」のか「なにかおかしい」のかを判断することが先決です。「なにかおかしい」場合には、アプリバグなどの可能性も視野に調査を進めることで結果的に手戻りなく課題解決に進むことができるはずです。

3-2-2.SQLの確からしさを確認する

次は実行されているSQLの確からしさを確認しましょう。機能的な確からしさはもちろんのこと、非機能的な観点でもこのタイミングでSQLをチェックするのが好ましいです。後続ステップにて実行計画を見ることにもなるので、SQLがどういう構造になっているのかをこの時点でしっかり把握しておくことが大事です。

• 結合時にパーティションキーがリテラル指定されていない

基礎的な部分ではありますが、パーティションプルーニングをしっかり効かせるためにもリテラル指定により静的パーティションプルーニングが動作する状態を作っておくことが重要です。それに違反しているSQLを見つけた場合にはまずSQLの修正を行いましょう。動的パーティションプルーニングに頼るしかないケースもあるかもしれませんが、その場合は性能を考慮した方針が各開発プロジェクトにはあるかと思いますので、その方針に沿っているか確認しましょう。

3-2-3.実行計画を分析する

ここまで到達したら、2-4で取得した実行計画をじっくり分析するパートに突入します。

我々のチームでは実行計画の可視化ツールとしてOSSのpev2を活用しました。可視化ツールの利用は必須ではないですが、私のように実行計画を素の状態で読み解くことに慣れてない人には利用をおすすめしたいです。チューニング時にどこから取り掛かるべきかが分かりやすくなります。

3-2-3-1. pev2をどう見るか

pev2に実行計画を食わせると、いくつかのビューが展開されます。

• Plan
  • 実行計画がノードに分解され、ビジュアライズされている
  • ノードごとのdurationやrowsも確認可能
  • 素人がこのビューから見始めても迷走しちゃうかも
• Grid
  • 実行計画がノードに分解されている
  • 初心者は、まずStatsを見たうえでGridを参照すると良い
  • 同一SQLのチューニングを行う際、前後比較などはやりやすい表示スタイル
• Raw
  • 素の実行計画が表示される
  • 素で読める玄人はここを見る
• Stats
  • テーブル・ノード・インデックスごとにどこで時間がかかっているかを表示している
  • SQLのどこに時間がかかっているのかをざっと把握することが可能
• Hint
  • これは私のチームメンバーが拡張してくれたタブなので素のpev2を使う際には存在しません

SQLチューニング素人だった私はStatsを見るところから始めました。

• 想定外に多めのパーティションをスキャンしている
• 単一のIndex scanが遅い
• 単一のSeq scanで時間がかかってる

など遅い原因となっている箇所を特定し、該当箇所の問題を解消するチューニングを行っていきます。どこから見ていいか悩むうちは、Statsにて一番時間のかかってるノードを特定し、その処理を高速化するチューニングを実施するところから始めるのがよいでしょう。

3-2-3-2. チューニングポイントあるある

これ以降のチューニングプロセスにおいては個々のSQLのごとに対応パターンが多岐に渡るため、本パートでは私が今年直面したあるあるパターンとそれに対してのアプローチをいくつかご紹介できればと思います。

• 全PT（パーティション）をスキャンしている
  • PTプルーニングが行われてない可能性が高い
  • 設計資料等にてテーブルのPTキーを確認し、SQLにて結合キーにPTキーが指定されてるか確認する
  • リテラル指定ができない場合は動的PTプルーニングに頼ることになるが、うまく効かないことがある
  • その場合は、スキャン対象PTの限界値を定め、少しでもスキャン対象のPT数を減らすよう調整するなど対応する
• Seq Scanが遅い
  • Index scan可能だがプランナーによりSeq scanが選択されてしまっているケースがある
  • 設計資料等にてテーブルのindex設計を確認し、SQLにて記載されてる結合キーにて利用可能なindexが無いか確認する
  • 使えそうなindexがある場合には、ヒント句によるチューニングを行う
  • 適切なindexがない場合はindexの追加を検討する
• Index Scanが遅い
  • 利用しているindexが適切でないケースが多い
  • 設計資料等にてテーブルのindex設計を確認し、使えそうなindexがある場合には、ヒント句によるチューニングを行う
  • 適切なindexがない場合はindexの追加を検討する

おわりに

改めて今年取り組んだSQLチューニングを振り返ると、実行計画を見る前にも確認すべきことがたくさんあったし、SQLの動作コンディションを確認したら「これは異常パターンだから対応不要だね」と解決することも少なくなかったです。

チューニングパターンについては個別対応なケースも多く、あまり「あるある」としてパターンを列挙できなかったのが心残りですが、2024年の初めの私にとってはこれでも十二分にうれしい内容にはなってるのかなと思います。今後「SQLのパフォーマンスチューニングに初めて取り組む」方にとって、この記事が参考になれば幸いです。