Re: Slides for last night's talk

[Tim Allen <tim(at)proximity(dot)com(dot)au>]

Gavin Sherry wrote:
> You can fine my slides here:
>
> http://pugs.postgresql.org/sydpug/archives/000056.html

Thanks for that, Gavin. There are a few points that seem new and 
interesting to me - wish I had been there for your explanation. Hope you 
don't mind a delayed Q&A session :-).

Setting wal_buffers to 64 to 512 seems much higher than any previous 
recommendation I'd seen. I (just now) noticed Josh Berkus's blog entry 
where he did some testing that showed performance goes up with 
wal_buffers in the range you've just indicated. But the annotated conf 
document just says that wal_buffers isn't all that important, it only 
needs to be big enough to hold a single transaction, and you're better 
off paying attention to checkpoint_segments. You haven't mentioned 
checkpoint_segments in your slides. Is there some way out of the 
apparent contradiction? I guess the annotated conf is still aimed at 
8.0, perhaps it needs updating for 8.1? Does your experience back up 
Josh's recent testing? And do you have any particular recommendation for 
checkpoint_segments?

Setting wal_buffers higher has a cost in consumption of shared memory, 
right? So presumably you don't want to be too profligate with it.

On shared_buffers, your slide says "up to" 1/3 of physical memory. Did 
you say anything about how close to that upper limit it's worth going? I 
gather for 8.0 and earlier there wasn't much point setting 
shared_buffers too high, as the kernel's own cacheing was about as good 
as postgres's buffer cache, but that 8.1 made enough improvements to the 
shared buffer cache to possibly change that situation. Do you have a 
recommendation based on the changes in 8.1?

You've listed Opteron, Itanium II and Xeon on the same line for cpu 
recommendations, which presumably implies you think they're roughly 
equivalent. Lots of comments I've seen on the mailing lists indicate the 
Opteron does rather better than anything from Intel; is that not your 
experience?

On H/A, and the RedHat cluster solution with shared SAN (or shared SCSI 
array), we've had one disaster with that at one customer (corrupted 
database) which we guessed (but weren't able to prove) was due to the 
cluster manager failing to adequately ensure that only one postgres 
instance had the filesystem mounted at a time. We couldn't really get to 
the bottom of it, because the install was difficult for other reasons, 
and we weren't able to do any other testing. I've seen some mailing list 
postings from Alvaro H to the effect that he knows of a site in 
Venezuela that had the same problem. Because of that, I've tended to 
regard the RedHat cluster solution with some suspicion. But upon further 
searching, I haven't come across any other reports of failures, and have 
seen a few mentions in passing of people actually using it. What's your 
impression and/or experience? Can we trust RedHat's cluster management?

> Thanks all,
>
> Gavin

Thanks,

Tim

--
-----------------------------------------------
Tim Allen          tim(at)proximity(dot)com(dot)au
Proximity Pty Ltd  http://www.proximity.com.au/