May 14, 2009 at 7:39 am
ondrej.bouda (5/14/2009)
Hey, what's wrong?Consider a small firm: Could you please explain me why I should make my customer spend money for more disks while both 🙂 users are content with the app performance?
What value will my customer gain for that money? What would YOU tell him?
Interesting approach; as long as a hard drive never fails, the application runs and the customer is happy. However, if the hard drive fails, your application is down. Is that something your customer is going to be ok with? Especially when going to just a simple RAID 1 configuration means you can sustain operations even in the event of a drive failure?
May 14, 2009 at 7:48 am
Well, that's clear. I already mentioned RAID 1 as the only acceptable use of two drives (see my first post).
Anyway, how does it relate to giving mdf and ldf files on separate drives - which is the subject of this discussion?
May 14, 2009 at 7:52 am
It seems like you're pretty convinced there is no need to keep your data protected and in the best configured way possible so I don't want to start arguing about it...
To answer the direct questions you asked very briefly...
>>What value will my customer gain for that money?
You're in the DBA forum so I'll speak directly as if you are a DBA. It's your job to secure data and serve it to the business in the highest performing manner possible with what you have at your disposal. That is what the customer gains. Nothing less, nothing more but you doing your job very well.
>>What would YOU tell him?
After gauging the business growth to hardware utilization (which is a extremely high level way of saying, "I would spend weeks determinig how the company needs to secure the business data and plan for growth with minimal interuption and high scalability") I would propose the cons and pros of the changes and provide a precise budget requirement for the initial and long term objectives.
May 14, 2009 at 7:56 am
ondrej.bouda (5/14/2009)
Well, that's clear. I already mentioned RAID 1 as the only acceptable use of two drives (see my first post).Anyway, how does it relate to giving mdf and ldf files on separate drives - which is the subject of this discussion?
Some people are ok with suggesting the cheapest possible solution when it comes to hardware; putting MDF / LDF files on one drive and doing backups to the second drive, or using RAID-1 and putting everything on one mirrored drive, would fall into that category.
Most people who are ready to invest in SQL Server are also going to want to invest in a hardware solution that provides the best performance for SQL while also isolating the business from single points of failure. In this case, putting MDFs and LDFs on separate RAID drives (most commonly separate RAID-5) falls into that category.
Personally, I would never recommend a single hard drive for hosting the OS and SQL in a server, no matter how happy the customer seems to be at that point in time. They might be happy short-term, but they won't be happy when all the data they've been putting in is lost because of a hard drive crash.
May 14, 2009 at 8:21 am
Sorry, I'd like to stop our disputation about security/backups because:
1. It seems to get off-topic: this forum was intended to discuss the performance gains of separating MDFs and LDFs on different drives.
2. In the subject of security/backups, I'm of the same opinion as my opponents (maybe my English skill failed to make it clear).
I just wanted to point out that :
1. While some said that separating MDFs/LDFs is a must-have, there are still scenarios where the performance gain is so small that it is not worth the money, and
2. Separating those files means more that just buying one second drive (which alone would be quite inexpensive).
May 14, 2009 at 4:08 pm
Just one parting comment to a topic that seems to have gone to the dogs... There is a technical reason why Data and log files should be on a separate physical hard drives. And its not cost or performance it is simple physics.
The point of failure and performance in most hard drive is the mechanical swing arm - Unless you are using a solid state drive, and from the conversation so far I don't think that's an option. We all know the fine tolerances within a hard drive. Where is this going ...?
Simple, the IO characteristics for a data file, by the very nature of data requests and updates, is RANDOM. Lots of movement, many hundreds / thousands for requests from the server for data in no particular order. The IO characteristics for the log file is, by the very nature of what a log is, SEQUENTIAL. In comparision to the data drives there is less movement required to complete a log write than a data disk write request. Two different IO types on one disk - even RAIDED disks would seem to be in contention.
BTW this is an interesting read
http://www.eweek.com/c/a/Data-Storage/Hard-Disk-MTBF-Flap-or-Farce/
Also, I like my job as a DBA and plan to keep it...
CodeOn
😛
May 14, 2009 at 7:58 pm
Malcolm Daughtree (5/14/2009)
... There is a technical reason why Data and log files should be on a separate physical hard drives. And its not cost or performance it is simple physics.Simple, the IO characteristics for a data file, by the very nature of data requests and updates, is RANDOM. ...The IO characteristics for the log file is, by the very nature of what a log is, SEQUENTIAL. ...Two different IO types on one disk - even RAIDED disks would seem to be in contention.
Not to be oppositional or anything, but contention is a performance reason.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2009 at 9:03 pm
RBarryYoung (5/14/2009)
Malcolm Daughtree (5/14/2009)
... There is a technical reason why Data and log files should be on a separate physical hard drives. And its not cost or performance it is simple physics.Simple, the IO characteristics for a data file, by the very nature of data requests and updates, is RANDOM. ...The IO characteristics for the log file is, by the very nature of what a log is, SEQUENTIAL. ...Two different IO types on one disk - even RAIDED disks would seem to be in contention.
Not to be oppositional or anything, but contention is a performance reason.
Nah ! All Good :w00t: but I thought it funny that those opposed are trying to use cost instead of best practices to justify their comment. Oh well at least they are the ones keeping me employed...:-P when their systems come grinding to a halt.
CodeOn !
😛
May 15, 2009 at 12:40 am
Malcolm Daughtree (5/14/2009)
The IO characteristics for the log file is, by the very nature of what a log is, SEQUENTIAL.CodeOn
A single Log files is sequential. But what happens when there are 20 log files (of 20 databases) on the same physical disk. Don't they access the disk randomly? Don't they make the disk activity non-sequential? Will it not degrade the performance? (This is what I asked in the OP)
May 15, 2009 at 8:12 am
Suresh B. (5/15/2009)
Malcolm Daughtree (5/14/2009)
The IO characteristics for the log file is, by the very nature of what a log is, SEQUENTIAL.CodeOn
A single Log files is sequential. But what happens when there are 20 log files (of 20 databases) on the same physical disk. Don't they access the disk randomly? Don't they make the disk activity non-sequential? Will it not degrade the performance? (This is what I asked in the OP)
Log is cached also, but in other way that Data. But from the disk perspective yes, it accesses the disk randomly. I don't know your possibilities, but you can monitor the performance with perfmon (AVG disk Q length) , and can decide whats next...
May 15, 2009 at 8:58 am
Suresh B. (5/15/2009)
Malcolm Daughtree (5/14/2009)
The IO characteristics for the log file is, by the very nature of what a log is, SEQUENTIAL.CodeOn
A single Log files is sequential. But what happens when there are 20 log files (of 20 databases) on the same physical disk. Don't they access the disk randomly? Don't they make the disk activity non-sequential? Will it not degrade the performance? (This is what I asked in the OP)
The short answer is that the Log files interfere with each other's sequential disk scanning optimizations less than the data files interfere with them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply