AWS Aurora PostgreSQLでNo space left on deviceエラーが発生

先にまとめ
背景
調査
work_memの値を設定
結果
まとめ

先にまとめ

Aurora PostgreSQLにSELECTを実行したら、No space left on deviceエラーが発生した
Parameter groupのwork_memの設定値をイジって回避できた

背景

could not write to file…/pgsql_tmp/pgsql_tmp691.1372

Aurora PostgreSQLに対してSELECTを実行したら、途中で次のエラーが発生した(Pythonのpsycopg2を使用)

psycopg2.errors.DiskFull: could not write to file "pg_tblspc/16399/PG_15_202209061/pgsql_tmp/pgsql_tmp691.1372": No space left on device

could not write to file "pg_tblspc/16399/PG_15_202209061/pgsql_tmp/pgsql_tmp691.1372 からtmpに書き込む容量がないことだけはわかる

実行したquery

実行したqueryは規模が大きいテーブルから全レコードを抽出する、かつ LEFT JOINで別の規模が大きいテーブルとprimary key同士でjoinしている

SELECT
  main.*,
  COALESCE(sub.updated_at, %(param_timestamp)s AT TIME ZONE 'Asia/Tokyo') AS _updated_at,
  current_timestamp AS _ts
FROM sample.main_table main
LEFT JOIN sample.sub_table sub
 ON sub.pkey1 = main.pkey1
 AND sub.pkey2 = main.pkey2
 AND sub.pkey3 = main.pkey3

実行後約40min後にエラーが発生し処理が終了した

モニタリング

FreeEphemeralStorageが徐々に減少してエラーが発生した8/8 18:00JST時点で回復しているのがわかった
一方、FreeLocalStorage, FreeableMemoryの値は一定でまだ余裕がある

調査

参考:ChatGPTに「Ephemeral Storage vs. Local Storage」について質問

> Ephemeral Storage vs. Local Storageの役割
>Aurora PostgreSQLは、クエリ実行中に必要な一時的な計算やデータ処理にEphemeral Storageを使用します。このため、LEFT JOINやデータ変換を含むクエリを実行すると、Ephemeral Storageが主に消費される結果になります。
> Local Storageは主にデータの永続化に使用されるため、クエリ実行中の一時的な処理には関与しないことが多いです。このため、クエリ実行時にEphemeral Storageが減少し、Local Storageが余っている状態となります。

参考: FreeEphemeralStorageを増やすには？

Monitor the storage space available on the instance store with the CloudWatch metric FreeEphemeralStorage. If the instance store is reaching its limit because of the workload on the DB instance, tune the concurrency and queries which heavily use temporary objects or modify it to use a larger DB instance class.

Improving query performance for Aurora PostgreSQL with Aurora Optimized Reads – Amazon Aurora

ここには「一時オブジェクトを多用するクエリの調整」をするか「DB instance classを上げる」、と書かれている

参考: 「work_memを増やす」対応方法

20.4. 資源の消費

work_mem (integer)

ChatGPT
適切な範囲でwork_memを増やすことで、メモリ内で処理できるデータ量が増え、Ephemeral Storageの使用量を減らせます。ただし、work_memを過度に増やすと、メモリ不足を引き起こす可能性があるため、インスタンスのメモリ容量に注意して調整します。

work_memの値を設定

変更前のwork_memの値を確認

SELECT name,setting,unit FROM pg_settings;

default値である4096KB(4MB)になっていた

work_memをいくつに設定するか？

参考にさせていただいた記事
チューニング～データベースチューニング～｜PostgreSQLインサイド : 富士通
 PostgresのRDSチューニング

実際に実施したのは
> work_mem * connection最大数が全メモリの1/4を超えないぐらいにしておく

計算

全メモリ
db.r6gd.xlargeなのでメモリは32GiB
参考: DB インスタンスクラス – Amazon Relational Database Service
connection最大数
1connectionしか使わないので1
結果
32GiB/4 = 8GiB(=8388608KB)
壊れても影響が少ないDBのため大胆な設定をしてます