11.3.2: default -omsize & high number of MTL_OM latches - Forum - OpenEdge RDBMS - Progress Community

11.3.2: default -omsize & high number of MTL_OM latches

 Forum

11.3.2: default -omsize & high number of MTL_OM latches

  • Hi!

    Just checked one of our databases and found that number of MTL_OM latches is pretty high:

    _Latch-Name _Latch-Lock _Latch-Wait     latch/sec
    MTL_OM       4483906701        6844 4,483,906,701
    MTL_OM       4483914640        6844         7,939
    MTL_OM       4483922014        6844         7,374
    MTL_OM       4483927715        6844         5,701
    MTL_OM       4483933176        6844         5,461
    MTL_OM       4483937684        6844         4,508
    MTL_OM       4483941763        6844         4,079
    MTL_OM       4483948088        6844         6,325
    MTL_OM       4483969421        6844        21,333
    MTL_OM       4484037072        6844        67,651
    MTL_OM       4484104383        6844        67,311
    MTL_OM       4484112012        6844         7,629
    MTL_OM       4484117447        6844         5,435
    MTL_OM       4484185073        6844        67,626
    MTL_OM       4484206032        6844        20,959
    MTL_OM       4484327023        6844       120,991
    MTL_OM       4484353623        6844        26,600

    Actual number of _storageobject records is 1600.

    Should I set -omsize to 2048 and expect something? Lower load? Increase in speed?

    Because of nature of our systems, it's almost impossible to set up full proper test environment, so I can change parameters only in production system.

    wbr,

    Toomas

    Experience is what you get when you don't get what you want.

  • To avoid om “paging”, you can increase the -omsize at runtime using the increaseto options of proutil.
     
    To avoid OM latching, yes, increase the –omsize at startup.
     
    All data access to the database (table, index, lob) goes through a mapping layer to determine the location of the object being requested.
    Objects that do not fit in the primary om cache will spill over to the secondary om cache.
    The secondary cache can page entries and maintains an LRU list of items in the cache.
     
    If everything fits in the primary om cache, there is no latching.
    Any lookup in the secondary cache requires latching for synchronization.  Thus all data mapping of objects whose mapping  information is in the secondary cache is single threaded.  This can cause a bottleneck when accessing data.
     
    It is not possible to tell you the expected performance gain for your deployment however, it is something that should be addressed.
     
     
     
     
    From: toomaskask [mailto:bounce-toomaskask@community.progress.com]
    Sent: Thursday, October 16, 2014 11:41 AM
    To: TU.OE.RDBMS@community.progress.com
    Subject: [Technical Users - OE RDBMS] 11.3.2: default -omsize & high number of MTL_OM latches
     
    Thread created by toomaskask

    Hi!

    Just checked one of our databases and found that number of MTL_OM latches is pretty high:

    _Latch-Name _Latch-Lock _Latch-Wait     latch/sec
    MTL_OM       4483906701        6844 4,483,906,701
    MTL_OM       4483914640        6844         7,939
    MTL_OM       4483922014        6844         7,374
    MTL_OM       4483927715        6844         5,701
    MTL_OM       4483933176        6844         5,461
    MTL_OM       4483937684        6844         4,508
    MTL_OM       4483941763        6844         4,079
    MTL_OM       4483948088        6844         6,325
    MTL_OM       4483969421        6844        21,333
    MTL_OM       4484037072        6844        67,651
    MTL_OM       4484104383        6844        67,311
    MTL_OM       4484112012        6844         7,629
    MTL_OM       4484117447        6844         5,435
    MTL_OM       4484185073        6844        67,626
    MTL_OM       4484206032        6844        20,959
    MTL_OM       4484327023        6844       120,991
    MTL_OM       4484353623        6844        26,600

    Actual number of _storageobject records is 1600.

    Should I set -omsize to 2048 and expect something? Lower load? Increase in speed?

    Because of nature of our systems, it's almost impossible to set up full proper test environment, so I can change parameters only in production system.

    wbr,

    Toomas

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.

  • code to check:

    define variable prev-latches as integer.

    repeat:

      find _latch where _latch-name = "MTL_OM".

      display _Latch-Name

              _Latch-Lock   /* # times latch acquired */

              _Latch-Wait   /* # time conflict occurred */

              _Latch-Lock - prev-latches label "latch/sec".

              prev-latches = _Latch-Lock.

      pause 1.

    end.

    code to figure out what to set -omsize to:

    select count(*) from _storageobject.

    the number of storage objects you have is how many object mappings you need. allow some (say 15% or 20%) extra for headroom.

  • gus --

    So is that resulting number of objects (from "select count...") what you set the -omsize to, or do you have ti multiply by some mem size constant like 1024k to get the actual parameter you need?

  • It is the exact number since –omsize is the number of entries in the primary and secondary om caches, not the amount of memory allocated.
     
    When adding new objects online, the mapping information will be stored in the secondary om cache – regardless if there is space in the primary cache or not.  Access to the secondary cache is always latched regardless of the –omsize.
     
    Setting the –omsize larger than the result of select count(*) from _storageobject will allow newly added objects (tables, indexes, lobs) to be stored in the primary (non-latched) part of the om cache the NEXT time the database is brought up.   Based on how often you add new object you might want to set the –omsize a bit larger than the current count.  Other than that, there is no need to set it larger.
     
    Cache paging in the secondary will only occur when both the primary and secondary caches are filled up.  If –omsize is set as described, that should not happen unless you have just added many new unaccounted for object to the database (say after a load .df).
    _________________________________
    Richard Banville
    Fellow, OpenEdge Development

    PROGRESS SOFTWARE CORPORATION

    14 Oak Park | Bedford, MA 01730 | USA
    DIRECT  +1 781 280 4875
    richb@progress.com
     
     
    From: S33 [mailto:bounce-S33@community.progress.com]
    Sent: Friday, October 17, 2014 2:10 PM
    To: TU.OE.RDBMS@community.progress.com
    Subject: RE: [Technical Users - OE RDBMS] 11.3.2: default -omsize & high number of MTL_OM latches
     
    Reply by S33

    gus --

    So is that resulting number of objects (from "select count...") what you set the -omsize to, or do you have ti multiply by some mem size constant like 1024k to get the actual parameter you need?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.