• Memory Utilisation 98%

    From Steve@21:1/5 to All on Wed Sep 9 19:06:27 2020
    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)
  • From Karl Schendel@21:1/5 to Ingres and related product discussi on Wed Sep 9 22:17:20 2020
    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)
  • From Paul White@21:1/5 to Steve on Thu Sep 10 12:29:54 2020
    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)
  • From =?iso-8859-1?Q?Laframboise=2C_Andr=@21:1/5 to Steve on Thu Sep 10 02:55:27 2020
    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 Donnes, Direction gnrale de la gestion de l'information et Technologie de l'information
    Bibliothque et Archives Canada / Gouvernement du Canada andre.laframboise@canada.ca / Tl. : 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)
  • From Steve@21:1/5 to All on Wed Sep 23 01:00:13 2020
    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)
  • From Steve@21:1/5 to All on Wed Sep 23 01:58:16 2020
    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)
  • From Steve@21:1/5 to Paul White on Wed Sep 23 02:06:14 2020
    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)
  • From Karl Schendel@21:1/5 to Ingres and related product discussi on Wed Sep 23 08:57:44 2020
    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)
  • From Steve@21:1/5 to All on Thu Sep 9 01:12:36 2021
    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)