-B and -L Database startup parameter values recommendations

-B and -L Database startup parameter values recommendation
How to analyze the current values of -B and -L
How to evaluate the values of -B and -L
How to plan the values of -B and -L
What is the recommended value for Database Buffers (-B)
What is the recommended value for Lock Table Entries (-L)

The reason this Article focuses on Database Buffers (-B) and Lock Table Entries (-L), is that these parameters have the largest influence on the shared memory the Operating System allocates to the Database. There are other database startup parameters which affect shared memory sizing such as -B2, -L, -Mxs, -shmsegsize, connection table, tablerangesize, indexrangesize, aibufs, bibufs, -omsize

1.   Database Buffers (-B)

This is the largest contribution to the shared-memory size: Database Buffers (-B) x Database Blocksize (-blocksize)

A ballpark figure of -B is 10% of the database size, Operating System and available memory constraints notwithstanding.

Review the Current Total buffers and Used buffers
When using the secondary or Alternate Buffer Pool (-B2) details can be found:
Sample: Buffer Hits
90% is considered a good percentage under typical load.  
There is a "sweet point" for -B, unique to every environment and application, whereby increasing -B any further has little effect and then rapidly starts dropping 

1. When the Buffer Pool is so large that there is not enough remaining memory for all the other "stuff", this causes the system to start paging. When the Buffer Pool is paged, things become very bad very quickly. Blindly raising -B settings can cause a process to crash with " ** stget: out of storage. (6498)" when the system is paging and the current value is still not large enough to accommodate the largest table that is being read into shared memory, for example.
2. When there is enough memory to accommodate the large Buffer Pool without paging, a 2 - 5 second (+ i/o related delays) at Checkpoint time is observed with very large buffer pools, due to the time taken to flush the bi blocks and scanning the large -B buffer pool looking for dirty buffers.
3. When the Database is started on Linux which allows memory over-commit other processes (or this Broker) will be terminated by the oom-killer to stabilize kernel panic.  Refer to Article  Would consuming all memory bring other databases down?  

Whatever the number of database buffers is allocated, ensure that it's going to be available for this process for the duration of the execution of the process to avoid process termination due to memory depletion. Keep an eye on swap in parallel. If for example, there is another memory intensive process running at the same time, then these processes will compete for memory. If there's enough memory available, this does not present a problem. For further discussion refer to Article:

• 00020141, What value should -B be set to?   

1.   Lock Table Entries (-L)

Since OpenEdge 10.1B lock table entry size is 64 bytes per lock table entry

•  What is the amount of memory used by each lock table entry? 

Review lock table entries in use out of the total lock table entries

•  How many Lock Tables entries are being used for a database?

Metrics to focus on for -L are:
The goal is to ensure that users are getting the correct number of locks for their queries and that the number of lock being upgraded is minimal. Record contention could also show up here.

• Exclusive Waits, Rec Get Waits, and Share Waits
  Any values in these Wait metrics are an indication that users are waiting to get record locks. 
• Upgrade Waits 
  Indicate that a user has gotten a record to update and must first wait for another user to release the record.
• Upgrade Requests
  Indicate that the record is not being retrieved appropriately in the first place. These are only requests to upgrade from a Share Lock to an Exclusive Lock.  A higher number of Lock Table entries only implies that more users will be able to lock more records. Increasing -L will not help in lowering lock contentions. Lock contentions are an application code related problem. 
  •  How to fix Lock Table Overflow errors?   

 

References to Written Documentation:

Progress Database Administration Guide and Reference, "PART II Administration" 14. Managing Performance