Re: db block size, too big wasting buffer?

Howard J. Rogers <dba_at_hjrdba.com> wrote:
>
>"sg" <s4v4g3_at_europe.com> wrote in message
>news:3D18ACAA.7050308_at_europe.com...
>> hi
>>
>> when we read a row from a segment do we read the whole block which that
>> row is located into buffer cache??
>>
>
>Yes we do. But that's not a 'waste'. It *would* be a waste if, having read
>vast amounts of data in which we were not interested into memory, it stayed
>in memory for ever more, untouched and unloved.
>
>But that's not how caches work. There's the small matter of the LRU list,
>which means that if some of the data *you* didn't want to read is found
>useful by someone else, it will stay in the cache, but if no-one is
>interested in it, it ages out, and the memory freed for different data.
>
>With a big block, the probability that someone else will find the block you
>caused to be read into memory goes up ('cos it's got more data in it). So,
>whilst *you* may have done extra work with the initial read of the block, in
>doing so, you've helped many more people coming after you.

I don't follow this at all. How can an LRU cache possibly be more efficient if it's retiring and promoting data at a coarse granularity? Given there's only finite space in the cache, and that's less than the entire dataset, you ideally only want the most frequently accessed data in the cache.

The fundamental assumption of LRU caches is that if you've used a datum before, you're likely to use it again. Not that if you read an arbitrary bunch of stuff into the cache, it might come in useful at some point.

When a row (which we can consider a datum for an RDBMS), the *entire* block is pushed to the MRU end of the queue, including the data that have not been touched. They're just tagging along, there's no reason to consider them as worthy of a place in the cache as the datum that was accessed. True, they might get accessed later, in which case we're lucky, but they might not, in which case we've wasted the space which could have been better used for other data.

Therefor, I believe the LRU would be most efficient for cache-hits if each row were treated as an individual entity (however big it was). However, there's very good reasons to handle the data as fixed-size blocks, rather than single rows. Given that, surely a block size of close to the average row length would give the best LRU use?

Before anybody thinks I'm actually advocating 512 byte blocks, I stress this is a pedantically picky point on efficient LRU use, and there are many other factors in block-size than just buffer cache hits...

>That all ignores the other physical issue. If you read less than the optimum
>amount of data into the cache (ie, not matching the file system buffer), you
>aren't "saving" memory, but increasing I/O work on the system.

...for example. Received on Wed Jun 26 2002 - 05:38:40 CDT