PgBouncer: help postgres manage many connections

The Problem

After migration from Mysql to Postgres, my database server started to go to swap. It has 64GB of memory, while my Mysql server used to have only 32GB and it didn’t face that problems.

I have 4 rails application servers(hosted by puma). Each server needs about 1000 connections to Postgres: I have 24 worker processes with 32..48 threads(for more details see the article on heroku). So I need about 4000 connections from webservers & some hundred connections from Sidekiq.

I configured my Postgres server to have 5000 max_connections. Every connection needs work_mem memory, so I set it to 1MB(I don’t have any large joins).

But my server was constantly swapping! When it was happening, I just restarted my puma proceeses & everything was becoming fine. It was obvious: my application was leaking DB connections.

The solution

At first I tried to set the reaping_frequency config variable to 60 seconds: it tells Rails to look for  connections from dead theads & try to recover them. It didn’t help me much :(

I googled and found info about pgbouncer. To be short: it proxies connections to Postgres managing them very effectively. To compare: Postgres needs a couple of MG to handle 1 connection. PgBouncer needs only about 2KB to handle the same connection. Here are the useful links:

What I did:

  1. Installed pgbouncer (accepts connections on the same host by listens on port 6432)
  2. Set PgBouncer config:
    1. max_connections to 5000(you set to number of connections you need)
    2. pool_mode = transaction
    3. default_pool_size = 500(max connections to Postgres)
  3. Set Postgres config:
    1. max_connections = 500(max connections from pgbouncer)
    2. work_mem = 4MB
  4. Allowed to connect to PbBouncer only from my webservers with iptables
  5. Switched my database.yml to connect to PgBouncer –  you need to specify port and disable prepared_statements:

Done!

Results

Before using PgBouncer I used to have about 3-4k active connections to Postgres from webservers, most of them were idle. They were using my RAM and my server was using about 50-60GB RAM and always wanted to go to swap.

Now: I have about 150-200 connections to Postgres from PgBouncer. My server users ONLY 3GB RAM. I don’t know what to do with the other RAM I have in rest :) Anyone needed? :)

Take the picture from Newrelic:

RAM consumtion after adding PgBouncer
RAM consumtion after adding PgBouncer

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">