April 12, 2005 at 8:28 am
Hi, I am in the process of putting together a database design for an OLTP and OLAP database. The OLAP and OLTP will be hosted on different db servers and will be clustered in an Active-Passive mode.
The databases will be hosted on a SAN and I have available to me 14 or maybe 16 146GB 15K RPM disks to use in whatever configuration I want. The OLTP will be around 70GB and the OLAP will be around 180GB and both databases will serve a group of 150 users.
Now the easy and simple solution would be to create one large RAID 0+1 array (would give approx 1TB useable space) and host both the OLTP and OLAP on the same array (data + logs + tempdb) striped across 7/8 disks which are then mirrored. However this would nowhere near be the most performant solution.
Based on the fact that I do want to host the OLTP and the OLAP on separate arrays to avoid disk contention I am considering something like the following setup for EACH of the databases (OLTP/OLAP).
- The two clustered DB servers will have 2x36GB disks in RAID 1 for OS/Paging File/SQL Server binaries
- 2x146GB disks on the SAN in RAID 1 for transaction logs
- 6x146GB disks on the SAN in RAID 0+1 for data and tempdb
Can anyone recommend any other solutions I should consider with this hardware available to me!
The questions/concerns I have are the following:
- I am only striping across three disks for the data files is this too few spindles?
- I am going to cause myself performance problems by hosting the tempdb and data on the same array? I don't think I should move tempdb to the Txn log array and I don't think I can move tempdb to the OS drive as it is a cluster.
- How much disk contention have other people seen when hosting an OLTP and OLAP on the same array?
Any tips ideas would be greatly appreciated.
Thanks,
TD
April 12, 2005 at 11:32 pm
We have a HP EVA storage on our SAN for the OLTP databases, and a HP MSA 1000 storage solution on the SAN for the OLAP databases.
This way, the OLAP can never interfere (except for the SAN throughput, but 2Gbit should be enough) with the OLTP databases.
The EVA storage is quick enough for handling our databases, but this could be very different in your situation...
April 13, 2005 at 6:03 am
Was this running on SQL server? If so how were the arrays configured for the OLTP?
April 13, 2005 at 6:44 am
SQL Server 2000 SP3a.
I do not know the exact physical configuration of the disks on the EVA storage.
April 13, 2005 at 7:14 am
Not bad.
For OLAP, an alternative is to use RAID 5 for dw. For 6 spindles in RAID5, the read performance (reads from 5 spindles) will be better than RAID0+1 (reads from 3 spindles). Since there is little transactions in DW, the writing penalty of RAID 5 is alleviated. You can enable the write cache to further smooth writing performance.
April 13, 2005 at 9:13 am
I'm not familiar with EVA but I've worked extensively with Hitachi and EMC SAN and SQL Server. The underlying arrays are important but not paramount (RAID 5, 1+0, 1 more or less depends on how much disk you want to 'waste' on parity/redundancy. We sue RAID 5 and arrays of at lease 5 -136 Gb 15K disks). However it is whether or not you are using LUNs or Mets-LUNS may be even more important. A LUN is a slice of disk space on a single array. A Mets-LUN is a slice of disk space across multiple disk arrays. The latter being superior in performance. Also, I'd probably get at least 50% of the maximum buffer memory on your SAN. i.e., if it supports 8 Gb of RAM buffering, I'd start with 4 Gb right off the bat.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
April 13, 2005 at 9:37 am
I will be considering using a single RAID 5 for the whole OLAP database but I have still read a number of posts where peoples ETL process times on an OLAP have been significantly reduced by having the txn logs separated.
The problem these days is that we couldn't get smaller disks. IBM's 76GB 10K RPM and 146GB 15K RPM disks were EXACTLY the same price so it made no sense to go with the smaller slower disks!
April 13, 2005 at 12:15 pm
Separation is another key point. Database data, Transaction Log(s) and Backups need to be on their own distinct LUNs/Mets-LUNs. Separation of database Data and Log is SQL 102.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply