There's a venerable piece of advice that runs thus: "To speed up SQL Anywhere file I/O put some or all of the busy files on different physical drives: database files, transaction log, temporary file, etc." with the idea being that "more heads are better than one" when reading and writing data from different files.
In the modern age of RAID, SANs and NAS, however, the notion of "separate physical drive" has pretty much disappeared... or at least, it is no longer under the control of developers: it's all just one big logical X drive now.
Is the notion of "separate physical drive" still important, or not?
Any and all answers are welcome, even if they are simply pointers to "the literature".
My opinion is in general yes: it is not necessary anymore, for the individual case the answer is: it depends on the hardware configuration you have.
Just my 5 cents on this topic:
The hardware configuration of the SAN is important. You can have a SAN with huge RAID sets below, which means your data is spread out through multiple drives which is advantageous, or you can have a SAN with e.g. 5400 rpm harddrives because your IT department wanted to spare some money, which might not bring you the performance you wanted to achieve.
For each RAID you also always have to look at this magic number: the stripeset size - if choosen wisely the exactly same RAID can be 3 times faster compared to the non optimal setting. The default nowadays is 64k which means for each block read from a disk the RAID will read the complete stripeset (which might be good if you want to access the following blocks too). This can be disadvantageous if you have a lot of random access and read always only small blocks - heh this sounds like a database application ;-)
Next question you have to ask is, who else (which application) is using the SAN, you might have your own partition or even RAID set on a SAN but still the SAN switch and HBAs might be shared across multiple applications bringing side effects to you.
And for security reasons I would place the transaction logs on a different RAID set than the database files.
answered 04 Oct '10, 08:01
I would argue that yes it is still important.
You are correct that it is becoming harder and harder for anyone, including developers, to tell at a glance whether the two (or more) drives presented to you in the user environment are two logical drives on one hardware setup or two logical drives on different hardware. The two drives could be on one RAID array, or they could each have their own RAID array. Without opening the RAID software it is not apparent.
SANs and NAS have the same issue: are your two logical drives on the same SAN / NAS using the same cabling and the same location, or are they split up physically.
You really have to work with the the people responsible for setting up the hardware to express your needs clearly. Explain that it is not important that the drives are merely separate, you need to know which drives of the ones available are representing physically different hardware.
If you can store each of the heavily accessed files on physically different hardware I think you will continue to see a performance increase.
In addition to speed, having these files on different hardware will also limit your exposure if one piece of hardware fails.
There are some caveats of course. It would not help to put the files on different physical hardware at all costs in every situation. I would rather have all of the files on a server's RAID array if the only available other option is a 100mbps NAS unit.
Then you can get into the arguments about which type of RAID or storage is best for which types of files and file access, depending on how they are read and written, but that is probably for another day.
So yes, given the right circumstances and the right physical hardware it is still important.
I would be interested as to whether the Sybase folks have established recommendations for this type of thing.
answered 02 Oct '10, 15:27