From Newsgroup: comp.databases.ingres

God willing, as mentioned above, I plan to increase the page size of all user tables and indexes to 8k and 4k, respectively. Currently both are 2k. This was a recommendation by Actian.
A small portion of the tables are already 8k; that being the default page size. Upon making these changes, I was thinking of reducing the 2k buffer cache by 75%, as the only 2k tables at that point would be the system catalogs (I think theyrCOre 2k) and possibly the odd usersrCO own private tables. I figured the memory saved could be reallocated to the 8k cache. Is that a reasonable idea?
Steve
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

Hi Steve,

Catalogs are typically 8k, but this would be version dependent. You can find out for sure by querying iitables.

select distinct table_pagesize from iitables\g

It's certainly reasonable to reduce the 2k cache and expand the 8k in its place, but you may like to monitor with dm420 to get some cache usage stats before going wild.

Martin Bowes

-----Original Message-----
From: Steve <s.anderson.au@gmail.com>
Sent: 09 September 2021 15:15
To: info-ingres@lists.planetingres.org
Subject: Re: [Info-ingres] 4k Cache

God willing, as mentioned above, I plan to increase the page size of all user tables and indexes to 8k and 4k, respectively. Currently both are 2k. This was a recommendation by Actian.

A small portion of the tables are already 8k; that being the default page size.

Upon making these changes, I was thinking of reducing the 2k buffer cache by 75%, as the only 2k tables at that point would be the system catalogs (I think theyrCOre 2k) and possibly the odd usersrCO own private tables. I figured the memory saved could be reallocated to the 8k cache. Is that a reasonable idea?

Steve
_______________________________________________
Info-ingres mailing list
Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

Steve wrote:

God willing, as mentioned above, I plan to increase the page size of
all user tables and indexes to 8k and 4k, respectively. Currently both
are 2k. This was a recommendation by Actian.

A small portion of the tables are already 8k; that being the default
page size.

Upon making these changes, I was thinking of reducing the 2k buffer cache
by 75%, as the only 2k tables at that point would be the system catalogs
(I think theyore 2k) and possibly the odd userso own private tables. I figured the memory saved could be reallocated to the 8k cache. Is that
a reasonable idea?

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.

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.

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.

Please let us know how you get on.

Roy

--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

On Friday, September 10, 2021 at 12:28:10 AM UTC+10, Martin Bowes wrote:

Hi Steve,

Catalogs are typically 8k, but this would be version dependent. You can find out for sure by querying iitables.

select distinct table_pagesize from iitables\g

It's certainly reasonable to reduce the 2k cache and expand the 8k in its place, but you may like to monitor with dm420 to get some cache usage stats before going wild.

Martin Bowes

Thanks Martin, I like your phrase "...before going wild." :)

Could running trace point DM420 cause a drag on the system and can I leave it running (for days), or should I run it for specific periods?
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

Hi Steve,
Trace point dm420 is just a quick snapshot of the existing buffer stats at the time you execute it.
So it has no impact on the system. Also you don't turn it on and leave it on, it's a matter of execute, wait, execute. Typically I would suggest a day between executions.
The default output channel is II_DBMS_LOG, but this can be altered if you do as follows:
set trace output '/full/path/to/file';
set trace point dm420;
set trace nooutput;
Typically I'd connect to iidbdb to do this, but you could use any database. Best of luck,
Marty
-----Original Message-----
From: Steve <s.anderson.au@gmail.com>
Sent: 10 September 2021 11:06
To: info-ingres@lists.planetingres.org
Subject: Re: [Info-ingres] 4k Cache
On Friday, September 10, 2021 at 12:28:10 AM UTC+10, Martin Bowes wrote:

Hi Steve,

Catalogs are typically 8k, but this would be version dependent. You can find out for sure by querying iitables.

select distinct table_pagesize from iitables\g

It's certainly reasonable to reduce the 2k cache and expand the 8k in its place, but you may like to monitor with dm420 to get some cache usage stats before going wild.

Martin Bowes

Thanks Martin, I like your phrase "...before going wild." :)
Could running trace point DM420 cause a drag on the system and can I leave it running (for days), or should I run it for specific periods?
_______________________________________________
Info-ingres mailing list
Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

Steve wrote:

Could running trace point DM420 cause a drag on the system

If it is already absolutely on its knees you might measure the drag but
you won't notice it. Don't worry about it.

can I leave it running (for days), or should I run it for specific
periods?

If you leave it running for days it will fade to grey goo. It's best to
use it just while you are running a job of considerable intensity that
you are hoping to improve. (There's no point getting hung up on what may
or may not be happening in the background. You are looking for profound improvements because minor improvements will never pay back. If the
improvement is profound it should shine through the noise of background activity.)

Roy
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

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

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 (IrCOm 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 havenrCOt 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).
--- Synchronet 3.21b-Linux NewsLink 1.2

From Newsgroup: comp.databases.ingres

Paul White wrote:

Hi Steve,
I run DM420 each hour on all my machines and find no performance hit.
I have a daily report, just tracking FIX CALLS, HITS and GREADIOS. You can imagine there is a lot of variation through the day. eg: Business hours transactions Vs Nightly batch updates and data warehouse type rebuilds.

Oh wow, you're handing it to me on a platter! Thanks Paul.
...and Roy and Martin.

Steve
--- Synchronet 3.21b-Linux NewsLink 1.2