Re: Monitoring number of backends

On Tue, Oct 22, 2013 at 1:13 PM, andy <andy(at)squeakycode(dot)net> wrote:

> 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.
>

I have not noticed a large difference between loopback and unix socket on
any modern computer. The difference between a loopback connection and a
connection between two machines in the same data center is more noticeable
when benchmarked in highly optimized code, but I'm skeptical about how
meaningful it would be in php. You can always use ab or ab2 and try it out
for yourself.

> 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.
>

Wasn't your question to figure out how to make sure things continue to run
fine when the demand increases to a higher level than it currently is? If
you cite its current OK performance to reject the advice, I'm not really
sure what we are going to accomplish.

> 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.

They both say to use persistent connections--the ones between pg_bouncer
and postgres itself. But for two different reasons, one to reduce the
number of connections you make and break to postgresql, the other to reduce
the number of active connections at any one time. Both are valid reasons.

> 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?
>

That depends on where the bottleneck is.

Cheers,

Jeff