How to setup pgbouncer with rails on heroku?

Question

Heroku recently decreased number of available connections to production database (from 500 to 60). Opened connections were consuming a lot of memory and causing problems, so it seems like a step in right direction.

My app has more than 100 concurrent processes which all access database at same time. Heroku suggests using https://github.com/gregburek/heroku-buildpack-pgbouncer to fix this issue.

I wasn't able to find any proper guide on how to do this. I was able to install and enable buildpack, but I have no ide what these configuration variables do and how do they work. With default configuration, i get tons of ActiveRecord::ConnectionTimeoutError errors.

Does anyone has experience with this and if can please provide provide step-by-step guide on how to do this properly and how to configure everything that needs to be configured?

Answer 1

What version of Rails are you running on? I just deployed pgbouncer to our production webapp using these steps (for a Rails 3.2 app running on Ruby 2.0)

1. Create .buildpacks file in root directory app that contains the text https://github.com/gregburek/heroku-buildpack-pgbouncer.git#v0.2.2 https://github.com/heroku/heroku-buildpack-ruby.git
2. I created a file called disable_prepared_statements_monkey_patch.rb in config/initializers that contained the code posted by cwninja in this thread: https://github.com/gregburek/heroku-buildpack-pgbouncer/pull/7
3. Modified my Procfile to add bin/start-pgbouncer-stunnel before bundle exec unicorn -p $PORT -c ./config/unicorn.rb
4. executed heroku config:set PGBOUNCER_PREPARED_STATEMENTS=false --app yourapp heroku config:add BUILDPACK_URL=https://github.com/ddollar/heroku-buildpack-multi.git --app yourapp
5. deployed the new .buildpacks and Procfile changes to production

It worked as advertised after that point. The first time I deployed I neglected to disable prepared statements, which caused my application to blow up with lots of 'duplicate prepared statement errors'. If you're using Rails 4.1 or above the monkey patch is apparently not necessary, however I guess there's a bug in Rails 3.2 that doesn't parse pgbouncer's change to the database URL in such a way that prepared statements are actually disabled.