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@xxxxxxxxxxxxx> 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
