ADSM-L

A little database performance foo

2006-10-21 17:01:04
Subject: A little database performance foo
From: Jason Lee <english AT ANIM.DREAMWORKS DOT COM>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Sat, 21 Oct 2006 14:00:06 -0700
Well,

I've beed trying my damnedest to get some actual performace out of my
database here and have come to  the conclusion that IBM was not lying
to me and that the database is, in fact, not all it might be. It also
appears, though I stand to be corrected, that the buffer pool cache
hit rate is a somewhat dubious number. It would be nice if someone
from IBM could weigh in on this....

All database accesses (to disk) are serial and synchronous. That is,
each IO has to complete before the next is dispatched. Therefore, the
maximum number of IOs that the database can sustain (to disk) is 1/
average latency, or about 300 per second for a nice array and 250 for
a not nice array. Each of those IOs is a single 4K database page,
working out to a maximum bandwidth of ~1.2-1.4 MB/s

I've tested this every which way, under different loads, fewer and
more volumes, one big LUN, multiple small LUNs. It doesn't seem to
matter what you do, that's all you get. Needless to say, an enormous
Shark or EMC does not seem to  be necessary in the TSM world (storage
pools and reliability not withstanding). In fact, 3 15K spindles will
probably do it just fine - I'll try that later.

As for the buffer pool numbers, I don't know exactly how the
internals work (nobody will tell me) but this is what I have been told.

There are two threads that actually access the database on disk, one
reader and one writer. All their operations are serial and
synchronous and they read and write to the buffer pool. The reader
thread does it's best to pre-fill the buffer pool with what it
assumes you will want. The writer thread looks for changes that have
occurred in the buffer pool and writes them out to disk. I.E. all
database activity takes place in the buffer pool.

A buffer pool miss occurs when you request something that has not
been pre-fetched into the buffer.

Here's a situation.

I have N clients all starting at the same time. They all are going to
request ~100MB of data (or at least that is the number being reported
by q session as bytes sent when they have been running for a while).
The clients are running on very lightly loaded boxes and can consume
anything the server can throw at them (or anything it's likely to
throw, anyway) from the database.

Seems to me, at this starting point, there is *nothing* of relevance
(excluding the odd index or two) in the buffer pool. Everything is
going to be a buffer miss. Now, the pre-fetcher is doing it's best to
load that stuff in, but I have gigs of data I'm looking for, and it's
feeding me at 1.3MB/s. I'm sure the data direct from the DB is not
the data sent to the client, but I would suggest that the sizes are
not wildly disparate.

Under this load, how can it be that there is *ever* anything useful
in the buffer pool, unless the clients are being throttled in some
way to allow the pre-fetcher to stay ahead? If that throttle is the
CPU (more time being given to the pre-fetcher thread), then getting
more horsepower will decrease my cache hit percentage as the DB
clients get more time to make request, but the pre-fetcher is already
maxed out (because of the 300 I/O limit).

This all suggests to me that on a heavily loaded box the buffer hit
rate is perhaps artificially high, and the true numbers can only be
achieved when the box has spare cycles and the requesting threads
have "time" to twiddle their thumbs.

Does this make sense, or have I been up too long?

Any thought, comments, words of wisdom or discussion would be
gratefully acknowledged.



Thanks


Jason


--
Jason Lee
DreamWorks Animation
(818) 695-3782

<Prev in Thread] Current Thread [Next in Thread>