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?

Best regards,

Doug

--

Robert D Oden
Database Marketing Technologies, Inc
951 Locust Hill Circle
Belton MO 64012-1786

Ph: 816-318-8840
Fax: 816-318-8841

roden@dbasetek.com


This email has been processed by SmoothZap - www.smoothwall.net


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Aug 17, 2007, at 6:55 PM, Merlin Moncure wrote:
> 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.

If you only have one controller, and it can cache writes (it has a
BBU), I'd actually lean towards putting all 12 drives into one raid
10. A good controller will be able to handle WAL fsyncs plenty fast
enough, so having a separate WAL mirror would likely hurt more than
help.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend