This is not a request for help but a report, in case it helps developers
or someone in the future. The setup is:
AMD64 machine, 24 GB RAM, 2x6-core Xeon CPU + HTT (24 logical CPUs)
FreeBSD 8.1-stable, AMD64
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
After 16 clients (which is still good since there are only 12 "real"
cores in the system), the performance drops sharply, and looking at the
processes' state, most of them seem to eat away system call (i.e.
executing in the kernel) in states "semwait" and "sbwait", i.e.
semaphore wait and socket buffer wait, for example:
3047 pgsql 1 60 0 10533M 283M sbwait 12 0:01 6.79% postgres
3055 pgsql 1 64 0 10533M 279M sbwait 15 0:01 6.79% postgres
3033 pgsql 1 64 0 10533M 279M semwai 6 0:01 6.69% postgres
3038 pgsql 1 64 0 10533M 283M CPU5 13 0:01 6.69% postgres
3037 pgsql 1 62 0 10533M 279M sbwait 23 0:01 6.69% postgres
3048 pgsql 1 65 0 10533M 280M semwai 4 0:01 6.69% postgres
3056 pgsql 1 65 0 10533M 277M semwai 1 0:01 6.69% postgres
3002 pgsql 1 62 0 10533M 284M CPU19 0 0:01 6.59% postgres
3042 pgsql 1 63 0 10533M 279M semwai 21 0:01 6.59% postgres
3029 pgsql 1 63 0 10533M 277M semwai 23 0:01 6.59% postgres
3046 pgsql 1 63 0 10533M 278M RUN 5 0:01 6.59% postgres
3036 pgsql 1 63 0 10533M 278M CPU1 12 0:01 6.59% postgres
3051 pgsql 1 63 0 10533M 277M semwai 1 0:01 6.59% postgres
3030 pgsql 1 63 0 10533M 281M semwai 1 0:01 6.49% postgres
3050 pgsql 1 60 0 10533M 276M semwai 1 0:01 6.49% postgres
The "sbwait" part is from FreeBSD - IPC sockets, but so much blocking on
semwait indicates large contention in PostgreSQL.
