PostgreSQLのDBが遅くなった

環境

• PostgreSQL 13.3
• kubernetes上で動作している
• ストレージは iSCSI

現状確認

スロークエリログ

PostgreSQLはデフォルトではスロークエリログを出力しないので、出力するように設定する。
ただ、スロークエリログにはSQLクエリとその時使用されたパラメタまで出力されるので、機密情報が出力される可能性もある。

以下のSQLを実行してPostgreSQLを再起動すれば反映される。

alter system set log_min_duration_statement = '500';

出力例

2021-08-11 10:36:48.598 UTC [19772] LOG:  duration: 1522.809 ms  execute <unnamed>: SELECT 1 AS one FROM "tablenames" WHERE "uri" = $1 LIMIT $2             
2021-08-11 10:36:48.598 UTC [19772] DETAIL:  parameters: $1 = 'some parameter', $2 = '1'

メモリ関係の設定パラメタの確認

メモリ関連の設定値がPGTune（または自分でチューンした値）になっているか確認。
バージョンアップ後（dockerとか使ってると特に）に初期値に戻っていることが多いので要注意。
※ dockerを使っている場合、メジャーバージョンアップは pg_dump -> importするのが手っ取り早い場合が多いため。（1敗）

SHOW max_connections;
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW maintenance_work_mem;
SHOW checkpoint_completion_target;
SHOW wal_buffers;
SHOW default_statistics_target;
SHOW random_page_cost;
SHOW effective_io_concurrency;
SHOW work_mem;
SHOW min_wal_size;
SHOW max_wal_size;

結局

アプリケーション（Mastodon）の性質から、万が一のデータ損失は許されると判断して、同期コミット（コミットがディスクに書き終わったら完了を返す）をOFFにした。ようするにコミットしたという事実がディスク書き込まれる前にコミットが完了したことになるので見かけ上のパフォーマンスがよくなる。
デメリットは、コミット直後にDBが落ちるとそのコミットが失われる可能性があるということらしい。データが破損する。とかではなくてトランザクションがまるごと無効になるだけなのでまぁOKかなと。

alter system set synchronous_commit = 'off';

振り返り

• スロークエリのログは普段から出していないと、ログが出てもそれがいつも出ているのか、それとも遅いと感じたタイミングから出始めたのかがわからない。

蛇足

• やはりスローログが出ているのはあまり気持ちの良いものではないので、RAIDカードを導入してI/Oを強化したところ、スローログの出力はピタリとやんで、快適な環境に戻った。