On 29/11/2012 17:33, Merlin Moncure wrote:
Since we have some idle cpu% here we can probably eliminate pgbench as
a bottleneck by messing around with the -j switch. another thing we
want to test is the "-N" switch -- this doesn't update the tellers and
branches table which in high concurrency situations can bind you from
locking perspective.
Using -N gives around a 15% increase in tps with no major changes in
load, etc. using more threads slightly drops the performance (as
expected with only 32 "cores"). dropping it does give a slight increase
(presumably because half the cores aren't real).
one thing that immediately jumps out here is that your wal volume
could be holding you up. so it's possible we may want to move wal to
the ssd volume. if you can scrounge up a 9.2 pgbench, we can gather
more evidence for that by running pgbench with the
"--unlogged-tables" option, which creates the tables unlogged so that
they are not wal logged (for the record, this causes tables to be
truncated when not shut down in clean state).
I did notice that using -S drives the tps up to near 30K tps, so it is
possibly the wal volume, although saying that I did move the pg_xlog
directory onto the ssd array before posting to the list and the
difference wasn't significant. I'll try and repeat that when I get some
more downtime (I'm having to run the current tests while the db is live,
but under light load).
I'll have a look at using the 9.2 pgbench and see what happens.
yeah -- this will tell us raw seek performance of ssd volume which
presumably will be stupendous. 2x is minimum btw 10x would be more
appropriate.
since you're building a beast, other settings to explore are numa
(http://frosty-postgres.blogspot.com/2012/08/postgresql-numa-and-zone-reclaim-mode.html)
and dell memory bios settings that are occasionally set from the
factory badly (see here:
http://bleything.net/articles/postgresql-benchmarking-memory.html).
Cheers for the links, I'd already looked at the numa stuff and disabled
zone reclaim. I was looking at using the patch previously posted that
used shared mode for the master process and then local only for the
workers - excuse the terminology - but time constraints prevented that.
Made sure the box was in performance mode in the bios, unfortunately I
spotted bens blog when I was setting the box up, but didn't have time to
go through all the tests. At the time performance seemed ok (well better
than the previous box :) - but having it live for a while made me think
I or it could be doing better.
Anyway, I still think it would be nice to post tps results for compative
purposes, so if I get a minute or two I'll create a site and stick mine
on there.
John
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
