Re: Performance under contention

Hi Ivan,

We have the same issue on our database machines (which are 2x6
Intel(R) Xeon(R) CPU X5670 @ 2.93GHz with 24 logical cores and 144Gb
of RAM) -- they run RHEL 5. The issue occurs with our normal OLTP
workload, so it's not just pgbench.

We use pgbouncer to limit total connections to 15 (this seemed to be
the 'sweet spot' for us) -- there's definitely a bunch of contention
on ... something... for a workload where you're running a lot of very
fast SELECTs (around 2000-4000/s) from more than 15-16 clients.

I had a chat with Neil C or Gavin S about this at some point, but I
forget the reason for it. I don't think there's anything you can do
for it configuration-wise except use a connection pool.

Regards,
Omar

On Mon, Nov 22, 2010 at 5:54 PM, Jignesh Shah <jkshah(at)gmail(dot)com> wrote:
> On Sun, Nov 21, 2010 at 9:18 PM, Ivan Voras <ivoras(at)freebsd(dot)org> wrote:
>> On 11/22/10 02:47, Kevin Grittner wrote:
>>>
>>> Ivan Voras  wrote:
>>>
>>>> After 16 clients (which is still good since there are only 12
>>>> "real" cores in the system), the performance drops sharply
>>>
>>> Yet another data point to confirm the importance of connection
>>> pooling.  :-)
>>
>> I agree, connection pooling will get rid of the symptom. But not the
>> underlying problem. I'm not saying that having 1000s of connections to the
>> database is a particularly good design, only that there shouldn't be a sharp
>> decline in performance when it does happen. Ideally, the performance should
>> remain the same as it was at its peek.
>>
>> I've been monitoring the server some more and it looks like there are
>> periods where almost all servers are in the semwait state followed by
>> periods of intensive work - approximately similar to the "thundering herd"
>> problem, or maybe to what Josh Berkus has posted a few days ago.
>>
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>
> Try it with systemtap or dtrace and see if you find the same
> bottlenecks as I do in
> http://jkshah.blogspot.com/2010/11/postgresql-90-simple-select-scaling.html
>
> I will probably retry it with pgBench and see what  I find ..
>
> Regards,
> Jignesh
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>