[Info-ingres] Memory Utilisation 98%

[Karl Schendel]

> On Sep 9, 2020, at 10:06 PM, Steve <s.anderson.au at 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