Re: Slides for last night's talk

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

Gavin Sherry wrote:
> On Wed, 3 May 2006, Tim Allen wrote:
> > 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
>
> Right. We found reasonably recently that increasing wal_buffers has a good
> effect -- mostly in 8.1.

OK, thanks, turning it up now :).

> The annotated postgresql.conf needs updating -- as you say. Please hassle
> Josh -- or even send in some of your own material if possible.

If I can assemble a coherent story for Josh, I'll send it to him.

> I didn't look at increasing checkpoint_segments. I meant to address it but
> overlooked it. It is a little trickly because of the space consumptions.
> For a dedicated system, I generally set this to 32.
>
> > 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.
>
> Right. It does start to have a negative effect at after 512 -- but this is
> a scaling thing. Even at 512, you're only using 4M of memory.

OK, so no need to be too stingy with it, then.

> > 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?
>
> I generally tell people to do their own testing between 1/20th of physical
> memory (what they probably have it at for < 8.1) and 1/3rd of memory.
> Generally, I've had a lot of success setting it as close to 1/3 as
> possible. You can set it higher but in my experience it scales back in
> effectiveness.

I'd better do some testing, then...

> > 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?
>
> I discussed this at length last night. With Opteron, you're getting more
> bang for buck (because they're cheaper) but if you look at performance
> alone for an OLTP workload (like TPC-C), Opteron is not way out in front.
> In fact, Itanium II has better performance -- but it is 10 times more
> expensive. Opteron is a good chip but I wouldn't rush out replacing
> existing 1-2 year old systems with them thinking throughput will increase
> more than 10-15%.

I've actually been struggling a bit with a "no-one ever got fired for 
buying Dell" attitude here, which of course excludes Opterons by 
definition. I have sometimes wished I _could_ fire someone for buying 
Dell :).

> > 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?
>
> I can tell you erm... privately that two customers have had the same
> problem. It gets things right most of the time but for some reason the
> following systems to happen some times:
>
> 1) SAN has some kind of 'blip'. Basically, the controller stalls or
> something for >10 seconds and writes are not performed.
> 2) Slaves do not see the heartbeat from the master -- which is done via
> the SAN
> 3) The slave is promoted to a master
> 4) The master is not taken off line. This is a short coming anyway because
> the master should be shot in the head via a network or power disconnect.
> 5) Now here's the weird bit. We have an interlock to guard against the
> multiple postmaster scenario. First, we have a shared memory segment. If
> it exists, we don't start. Obiously, this isn't effective in a cluster
> environment. We then have a postmaster.pid. If this exists, we don't
> proceed.
>
> I'm sure I checked RHEL's init script to make sure it didn't do something
> like:
>
> if [ -f $PGDATA/postmaster.pid ]
> then
> 	pid=`head -1 $PGDATA/postmaster.pid`
> 	if [ -f /proc/$pid ]
> 	then
> 		# already running
> 	else
> 		rm $PGDATA/postmaster.pid
> 		# start
> 	fi
> fi
>
> It might be worth seeing if this kind of pattern is used at your customer
> site(s). Obviously, this breaks in the shared storage environment.

Yes, something to check. So it sounds like caution _is_ warranted re 
using RH Cluster for postgres.

> Thanks,
>
> Gavin

Thanks for the responses,

Tim

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