Re: Performance under contention

Ivan Voras wrote:
> PostgreSQL 9.0.1, 10 GB shared buffers, using pgbench with a scale
> factor of 500 (7.5 GB database)
>
> with pgbench -S (SELECT-queries only) the performance curve is:
>
> -c#    result
> 4    33549
> 8    64864
> 12    79491
> 16    79887
> 20    66957
> 24    52576
> 28    50406
> 32    49491
> 40    45535
> 50    39499
> 75    29415

Two suggestions to improve your results here:

1) Don't set shared_buffers to 10GB.  There are some known issues with
large settings for that which may or may not be impacting your results.
Try 4GB instead, just to make sure you're not even on the edge of that area.

2) pgbench itself is known to become a bottleneck when running with lots
of clients.  You should be using the "-j" option to spawn multiple
workers, probably 12 of them (one per core), to make some of this go
away.  On the system I saw the most improvement here, I got a 15-25%
gain having more workers at the higher client counts.

> The "sbwait" part is from FreeBSD - IPC sockets, but so much blocking
> on semwait indicates large contention in PostgreSQL.

It will be interesting to see if that's different after the changes
suggested above.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services and Support        www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books