[Info-ingres] 4k Cache

[Fajok, Artur, (ProService Finteco)]

Hi Guys,
Some time ago (on Ingres ~9) I've made quite extensive performance testing on different page sizes. 8k pages (for tables and indexes) were largest improvement from 2k, 4k was in between. Larger sizes gave no real improvement in OLTP nor batch processing. 
Cache was set on "huge" preset, and enlarging it was degrading performance.

Regards,
Artur Fajok

-----Original Message-----
From: info-ingres-bounces at lists.planetingres.org <info-ingres-bounces at lists.planetingres.org> On Behalf Of Steve
Sent: Friday, September 10, 2021 2:17 PM
To: info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] 4k Cache

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).
_______________________________________________
Info-ingres mailing list
Info-ingres at lists.planetingres.org
https://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.planetingres.org%2Fmailman%2Flistinfo%2Finfo-ingres&amp;data=04%7C01%7Cartur.fajok%40psat.pl%7Ca23603fd1d1e49abe99d08d9745551f9%7Cee7c45a7678745ba9b9f2f3b38bdbf05%7C0%7C0%7C637671113707823431%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&amp;sdata=f7Kx6pJN2B4EIeHXnEenQ7%2BsyhhDubeVDtjOL1%2BEzTI%3D&amp;reserved=0