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
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: