Re: Linux: more cores = less concurrency.

On Tue, Apr 12, 2011 at 3:54 AM, Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
> --- On Tue, 12/4/11, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
>> >> The issue I'm seeing is that 8 real cores
>> outperform 16 real
>> >> cores, which outperform 32 real cores under high
>> concurrency.
>> >
>> > With every benchmark I've done of PostgreSQL, the
>> "knee" in the
>> > performance graph comes right around ((2 * cores) +
>> > effective_spindle_count).  With the database fully
>> cached (as I
>> > believe you mentioned), effective_spindle_count is
>> zero.  If you
>> > don't use a connection pool to limit active
>> transactions to the
>> > number from that formula, performance drops off.  The
>> more CPUs you
>> > have, the sharper the drop after the knee.
>>
>> I was about to say something similar with some canned
>> advice to use a
>> connection pooler to control this.  However, OP
>> scaling is more or
>> less topping out at cores / 4...yikes!.  Here are my
>> suspicions in
>> rough order:
>>
>> 1. There is scaling problem in client/network/etc.
>> Trivially
>> disproved, convert the test to pgbench -f and post results
>> 2. The test is in fact i/o bound. Scaling is going to be
>> hardware/kernel determined.  Can we see
>> iostat/vmstat/top snipped
>> during test run?  Maybe no-op is burning you?
>
> This is during my 80 clients test, this is a point at which the performance is well below that of the same machine limited to 8 cores.
>
> http://www.privatepaste.com/dc131ff26e
>
>> 3. Locking/concurrency issue in heavy_seat_function()
>> (source for
>> that?)  how much writing does it do?
>>
>
> No writing afaik - its a select with a few joins and subqueries - I'm pretty sure it's not writing out temp data either, but all clients are after the same data in the test - maybe theres some locks there?
>
>> Can we see some iobound and cpubound pgbench runs on both
>> servers?
>>
>
> Of course, I'll post when I've gotten to that.

Ok, there's no writing going on -- so the i/o tets aren't necessary.
Context switches are also not too high -- the problem is likely in
postgres or on your end.

However, I Would still like to see:
pgbench select only tests:
pgbench -i -s 1
pgbench -S -c 8 -t 500
pgbench -S -c 32 -t 500
pgbench -S -c 80 -t 500

pgbench -i -s 500
pgbench -S -c 8 -t 500
pgbench -S -c 32 -t 500
pgbench -S -c 80 -t 500

write out bench.sql with:
begin;
select * from heavy_seat_function();
select * from heavy_seat_function();
commit;

pgbench -n bench.sql -c 8 -t 500
pgbench -n bench.sql -c 8 -t 500
pgbench -n bench.sql -c 8 -t 500

I'm still suspecting an obvious problem here. One thing we may have
overlooked is that you are connecting and disconnecting one per
benchmarking step (two query executions). If you have heavy RSA
encryption enabled on connection establishment, this could eat you.

If pgbench results confirm your scaling problems and our issue is not
in the general area of connection establishment, it's time to break
out the profiler :/.

merlin