Re: How to keep queries low latency as concurrency increases

On Mon, Nov 5, 2012 at 3:58 PM, Marko Kreen <markokr(at)gmail(dot)com> wrote:
> On Tue, Nov 6, 2012 at 1:31 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>> On Mon, Nov 5, 2012 at 2:58 PM, Marko Kreen <markokr(at)gmail(dot)com> wrote:
>>> On Sun, Nov 4, 2012 at 1:53 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>>> On a 4 CPU machine, if I run pgbench -c10 -j10 with dummy queries
>>>> (like "select 1;" or "set timezone...") against 2 instances of
>>>> pgbouncer, I get nearly twice the throughput as if I use only one
>>>> instance.
>>>>
>>>> A rather odd workload, maybe, but it does seem to be similar to the
>>>> one that started this thread.
>>>
>>> Every-connection-is-busy is pessimal workload for pgbouncer,
>>> as it has nothing useful to contribute to setup, just overhead.
>>
>> It still has something to contribute if connections are made and
>> broken too often (pgbench -C type workload), as seems to be the case
>> here.
>
> I did not notice -C in your message above.

Right, I was assuming he would somehow solve that problem and was
looking ahead to the next one.

I had also tested the -C case, and pgbouncer can be the bottleneck
there as well, but bypassing it will not solve the bottleneck because
it will be even worse with direct connections. Running multiple
instances of pgbouncer can, but only if you can make the application
do some kind of load balancing between them.

I think there are three different uses of pgbouncer.

1) connections made and closed too often, even if there are never very
many at a time (e.g. stateless CGI)
2) hundreds or thousands of connections, with most idle at any given time.
3) hundreds or thousands, all of which want to be active at once but
which need to be forced not to be so the server doesn't fall over due
to contention.

I'm not sure 2 and 3 are really fundamentally different.

Cheers,

Jeff