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=c5zYyh7lVebPyKAhDuHSj9aPjbowFevymwy/c5DwQolnNZPSToy4qc533BkMXeV86Q
	xhDRlF7v4oBrMYOQkU2weQcszymAfFp0+xe1QNWitT/V3KV6O6VZ0jZ67n2+g07Mtznu
	46U3hN/2/9SHyfeDikqGNAUuaS9vxlzkxUkKg=
MIME-Version: 1.0
In-Reply-To: <4AF99666.5090705@2ndquadrant.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>
Date: Tue, 10 Nov 2009 17:52:00 +0100
Message-ID: <8a1bfe660911100852l5573ab30ne91c6d8937dc836b@mail.gmail.com>
Subject: Re: limiting performance impact of wal archiving.
From: Laurent Laborde <kerdezixe@gmail.com>
To: Greg Smith <greg@2ndquadrant.com>
Cc: 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 5:35 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Laurent Laborde wrote:
>>
>> It is on a separate array which does everything but tablespace (on a
>> separate array) and indexspace (another separate array).
>>
>
> On Linux, the types of writes done to the WAL volume (where writes are
> constantly being flushed) require the WAL volume not be shared with anyth=
ing
> else for that to perform well. =A0Typically you'll end up with other thin=
gs
> being written out too because it can't just selectively flush just the WA=
L
> data. =A0The whole "write barriers" implementation should fix that, but i=
n
> practice rarely does.
>
> If you put many drives into one big array, somewhere around 6 or more
> drives, at that point you might put the WAL on that big volume too and be=
 OK
> (presuming a battery-backed cache which you have). =A0But if you're carvi=
ng up
> array sections so finely for other purposes, it doesn't sound like your W=
AL
> data is on a big array. =A0Mixed onto a big shared array or single dedica=
ted
> disks (RAID1) are the two WAL setups that work well, and if I have a bunc=
h
> 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.

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.


>> Well, actually, i also change the configuration to synchronous_commit=3D=
off
>> It probably was *THE* problem with checkpoint and archiving :)
>>
>
> This is basically turning off the standard WAL implementation for one whe=
re
> you'll lose some data if there's a crash. =A0If you're OK with that, grea=
t; if
> not, expect to lose some number of transactions if the server ever goes d=
own
> unexpectedly when configured like this.

I have 1 spare dedicated to hot standby, doing nothing but waiting for
the master to fail.
+ 2 spare candidate for cluster mastering.

In theory, i could even disable fsync and all "safety" feature on the maste=
r.
In practice, i'd like to avoid using the slony's failover capabilities
if i can avoid it :)

> Generally if checkpoints and archiving are painful, the first thing to do=
 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 of
> memory.

Shared_buffer is 2GB.
I'll reread domcumentation about checkpoint_segments.
thx.

> Disabling synchronous_commit should be a last resort if your
> performance issues are so bad you have no choice but to sacrifice some da=
ta
> integrity just to keep things going, while you rearchitect to improve
> things.
>
>> eg: historically, we use JFS with LVM on linux. from the good old time
>> when IO wasn't a problem.
>> i heard that ext3 is not better for postgresql. what else ? xfs ?
>>
>
> You never want to use LVM under Linux if you care about performance. =A0I=
t
> adds a bunch of overhead that drops throughput no matter what, and it's
> filled with limitations. =A0For example, I mentioned write barriers being=
 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 rega=
rdless
> if you're using LVM. =A0Much like using virtual machines, LVM is an appro=
ach
> only suitable for low to medium performance systems where your priority i=
s
> 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 :)

> Given the current quality of Linux code, I hesitate to use anything but e=
xt3
> because I consider that just barely reliable enough even as the most popu=
lar
> filesystem by far. =A0JFS and XFS have some benefits to them, but none so
> compelling to make up for how much less testing they get. =A0That said, t=
here
> seem to be a fair number of people happily running high-performance
> PostgreSQL instances on XFS.

Thx for the info :)

--=20
ker2x