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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

To: [email protected] ([email protected])

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 [email protected] / T�l. : 613-298-1346

Senior Database Advisor, Information Management and Information Technology Branch
Library and Archives Canada / Government of Canada
[email protected] / Tel: 613-298-1346
-----Original Message-----
From: [email protected] <[email protected]> On Behalf Of Steve
Sent: Thursday, July 23, 2020 8:40 AM
To: [email protected]
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
[email protected] https://lists.planetingres.org/mailman/listinfo/info-ingres

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Steve wrote:

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.

That makes sense. My preference for 8kb pages versus 16kb or even 32kb
pages is slight. In some cases the row size will force you to choose a
bigger page. In some cases severe lock contention will encourage
smaller pages. (I bet one could use up days and weeks making
exquisitely precise measurements to decide which is best for any given
table.)

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?

No reason at all. It will work.

The reason 4kb pages might not be a win is because page sizes above
2kb have a bigger per-row overhead to support row versioning and
row-level locking. If you had a 2kb table with big rows that
only just fit the page you would get one row per page. Because of
the row overhead you might still get only one row per 4kb page and you'd
get a lot of waste. You end up needing the same number of pages. But
because the pages are twice as big you use twice the disk space.

On the other hand very short rows, each with a big row overhead, can
also mean using more disk space. Secondary indexes will often have very
short rows (unless the keys are big or it is a covering index with
non-key columns in it).

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

The argument for bigger pages is more getting more bang for your I/O
buck. Even very fast electromechanical disks struggle to sustain
more than about 120 IOs per second once their cache is flooded. That is sloooooooooooooooooow. These days you are probably using SSD though,
which is going to be ~100 times faster. But you are still going to have
to go through layers of drivers and caches and context switches and...I
lose the will... So bigger pages get more data into (and out of) memory
faster.

The down side is when a big page with lots of data in it is locked for a
long time, a lot of data is locked. Potentially concurrency can suffer.

In reality most systems I see have baked-in lock contention; the page
size makes it neither better nor worse. In which case I'd say: default
to a big page size and revert to a smaller page size only when forced
into it.

Roy

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Roy Hann wrote:

Following up on my own post, I said:

The argument for bigger pages is more getting more bang for your I/O
buck.

Andr� reminds us bigger pages enable things you simply can't do with 2kb
pages! It's not just about performance.

Roy

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

To: [email protected] ([email protected])

SGkgU3RldmUsDQoNCkNhdGFsb2dzIGFyZSB0eXBpY2FsbHkgOGssIGJ1dCB0aGlzIHdvdWxkIGJl IHZlcnNpb24gZGVwZW5kZW50LiBZb3UgY2FuIGZpbmQgb3V0IGZvciBzdXJlIGJ5IHF1ZXJ5aW5n IGlpdGFibGVzLg0KDQpzZWxlY3QgZGlzdGluY3QgdGFibGVfcGFnZXNpemUgZnJvbSBpaXRhYmxl c1xnDQoNCkl0J3MgY2VydGFpbmx5IHJlYXNvbmFibGUgdG8gcmVkdWNlIHRoZSAyayBjYWNoZSBh bmQgZXhwYW5kIHRoZSA4ayBpbiBpdHMgcGxhY2UsIGJ1dCB5b3UgbWF5IGxpa2UgdG8gbW9uaXRv ciB3aXRoIGRtNDIwIHRvIGdldCBzb21lIGNhY2hlIHVzYWdlIHN0YXRzIGJlZm9yZSBnb2luZyB3 aWxkLg0KDQpNYXJ0aW4gQm93ZXMNCg0KLS0tLS1PcmlnaW5hbCBNZXNzYWdlLS0tLS0NCkZyb206 IFN0ZXZlIDxzLmFuZGVyc29uLmF1QGdtYWlsLmNvbT4gDQpTZW50OiAwOSBTZXB0ZW1iZXIgMjAy MSAxNToxNQ0KVG86IGluZm8taW5ncmVzQGxpc3RzLnBsYW5ldGluZ3Jlcy5vcmcNClN1YmplY3Q6 IFJlOiBbSW5mby1pbmdyZXNdIDRrIENhY2hlDQoNCkdvZCB3aWxsaW5nLCBhcyBtZW50aW9uZWQg YWJvdmUsIEkgcGxhbiB0byBpbmNyZWFzZSB0aGUgcGFnZSBzaXplIG9mIGFsbCB1c2VyIHRhYmxl cyBhbmQgaW5kZXhlcyB0byA4ayBhbmQgNGssIHJlc3BlY3RpdmVseS4gQ3VycmVudGx5IGJvdGgg YXJlIDJrLiBUaGlzIHdhcyBhIHJlY29tbWVuZGF0aW9uIGJ5IEFjdGlhbi4NCg0KQSBzbWFsbCBw b3J0aW9uIG9mIHRoZSB0YWJsZXMgYXJlIGFscmVhZHkgOGs7IHRoYXQgYmVpbmcgdGhlIGRlZmF1 bHQgcGFnZSBzaXplLg0KDQpVcG9uIG1ha2luZyB0aGVzZSBjaGFuZ2VzLCBJIHdhcyB0aGlua2lu ZyBvZiByZWR1Y2luZyB0aGUgMmsgYnVmZmVyIGNhY2hlIGJ5IDc1JSwgYXMgdGhlIG9ubHkgMmsg dGFibGVzIGF0IHRoYXQgcG9pbnQgd291bGQgYmUgdGhlIHN5c3RlbSBjYXRhbG9ncyAoSSB0aGlu ayB0aGV54oCZcmUgMmspIGFuZCBwb3NzaWJseSB0aGUgb2RkIHVzZXJz4oCZIG93biBwcml2YXRl IHRhYmxlcy4gSSBmaWd1cmVkIHRoZSBtZW1vcnkgc2F2ZWQgY291bGQgYmUgcmVhbGxvY2F0ZWQg dG8gdGhlIDhrIGNhY2hlLiBJcyB0aGF0IGEgcmVhc29uYWJsZSBpZGVhPw0KDQpTdGV2ZSAgDQpf X19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fXw0KSW5mby1pbmdy ZXMgbWFpbGluZyBsaXN0DQpJbmZvLWluZ3Jlc0BsaXN0cy5wbGFuZXRpbmdyZXMub3JnDQpodHRw czovL2xpc3RzLnBsYW5ldGluZ3Jlcy5vcmcvbWFpbG1hbi9saXN0aW5mby9pbmZvLWluZ3Jlcw0K

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Roy,

I had to look up "abstemious" ...


Michael Leo

Kettle River Consulting Inc

612-859-2108

[email protected]

________________________________
From: [email protected] <[email protected]> on behalf of Roy Hann <[email protected]>
Sent: Thursday, September 9, 2021 9:44 AM
To: [email protected] <[email protected]> Subject: Re: [Info-ingres] 4k Cache

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

_______________________________________________
Info-ingres mailing list
[email protected] https://lists.planetingres.org/mailman/listinfo/info-ingres

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-7">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">
Roy,</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">

</div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255);">
I had to look up &quot;abstemious&quot; ...</div>
<div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">

</div>
<div id="Signature">
<div>
<div id="divtagdefaultwrapper" dir="ltr" style="font-size: 12pt; font-family: Calibri, Helvetica, sans-serif; color: rgb(0, 0, 0);">
<p style="margin-top: 0px; margin-bottom: 0px;">Michael Leo</p>
<p style="margin-top: 0px; margin-bottom: 0px;">Kettle River Consulting Inc</p> <p style="margin-top: 0px; margin-bottom: 0px;">612-859-2108</p>
<p style="margin-top: 0px; margin-bottom: 0px;">[email protected]</p>
</div>
</div>
</div>
</div>
<div>
<div id="appendonsend"></div>
<div style="font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">

</div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size: 11pt;"><b>From:</b> [email protected] &lt;[email protected]&gt; on behalf of Roy Hann &lt;specially@
processed.almost.meat&gt;<br>
<b>Sent:</b> Thursday, September 9, 2021 9:44 AM<br>
<b>To:</b> [email protected] &lt;[email protected]&gt;<br>
<b>Subject:</b> Re: [Info-ingres] 4k Cache</font>
<div>&nbsp;</div>
</div>
<div class="BodyFragment"><font size="2"><span style="font-size:11pt">
<div class="PlainText">Steve wrote:<br>

&gt; God willing, as mentioned above, I plan to increase the page size of<br> &gt; all user tables and indexes to 8k and 4k, respectively. Currently both<br> &gt; are 2k. This was a recommendation by Actian.<br>
&gt; <br>
&gt; A small portion of the tables are already 8k; that being the default<br> &gt; page size.<br>
&gt; <br>
&gt; Upon making these changes, I was thinking of reducing the 2k buffer cache<br>
&gt; by 75%, as the only 2k tables at that point would be the system catalogs<br>
&gt; (I think they�re 2k) and possibly the odd users� own private tables. I<br> &gt; figured the memory saved could be reallocated to the 8k cache. Is that<br> &gt; a reasonable idea?<br>

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

I am not the world's biggest fan of 4k pages. Owing to the larger<br>
per-row overhead of &quot;large&quot; pages you might find you waste less disk<br>
space if you just use 8k pages for everything.<br>

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

Please let us know how you get on. <br>

Roy<br>

_______________________________________________<br>
Info-ingres mailing list<br>
[email protected]<br>
<a href="https://lists.planetingres.org/mailman/listinfo/info-ingres">https://lists.planetingres.org/mailman/listinfo/info-ingres</a><br>
</div>
</span></font></div>
</div>
</body>
</html>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

To: [email protected] ([email protected])

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 <[email protected]>
Sent: 10 September 2021 11:06
To: [email protected]
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
[email protected] https://lists.planetingres.org/mailman/listinfo/info-ingres

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

To: [email protected] ([email protected])

This is a multi-part message in MIME format. --------------Qz5loRECEsfAQa8QKpZo1Wxf
Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit

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.

DM420 Summary
Tue Aug 10 23:59:01 2021 8K 15147542269 14270336064 81203000 Tue Aug 10 23:59:01 2021 16K 199351 163232 0 Wed Aug 11 23:59:01 2021 2K 2288674635 2145159221 14345379 Wed Aug 11 23:59:01 2021 4K 138029033284 116068917854 7373573391 Wed Aug 11 23:59:01 2021 8K 15230093493 14344747905 84427376 Wed Aug 11 23:59:01 2021 16K 199821 163420 0 Thu Aug 12 23:59:01 2021 2K 2297930809 2153676516 14526936 Thu Aug 12 23:59:01 2021 4K 138675202115 116598765877 7414216733 Thu Aug 12 23:59:01 2021 8K 15304264245 14415316317 84572496 Thu Aug 12 23:59:01 2021 16K 200131 163544 0 Fri Aug 13 23:59:01 2021 2K 2318102537 2172851157 14666209 Fri Aug 13 23:59:01 2021 4K 139303943428 117126194520 7446085449 Fri Aug 13 23:59:01 2021 8K 15387181712 14493897241 84776536 Fri Aug 13 23:59:01 2021 16K 200586 163726 0 Sat Aug 14 23:59:01 2021 2K 2320799021 2175289002 14666209 etc


The hourly summary is stored in /tmp looks like this
Tue Aug 10 05:59:01 2021 4K 136895848951 115119235768 7313740965 Tue Aug 10 05:59:01 2021 8K 15084022752 14211247981 80677416 Tue Aug 10 05:59:01 2021 16K 199126 163142 0 Tue Aug 10 06:59:01 2021 2K 2275300590 2133421181 13842732 Tue Aug 10 06:59:01 2021 4K 136926793703 115146579337 7313836100 Tue Aug 10 06:59:01 2021 8K 15085432932 14212554643 80679795 Tue Aug 10 06:59:01 2021 16K 199126 163142 0 Tue Aug 10 07:59:01 2021 2K 2275535207 2133631420 13842732 Tue Aug 10 07:59:01 2021 4K 136953509680 115169631080 7314479057 ...

If there is unusual activity I can go back to the full log to
investigate time of day. DM420.log is in ingres/files saved for later investigations. I take the hourly outputs and paste them into Excel to calculate differences and make some pretty graphs.

See attached file dm420.txt with two scripts embedded you might be
useful. Please excuse primary school shell programming.

run_dm420_summ runs daily.  This pulls out the last 122 lines of just the 11.59pm event.

echo "DM420 Summary" >> $daylog
run_dm420_summ -l 122 >> $daylog

dm420 runs hourly. It assumes II_DBMS_LOG is enabled. I reset it once a week to keep the log manageable

ingprenv...

II_DBMS_LOG=/home/ingres/IngresII/ingres/files/ii_dbms210905000001.%p.log

run_dm420 grabs the last xxx lines of the most recently modified ii_dbms*.log

You may choose to modify this line...

tail -280 $logname |

to make sure you can capture the whole event depending on the number of caches you have active.

Paul


--------------Qz5loRECEsfAQa8QKpZo1Wxf
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<pre>Hi Steve,</pre>
<pre>I run DM420 each hour on all my machines and find no performance hit. </pre>
<pre>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.
</pre>
<pre>DM420 Summary
Tue Aug 10 23:59:01 2021 8K 15147542269 14270336064 81203000 Tue Aug 10 23:59:01 2021 16K 199351 163232 0 Wed Aug 11 23:59:01 2021 2K 2288674635 2145159221 14345379 Wed Aug 11 23:59:01 2021 4K 138029033284 116068917854 7373573391 Wed Aug 11 23:59:01 2021 8K 15230093493 14344747905 84427376 Wed Aug 11 23:59:01 2021 16K 199821 163420 0 Thu Aug 12 23:59:01 2021 2K 2297930809 2153676516 14526936 Thu Aug 12 23:59:01 2021 4K 138675202115 116598765877 7414216733 Thu Aug 12 23:59:01 2021 8K 15304264245 14415316317 84572496 Thu Aug 12 23:59:01 2021 16K 200131 163544 0 Fri Aug 13 23:59:01 2021 2K 2318102537 2172851157 14666209 Fri Aug 13 23:59:01 2021 4K 139303943428 117126194520 7446085449 Fri Aug 13 23:59:01 2021 8K 15387181712 14493897241 84776536 Fri Aug 13 23:59:01 2021 16K 200586 163726 0 Sat Aug 14 23:59:01 2021 2K 2320799021 2175289002 14666209 etc


The hourly summary is stored in /tmp looks like this
Tue Aug 10 05:59:01 2021 4K 136895848951 115119235768 7313740965 Tue Aug 10 05:59:01 2021 8K 15084022752 14211247981 80677416 Tue Aug 10 05:59:01 2021 16K 199126 163142 0 Tue Aug 10 06:59:01 2021 2K 2275300590 2133421181 13842732 Tue Aug 10 06:59:01 2021 4K 136926793703 115146579337 7313836100 Tue Aug 10 06:59:01 2021 8K 15085432932 14212554643 80679795 Tue Aug 10 06:59:01 2021 16K 199126 163142 0 Tue Aug 10 07:59:01 2021 2K 2275535207 2133631420 13842732 Tue Aug 10 07:59:01 2021 4K 136953509680 115169631080 7314479057 ...

</pre>
<p>If there is unusual activity I can go back to the full log to
investigate time of day. DM420.log is in ingres/files saved for
later investigations. I take the hourly outputs and paste them
into Excel to calculate differences and make some pretty graphs. <br>
</p>
See attached file dm420.txt with two scripts embedded you might be
useful. Please excuse primary school shell programming.
<pre>
run_dm420_summ runs daily.  This pulls out the last 122 lines of just the 11.59pm event.</pre>
<pre>echo "DM420 Summary" &gt;&gt; $daylog
run_dm420_summ -l 122 &gt;&gt; $daylog
</pre>
<pre>
</pre>
<pre class="moz-quote-pre" wrap="">dm420 runs hourly. It assumes II_DBMS_LOG is enabled. I reset it once a week to keep the log manageable
</pre>
<pre>ingprenv...</pre>
<pre>II_DBMS_LOG=/home/ingres/IngresII/ingres/files/ii_dbms210905000001.%p.log</pre>
<pre>
run_dm420 grabs the last xxx lines of the most recently modified ii_dbms*.log</pre>
<pre>You may choose to modify this line...

tail -280 $logname |</pre>
<pre>to make sure you can capture the whole event depending on the number of caches you have active.
</pre>
<pre>Paul
</pre>
<br>
</body>
</html>
--------------Qz5loRECEsfAQa8QKpZo1Wxf--

L2hvbWUvdXRpbHMvcnVuX2RtNDIwDQoNCiMhL2Jpbi9zaA0KLiAvaG9tZS91dGlscy9pbmdy ZXNfZW52DQoNCnNxbCBpaWRiZGIgPDwgZW9mID4vZGV2L251bGwNCnNldCB0cmFjZSBwb2lu dCBkbTQyMCBcZw0KY29tbWl0IFxnDQpccQ0KZW9mDQoNCmxvZ25hbWU9YGxzIC0xdCAkSUlf U1lTVEVNL2luZ3Jlcy9maWxlcy9paV9kYm1zKi5sb2d8aGVhZCAtMWANCnRhaWwgLTI4MCAk bG9nbmFtZSB8DQpuYXdrICcNCkJFR0lOICAge3A9MH0NCi90cmFjZSBwb2ludCBkbTQyMC8g ICAgIHtwPTF9DQpwPT0xICAgIHtwcmludH0NCicgPiAkSUlfU1lTVEVNL2luZ3Jlcy9maWxl cy9kbTQyMC50bXANCg0KY2F0ICRJSV9TWVNURU0vaW5ncmVzL2ZpbGVzL2RtNDIwLnRtcCA+ PiAkSUlfU1lTVEVNL2luZ3Jlcy9maWxlcy9kbTQyMC5sb2cNCg0KDQovaG9tZS91dGlscy9y dW5fZG00MjBfc3VtbQ0KOg0KIyBUd28gZmlsZXMgZ2VuZXJhdGVkIGluIC90bXANCiMNCiMg ZG00MjBzdW1tMS4kJCBob3VybHkgZXh0cmFjdA0KIyBkbTQyMHN1bW0yLiQkIGRhaWx5IGV4 dHJhY3QgYmFzZWQgb24gMjM6NTkNCiMNCiMgMTZqdW4yMDE2IFBXIHVzZSBJSV9TWVNURU0u IG9wdGlvbnMgYXJlIG1hbmRhdG9yeSBub3cuIEFkZCBvcHRpb24gdG8gZ2V0IGxhc3QgWCBy b3dzLiBzdGRvdXQuDQojDQp1c2FnZSgpDQp7DQplY2hvIHJ1bl9kbTQyMF9zdW1tIFstTXwt bSByZWNpcGllbnRdIFstbCBudW1saW5lc10gWy1mIGRtNDIwbG9nXQ0KZWNobyAiIg0KZWNo byAtTSBkZWZhdWx0IGVtYWlsIGFkZHJlc3MgaXMgc2hpZnQ3LCBkZWZhdWx0IG51bWxpbmVz IGlzIGFsbA0KZWNobyBkZWZhdWx0IGRtNDIwbG9nIGlzICRJSV9TWVNURU0vaW5ncmVzL2Zp bGVzL2RtNDIwLmxvZw0KfQ0KDQppZiBbICJJSV9TWVNURU0iID0gIiIgXSA7IHRoZW4NCiAg ICAgICAgLiAvaG9tZS91dGlscy9kZWZhdWx0X2Vudg0KICAgICAgICAuIC9ob21lL3V0aWxz L2RlZmF1bHRfaW5ncmVzDQpmaQ0KDQptYWlsaXQ9MA0KbWFpbHRvPXNoaWZ0Nw0KdGFpbD1j YXQNCmRtNDIwbG9nPSRJSV9TWVNURU0vaW5ncmVzL2ZpbGVzL2RtNDIwLmxvZw0KdG1wMT0v dG1wL2RtNDIwc3VtbTEuJCQNCnRtcDI9L3RtcC9kbTQyMHN1bW0yLiQkDQoNCndoaWxlIGdl dG9wdHMgTW06bDpmOiBjDQpkbw0KICAgIGNhc2UgJGMgaW4NCiAgICBtKSAgICAgIG1haWxp dD0xO21haWx0bz0kT1BUQVJHOzsNCiAgICBNKSAgICAgIG1haWxpdD0xOzsNCiAgICBsKSAg ICAgIHRhaWw9InRhaWwgLSRPUFRBUkciOzsNCiAgICBmKSAgICAgIGRtNDIwbG9nPSRPUFRB Ukc7Ow0KICAgIFw/KSAgICAgdXNhZ2UNCiAgICAgICAgICAgIGV4aXQgMjs7DQogICAgZXNh Yw0KZG9uZQ0KDQpjYXQgJGRtNDIwbG9nIHwgbmF3ayAnDQpCRUdJTiAgIHtkdD0iIg0KICAg ICAgICBjYWNoZT0iIn0NCg0KL2lpZGJkYi8gICAgICAgIHtkdD1zdWJzdHIoJDAsMiwyNCkg O25leHR9DQovU3VtbWFyeSBTdGF0aXN0aWNzLyAgICB7DQogICAgICAgIGNhY2hlPXN1YnN0 cigkMCwyNCwzKQ0KICAgICAgICBuZXh0DQogICAgICAgIH0NCg0KL0ZJWCBDQUxMUy8gew0K ICAgICAgICBpZiAoIGNhY2hlICE9ICIiICkgew0KICAgICAgICAgICAgICAgIGdldGxpbmU7 IGZpeD1zdWJzdHIoJDAsMTAsMTUpOyBoaXQ9c3Vic3RyKCQwLDMxLDE1KTsNCiAgICAgICAg ICAgICAgICBnZXRsaW5lDQogICAgICAgICAgICAgICAgZ2V0bGluZQ0KICAgICAgICAgICAg ICAgIGdldGxpbmUNCiAgICAgICAgICAgICAgICBnZXRsaW5lDQogICAgICAgICAgICAgICAg Z3JlYWQ9c3Vic3RyKCQwLDk0LDE1KQ0KICAgICAgICAgICAgICAgIHByaW50IGR0ICIgIiBj YWNoZSAiICIgZml4ICIgIiBoaXQgIiAiIGdyZWFkDQogICAgICAgICAgICAgICAgY2FjaGU9 IiINCiAgICAgICAgfQ0KfQ0KJyA+ICR0bXAxDQpncmVwICIyMzo1OSIgJHRtcDEgPiAkdG1w Mg0KDQppZiBbICIkbWFpbGl0IiA9ICIxIiBdIDsgdGhlbg0KICAgICAgICAoDQojICAgICAg IGNhdCBkbTQyMHN1bW0udG1wIHwgZ3ppcCAtYyB8IHV1ZW5jb2RlIGRtNDIwc3VtbS50eHQu Z3oNCiAgICAgICAgJHRhaWwgJHRtcDINCiAgICAgICAgKXwgbWFpbHggLXNkbTQyMCAkbWFp bHRvDQplbHNlDQogICAgICAgICR0YWlsICR0bXAyDQpmaQ==

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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: [email protected] <[email protected]> On Behalf Of Steve
Sent: Friday, September 10, 2021 2:17 PM
To: [email protected]
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
[email protected] 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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)