4

I'm running 7 sidekiq processes (currency set to 40) plus a passenger webserver, connecting to a postgres database. Rails pool setting is set to 100 and and postgres max_connections setting is also the default 100.

I just added a new job class where each job makes multiple postgres requests, and I started getting this error on many sidekiq jobs and sometimes on my webserver: PG::ConnectionBad: FATAL: remaining connection slots are reserved for non-replication superuser connections

I tried increasing postgres max_connections to 200, and the error still occurs. Then I tried reducing the activerecord pool setting to 25 (25 connections for each process = 200 total connections), figuring I might start getting DB connection timeout errors but at least it would stop the "no remaining connection slots" errors.

But I'm still getting the remaining connection slots are reserved error.

The smarter way to to deal with this issue might be to load the important postgres data that I keep reusing into redis, and then access it from redis - which obivously plays much more nicely and quickly with sidekiq. But even as I do that, I'd like to understand what's going on here with the postgres connections:

  • Am I likely leaking connections, and is that something I should be managing inside the sidekiq jobs?

(see Releasing ActiveRecord connection before the end of a Sidekiq job)

  • Should I look into more obscure things like locking/contention issues or threading issues with the PG driver?

(see https://github.com/mperham/sidekiq/issues/594. I think I'm using ActiveRecord pretty simply without much obscure or abnormal logic for a rails app...)

  • Or maybe I'm just not understanding how the ActiveRecord pool setting and postgres max_connection settings work together...?
Improve this question

1 Answer 1

Reset to default
6

My situation may be too specific to help many others running into this error, but I'll share what I've found out in case it helps to point you in the right direction.

Am I likely leaking connections, and is that something I should be managing inside the sidekiq jobs?

No, not likely. Sidekiq's default middleware includes a hook to close connections even if a job fails. It took me a long time to understand what the heck that means, so if you're not sure what that means, tl;dr: Sidekiq won't leak connections if you're using it normally.

Should I look into more obscure things like locking/contention issues or threading issues with the PG driver?

Unless you're using a very obscure setup, its probably something more simple.

Or maybe I'm just not understanding how the ActiveRecord pool setting and postgres max_connection settings work together...?

Anyone can feel free to correct me if I'm wrong, but here's the guidelines I'm going on for pool settings, max_connections, and sidekiq processes:

Minimum DB pool size = sidekiq concurrency setting

Maximum DB pool size* = postgres max_connections / total sidekiq processes (+ leave a few connections for web processes)

*note that active record will only create a new connection when a new thread needs one, so if 95% of your threads don't use postgres at the same time, you should be able to get away with far fewer max_connections than if every thread is trying to check out a connection at the same time.

What fixed my problem:

On my Ubuntu machine, I had changed the vm.overcommit_memory setting to 1 as recommended by redis, so that it can spawn it's write to disk process without breaking the machine.

This is the right way to go, but leaves postgres vulnerable to being killed by OOM (out of memory) Killer if memory usage gets too high. Turns out that postgres will stop allowing new connections if it receives a kill signal from the OOM Killer.

Once I restarted postgres, sidekiq was able to connect again. The longer term solution is simply to work on memory leaks and make sure memory usage doesn't get too high. Also it's possible to configure the OOM killer to prioritize killing my sidekiqs before killing postgres.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.