Postgresql – What configuration to use for PGbouncer over a WAN link

pgbouncerpostgresql

We have two datacenters, one in America and one in Europe. For various reasons, the Europe datacenter has the database server, while the workers and web apps are in America.

PGbouncer is in the American datacenter, in transaction pooling mode, helping reduce connection latency to the european datacenter. Unfortunately, I often have transactions in the "<IDLE> in transaction" state, which are holding locks and preventing the rest of the machines from making useful progress.

America
-------

worker      webapp
   |           |
    \         /
     \       /
      +--+--+
         |
     pgbouncer
         |
         :

      public
      Internet

         :
         |
      PostgreSQL

------
Europe

What pgbouncer parameters should be set/reset/changed to prevent those idle in transactions?

Best Answer

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.

Related Question