Re: How to setup disk spindles for best performance

On Wed, Aug 20, 2008 at 6:25 PM, Christiaan Willemsen
<cwillemsen(at)technocon(dot)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