メモリ1GBのVPSで、PostgreSQLを1台動かしている。ブログ、カレンダー、地図、日記、ゲームなど7つのサービスが、それぞれ別のデータベースを持って同居している。フロントはCloudflare Workersで、Hyperdriveを経由して繋ぐ。

ある日、1つのサービスに負荷試験をかけたら、負荷が止んで15分経っても接続が43本残っていた。max_connections は50だ。他の6サービスは、残り7本を分け合う状態に置かれていた。

Hyperdriveはバーストで広げた接続を返さない

発端は、1つのサービスに本番でかけた負荷試験で接続が枯渇したことだ。そのときのログはこうだった。

FATAL: sorry, too many clients already
FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute

枯渇そのものと、対処としてPgBouncerを入れるまでの経緯は別に書いた。この記事はその後日談で、負荷試験が終わったあとに気づいたことを書く。

負荷が止んでも接続が減らなかった。 15分後に pg_stat_activity を見たら、そのサービスのロールが43接続を保持したままだった。全部 idle。

Hyperdriveは負荷に応じてプールを広げるが、縮めるのは遅い、あるいは縮めない。そしてPostgreSQL側の idle_session_timeout は既定で0、つまり無効。アイドルの接続を切る仕組みが、どちらの側にも無かった。

手で pg_terminate_backend を42本分流して解放した。

idle_session_timeout を10分に

対処はこれだけだ。

ALTER SYSTEM SET idle_session_timeout = '10min';
SELECT pg_reload_conf();   -- 再起動は不要

ALTER SYSTEM は postgresql.auto.conf に書き込む。postgresql.conf を手で編集せずに済み、SQLだけで完結する。戻すときは ALTER SYSTEM RESET idle_session_timeout してreloadする。

このパラメータはidleのセッションだけを切る。クエリ実行中(active)や、トランザクションを開いたまま止まっている(idle in transaction)ものは対象外だ。後者を切りたければ idle_in_transaction_session_timeout が別にある。

切られた側は、次に使うときに再接続するだけでいい。Hyperdriveでもpsqlでも実害は無く、最初の1回に接続の手間が乗るだけだ。

入れる前に、短い値で効くことを確かめた。 一時的に 20s にして、stdinを開いたまま待たせたpsqlのセッションが28秒後に消えることを確認してから、10分に戻した。設定を入れて「効いているはず」で終わらせると、次に枯渇したときに「入れたはずなのに」から調査が始まる。

max_connections = 50 は少なくない

「そもそも50が少ないのでは。既定の100に上げれば」と思うかもしれない。1GBのVPSでは、上げるほうが危ない。

項目値
搭載メモリ958 MB(定常448 MB使用)
shared_buffers256 MB
work_mem8 MB(接続ごと・ソートごと)
max_connections50

work_mem は接続ごと、しかもクエリ内のソートやハッシュごとに確保される。50接続が同時に重いクエリを走らせると、work_memだけで400 MB。shared_buffersと合わせて656 MBになる。

これを100に上げると、work_memだけで800 MB。OOMでPostgreSQLごと落ちる。

接続を断られるのと、DBが落ちるのとでは、被害の桁が違う。前者は1リクエストが500になるだけだが、後者は7サービス全部が止まる。50は安全側の妥当な設定で、同時性が足りないなら上限を上げるのではなく、DB側にプーラーを置くのが筋だ。実際、PgBouncerを入れてからは実接続9本で80並列を全件捌けている。

コメントは「DB側で掛けてある」と言っていた

接続の話が片づいたので、ついでにクエリ側の上限も見た。

各サービスのDB接続ヘルパーには、こう書いてあった。

// クエリ自体の上限は DB 側(ALTER ROLE ... SET statement_timeout)で掛ける

なるほど、と思って確認した。

SELECT rolname, rolconfig FROM pg_roles WHERE rolname LIKE '%_app';

全部空だった。 8つのアプリ用ロール、1つも設定されていない。コメントは「掛けてある」と主張しているが、掛けた形跡が無い。おそらく「掛けるつもりで書いた」コメントが、掛けないまま残ったのだと思う。

コメントは実態を保証しない。 DB側の設定は、コードを読んでも分からない。pg_roles を引く1行で確かめられるのだから、確かめるべきだった。

しきい値はログから決めた

statement_timeout を何秒にするか。勘で決めずに、ログを見た。

PostgreSQLは log_min_duration_statement で、一定時間を超えたクエリを自動記録する。うちは0.5秒に設定してある。ログを遡ると、アプリのロールが0.5秒を超えた記録は1件も無かった。

遅いクエリは2つだけあって、どちらも postgres(superuser)のものだった。

記録正体
45,014 msidle_session_timeout を検証したときの select pg_sleep(45)
2,300 ms前後夜間バックアップの COPY ... TO stdout(pg_dump)

superuserにはロール別の設定が効かないので、バックアップは巻き込まれない。アプリのクエリは0.5秒すら超えていない。3秒にした。実測の最長より一桁上で、それでも暴走したクエリは3秒で止まる。

ALTER ROLE blog_app SET statement_timeout = '3s';
-- 8 つの *_app ロール全部に

これも入れたあとに確かめた。

  • select pg_sleep(5) が canceling statement due to statement timeout で切られる
  • 通常のクエリは通る
  • 全7サービスの /api/health/ が200のまま

なぜ「上限を掛ける」にこだわるか

以前、別のサービスで非効率なクエリにクローラーが張り付いて、従量課金のDBに$240の請求が来たことがある。そのとき決めた鉄則の一つが「一覧・集計クエリはインデックスを確認してから本番に入れる。1GBのVPSを守るため」だった。

従量課金のDBはやめて固定費のVPSにしたので、課金の心配は消えた。でも共有しているDBが暴走クエリで落ちれば、7サービスが道連れになる。その意味で、上限が無いことは鉄則の趣旨に反していた。コメントだけあって設定が無い状態は、無いよりも悪い。「あると思っていた」から。

まとめ

  • Hyperdriveはバーストで広げた接続を返さない。 idle_session_timeout が0(既定)だと、アイドル接続が居座り続ける
  • ALTER SYSTEM SET idle_session_timeout = '10min' + pg_reload_conf()。再起動不要。idleだけを切り、実行中とトランザクション中は切らない
  • 1GBでは max_connections を上げるほうが危ない。 work_memは接続ごと・ソートごとに積まれる。50で656 MB、100でOOM。同時性はプーラーで稼ぐ
  • コードのコメントはDB側の設定を保証しない。 pg_roles の rolconfig を引いて確かめる。うちは8ロール全部空だった
  • statement_timeout はログから決める。log_min_duration_statement の記録で「アプリの最長」を測り、その一桁上に置く
  • superuserにはロール別設定が効かない。バックアップ(pg_dump)は巻き込まれない
  • 入れたら短い値で効くことを確かめてから本番の値にする。「入れたはず」を残さない

「掛けてある」と書いてあるものは、掛かっているかどうかとは別の話だ。設定は設定を見て確かめる。