Message-ID: <4AF9A77E.9070406@2ndquadrant.com>
Date: Tue, 10 Nov 2009 12:48:46 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: Laurent Laborde <kerdezixe@gmail.com>, Ivan Voras <ivoras@freebsd.org>,
	pgsql-performance@postgresql.org
Subject: Re: limiting performance impact of wal archiving.
References: <8a1bfe660911100355l34a557ffj8244079c1fb689b5@mail.gmail.com>	
	<hdbrv8$p35$1@ger.gmane.org>	
	<8a1bfe660911100700l2a44f638m97ea6136b65deb21@mail.gmail.com>	
	<hdbvqt$7v8$1@ger.gmane.org>	
	<8a1bfe660911100729q42ac732cga9bbadd52e2533dd@mail.gmail.com>	
	<4AF99666.5090705@2ndquadrant.com>	
	<8a1bfe660911100852l5573ab30ne91c6d8937dc836b@mail.gmail.com>
	<dcc563d10911100901i3af595dr71920fe99c1f9f70@mail.gmail.com>
In-Reply-To: <dcc563d10911100901i3af595dr71920fe99c1f9f70@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Scott Marlowe wrote:
> On some busy systems with lots of small transactions large
> shared_buffer can cause it to run slower rather than faster due to
> background writer overhead.
>   
This is only really true in 8.2 and earlier, where background writer 
computations are done as a percentage of shared_buffers.  The rewrite I 
did in 8.3 changes that to where it's proportional to overall system 
activity (specifically, buffer allocations) and you shouldn't see this 
there.  However, large values for shared_buffers do increase the 
potential for longer checkpoints though, which is similar background 
overhead starting in 8.3.  That's why I mention it hand in hand with 
decreasing the checkpoint frequency, you really need to do that before 
large shared_buffers values are viable.

This is actually a topic I meant to mention to Laurent:  if you're not 
running at least PG8.3, you really should be considering what it would 
take to upgrade to 8.4.  It's hard to justify the 8.3->8.4 upgrade just 
based on that version's new performance features (unless you delete 
things a lot), but the changes from 8.1 to 8.2 to 8.3 make the database 
faster at a lot of common tasks.

> Note that if you've got a slow IO subsystem, a large number of
> checkpoint segments can result in REALLY long restart times after a
> crash, as well as really long waits for shutdown and / or bgwriter
> once you've filled them all up.
>   
The setup here, with a decent number of disks and a 3ware controller, 
shouldn't be that bad here.  Ultimately you have to ask yourself whether 
it's OK to suffer from the rare recovery issue this introduces if it 
improves things a lot all of the rest of the time, which increasing 
checkpoint_segments does.

> Note that XFS gets a LOT of testing, especially under linux.  That
> said it's still probably only 1/10th as many dbs (or fewer) as those
> running on ext3 on linux.  I've used it before and it's a little
> faster than ext3 at some stuff, especially deleting large files (or in
> pg's case lots of 1G files) which can make ext3 crawl.
>   
While true, you have to consider whether the things it's better at 
really happen during a regular day.  The whole "faster at deleting large 
files" thing doesn't matter to me on a production DB server at all, so 
that slam-dunk win for XFS doesn't even factor into my filesystem 
ranking computations in that context.

--
Greg Smith    greg@2ndQuadrant.com    Baltimore, MD