Postgresql – pgBouncer does not use all available CPUs

I'm not really sure this is a problem in itself, because, as you can see, a single SSD drive can outperform an 8 disk RAID 10 setup in many tests.

Almost all the tests point to a better speed of the single SSD drive:

• better latencies
• lower CPU usage (if I'm reading correctly in some case it's 44% vs 95%)
• no of transactions per second is bigger with 55%
• no of transactions in total is bigger with the same 55%

In a single case that SSD was outperformed, and that was sequential writes. Which, I'd say, is most usual for batches, not for an OLTP style of load. So if you're having mostly these kind of writes, maybe a single SSD is not a solution for you now.

And we're not talking about Fusion-IO drives (which I suspect might bring you that next level you'll expect, but at a next level price).

From the point of view of the DBA that had to work with crappy storage over the years, this is a fair advancement in technology and they seem to work properly, but maybe I have set my expectations too low.

I would expect to see more improvements from your SSD in testing the benchmark with more threads and with higher concurrency, as this is where the SSDs shine. So if you could repeat your tests with way more clients and more threads, I'd be curious about that comparison result.

Well, applications staying '<IDLE> in transaction' is really an application-level problem, not something pgBouncer can do much about.

First, you should figure out how long your applications are remaining '<IDLE> in transaction' for, and go after the egregious offenders. Since your clients are far away on a WAN link, it is perfectly normal for clients to be '<IDLE> in transaction' for, say, a few hundred milliseconds between short queries (i.e. network latency plus some processing time on the client side between subsequent queries). Check CURRENT_TIMESTAMP - query_start in pg_stat_activity, and sort by the largest differences to find the real offenders. A minute or two should cause some concern, hours-long you should get rid of immediately.

If you have a large codebase and many clients, it can be tricky to nail down exactly where in your code such '<IDLE> in transaction' connections are originating (and may even be a framework problem instead of from your own codebase -- Django had a bad history of causing mysterious '<IDLE> in transaction' connections). I recommend setting a different application_name for separate applications within your codebase, to better help you track where these connections are coming from.

Finally, there is a pending patch to allow Postgres to kill off '<IDLE> in transaction' connections you might be interested in: http://www.postgresql.org/message-id/538DC843.2070608@dalibo.com

If we're lucky, we might have that idle_in_transaction_timeout (or whatever name it ends up with) in Postgres 9.5.