Storage layout for RAID10 OLTP server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Storage layout for RAID10 OLTP server

Author

Topic

mstclair
Starting Member

13 Posts

Posted - 2009-09-03 : 16:26:12

I have an existing OLTP server that I am migrating to a 2008 cluster. At this moment I have no ability to rearchitect the way the databases are used by the applications. Essentially there is one big OLTP database and several support databases that have to live on the same server at the moment because of extensive cross-database joins and performance considerations.

I currently have two RAID5 arrays for data. The OLTP database uses multiple filegroups split across the logical drives that sit on the two different arrays. The small supporting databases use single filegroups and some sit on the first drive/array and some sit on the second. The big OLTP database also has indexes on a dedicated RAID5 drive/array, the support databases have their indexes in their data filegroups. The big OLTP database has a dedicated RAID1 for tlogs and the rest share a different RAID1. TEMPDB has its own RAID5.

LETTER   PURPOSE   RAID   #DISKS

D        DATA1     RAID5     3
E        DATA2     RAID5     3
I        INDEX     RAID5     3
L        LOG1      RAID1     2
M        LOG1      RAID1     2
T        TEMPDB    RAID5     3

The new cluster has 24 disks I can use for database minus what I need to set up for the (2008) cluster quorum.

Backups are separate SAN storage.

The good news is that I can use RAID10 on the new server, and that I'll have more physical discs. Here's what I'm thinking for the new server.

LETTER   PURPOSE   RAID   #DISKS

D        DATA1     RAID10    4
E        DATA2     RAID10    4
F        DATA3     RAID1     2
I        INDEX     RAID10    4
L        LOG1      RAID1     2
M        LOG1      RAID1     2
T        TEMPDB    RAID10    4
X        QUORUM    RAID1     2

I'd rather have an additional RAID10, but the quorum messes that up. The DATA3 RAID1 is for future growth of support databases. The tlogs are in two separate RAID 1 instead of one RAID10 so that the OLTP database doesn't have to share sequential log writes with all of the support databases.

Currently the DATA drives become I/O bound and stall a lot, followed by the INDEX drive. TEMPDB does not, nor do the tlogs. TempDB I/O (reads+writes) is about 1/10 of that of the big OLTP database.

Any thoughts? I know without detailed analysis there are probably no definitive specific changes to make, but are there any rules of thumb that I should consider? I could combine TEMPDB with the OLTP on a third RAID10 (and spread out the OLTP filegroups), for example.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources