Network.Socket.accept: resource exhausted (Too many open files)

[steve-chavez]

(PostgREST v8.0.0.20211102 behind Kong/Nginx)

This happens when there's a good amount of traffic going into PostgREST, then it stops processing any new requests until it's restarted manually.

Nov 23 01:36:27 postgrest[11475]: 23/Nov/2021:01:36:27 +0000: Attempting to connect to the database...
Nov 23 01:36:27 postgrest[11475]: 23/Nov/2021:01:36:27 +0000: Connection successful
Nov 23 01:36:27 postgrest[11475]: 23/Nov/2021:01:36:27 +0000: Config re-loaded
Nov 23 01:36:27 postgrest[11475]: 23/Nov/2021:01:36:27 +0000: Schema cache loaded
Nov 23 03:54:41 postgrest[11475]: Network.Socket.accept: resource exhausted (Too many open files)
Nov 23 03:55:17 postgrest[11475]: 127.0.0.1 - - [23/Nov/2021:03:55:16 +0000] "POST /<redacted> HTTP/1.1" 503
Nov 23 04:13:51 systemd[1]: Stopped PostgREST.
Nov 23 04:13:51 systemd[1]: Started PostgREST.
Nov 23 04:13:51 postgrest[15073]: 23/Nov/2021:04:13:51 +0000: Attempting to connect to the database...
Nov 23 04:13:51 postgrest[15073]: 23/Nov/2021:04:13:51 +0000: Connection successful
Nov 23 04:13:51 postgrest[15073]: 23/Nov/2021:04:13:51 +0000: Listening on port 3000
Nov 23 04:13:51 postgrest[15073]: 23/Nov/2021:04:13:51 +0000: Config re-loaded
Nov 23 04:13:51 postgrest[15073]: 23/Nov/2021:04:13:51 +0000: Listening for notifications on the pgrst channel

Ideally PostgREST should recover or at least die and let systemd restart it.

This is a fix that needs to be done upstream: yesodweb/wai#830.

A patch is already up for review: yesodweb/wai#831

Also, a way to reproduce is mentioned on: yesodweb/wai#825.

---

Not yet confirmed, but increasing the db-pool size should help since PostgREST wil be able to process more requests without running out of sockets first.

[steve-chavez]

Seems related to #1399 (comment) and #905.

[steve-chavez]

A way to reproduce this was found on #2092 (comment), basically using pg_sleep on a function. That makes sense since the db-pool connection slots are busy and the db can't handle incoming traffic however postgrest keeps accepting new requests and then it runs out of sockets.

[steve-chavez]

Seems yesodweb/wai#831 would help with EMFILE in that postgrest won't become blocked and will start responding if sockets become freed. However, if the db is still busy and can't handle incoming traffic, EMFILE will be reached again and thus, postgrest will stop responding again.

I think it would be better if we apply backpressure whenever EMFILE happens so we give a chance for db connections to become available. So we would start rejecting any new request with 429 Too Many Requests, this would also serve as a hint to scale or tune the db.

[steve-chavez]

whenever EMFILE happens

I guess we could try catching that exception somehow but I'm not sure how we would stop responding with 429. Can we count the number of used sockets?

[steve-chavez]

Can we count the number of used sockets?

Another option could be to try and fix this at the pool level. With that we could even stop hitting EMFILE by somehow failing when too many requests are waiting for a pool resource.

Hasql.Pool is a small wrapper on Data.Pool, which seems to be lacking an introspection feature(bos/pool#36) that could help us. It also looks to be unnmaintained(bos/pool#39).

[wolfgangwalther]

So we would start rejecting any new request with 429 Too Many Requests, this would also serve as a hint to scale or tune the db.

429 is a client error, i.e. rate limiting. A user could hit this with just a single request - it's not their fault. This absolutely needs to be a 5xx error code.

[wolfgangwalther]

Hasql.Pool is a small wrapper on Data.Pool, which seems to be lacking an introspection feature(bos/pool#36) that could help us. It also looks to be unnmaintained(bos/pool#39).

Looks like there is a maintained fork: https://hackage.haskell.org/package/resource-pool-fork-avanov-0.2.4.0

Does that have the introspection feature?

Maybe we should just make Hasql.Pool use that fork instead.

[wolfgangwalther]

Maybe we can also use https://hackage.haskell.org/package/unix-2.7.2.2/docs/System-Posix-Resource.html. We could:

• Use setResourceLimit to increase our process' softlimit to the hardlimit allowed by the kernel. This seems to be what other webservers do, too [citation needed]. This could already avoid hitting EMFILE for most cases.
• Use getResourceLimit and compare that with the number of pending requests in the pool.

[steve-chavez]

429 is a client error, i.e. rate limiting. A user could hit this with just a single request - it's not their fault. This absolutely needs to be a 5xx error code.

True, agree. I think having a different status than 503 for this would be better. Relevant options:

• 504 Gateway Timeout: more related to proxies and doesn't seem to convey a temporary state
• 529 Site is overloaded: more precise but non-standard, only used by some companies

I'm leaning towards 529 for now.

[wolfgangwalther]

I think having a different status than 503 for this would be better.

I don't see why, yet. 503 is perfect. For the client, there is no difference. The server admin needs to take a look into the logs anyway, to figure out what's happening.