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 they’re 2k) and possibly the odd users’ own private tables. I figured the memory saved could be reallocated to the 8k cache. Is that a reasonable idea?

Steve

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 they’re 2k) and possibly the odd users’ 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

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 they’re 2k) and possibly the odd users’ 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

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?

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

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

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

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

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@lists.planetingres.org <info-ingres-bounces@lists..planetingres.org> On Behalf Of Steve
Sent: Friday, September 10, 2021 2:17 PM
To: info-ingres@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@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