11.3.2: default -omsize & high number of MTL_OM latches

Posted by toomaskask on 16-Oct-2014 10:40

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

OpenEdge RDBMS - Forum

All Replies

Posted by Richard Banville on 16-Oct-2014 11:17

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.
 
 
 
 
[collapse]
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.

[/collapse]

Posted by gus on 17-Oct-2014 12:55

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.

Posted by S33 on 17-Oct-2014 13:10

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?

Posted by Richard Banville on 17-Oct-2014 13:32

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
 
 
[collapse]
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.

[/collapse]

This thread is closed