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

 

Rails middleware is slow: what to do?

If you see in Newrelic that Middlware is slow, try to increase ActiveRecord Connection pool: probably(especially if you use a threaded webserver, for example puma) it blocks waiting for DB connection to be free.

I don’t promise it’s a reason. It might be reason. It was a reason in my case.

Just edit config/database.yml and try to set pool variable:

To understand the value for pool size, see the heroku article.

could not create semaphores: No space left on device

Hi guys,

If you ever meet this message while running Postgres on Ubuntu:

Know: you need to increase semaphore limits. I met this problem while trying to run Postgres with max_connections > 2000.

What to do(Awerywhere use root):

add this line:

then reboot or:

to apply changes.

Run migration code in Rails console

If you guys ever need to run migration code from Rails console, do it directly:

Hope it helps

When your Mysql is slow: what to do?

Step 0: The problem

Hi there!

I use Mysql db for my like4u project. It handles about 1000 rec/s(according to NewRelic). And I felt that mysql db was very slow: it used to take about 2-5 seconds for the most popular requests. As that requests are called about 300 times per second, my site was really slow.

Need to say that the indexes were built correctly. I had used EXPLAIN many times, built some indexes. And it didn’t help.

At the moment when I realized that I needed to do something with my mysql server, I was using Mysql 5.1(yes, that old) with Debian 6.0(old either).

Okay, I decided to speed up my DB server. Let’s begin! But at first…

Step 1: Application optimization

What requests were slow? My site has an API to give tasks to bots. And one of the API methods returns info about what community the bot should enter. When there are not tasks to enter, we should return nothing.

How it used to work:

The query returned usually about 300 rows. I inspected that the query took for about 5-20 seconds to finish! With all the indexes used! That was awful.

Then I used to load task limits with that query:

And task_limit id was taken from tasks table. That query took much time either!

Then I filtered the tasks rows with Ruby and, if any suited, returned them to user.

What was slow?

I tried to run the same query for tasks but to select only id field:

And it worked mush faster! Selection of only 1 field is much faster than selecting many(about 20) fields!

Then I thought: if every tasks needs to have 0 or 1 task limit, I need to use join. I tried the query and it was really faster to join 2 tables rather than make 2 queries!(1 to select tasks and 1 to select task_limits)

How I optimized my application

First of all, I realized that I didn’t need all the columns from tasks table. I decided to take only what I needed and to use JOIN to select task_limits in the same query:

That request took much less time than my previous solutions.

Then I filtered all that rows with Ruby. And, in case any task was found, I loaded all the columns from task with only 1 id by primary_key:

And returned them to user.

Conclustion #1

Optimize application:

  • select only columns you need
  • use joins instead of 2 queries if possible
  • test all the queries in mysql console(take query examples from SHOW FULL PROCESSLIST)

Mysql config optimization

At first, I highly recommend to run tuning-primer.sh . All the advises were taken from it and helped me much. I recommend you to run the script & follow all the advises.

Increase query_cache_size

Mysql caches the same requests. If you have many requests with the same parameters it makes sense to use the cache. After I increased query_cache_size, my requests started to perform faster. It depends on your project, how much RAM you have and so on. I use the value of 328M.

set innodb_flush_log_at_trx_commit to 2

You should read the documentation about this parameter. At my slow server it was set to 1: it used to write to the hdd every commit. After I had set it to 2 my server became faster.

Update Mysql server

I used to run mysql 5.1. I updated mysql to 5.5 and it became faster.

Meteor custom registration form with username

I’m playing with meteor now. And I wanted to create custom registration/login form for users. There is a great article about it:

http://meteortips.com/tutorial/custom-registration-form/

But I wanted my users to be able to register with username instead of email. What steps should I take to do it?

1) Change input field types:

And login form:

Note I use Jade for my views.

2) Change event handlers:

And login handlers:

That’s it! Now it works to register and login with any username. Note I user CoffeScript to write all my JS code.

When to use lock(true) in Rails with transactions

The problem
Recently I faced a problem with one of my Rails apps: users could activate one coupon more than once. My code looked this way:

Controller:

And the model Coupon:

At the first glance it looks fine:

  1. whe find not activated coupon
  2. in transaction we add money to the activator
  3. in transaction whe mark coupon as activated

Everything should work fine and will work correctly in non-concurrent environment. But will it work if we get 2 concurrent requests for activation?

Let’s see the sequence below:

  1. Request 1 finds the coupon. It’s not activated.
  2. Request 2 finds the coupon. It’s not activated either.(request 1 has not done anything yet)
  3. Request 1 runs the transaction: adds money to buyer & marks coupon as activated.
  4. Request 2 runs the transaction: adds money to buyer(!!!) and marks coupon as activated. (because coupon was loaded to RAM at step 2 and it does not know it was marked as activated).

So what to do?

The solution

We must us Row Locks in Rails. We will lock the coupon row until the transactions ends: coupon is marked as activated or transaction is finished. Now coupon search will be wrapped in transaction. I created a separate class Activator that does all the job.

Here is the code:

Controller is very simple:

And the code is extracted from the model to a service object:

Let’s see the sequence:

  1. Request 1 finds the coupon and locks it in DB(MySQL in my case). It’s not activated.
  2. Request 2 tries to find the coupon, but it’s suspended until coupon’s lock is released. Waiting…
  3. Request 1 runs the transaction: adds money to buyer & marks coupon as activated. Then it releases coupon row lock.
  4. Request 2 tries to find the coupon but fails: there is no not activated coupon with that code.

Profit!

Note the bang! methods used in transaction block. As transactions are rolled back only when exception happens, whe need to use finders and savers that raise an exception in case of failure.