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
