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

Greg, It's so nice to get a reply from the author of *the book*.  Thank
you for taking the time to help us out.

On 02/01/11 18:30, Greg Smith wrote:
> Do you not want any excitement in your life?

I've had database excitement enough to last a lifetime.  That's why I'm
mending my ways.  Your book is the first step of our 12 step program.

> 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.

I'll look for 8.4.7, but we'll be switching to 9 before too long.

>> File system: XFS (nobarrier, noatime)
>
> Should probably add "logbufs=8" in there too.

Will do.

>> work_mem = 192MB
>> wal_buffers = 8MB
>> random_page_cost = 1.0
>
> 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.

That's a leftover from the days when we *really* didn't know what we're
doing (now we only *mostly* don't know what we're doing).  I'll set
work_mem down to something less scary.

> Might as well bump wal_buffers up to 16MB and be done with it.

Will do.

> 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.

Oops, that was a typo.  We've set random_page_cost to 2, not 1.

> 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'?

See "we really didn't know what we were doing."  We'll leave
constraint_exclusion at its default.

>> 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.

I also thought the "cache off" figures were odd.  I expected something
much closer to 120 req/sec (7200 rpm drives).  I probably won't
investigate that with any vigor, since the cache-on numbers are OK.

> 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.

TCB-B-like tests, will do.

Greg, Thanks a million.

     Wayne Conrad