DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=W9tHt0QUoSGE53CEAUwkEMJ2b9/rHkwU4445fyJBl8RWvFBdLw8K8lmgmyqYJnU6OU
	3NlF7g/jJoQeYXN6+fmrS95QyhqBzhVeSmcjxo4tfbq6nzwbXwfeyBGsGsTnhA5Pqjgc
	FBcC3XtTivKNeSTy9fsZTYvra9wK5TuLiDIOY=
MIME-Version: 1.0
In-Reply-To: <8a1bfe660911100852l5573ab30ne91c6d8937dc836b@mail.gmail.com>
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>
Date: Tue, 10 Nov 2009 10:01:30 -0700
Message-ID: <dcc563d10911100901i3af595dr71920fe99c1f9f70@mail.gmail.com>
Subject: Re: limiting performance impact of wal archiving.
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Laurent Laborde <kerdezixe@gmail.com>
Cc: Greg Smith <greg@2ndquadrant.com>, Ivan Voras <ivoras@freebsd.org>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Tue, Nov 10, 2009 at 9:52 AM, Laurent Laborde <kerdezixe@gmail.com> wrot=
e:
> On Tue, Nov 10, 2009 at 5:35 PM, Greg Smith <greg@2ndquadrant.com> wrote:
>> disks (RAID1) are the two WAL setups that work well, and if I have a bun=
ch
>> of drives I personally always prefer a dedicated drive mainly because it
>> makes it easy to monitor exactly how much WAL activity is going on by
>> watching that drive.

I do the same thing for the same reasons.

> On the "new" slave i have 6 disk in raid-10 and 2 disk in raid-1.
> I tought about doing the same thing with the master.

It would be a worthy change to make.  As long as there's no heavy log
write load on the RAID-1 put the pg_xlog there.

>> Generally if checkpoints and archiving are painful, the first thing to d=
o is
>> to increase checkpoint_segments to a very high amount (>100), increase
>> checkpoint_timeout too, and push shared_buffers up to be a large chunk o=
f
>> memory.
>
> Shared_buffer is 2GB.

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.

> I'll reread domcumentation about checkpoint_segments.
> thx.

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.

>> You never want to use LVM under Linux if you care about performance. =A0=
It
>> adds a bunch of overhead that drops throughput no matter what, and it's
>> filled with limitations. =A0For example, I mentioned write barriers bein=
g one
>> way to interleave WAL writes without other types without having to write=
 the
>> whole filesystem cache out. =A0Guess what: =A0they don't work at all reg=
ardless
>> if you're using LVM. =A0Much like using virtual machines, LVM is an appr=
oach
>> only suitable for low to medium performance systems where your priority =
is
>> easier management rather than speed.
>
> *doh* !!
> Everybody told me "nooo ! LVM is ok, no perceptible overhead, etc ...)
> Are you 100% about LVM ? I'll happily trash it :)

Everyone who doesn't run databases thinks LVM is plenty fast.  Under a
database it is not so quick.  Do your own testing to be sure, but I've
seen slowdowns of about 1/2 under it for fast RAID arrays.

>> Given the current quality of Linux code, I hesitate to use anything but =
ext3
>> because I consider that just barely reliable enough even as the most pop=
ular
>> filesystem by far. =A0JFS and XFS have some benefits to them, but none s=
o
>> compelling to make up for how much less testing they get. =A0That said, =
there
>> seem to be a fair number of people happily running high-performance
>> PostgreSQL instances on XFS.
>
> Thx for the info :)

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.