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