Re: How to keep queries low latency as concurrency increases

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Oct 30, 2012 at 3:16 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
> On Tue, Oct 30, 2012 at 4:11 PM, Greg Williamson
> <gwilliamson39@xxxxxxxxx> wrote:
>> Jeff / Catalin --
>>
>> Jeff Janes wrote:
>>
>>>On Mon, Oct 29, 2012 at 5:11 PM, Catalin Iacob <iacobcatalin@xxxxxxxxx> 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.
>>>
>>>Cheers,
>>>
>>
>>
>> I'm sure pgbouncer has some threshold where it breaks down, but we have servers (postgres 8.4 and 9.1) with connections from runtime (fed via haproxy) to pgbouncer that routinely have tens of thousands of connections in but only 40-70 postgres connections to the postgres cluster itself. Mix of queries but most are simple. Typically a few thousand queries a second to the readonly boxes, about the same to a beefier read / write master.
>>
>> This is a slightly old pgbouncer at that ... used is a fairly basic mode.
>
> I've used pgbouncer in two different environments now with thousands
> of connections and hundreds upon hundreds of queries per second and it
> has yet to be a bottleneck in either place as well.

The original poster has over 9000 queries per second in his best case,
so I think that that is at the upper range of your experience.  Using
"pgbench -S" type workload, pgbouncer is definitely a bottleneck (1.7
fold slower at -c4 -j4 on a 4 CPU machine, and using -f with a dummy
statement of "select 1;" it is 3 fold slower than going directly to
the server.  As -c increases, pgbouncer actually falls off faster than
direct connections do up through at least -c20 -j20).

Of course with your thousands of connections, direct connections are
probably not feasible (and with that many connections, most of them
are probably idle most of the time, pgbouncer's strength)

Anyway, opening and closing connections to pgbouncer is far less
costly than opening them directly to psql, but still very expensive
compared to not doing so.  The original poster should see if he can
avoid that.

Cheers,

Jeff


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux