[Info-ingres] 4k Cache

[Laframboise, André (BAC/LAC)]

Hi Steve,

I moved most tables to non-2K page size for 2 main reasons.

Row level locking, anything but 2K pages enables row level locking.
Keep the total number of pages for a table below the Ingres limit for very big tables.

I usually keep the Index page size the same as the table.
I don't see any benefit to using 4K indexes with 2k tables.
 

André Laframboise

Conseiller Principal Base de Données, Direction générale de la gestion de l'information et Technologie de l'information
Bibliothèque et Archives Canada / Gouvernement du Canada
andre.laframboise at canada.ca / Tél. : 613-298-1346

Senior Database Advisor, Information Management and Information Technology Branch
Library and Archives Canada / Government of Canada
andre.laframboise at canada.ca / Tel: 613-298-1346
-----Original Message-----
From: info-ingres-bounces at lists.planetingres.org <info-ingres-bounces at lists.planetingres.org> On Behalf Of Steve
Sent: Thursday, July 23, 2020 8:40 AM
To: info-ingres at lists.planetingres.org
Subject: Re: [Info-ingres] 4k Cache

On Thursday, 9 July 2020 18:49:06 UTC+10, Roy Hann  wrote:
> Steve wrote:
> 
> > Recently the 4k DMF cache was enabled across our Ingres environments.
> > 
> > I notice in CBF, for our production environment, the cache type is 
> > "shared"  vs in our development environment, where the cache type is 
> > "private ( owner=(default) )." The respective lines from the 
> > production and development configuration files are shown below:
> > 
> > Production config.dat: 
> > ii.<server>.dbms.private.*.cache.p4k_status: OFF
> > ii.<server>.dbms.shared.cach_def.cache.p4k_status: ON
> > 
> > Development config.dat: 
> > ii.<myserver>.dbms.private.*.cache.p4k_status: ON
> > 
> > Is the difference due to the fact that production has four DBMS 
> > servers vs the single server in development?
> 
> Yep.
> 
> In principle an iidbms server process can serve all your databases. If 
> you have more than one server process they need to have a coherent 
> view of the cached data, so they need to share the cache.
> 
> Back in the steam age it was claimed--though I doubt it was ever 
> experienced by any human user--that having a private cache eliminated 
> the need to negotiate for access. Being private was claimed to give a 
> performance benefit. (Personally I never saw a system where that would 
> have been anywhere near the top of the list of performance drags.
> 
> Roy

Thanks, that's interesting, particularly what you said about generating appropriate stats.

Regarding the 4k cache, almost all tables in the database have 2k pages. Actian recommended the tables be changed to 8k and the secondary indexes to 4k. Actually, that brings up another question, I was considering changing the indexes to 4k first and sometime later changing the tables to 8k. Is there any reason to think 2k tables cannot have 4k secondary indexes?

What are the benefits, reasons for using 16 or higher page sizes?

Steve
_______________________________________________
Info-ingres mailing list
Info-ingres at lists.planetingres.org
https://lists.planetingres.org/mailman/listinfo/info-ingres