Re: How to keep queries low latency as concurrency increases

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.

Cheers,

Jeff