Wayne Conrad wrote:
We're building a new database box. With the help of Gregory Smith's
book, we're benchmarking the box: We want to know that we've set it up
right, we want numbers to go back to if we have trouble later, and we
want something to compare our _next_ box against.
Do you not want any excitement in your life?
PostgreSQL 8.4.1 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Debian 4.3.4-2) 4.3.4, 64-bit
8.4.7 is current; there are a lot of useful fixes to be had. See if you
can get a newer Debian package installed before you go live with this.
File system: XFS (nobarrier, noatime)
Should probably add "logbufs=8" in there too.
shared_buffers = 8192MB
temp_buffers = 16MB
work_mem = 192MB
maintenance_work_mem = 5GB
wal_buffers = 8MB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
random_page_cost = 1.0
constraint_exclusion = on
That work_mem is a bit on the scary side of things, given how much
memory is allocated to other things. Just be careful you don't get a
lot of connections and run out of server RAM.
Might as well bump wal_buffers up to 16MB and be done with it.
Setting random_page_cost to 1.0 is essentially telling the server the
entire database is cached in RAM. If that's not true, you don't want to
go quite that far in reducing it.
With 8.4, you should be able to keep constraint_exclusion at its default
of 'partition' and have that work as expected; any particular reason you
forced it to always be 'on'?
Bonnie++ (-f -n 0 -c 4)
$PGDATA/xlog (RAID1)
random seek: 369/sec
block out: 87 MB/sec
block in: 180 MB/sec
$PGDATA (RAID10, 12 drives)
random seek: 452
block out: 439 MB/sec
block in: 881 MB/sec
sysbench test of fsync (commit) rate:
$PGDATA/xlog (RAID1)
cache off: 29 req/sec
cache on: 9,342 req/sec
$PGDATA (RAID10, 12 drives)
cache off: 61 req/sec
cache on: 8,191 req/sec
That random seek rate is a little low for 12 drives, but that's probably
the limitations of the 3ware controller kicking in there. Your "cache
off" figures are really weird though; I'd expect those both to be around
100. Makes me wonder if something weird is happening in the controller,
or if there was a problem with your config when testing that. Not a big
deal, really--the cached numbers are normally going to be the important
ones--but it is odd.
Your pgbench SELECT numbers look fine, but particularly given that
commit oddity here I'd recommend running some of the standard TPC-B-like
tests, too, just to be completely sure there's no problem here. You
should get results that look like "Set 3: Longer ext3 tests" in the set
I've published to http://www.2ndquadrant.us/pgbench-results/index.htm
presuming you let those run for 10 minutes or so. The server those came
off of has less RAM and disks than yours, so you'll fit larger database
scales into memory before performance falls off, but that gives you
something to compare against.
--
Greg Smith 2ndQuadrant US greg@xxxxxxxxxxxxxxx Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
