Re: Monitoring number of backends

On 10/22/2013 2:18 PM, John R Pierce wrote:
> On 10/22/2013 11:25 AM, andy wrote:
>> Hum.. I had not thought of that. My current setup uses 40 max
>> connections, and I don't think I've ever hit it. I use apache and
>> php, and my db connections are not persistent.
>
> that style of php programming, you're getting some HUGE overhead in
> connect/disconnect per web page. putting pg_bouncer in the middle
> will make a HUGE improvement, possibly a second per page load on a busy
> server.
>
>

No, actually, I don't think my connect overhead is huge. My apache and
postgres are on the same box, and it connects using unix socket.
Perhaps if my apache on db were on different boxes it would be a problem.

My page response time is sub-second, and I run quite a few queries to
build the page. But also, my server isn't to busy at the moment. The
load is around 0.3 to 0.5 when its busy.

Stephen Said:
>> If I did plugin pg_bouncer, is it worth switching my php from
>> pg_connect to pg_pconnect?
>
> No, let pg_bouncer manage the connection pooling. Having two levels of
> pooling isn't a good idea (and pg_bouncer does a *much* better job of it
> anyway, imv..).
>

So you say DO use persistent connections, and Stephen says DONT use
them. Although there are a few new players. Assuming Apache, pgbouncer
and postgres are all on the same box, and I'm using unix sockets as much
as possible, it probably doesn't matter if I use non-persistent
connections from php.

But if I need to move the db to its own box... then should I move
pgbouncer there too?

Assuming db is on a different box, persistent connections from php to
postgres might be bad. But how about persistent connections to pgbouncer?

Thinking about it, if I need to move the db, I'll leave pgbouncer on the
webserv box. That way I can unix socket from php to pgbouncer
(non-persistent, cuz its fast enough anyway), and let pgbouncer do it's
own thing to the database box. Seem like a reasonable sort of thing?

-Andy