PostgreSQLの「FATAL: too many connections」で真夜中に叩き起こされた話

はじめに

深夜1時にPagerDutyが鳴って、APIサーバーのエラーログを見たら同じ文字列がずらーっと並んでたんですよね。

FATAL: sorry, too many clients already

アプリ側からは接続できず、ヘルスチェックも全部落ちてる状態。ユーザー影響も出てたので、正直かなり焦りました。原因を調べる前にとりあえずできることをやろう、と動いたんですが、それが結局あまり良くない対処だったなと後から思います。

最初にやった間違った対処

とにかく繋がらないなら再起動すればいいだろうと思って、PostgreSQLを再起動しました。

sudo systemctl restart postgresql

これで一時的にはエラーが消えるんですよね。でも15分後には同じエラーが再発しました。当たり前なんですが、原因のコネクションを吐き続けているプロセスが生きてる限り、再起動は本当に一時しのぎにしかならないんです。
さらに焦って max_connections を倍にするという対処もやってしまいました。

# postgresql.conf
max_connections = 200

これも再起動が必要な上に、根本原因を放置したまま「箱」を大きくしただけなので、結局また埋まるのは時間の問題でした。実際、数時間後に同じ現象が起きて、今度はメモリ不足の警告まで出てくる始末でした。

正しい原因調査と解決策

ステップ1: 今何が繋がっているか確認する

まず落ち着いて、現在の接続状況をpg_stat_activityで確認しました。

SELECT pid, usename, application_name, client_addr, state, query, backend_start
FROM pg_stat_activity
ORDER BY backend_start;

これで見えたのは、同じapplication_nameから出ている接続が数百件、しかもstateがidleのままずっと残っているという状況でした。

ステップ2: 状態別に集計して傾向をつかむ

一件ずつ見るのは大変なので、状態ごとに集計しました。

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY count(*) DESC;

     state      | count
-----------------+-------
 idle            |   187
 active          |     8
 idle in transaction |  12

idleが187件というのが異常です。通常のアプリケーションであれば、コネクションプールが使い終わった接続をちゃんと返却しているはずなので、ここまで溜まるのは何かがリークしているサインでした。

ステップ3: アプリ側のコネクションプール設定を疑う

アプリのコードを見ていくと、ORMのコネクションプール設定が原因でした。デプロイ時にプロセス数を増やしたのに、1プロセスあたりのプール上限を見直していなかったんですよね。

# 旧設定（プロセス数×プール数で想定以上に膨張していた）
database:
  pool_size: 20
  max_overflow: 10

プロセス数が4から12に増えていたので、単純計算でも最大360コネクションを要求できる状態になっていました。max_connectionsが100前後だったサーバーなので、そもそも設計上無理があったわけです。

ステップ4: 緊急措置として不要な接続を切る

根本対応の前に、まずは溜まっているidle接続を安全に切って息をつかせました。

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > interval '10 minutes';

これで一気に空きコネクションが確保できて、サービスは復旧しました。

ステップ5: 再発防止の本対応

その後、以下の3点を対応しました。
まず、アプリ側のプール設定をプロセス数に応じて計算し直しました。

database:
  pool_size: 5
  max_overflow: 2

次に、コネクションプーラーとしてPgBouncerを導入し、アプリとDBの間でコネクションを一元管理するようにしました。

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 500
default_pool_size = 20

これで、アプリ側が何プロセスに増えても、PostgreSQLへの実接続数はPgBouncerのdefault_pool_sizeで制御できるようになりました。
最後に、idle in transactionが一定時間続いたら自動で切断するように設定しておきました。

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

おわりに

今回の件でわかったのは、too many connectionsは結果であって原因じゃないということなんですよね。max_connectionsを増やすのも再起動するのも対症療法で、根本にはアプリ側のプール設計とDB側の受け皿設計がズレているという問題がありました。
同じような状況になったら、まずはpg_stat_activityで「誰が」「どういう状態で」溜まっているのかを確認するのが先だと思います。そこを見ずに設定値をいじると、今回の自分のように余計に状況を悪化させることもあるので気をつけてください。
PgBouncerのようなコネクションプーラーを間に挟んでおくと、アプリのスケールとDBの接続上限を分離して考えられるので、同じような構成の方には特におすすめです。