Questions about Rails-Postgres Database connections

Question

We have recently been having issues with postgres running out of connection slots, and after a lot of debugging and shrugging of shoulders we have pretty much tracked it down to the fact that we understood Connection pools wrong.

We use Rails, Postgres and Unicorn, and Delayed Job Are we correct to assume that the connection pool is process specific, i.e each process has its own 10 (our connection pool limit) connections to the db in the pool?

And If there are no threads anywhere in the app, are we correct to assume that for the most part each process will use 1 connection, since noone ever needs a second one?

Based on these assumptions we tracked it down to the number of processes

Web server - 4x unicorn
Delayed job 3x server - 30 processes = 90 connections

That's 94 connections, and a couple connections for rails:consoles and a couple of rails runner or rake tasks would explain why we were hitting the limit often right? It has been particularly often this week after I converted a ruby script into a rails runner script.

We are planning to increase the max from 100 -> 200 or 250 to relieve this but is there a trivial way to implement inter process connection pooling in rails?

No, there is no trivial way to implement inter process connection pooling in rails and I don't think you want that. You may be just worried because of the recent events. But think about when you want to scale. Having separate resources for each process makes scaling trivial for you. Also, I think 10 connections for each DelayedJob process is a bit too much. Do you really have so many requests filling up the queue? — Chandranshu
– Chandranshu, Commented Nov 12, 2013 at 8:31
@Chandranshu its 10 in the pool but mathematically they are using only 1 each. I have 30 x 3 processes running — Karthik T
– Karthik T, Commented Nov 12, 2013 at 9:28
@Chandranshu scaling is the problem. If I need to scale up my workers, I need a lot of connections :( — Karthik T
– Karthik T, Commented Nov 12, 2013 at 9:29

Richard Huxton · Accepted Answer · 2013-11-12 09:11:32Z

2

You probably want to take a look at pgbouncer. It's a purpose-built PostgreSQL connection pooler. There are some notes on the wiki too. It's packaged for most linux distros too.

answered Nov 12, 2013 at 9:11

Richard Huxton

23.6k5 gold badges43 silver badges53 bronze badges

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

3 Comments

Karthik T Over a year ago

Hmm looks interesting.. perhaps we would need to look at this if we hit this problem again..

Richard Huxton Over a year ago

You almost certainly don't want 100 concurrent connections at the database end. Since you're on rails and haven't come across pgbouncer I'm guessing you're not running a 64-core box. Quick rule of thumb is no more than double the number of cores available. PostgreSQL will handle 100 concurrent queries just fine, but you probably won't like how long it will take for the last one to finish.

Karthik T Over a year ago

Yeah, I was thinking we should probably optimize, but right now we are on low load and most of them are background workers i.e delay is ok (I realize just now that this means my web processes might get edged out..)

Collectives™ on Stack Overflow

Questions about Rails-Postgres Database connections

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related