Re: How to keep queries low latency as concurrency increases

On Sat, Nov 3, 2012 at 6:53 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Wed, Oct 31, 2012 at 11:39 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> On Tue, Oct 30, 2012 at 4:58 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> On Mon, Oct 29, 2012 at 5:11 PM, Catalin Iacob <iacobcatalin(at)gmail(dot)com> wrote:
>>>
>>>> pgbouncer 1.4.2 installed from Ubuntu's packages on the same machine
>>>> as Postgres. Django connects via TCP/IP to pgbouncer (it does one
>>>> connection and one transaction per request) and pgbouncer keeps
>>>> connections open to Postgres via Unix socket.
>>>
>>> Isn't pgbouncer single-threaded?
>>>
>>> If you hitting it with tiny queries as fast as possible from 20
>>> connections, I would think that it would become the bottleneck.
>>
>> Single threaded asynchronous servers are known to scale better for
>> this type of workload than multi-threaded systems because you don't
>> have to do locking and context switching.
>
> How much locking would there be in what pgbouncer does?
>
> 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.
>
>
>> pgbouncer is an absolute marvel and should be standard kit in any case
>> you're concerned about server scaling in terms of number of active
>> connections to the database. I'm in the camp that application side
>> connection pools are junk and should be avoided when possible.
>
> I have nothing against pgbouncer, but it is not without consequences.

agreed -- also, I was curious and independently verified you results.
pgbouncer doesn't lock -- if you strace it, it just goes epoll_wait,
recv_from, send_to endlessly while under heavy load from pgbench.
This suggests that the bottleneck *is* pgbouncer, at least in some
cases. It's hard to believe all the userland copying is causing that,
but I guess that must be the case.

merlin