| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2004-01-30 : 20:27:06
|
| Without wishing to be controversial, I would like to ask what the optimum configuration would be for a new SQL server box that has a 3 disc raid array and a single disc (for OS etc). I have recently been advised that storing the log file on a RAID array causes it to fragment massively.Does anyone have any comments on thisthankssteveSteve no function beer well without |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-01-31 : 22:08:44
|
| RAID 1, or RAID 10 if you can afford it, is best for logging.While we're on the subject, RAID 10 is also the best for data (RAID 5 is the worst).JonathanGaming will never be the same |
 |
|
|
MuadDBA
628 Posts |
Posted - 2004-02-02 : 10:47:09
|
| RAID 5 is the worst?? Care to share? Articles I have read indicate that RAID 5 offers superior read performance due to spreading out the data over more spindles. I do data on RAID5 and log on RAID1. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-02-02 : 14:03:48
|
| RAID 5 is the worst for transactional data volumes b/c the controller must compute the parity bit on every write. At the implementation level, this means a write operation consists of 2 reads plus the write.Spreading data out over spindles does increase speed (and, logically, RAID 0 or any of its mirrored analogs such as RAID 10, 50, etc is the fastest you can get), but is outweighed by needing additional I/O on writes.JonathanGaming will never be the same |
|
|
|
jon3k
Starting Member
49 Posts |
Posted - 2004-02-02 : 17:13:39
|
| It really depends on the kind of redundancy vs. performance needs. You could run them all as independent disks, and use one disk for data files, one for tlogs and one for tempdb (if you make extensive use of temporary tables), which would give you, arguably, the best performance out of your disks. Of course, this offers no redundancy. You might want to consider using 1 disk for transaction log, and then a RAID1 pair for the data files. This will give you a nice performance increase (by putting the tlogs on a seperate disk than the data files) and you'll also have a mirror of your data files, in case of the worst. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-02-03 : 11:49:59
|
| Not really, no. Running them as independent disks and creating a filegroup for each disk, in some sort of "software RAID" config will be slower.Again, the fastest you can possibly get is RAID 0. You add redundancy to that by mirroring the entire array a la RAID 10, or for serious speed, multiple times such as RAID 50.RAID 5 is a poor man's config that sacrifices performance for cost. In the past this has not been a bad tradeoff, but with today's prices it is harder to justify.JonathanGaming will never be the same |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-02-03 : 14:56:38
|
| I dunno. I guess it depends to what level you take your array and what is the primary purpose of your database. Comparing a 100GB RAID 1 to a 100GB Raid5 split across 8 disks, I would think you wold still see better perfomance out of the RAID5. Before I came to work here for the bank, all of the servers used RAID1, and were growing pretty big. I would say on some of our databases we get typically between 10,000 and 30,000 updates per day. However, the database has over 60 million rows and often large chunks of it are read to do indexing, reporting, etc....switching to RAID5 definitely improved performance over the previous RAID1 array.This is just personal experience in my unique envoronment, but pure dollar cost is not the only issue in today's server room....floor space is. Racks up on racks of disks using RAID10 take up a lot of space. The best solution we have utilized to date is a SAN, but we haven't converted any of our major DBs to it yet because we share it with another area and want to make sure we don't suffer any performance hits. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2004-02-04 : 11:41:41
|
quote:
Comparing a 100GB RAID 1 to a 100GB Raid5 split across 8 disks, I would think you wold still see better perfomance out of the RAID5
Absolutely- though we're not comparing RAID 1 to RAID 5, we're comparing RAID 5 to RAID 10. I stand by my earlier statement: the fastest you can possibly get is RAID 0 or its mirrored equivalents.JonathanGaming will never be the same |
|
|
|
chisholmd
Starting Member
5 Posts |
Posted - 2004-03-08 : 15:32:46
|
| To hijack this thread for a moment. I am looking at setting up raid 1 with two 80gb SATA disks. I was wondering if you knew if it will fail over automaticaly to the mirrored drive in case of a failure on the primary? i have read that SCSI would, and that with regular ATA you would probably have to flip jumpers.Hmm I guess you would only have to flip jumpers if they were both on the same channel and the master failed. since both of these would be on thier own controller that would be the deciding factor not saTA vs ATA.Well anyway i'm still looking for an opinion.Do you guys disable write caching? how does that effect your raid performance?davethink things not words -O.W.Holms |
|
|
|
|
Logging