Joseph S Wrote
> If I have 14 drives in a RAID 10 to split between data tables
> and indexes what would be the best way to allocate the drives
> for performance?
RAID-5 can be much faster than RAID-10 for random reads and writes. It is much slower than RAID-10 for sequential
writes,but about the same for sequential reads. For typical access patterns, I would put the data and indexes on
RAID-5unless you expect there to be lots of sequential scans.
If you do this, you can drop the random_page_cost from the default 4.0 to 1.0. That should also encourage postgres to
usethe index more often. I think the default costs for postgres assume that the data is on a RAID-1 array. Either
that,or they are a compromise that isn't quite right for any system. On a plain old disk the random_page_cost should
be8.0 or 10.0.
The division of the drives into two arrays would depend on how much space will be occupied by the tables vs the
indexes. This is very specific to your database. For example, if indexes take half as much space as tables, then you
want2/3rds for tables and 1/3rd for indexes. 8 drives for tables, 5 drives for indexes, and 1 for a hot standby. The
smallerarray may be a bit slower for some operations due to reduced parallelism. This also depends on the intelligence
ofyour RAID controller.
Always put the transaction logs (WAL Files) on RAID-10 (or RAID-1 if you don't want to dedicate so many drives to the
logs). The only significant performance difference between RAID-10 and RAID-1 is that RAID-1 is much slower (factor of
4or 5) for random reads. I think the ratio of random reads from the transaction logs would typically be quite low.
Theyare written sequentially and during checkpoint they are read sequentially. In the interim, the data is probably
stillin shared memory if it needs to be read.
You don't want your transaction logs or any swapfiles on RAID-5. The slow sequential write performance can be a
killer.
-Luke