To: pgsql-performance@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: per-review of  PgBouncer / Slony design 
Date: Mon, 07 Apr 2008 15:50:19 -0400
Lines: 41
Message-ID: <ftdttr$8n4$1@ger.gmane.org>
References: <ftdpih$nid$2@ger.gmane.org>
	<20080407114446.43a1cc50@commandprompt.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 2.0.0.12 (X11/20080227)
In-Reply-To: <20080407114446.43a1cc50@commandprompt.com>


> I would stick to proven postgresql technologies such as pgbouncer.

Thanks for the fast recommendation, Joshua.  I'll consider it.

Our application is Slony-replicated web/db project with two slaves.

Does this design seem sensible?

- Run one pgbouncer server on the master, with settings to
   service the master and both slaves.

- We already handle balancing traffic between the slaves separately, so 
that can remain unchanged.

- Use Session Pooling both both the masters and the slaves. In theory, 
the slaves should just be doing transaction-less SELECT statements, so a 
more aggressive setting might be possible, but I believe there might be 
a "leak" in the logic where we create a temporary table on the slave in 
one case.

- Redirect all application connections through pgbouncer

###

 From graphs we keep, we can see that the slaves currently use a max of 
about 64 connections...they are far from maxing out what's possible. So 
I was trying to think through if made sense to bother using the 
pgBouncer layer with them. I through of two potential reasons to still 
use it:
  - In the event of a major traffic spike on the web servers, pgbouncer 
would keep the number of db slots under control.
  - Potentially there's a performance gain in having PgBouncer hold the 
connections open.

Does that analysis seem correct?

For the master's pool size, I thought I would just choose a number 
that's a little larger that the daily max number of DB slots in use.

    Mark