On 8/16/07, Robert D Oden <roden@dbasetek.com> wrote:
> After reading many articles which indicate the more disk spindles the
> better performance and separating indexes, WAL and data on different
> sets of spindles, I've come up with a couple of questions.
>
> We am planning to buy an external raid sub-system utilizing raid 10. The
> sub-system will consist of 12 73GB SAS drives total.
> Based on our data requirements we can set this system up using two
> different configurations.
>
> First, we could have two raid sets, one with two drives mirrored for
> indexes and the other with four drives mirrored for data. Second, we
> could configure as one raid set with six drives mirrored housing both
> indexes and data.
>
> Our environment consists of up to 10-20 users doing a variety of
> queries. We have data entry, batch processing, customer lookups and
> ad-hoc queries happening concurrently through out the day.
>
> Almost all queries would be using indexes, so we were concerned about
> performance of index lookups with only two spindles dedicated to indexes
> (using the first configuration). We thought it may be better to put data
> and indexes on one raid where index lookups and data retrieval would be
> spread across all six spindles.
>
> Any comments would be appreciated!
>
> Second Question:
>
> Would there be any problems/concerns with putting WAL files on the
> server in a raid 10 configuration separate from external raid sub-system?
This question comes up a lot, and the answer is always 'it depends'
:-). Separate WAL volume pays off the more writing is going on in
your database...it's literally a rolling log of block level changes to
the database files. If your database was 100% read, it would not help
very much at all. WAL traffic is mostly sequential I/O, but heavy.
As for splitting data and indexes, I am skeptical this is a good idea
except in very specific cases and here's my reasoning...splitting the
devices that way doesn't increase the number of random I/O of the data
subsystem. Mostly I would be doing this if I was adding drives to the
array but couldn't resize the array for some reason...so I look at
this as more of a storage management feature.
So, I'd be looking at a large raid 10 and 1-2 drives for the WAL...on
a raid 1. If your system supports two controllers (in either
active/active or active/passive), you should look at second controller
as well.
merlin