[Info-ingres] 4k Cache

[Steve]

Roy Hann wrote:
> It's not unreasonable, but generally I think Ingres is abstemious 
> to a fault with DMF memory. Freeing up what is probably only a very 
> small amount of memory won't make much difference.

OK, interesting. 

I possibly phrased my question poorly. I guess I want to know, in moving all the tables to 8K, should I be allocating more memory to the 8k cache and should I expect a performance hit if I don’t?
  
> I am not the world's biggest fan of 4k pages. Owing to the larger 
> per-row overhead of "large" pages you might find you waste less disk 
> space if you just use 8k pages for everything. 

OK.

> You don't mention what you are expecting to get from larger page sizes. 
> They do enable ALTER TABLE which is handy, but if you are 
> using locking (and you will be) rather than MVCC you might find page 
> sizes bigger than 2k aggravate any lock-contention you may have. 

Moving the indexes to 4k and the tables to 8k was Actian's recommendation to benefit from improved query performance, not available with 2k tables (I’m paraphrasing). They seem to be suggesting that separate table and index caches may improve performance.

Due to the Y2K38 fix in Ingres 11, 2k pages seem to be inefficient (like what you are saying about 4k pages) and at least one table is slowly approaching the hard limit for the number of rows a 2k table can hold.

I guess the proof will be in the pudding re locking contention. We haven’t seen any locking issues in test, but that may not be a great gauge.

>From my perspective, I know I can push this change through, as it's recommended by Actian.

MVCC is a whole nother story (conversation).