Latch Concepts

Database latches are low level locks on a memory structure or other resource. Latches serialize access to resources so that multiple processes cannot access the resource simultaneously. Allowing multiple processes to write to the same database block would corrupt that block.

Latches are not directly controlled by 4GL/ABL code. Instead they are handled at a much lower level by the OpenEdge runtime engines. This doesn't mean that you can't alter your code to be more latch friendly, just that you cannot programmatically control individual latches.

Tuning Latch Waits

OpenEdge provides a set of database startup parameters to control the latching behavior. Some increase the number of available structures for a certain latch type and some control how clients respond to a latch being in use by other clients.

Use ProTop or promon to examine the amount and types of latch waits your application generates. Sample during peak times to focus your attention on the most important latches to tune.

Spinning and napping

OpenEdge provides parameters to control how many times a process will retry a latch (-spin) and the maximum amount of time it will sleep before starting another spin cycle (-napmax). Changing -napmax from the default can greatly increase your CPU utilization and should only be used after all other options are exhausted.

There are many different calculations and suggestions floating around to properly set the value of -spin. They are all wrong, including my suggested value of 1000 as a starting point. The reason I suggest 1000 as a starting point is that it will provide a benefit over the default of 0 without consuming excessive amounts of CPU.

Properly setting -spin depends on your hardware and the specifics of your application. Aggressively increasing -spin without addressing the underlying issues can quickly become counterproductive as you spend more and more CPU cycles hammering the latches instead of doing productive work.

Object cache latches (OM)

Object cache refers to the internal cache that OpenEdge uses to cross reference database objects (tables, indexes, LOBs) to their specific storage locations. You can determine the actual value used by looking at your startup scripts and the database log file.

To determine if you need to adjust the -omsize parameter compare the output from the query below to the current value of your omsize parameter.

select count(*) from _storageobject.

If the count of storage objects is larger than your current omsize setting change the parameter to avoid any OM latch activity.

Buffer pool latches (BHT,BF*)

If your application is experiencing a high number of buffer pool latch timeouts there are a few things to investigate before you increase the -spin setting.

First make sure that the record activity is reasonable for the amount of work being done. Poorly indexed queries will cause excessive buffer pool activity. Correcting the problem queries will provide better overall benefits by reducing the amount of work being done and increasing the efficiency of the buffer pool.

If you have certain application functions that must read a large amount of data consider assigning private buffer pools to isolate them from the rest of your application. You can get more information about private buffers and other tuning options on the Buffer Pools page.

If you have small lookup tables that are being read heavily you should consider placing those tables in the alternate buffer pool. If they are already in the alternate buffer pool and you are still having issues investigate the queries accessing those tables. You can also investigate implementing caching at the application level to prevent a large percentage of those reads.

LRU chain latches (LRU,LR2,LR*)

Alternate buffer pool

If the alternate buffer pool (ABP) is experiencing LRU latching your -B2 setting is not large enough. The ABP should be sized large enough to hold all of the objects assigned without overflowing. The entire purpose of the ABP is to avoid any LRU overhead.

Primary buffer pool

If the primary buffer is experiencing LRU related latch timeouts you should first consider the suggestions from the buffer pool latches section. Once those steps have been taken you can consider using the -lruskips parameter.

The OpenEdge buffer pool uses a LRU chain to keep track of which buffers have been least recently used. This chain controls which buffers are evicted when more space is needed in the buffer pool ("oldest" first). By default each time a buffer block is accessed it is moved to the top of the chain

The lruskips parameter alters this logic by forcing a buffer block to be accessed a certain number of times (-lruskips value) before it is moved to the top of the chain. A reasonable starting point is -lruskips 100. You will need to monitor the performance before and after the change to verify that this actually has a positive effect.