To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: limiting performance impact of wal archiving.
Date: Tue, 10 Nov 2009 16:11:33 +0100
Lines: 48
Message-ID: <hdbvqt$7v8$1@ger.gmane.org>
References: <8a1bfe660911100355l34a557ffj8244079c1fb689b5@mail.gmail.com>	
	<hdbrv8$p35$1@ger.gmane.org>
	<8a1bfe660911100700l2a44f638m97ea6136b65deb21@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 2.0.0.23 (X11/20090928)
In-Reply-To: <8a1bfe660911100700l2a44f638m97ea6136b65deb21@mail.gmail.com>

Laurent Laborde wrote:
> On Tue, Nov 10, 2009 at 3:05 PM, Ivan Voras <ivoras@freebsd.org> wrote:
>> Laurent Laborde wrote:
>>> Hi !
>>> We recently had a problem with wal archiving badly impacting the
>>> performance of our postgresql master.
>> Hmmm, do you want to say that copying 16 MB files over the network (and
>> presumably you are not doing it absolutely continually - there are pauses
>> between log shipping - or you wouldn't be able to use bandwidth limiting) in
>> an age when desktop drives easily read 60 MB/s (and besides most of the file
>> should be cached by the OS anyway) is a problem for you? Slow hardware?
>>
>> (or I've misunderstood the problem...)
> 
> Desktop drive can easily do 60MB/s in *sequential* read/write.

... and WAL files are big sequential chunks of data :)

> We use high performance array of 15.000rpm SAS disk on an octocore
> 32GB and IO is always a problem.
> 
> I explain the problem :
> 
> This server (doing wal archiving) is the master node of the
> over-blog's server farm.
> hundreds of GB of data, tens of millions of articles and comments,
> millions of user, ...
> ~250 read/write sql requests per seconds for the master
> ~500 read sql request per slave.
> 
> Awefully random access overload our array at 10MB/s at best.

Ok, this explains it. It also means you are probably not getting much 
runtime performance benefits from the logging and should think about 
moving the logs to different drive(s), among other things because...

> Of course, when doing sequential read it goes to +250MB/s :)

... it means you cannot dedicate 0.064 of second from the array to read 
through a single log file without your other transactions suffering.

> Waiting for "cheap" memory to be cheap enough to have 512Go of ram per server ;)
> 
> We tought about SSD.
> But interleaved read/write kill any SSD performance and is not better
> than SSD. Just more expensive with an unknown behaviour over age.

Yes, this is the current attitude toward them.