Re: db block size, too big wasting buffer?

In article <qZb*SzMrp_at_news.chiark.greenend.org.uk>, you said (and I quote):

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

In theory, and if your application only ever handled one row at a time for each user transaction, and if your data was ideally spread so that for sure no two users would ever access rows that were anywhere near each other, and if all inserts happened to different areas of the disk, and if updates all happened on different rows guaranteed to live in opposite ends of the partition, yes, what you said is exactly true.

However, don't forget two very important things: LRU is for blocks, not rows. And you will never get an ideal distribution of data across your entire application. That's reality. We gotta stick to it, it has a darn habit of creeping back in, like it or not...

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

Bingo. ;-)

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam