Hi there

Memory utilisation in our production box gets as high as 98% each day. With Ingres occupying most of the memory, I would like to reduce how much it uses. Am I right in thinking Ingres pre-allocates all the memory it requires at startup?

Previously, Actian had recommended reducing the QSF pool size, as its utilisation was low. So I guess that's an obvious candidate to recover some memory.

Where should one start when wanting to reduce the amount of memory Ingres uses? I would like to reduce the installation's memory footprint by 5%.

Thanks
Steve

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

On Sep 9, 2020, at 10:06 PM, Steve <s.anderson.au@gmail.com> wrote:

Hi there

Memory utilisation in our production box gets as high as 98% each day. With Ingres occupying most of the memory, I would like to reduce how much it uses. Am I right in thinking Ingres pre-allocates all the memory it requires at startup?

No, it doesn't. It preallocates some things, such as the buffer cache. Other areas grow as
needed in steps: parser, optimizer, QEF, QSF, RDF memory.

Previously, Actian had recommended reducing the QSF pool size, as its utilisation was low. So I guess that's an obvious candidate to recover some memory.

Where should one start when wanting to reduce the amount of memory Ingres uses? I would like to reduce the installation's memory footprint by 5%.

I'd start by looking at the DMF buffer cache sizes / numbers to make sure that it's not overallocated. The other step would be to use trace point SC916
to print out the current sizes of the various ULM memory pools (these
are the ones that grow). You can also use the DBMS log, if enabled, to
see when the ULM pools grow.

Finally, I'd make sure that you're looking at memory actually in use by
the DBMS server, and not simply used as a file page cache.

Karl

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

Copy: info-ingres@lists.planetingres.org (Ingres and related product discussion forum)

Hi Steve. I would consider reducing DBMS cache. Do you know how to run a DM420 tracepoint which reports statistics for each cache.

Look at page calls versus hits. I aim for 90% to 95%. If you find a cache
with 100% hit rate then it may be a candidate for reduction. And perhaps
you have a cache which is not often used also can be reduced.

Paul
&

<div dir="auto">Hi Steve.  I would consider reducing DBMS cache.  Do you know how to run a DM420 tracepoint which reports statistics for each cache.<div dir="auto"><br></div><div dir="auto">Look at page calls versus hits. I aim for 90% to 95%.  If you
find a cache with 100% hit rate then it may be a candidate for reduction. And perhaps you have a cache which is not often used also can be reduced.</div><div dir="auto"><br></div><div dir="auto">Paul</div><div dir="auto">&amp;<br><div dir="auto"><br></

<div dir="auto"><br><div dir="auto"><br></div></div></div></div>

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

To: info-ingres@lists.planetingres.org (info-ingres@lists.planetingres.org)

If it's a Linux box, 'free' memory can be misleading. The Linux OS uses as much free memory as possible as I/O buffers.
If will free up more memory if needed.

Check the actual memory size of the DBMS processes. If the server is a dedicated Ingres DB server, it would be normal for Ingres to use most of it.

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@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@canada.ca / Tel: 613-298-1346
-----Original Message-----
From: info-ingres-bounces@lists.planetingres.org <info-ingres-bounces@lists.planetingres.org> On Behalf Of Steve
Sent: Wednesday, September 9, 2020 10:06 PM
To: info-ingres@lists.planetingres.org
Subject: [Info-ingres] Memory Utilisation 98%

Hi there

Memory utilisation in our production box gets as high as 98% each day. With Ingres occupying most of the memory, I would like to reduce how much it uses. Am I right in thinking Ingres pre-allocates all the memory it requires at startup?

Previously, Actian had recommended reducing the QSF pool size, as its utilisation was low. So I guess that's an obvious candidate to recover some memory.

Where should one start when wanting to reduce the amount of memory Ingres uses? I would like to reduce the installation's memory footprint by 5%.

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

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

On Thursday, September 10, 2020 at 12:18:04 PM UTC+10, Karl Schendel wrote:

I'd start by looking at the DMF buffer cache sizes / numbers to make sure that
it's not overallocated.

Thanks Karl. Would I do that by setting trace point DM420 that Paul suggested?

The other step would be to use trace point SC916
to print out the current sizes of the various ULM memory pools (these
are the ones that grow). You can also use the DBMS log, if enabled, to
see when the ULM pools grow.

Am I right to think most/all trace points can be set without needing to restart Ingres?

Also, if the DBMS log is not enabled (i.e. II_DBMS_LOG is not set), am I right in thinking I must specify the log file using the SET TRACE OUTPUT statement?

Is there any issue using the SET TRACE OUTPUT statement with multiple DBMS servers running? I see, for example, one can use %d and %p when setting II_DBMS_LOG.

Finally, I'd make sure that you're looking at memory actually in use by
the DBMS server, and not simply used as a file page cache.

I am naively looking at the RSS figure returned by prstat -t (installation is running on Solaris).

In wanting to tweak the memory footprint of Ingres, it's not so much that there is a problem with Ingres, it's just that Ingres is the only thing under my control, the server is managed by another outfit.

Thanks
Steve

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

On Thursday, September 10, 2020 at 12:56:03 PM UTC+10, Laframboise, André (BAC/LAC) wrote:

If it's a Linux box, 'free' memory can be misleading. The Linux OS uses as much free memory as possible as I/O buffers.
If will free up more memory if needed.

Thanks André. Installation is running on Solaris.

It's an interesting point you raise. Even though memory usage does get high, it doesn't seem to reach 100% or cause things to fail. I wonder however, if in the past jobs have failed due to memory usage.

It's a particular time of the day when the memory gets above 95% and there is a particular batch job runnning at that time. That job is not doing anything in the database; I may need to tweak how that batch job works.

Check the actual memory size of the DBMS processes. If the server is a dedicated Ingres DB server, it would be normal for Ingres to use most of it.

Yes, it's the box is a dedicated Ingres server.

Thanks
Steve

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

On Thursday, September 10, 2020 at 12:32:03 PM UTC+10, Paul White wrote:

Hi Steve. I would consider reducing DBMS cache. Do you know how to run a DM420 tracepoint which reports statistics for each cache.

Look at page calls versus hits. I aim for 90% to 95%. If you find a cache with 100% hit rate then it may be a candidate for reduction. And perhaps you have a cache which is not often used also can be reduced.

Thanks Paul. I will experiment with trace point DM420 God willing and see what I can deduce.

Thanks
Steve

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

On Sep 23, 2020, at 4:00 AM, Steve <s.anderson.au@gmail.com> wrote:

On Thursday, September 10, 2020 at 12:18:04 PM UTC+10, Karl Schendel wrote: >>
I'd start by looking at the DMF buffer cache sizes / numbers to make sure that
it's not overallocated.

Thanks Karl. Would I do that by setting trace point DM420 that Paul suggested?

Yes, or possibly easier, take a look at config.dat in $II_SYSTEM/ingres/files to see
what the cache settings are. If you have a shared cache running, you might
see private cache settings in config.dat, just ignore those.

The other step would be to use trace point SC916
to print out the current sizes of the various ULM memory pools (these
are the ones that grow). You can also use the DBMS log, if enabled, to
see when the ULM pools grow.

Am I right to think most/all trace points can be set without needing to restart Ingres?

It depends, but yes you can use SC916 without restarting

Also, if the DBMS log is not enabled (i.e. II_DBMS_LOG is not set), am I right in thinking I must specify the log file using the SET TRACE OUTPUT statement?

Is there any issue using the SET TRACE OUTPUT statement with multiple DBMS servers running? I see, for example, one can use %d and %p when setting II_DBMS_LOG.

Yes, you'll need to use SET TRACE OUTPUT. That sets the DBMS log for the server you're
connected to, if you have multiple servers you'll want to arrange to connect to each of them.

Finally, I'd make sure that you're looking at memory actually in use by
the DBMS server, and not simply used as a file page cache.

I am naively looking at the RSS figure returned by prstat -t (installation is running on Solaris).

Hmm, I've been away from Solaris too long, not sure if that will over-estimate or not.

You might try pmap and add up the mapped segment sizes, omitting shared libraries,
as a double check to see if prstat is in the right ballpark

In wanting to tweak the memory footprint of Ingres, it's not so much that there is a problem with Ingres, it's just that Ingres is the only thing under my control, the server is managed by another outfit.

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

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

It turns out another system which interacts with the database has many processes running on the same server and the number and size of those processes, changes with the number of users using that system. It seems these processes are the cause of the
memory utilisation approaching 100%. Solaris doesn’t seem to complain and Ingres seems to be consistent in its memory use - no surprises there.

From what I’ve read, it seems Solaris will attempt to utilise as much memory as possible, for example using all available free memory as a file system cache; others mentioned this in their answers. Thanks again for the answers.

Steve

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