New MessagesAuthor: Sheldon Bird (essbird@state.me.us) Subject: Database I/O Comment (July Sys Admin) For Jim McKinstry: Information-packed article, good basic primer on concepts, but it has some critical omissions. One possibly dangerous omission for less sophisticated readers regarding cache: Many DBMS require that data be written to disk in the order that it was committed, so that rollback can occur properly. Writeback cache can involve neat algorithms like elevator logic, where data is written out of sequence in order to make efficient use of head travel -- things like that. If you call for support on a corrupt database and have write cache enabled, they're apt to turn you away. Some battery-backed cache controllers are approved by some DBMS vendors. I never use write cache. Beware. An excellent but aging newsgroup thread on RAID, cache, and other I/O issues is at:
http://www.ozemail.com.au/~pfred/faq/raid.txt Oracle also has some excellent I/O white papers. A few quotes and a bibliography would be really helpful to readers wanting to learn more. It's a REALLY complex issue about which you can never know enough. There are other issues worthy of brief mention in an article like this so that readers can at least be aware that more research may be required. Examples are that special database files like transaction logs, before-image files etc. are written to sequentially and constantly, and merit their own spindles. Just doing this on a busy database server can give more performance boost than a lot of higher profile issues like RAID and cache.
Sheldon Bird
Sheldon, Thanks for the feedback. I love to hear from people that have read an article that I wrote and have taken the time to think about what was written. I'd like to address your issues:
1. "Many DBMS require that data be written to disk in the order that it was committed... Write back cache can involve neat algorithms..." RAID devices that implement write-back cache "guarantee" that the availability, consistency, and reliability of data is identical to magnetic disk. As long as you are buying from a reputable company, there should be no issues. I'm sure that the company line from DB companies states that they'd like their databases to be stored on magnetic media but then two of the big ones (Oracle and Sybase) have strategic alliances with EMC (one of the largest storage providers in the industry). EMC, for example, has what's called "fast writes", which use write-back cache technology. Why are Oracle and Sybase aligned with EMC if they don't support write-back cache? I took a look at the newsgroup thread that you referenced. That data is 4-5 years old. The technology has advanced quite a bit since then. In 1999, any RAID device that does not emulate a "normal" hard drive to the operating system, no matter what technology is behind curtains, should not be in the market.
2. "An excellent but aging newsgroup thread on RAID" I'd be real careful about what's in this newsgroup thread. It is about 4-5 years old, and the technology is much matured now. Also, the info can be downright wrong. Here's one blatantly incorrect quote (I just skimmed the thread, there may be more): "RAID 0 YES, RAID 1 NO (after all Raid 1 is mirroring, not really RAID)" What does this guy think the "R" stands for in RAID? It's stands for redundant. Mirroring is about as redundant as it gets.
3. "Oracle also has some excellent I/O white papers." I have read a lot of white papers (from Oracle, Sybase, RAID vendors, etc.) as well as Oracle Performance Tuning (O'Reilly and Associates) over the past few years and have not run into the write-back cache issue you stated. If you can send me a white paper discussing this problem, I'd love to see it.
4. "A few quotes and a bibliography would be really helpful to readers wanting to learn more." I used my experience in the industry to compose this article, so quotes would be inappropriate. I also feel that quotes frequently get in the way and that some people use them simply to give themselves more credibility. Feel free to quote my article though! As for a bibliography, good point. I would recommend Oracle Performance Tuning as a good place to start (for Oracle DBAs).
5. "There are other issues worthy of brief mention in an article like this so that readers can at least be aware that more research may be required. Examples are that special database files like transaction logs, before-image files etc. are written to sequentially and constantly, and merit their own spindles" Very true. If there is no RAID in the picture then definitely isolate these types of DB files on their own disks and their own I/O channel if possible. Unfortunately, while many of these special files are re-creatable, the loss of one of these files means downtime for the database. These files should be protected by RAID, if at all possible, and cache or solid state disks should be used to get the performance needed (in a perfect world of course!).
Thanks again for the feedback. I really appreciate it. The goal of my article was to give people enough information to go forward and evaluate products with some base of information. Hopefully, I've done that.
Jim McKinstry |