Re: Are we in the ballpark? - Mailing list pgsql-performance

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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books