August 5, 2008 at 3:30 pm
Hi ,
1.Can somebody tell me that where should we put our data files on raids . I mean which raid should the data file be and which raid the log file should be? Which one is the most expensive raid?
Thanks
August 5, 2008 at 11:22 pm
Jr.DBA (8/5/2008)
Hi ,1.Can somebody tell me that where should we put our data files on raids . I mean which raid should the data file be and which raid the log file should be? Which one is the most expensive raid?
Thanks
That's not a simple question. A couple of resources then a simple answer...
For RAID, look at wikipedia to start. http://en.wikipedia.org/wiki/RAID
A good medium-level intro to some of the issues can be found at http://oreilly.com/catalog/oressentials2/chapter/vldb1.pdf. This is an Oracle text, a lot of concepts that just don't exist with SQL Server, but the stuff about disk load, etc., is pretty good.
Simple answers:
- Not considering cost differences, RAID 10 everything, data and log on separate arrays. Move data to something else first.
- Next step down is probably RAID 5 data, RAID 10 log. Log is much heavier percentage write, with RAID 5 sucks at.
- Spend the money on SCSI drives and a good controller, even if it's RAID 5. SATA with extraordinarily few exceptions doesn't handle random I/O well. A rule of thumb is 100 I/O per second for 10k SCSI, 125 iops for 15k, I've seen 12 drive SATA arrays with heavy random writes not able to maintain 40 iops total.
- The most expensive RAID is the one you have to replace because it's not upgradeable to add performance and won't handle your load. In most database environments, you're sizing for I/O per second, not storage capacity, so there's not a simple answer.
August 6, 2008 at 8:50 am
Two questions to start with:
First: What type of database(s) are you dealing with? Are they OLTP (lots of updates, inserts, deletes) or OLAP (very few updates, deletes, large inserts on a scheduled basis, lots of big selects)? OLTP would be things like customer and order tracking, OLAP would be for reports and analysis. If it's one database that does both, consider it OLTP.
Second: How many drives, what size, what speed, and what type of controller? Are they on a SAN, or in the server, for example.
Type of database, and disk data. With that, recommendations can be made.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply