Re: How to generate a lot of load on an Oracle database?

  • From: Mark Brinsmead <mark.brinsmead@xxxxxxx>
  • To: BSpears@xxxxxxxxxxxxxxxxx
  • Date: Fri, 18 Nov 2005 11:28:51 -0700

Spears, Brian wrote:

I am told that most of the io just just written to memory (cache) so that the speed of the underlying disk is not an issue.

Ouch! That's probably only half-true. If that. Who told you this? Somebody selling disk arrays, I'll bet... ;-)

Yes, with may/most disk arrays, I/Os are performed (first) against cache. But *all* I/Os have to go to disk eventually, so the size/speed/configuration of the disks certainly does remain an issue.

Yes, Disk Arrays ("SANs") are coming equipped with bigger and bigger caches. Yes, cache is good. But as a rule (and with some limited exceptions), cache cannot be relied up to indefinitely sustain I/O levels greater than the underlying disks can support. This is especially true for write operations -- reads are one of the limited exceptions, provided the cache in the disk array is something like 10x to 50x the size of the memory buffers used on your server. (In cases where you have multi-gigabyte database block caches, this is unlikely.)

Disk drives are physical devices, and can only perform a limited number of random I/Os per second. If you have a truly huge READ cache (i.e., at least 10x your database buffers) your disk array can probably keep up a 90% + cache hit rate indefinitely, and thus make the disks appear to be much faster than they really are, however, it will take time to "load" the cache. Until the cache is populated, I/O will occur only at the (nominal) native speed of the disks.

When you are performing random WRITEs, there is comparatively little the cache can do for you. In the best case, it can allow the disk array to re-order the write requests to make them "less random" -- this can reduce the size and frequency of disk seeks (and *maybe* even reduce the effects of rotational latency). While this can allow disks to (appear to) perform more random I/Os per second than they are really able to, if you over-drive the I/O rates for the disk hard enough and long enough, the cache *will* fill up. When the cache becomes full of pending writes, I would expect everything to slow down to about the native speed of the underlying disks.

In some cases, you may have to work hard to make this happen. In other instances (e.g., if you build a 7-TB RAID-5 stripe out of 500GB IDE disks, and then try to put multiple ERP databases on it, as one person I know contemplates doing) you're likely to find this painfully easy to achieve...

Note: I doubt that all disk arrays re-order writes -- although I expect most probably will these days. When you "allow" your disk array to re-order writes, you are placing a *large* amount of faith in the non-volatility of the array's cache. If the cache (or maybe even firmware) fails, you stand to be restoring a *lot* of data from backups -- assuming you realise you have a problem in time. (*That* is a different discussion, though...)

Anyway, as far as I'm concerned, disk arrays are great. (As long as we can really trust them.) But they are not "magical", despite the best efforts of their manufacturers to convince us otherwise.

-- Mark Brinsmead

P.S. These comments are based on "theory" (or maybe even speculation). It's been years since I have done any serious I/O benchmarks. I do, however, still know how to read a datasheet and compute (estimate) "Random IOs per Second" for a disk. If anybody has solid benchmark results (particularly measuring Random IOs per Second over a long period of time) I would be quite interested in seeing them...

Other related posts: