Zenn
🐐

Aurora PostgreSQLのチューニングポイント

2023/09/14に公開
PostgreSQL
aurora
index
チューニング
hint
tech

Aurora PosgreSQLのチューニングを行う機会があったので、そのときに私が感じたPostgreSQLのチューニングポイントを紹介します。
担当したのは1億件を超えるデータに対するバッチ処理部分ですので、バッチでの改善ポイントに偏っています。
また、私はこれまでOracleでの開発が多かったので、Oracleと同じつもりで触ってつまづいたところなどを紹介していきます。

利用したPostgreSQLバージョンは13.7です。

なお、以下の説明でのSQLは説明用に適当に書いた疑似SQL（例として提示しただけのもの）です。
例として単純化しているので「そんな処理しねーよ」とか突っ込まないでね。
そのSQLで言わんとしていることをくみ取っていただければ。

要約

以下の点について記載しています。

  • PostgreSQLのUPDATEは仕組みが異なるので理解すると性能改善できるかも
    • 同じ値に更新するUPDATEは1行INSERTと同じ
    • indexがあるテーブルのUPDATEは遅い
  • JOINでパラレルクエリを活用すると劇的に改善することがある
  • indexは効果的に
    • 不要なものの見つけ方
    • 効果的な作り方

説明の前に

実行計画はexplain analyzeで取得する

チューニングするときには実行計画を見てPostgreSQLがどんな判断をしているのか確認することになります（実行計画の見方は本記事では説明しませんのでググって探してもらえれば）。
そのコマンドですが、以下のようにSQLの前に「explain」をつけて実行するだけです。

explain SELECT * FROM table1;

なのですが、explainだけだと「想定コスト」しか見られません。
実際には「想定コストは優秀なのに実処理時間が遅い」という場合があります。
チューニング対象となるSQLでは特にこれが起こりやすいです（例：indexを使って早く処理できるのにPostgreSQLは順次処理を選択して遅くなっている、など）。

なので、実際にチューニング前後の性能確認は必ず「explain analyze」で実際の処理時間も確認するようにしてください。
（さらにbuffersをつけるとキャッシュをどのくらい使っているかどもわかる）

私の場合はいつも以下のようにしていました。

explain (analyze, buffers) SELECT * FROM table1;

UPDATE文などはデータを変えてしまい気軽に実行できないかもしれませんが、バックアップを取っておくなどして対応しましょう。
くれぐれも「想定コスト」だけでチューニングを進めないようにしてください。

キャッシュに注意する

同じSQLを何度も実行するとそのSQL結果がキャッシュされます。これにより、同じSQLでも2回目以降は劇的に早くなる場合がありました。
Aurora PostgreSQLではキャッシュ管理はAWS側が請け負っていて外からキャッシュクリアする手段がなくてとても困りました。
私の場合は、きちんと実測したいときはテーブルをDROP・テーブル作成・データ取り込みなど、最初から全部やり直していました。
（とても時間がかかるのでもっと良い方法があればいいのですが...）

対応の仕方はともかく、チューニング対象のSQLを特定している場合、同一SQLを何度も実行することになると思いますので、キャッシュで性能改善してしまっていないかに注意しておく必要があります。

では、ここからチューニングのポイントです。

UPDATE

UPDATE対象を絞る

PostgreSQLで特に注意しなければいけないのは「仕組み上UPDATEが遅い」ということです。

仕組みについてはfillfactorの記事でも書きましたのでそちらも参照ください。
簡単に言うとPostresQLのUPDATEは1項目だけのUPDATEでも1行分のINSERT（とほぼ同じ）になります。

これがどのように影響するかですが、私が実際にやってしまった失敗を例にします。
以下のような状況を思い浮かべてみてください。

  • 1億件のデータがある
  • ある特定の項目列で1万行には何らかの値が入っており、残りはNULL値になっている
  • この項目を全件NULL値でリセットしたい

以下のようなSQLでよさそうに思いませんか？

UPDATE table1 SET col1 = NULL;

元がnull値の行は同じ値なのだから影響するのは結局1万件だけ。これで目的は果たせるだろう、と。

PostgreSQLではこのSQLはNGです。
なぜってこのSQLだと「1億件のINSERTが行われてしまう」からです。

PostgreSQLでは、同一値にUPDATEする場合でも1行分のデータ追加が行われます。

改善策

今回の例では以下のように書く必要があります。

UPDATE table1 SET col1 = NULL WHERE col1 IS NOT NULL;

このようにPostgreSQLのUPDATEでは「UPDATE対象を絞る」というのがとても大事です。

indexがあるテーブルのUPDATEは遅い

PostgreSQLの仕組みに関連してもう一つイメージと異なる挙動をする箇所がありました。
それが「indexのあるテーブルのUPDATEが遅い」ということでした。

たとえば以下のようにcol1とcol2にindexがあるとします。

CREATE INDEX index1 ON table1 (col1, col2);

このとき、以下のようなcol1やcol2に対するUPDATEが遅いのはイメージしやすいと思います。

-- 遅そう
UPDATE table1 SET col2 = (col9 % 5);

col2はindexに関わる項目なのでindexに対する再計算や更新が発生しそうですよね。
実務ではあまり発生しないようなUPDATEですが、これが遅そうなのはイメージできます。

ですが、PostgreSQLの場合、indexに関わらない項目列のUPDATEでも遅いのです。

たとえばindexに関わらないcol3に対する以下のようなSQLにも比較的時間がかかります。

-- indexに関係ないけどこれも遅い
UPDATE table1 SET col3 = '1';

これもPostgreSQLの仕組みに起因します。

「UPDATEは1行分のINSERT」と書きましたが、UPDATEでも新たにデータ書込しているので、対象行の格納位置（アドレス）が変わります。このため、indexに保存されている「このデータはここにある」という参照先も更新する必要があります。
この処理のため、indexに指定していない項目列でもUPDATEには時間がかかります。
これに対応するために「HOT」という機構が用意されていてHOTが使えるとかなり影響は低減されますが、それでも時間がかかる処理であることには気を付けておく必要があります。

改善策

私が担当したシステムでは、更新対象行数が多くどうしても遅いUPDATEに対して、いったんindexをDROPしてからUPDATEしその後にまたindex再作成というように書き直しただけでかなりの時間短縮になった箇所がありました。

注意点としては、index再作成にも時間がかかるので、どこでもこの手で早くなるわけではありません（あんまり推奨するような対応でもないですし）。
データ量によっては効果は低くなるか返って遅くなることもありますので、実際の処理時間をしっかり確認することが大事です。

パラレルクエリの活用

hint句でJOINをパラレルクエリ化

処理時間がかかりやすい箇所としてJOINがあります。

たとえば以下のようにtable1とtable2を結合するようなSQL。

SELECT t1.*, t2.* FROM table1 AS t1
INNER JOIN table2 AS t2
        ON t1.col1 = t2.col1
WHERE t2.col3 = '1';

このときtable1が1億件、table2が100万件などだと非常に時間のかかるのですが、単純なSQLなのでSQLの書き直しではなかなか性能改善が難しいです。

改善策

まずはSQLにEXPLAINをつけて実行し、JOINがどのように行われているかを確認します。
PostgreSQLでは以下のJOINがあります。

  • NestedLoop
  • MergeJoin
  • HashJoin

現在のJOIN戦略はどうあれ、これをHashJoinでパラレス数を増やして処理させる、というのがここでの案です。
以下のように記述を追加します。

SELECT /*+ HashJoin(t1 t2) Parallel(t1 8 hard) */ t1.*, t2.* FROM table1 AS t1
INNER JOIN table2 AS t2
        ON t1.col1 = t2.col1
WHERE t2.col3 = '1';

※ hint句はデフォルト設定では使えません。利用可能にする設定はググって調べて適用してください。

※ hint句はpgAdmin上からであればSELECTの前に記述しても適用されるのですが、ストアドの場合には上記のように書かないと適用されないのでご注意ください。

上記に追加したhint句は以下の意味です。

  • t1とt2のJOINはHashJoinで行う
  • パラレル数は8を強制固定

これで実行してみて性能向上するかを確認してみてください。
うまくいけば10分の1くらいにまで高速化する場合があります。

上記のhint句はいろいろ試した結果、私のときに最も効果の高かった設定です。
実際のSQLによってなにがもっとも効果的かは変わると思うのでMergeJoinにするとか、パラレル数を変えてみるとか、いろいろ試して探ってみてください。

適用時の注意点

私が試している中で引っかかった点を挙げておきます。

  • パラレルクエリはデフォルトで「1SQLでは最大パラレル数2」なので、Parallelでパラレル数を指定しないと2で実行される
    • 具体的には以下のパラメータです。実際のパラメータ値を確認してみてください。
      • max_parallel_workers_per_gather：デフォルト2
      • もう一つmax_worker_processesがあり、これはデフォルト8ですが上のパラメータが2なので何もしないと最大2です。
  • Parallel指定で数字を指定してもhardをつけないとPostgreSQLの判断にまかせることになり、2や4が選択されることが多い（実際にhardをつけて実行してみると8の方が早いのに）。
    • hardをつけないと、あるときは8あるときは4、といった挙動で処理性能が安定しなかったので固定指定とした
  • 「想定コスト」としてはNestedLoopがもっとも低い。hint句をつけたHashJoinは桁が異なるレベルで高く見積もられる。このためPostgreSQLが自分でHashJoinを選択することはなかった。
  • MergeJoinを指定した場合はパラレル数が8 hardで指定してもパラレル数が8までいかず、結果として性能改善も限定的だった。

効果的なindex

不要なindexの削除

PostgreSQLではindexを適切につける、または不要なものは作成しないことはとても重要です。

まずは、作ったはいいけど使われていないindexを削除しましょう。

使われていないindexの探し方２つ

実行計画を見る

使われていないことはSQL単体については実行計画を見ることで確認できます。
例えば以下のように表示されていればindexが使われています。

   -> Index Scan using index1 on t1 (cost=0.00..12345.67 .....

index1というindexを利用していることがわかります。
使われていないときはこんな感じ。

   -> Seq Scan on t1 (cost=0.00..12345.67 .....

特定のSQLでindexが使われているかどうかはこれでわかります。

統計テーブルを見る

一連のバッチ処理全体でindexが使われているかどうかは以下のように確認できます。

pg_stat_user_indexesというテーブルでindexの統計情報を見ることができます。

SELECT * FROM pg_stat_user_indexes where rel_name = 'index1';

こうするとindex1がどのくらい利用されているかがわかります。
idx_scan, idx_tup_read, idx_tup_fetchという項目に数値が出力されると思いますが、そのindexが使われるごとにこの数値が増えていきます。

これを利用して、以下を確認します。

  • idx_scan, idx_tup_read, idx_tup_fetchの数値が0
    • →まったく使われていないので即削除
  • バッチ実行前とバッチ実行後のidx_scan, idx_tup_read, idx_tup_fetchの差を確認
    • →差がない場合は、そのバッチではindexは使われていない

注意点ですが、「統計値は累積」です。
なので、たとえばpgAdminで誰かがちょっとした調査のため実行したSQLでも統計値に加算されます。

これを念頭において、「バッチ前後での数値を比較する」などを行い、実運用上でindexが使われているかどうかを判断するようにしてください。

使いやすいindexを用意

たとえば以下のようなSQLがあるとします。

SELECT * FROM table1 WHERE col1 = '1' AND col3 = 'val1' ORDER BY col1, col2, col3;

このSQLを早くするindexですが、データの状況によって効果的なindexは変わってきます。

複合index

まずすぐ思いつくのはORDER BYが重い処理なのでここに対するindexかなと思います。

CREATE INDEX index1 ON table1 (col1, col2, col3);

これはこれで正解なのですが、これだとWHERE句はSeqScan、つまり全件を順番に見ていく処理になります。
table1のデータ件数が多いとこれはとても時間がかかります。

また、このindexは「複合index」といって複数項目を指定するものですが、基本的にこの項目順で処理する場合にのみindexが使われるため、パターンにうまくはまらないと効果が出ません。

状況に応じた効果的なindex

たとえばcol3は区分値でval1からval10がどれも同じくらいの件数で指定されているとします。

この場合は以下のindexが効果的です。

CREATE INDEX index2 ON table1 (col3);

これだとWHERE句で対象件数をindexを使って一気に10分の1にすることができます。

これはテクニカルに言うとカーディナリティが高い項目にindexを作成するということですが、要は「処理する件数を早く減らせるようなindexをつける」ということです。

また、このindexは複合indexと違い、「col3が条件に含まれるSQLすべてで利用可能」です。
結果としてはここだけではなく、他のSQLでも性能改善につながる可能性が高くなります。

このようにデータの中身も考慮することで、より効果的なindexを作成することができます。

終わりに

一般的なチューニングに関しての良記事はたくさんあるのですが、PostgreSQL特有のポイントについての記事は意外と見当たらなかったので書いてみました。
また、何とか性能改善しなければいけなかったので、普通は推奨されないような方法も書いてあり、より実践的かと思います。
チューニングでお困りの方の少しでも参考になれば。

Discussion