Cc: pgsql-performance@postgresql.org
Message-Id: <331B8FD1-7460-4B97-BD2C-03D8381E1C4D@technocon.com>
From: Christiaan Willemsen <cwillemsen@technocon.com>
To: "Merlin Moncure" <mmoncure@gmail.com>
In-Reply-To: <b42b73150808201849t3577d65eq76e7484d8519af51@mail.gmail.com>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v926)
Subject: Re: How to setup disk spindles for best performance
Date: Thu, 21 Aug 2008 07:38:39 +0200
References: <972BDF1A-B4ED-46EC-B416-0CDF15A0439A@technocon.com>
	<b42b73150808201849t3577d65eq76e7484d8519af51@mail.gmail.com>

So, what you are basically saying, is that a single mirror is in  
general more than enough to facilitate the transaction log.

So it would not be smart to put the indexes onto a separate disk  
spindle to improve index performance?

On Aug 21, 2008, at 3:49 AM, Merlin Moncure wrote:

> On Wed, Aug 20, 2008 at 6:25 PM, Christiaan Willemsen
> <cwillemsen@technocon.com> wrote:
>> I'm currently trying to find out what the best configuration is for  
>> our new
>> database server. It will server a database of about 80 GB and  
>> growing fast.
>> The new machine has plenty of memory (64GB) and 16 SAS disks, of  
>> wich two
>> are already in use as a mirror for the OS.
>>
>> The rest can be used for PostgreSQL. So that makes a total of 14  
>> 15k.5 SAS
>> diks. There is obviously a lot to interesting reading to be found,  
>> most of
>> them stating that the transaction log should be put onto a separate  
>> disk
>> spindle. You can also do this with the indexes. Since they will be  
>> updated a
>> lot, I guess that might be a good idea. But what no-one states, is  
>> what
>> performance these spindle should have in comparison to the data  
>> spindle? If
>> I create a raid 10 of 6 disks for the data, 4 disk raid 10 for the  
>> log, and
>> 4 disk raid 10 for the indexes, will this yield best performance?  
>> Or is it
>> sufficient to just have a simple mirror for the log and/or  
>> indexes...? I
>> have not found any information about these figures, and I guess it  
>> should be
>> possible to give some pointers on how these different setup might  
>> affect
>> performance?
>
> Well, the speed of your logging device puts an upper bound on the
> write speed of the database.  While modern sas drives can do 80mb/sec
> + with sequential ops, this can turn to 1mb/sec real fast if the
> logging is duking it out with the other generally random work the
> database has to do, which is why it's often separated out.
>
> 80mb/sec is actually quite a lot in database terms and you will likely
> only get anything close to that when doing heavy insertion, so that
> it's unlikely to become the bottleneck.  Even if you hit that limit
> sometimes, those drives are probably put to better use in the data
> volume somewhere.
>
> As for partitioning the data volume, I'd advise this only if you have
> a mixed duty database that does different tasks with different
> performance requirements.  You may be serving a user interface which
> has very low maximum transaction time and therefore gets dedicated
> disk i/o apart from the data churn that is going on elsewhere.  Apart
> from that though, I'd keep it in a single volume.
>
> merlin