Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

How to size monitor and manage the Alternate Buffer Pool (-B2)

« Go Back

Information

 
Article Number000049583
EnvironmentProduct: OpenEdge
Version: 10.2B, 11.x
OS: All supported platforms
Other: Database, Alternate Buffer Pool
Question/Problem Description
How to size the Alternate Buffer Pool (-B2)?
What value does the database startup parameter -B2 need to be set to for the objects assigned?
How to monitor if -B2 LRU2 is enabled?
When Is LRU2 enabled or disabled?
How to disable LRU2 alternate buffer pool replacement policy online?
Can the size of the Alternate Buffer Pool be increased online?
How to change LRU2 force skips -LRU2SKIPS online?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
While there is no universally applicable right answer for what should be located in the secondary buffer pool, the point of using the secondary buffer pool is two fold:
  • Reduce disk i/o by keeping more data in memory
  • Reduce LRU chain, CPU and locking overhead as the secondary buffer pool does not use LRU2 chain when all the objects located in if fit completely.
Once the Alternate Buffer Pool assignments have been undertaken at the database object and/or Storage Area level, an Alternate Buffer Pool scoping exercise is needed.

Monitoring with _tablestat/_indexstat VST's is only reliable where object READS are concerned. Object WRITES for example in the Alternate Buffer Pool will reserve -B2 space and may result in the LRU2 being enabled if undersized.

Initial sizing of -B2 and therefore when the LRU2 mechanism is enabled, which one aims to prevent, needs to be undertaken in a controlled fashion:
  1. For LOBS, load lobs into a test database with a variable length database extent, then divide the file extent size on disk by the database blocksize.
  2. For Tables, either by loading each "Alternate Buffer Pool" table to a variable length database extent, then dividing the file extent size on disk by the database blocksize, or relying on _tablestat-OSread values after a full table scan.
  3. For Indexes, while _indexstat-OSread could similarly be used, it is easier to simply find the number of index blocks occupied through an IDXANALYS report. The number of blocks for a given index are reported in the "INDEX BLOCK SUMMARY FOR AREA" section under the BLOCKS header.
Example:
 
$  PROUTIL dbname -C idxanalys
 
There are 40941 index blocks for all the Customer indexes.
 
INDEX BLOCK SUMMARY FOR AREA "Cust_Index" : 10 
------------------------------------------------------- 
Table          Index  Fields Levels  Blocks    Size  % Util  Factor
PUB.Customer                                                       
  Comments         9       1      4   19250  133.1M    88.8     1.2
  CountryPost     10       2      3    6834   47.5M    89.2     1.2
  CustNum          8       1      3    5874   40.8M    89.2     1.2
  Name            11       1      3    4747   33.0M    89.2     1.2
  SalesRep        12       1      3    4236   29.4M    89.2     1.2

 
The same information (in more detail) can be obtained through the IDXBLOCK report: 
 
Example:  
 
$ PROUTIL -C idxblock Customer.Name
 
There are 1 root, 11 nonleaf and 4735 leaf = 4747 index blocks for the Customer.Name index.
 
BlockSize = 8192  Block Capacity = 8100
               Number   Length      On  Length   Delete
                   of       of  Delete      of    Chain   Percent
DBKEY  Level  Entries  Entries   Chain    Size     Type  Utilized
            
11279      1       11      212       0       0     root         2
10818      2      458     7290       0       0  nonLeaf        90
10819      2      452     7282       0       0  nonLeaf        89
10365      2      454     7275       0       0  nonLeaf        89
 9910      2      441     7285       0       0  nonLeaf        89
 9468      2      445     7286       0       0  nonLeaf        89
 9022      2      454     7288       0       0  nonLeaf        89
56660      2      453     7280       0       0  nonLeaf        89
57114      2      448     7285       0       0  nonLeaf        89
57563      2      451     7286       0       0  nonLeaf        89
58015      2      455     7284       0       0  nonLeaf        89
58471      2      224     3582       0       0  nonLeaf        44
                                                                 
11277      3     1374     7290       0       0     leaf        90
11278      3     1249     7289       0       0     leaf        89
11276      3     1260     7290       0       0     leaf        90
...         
58692      3     1246     7288       0       0     leaf        89
58693      3     1263     7289       0       0     leaf        89
58694      3      553     3245       0       0     leaf        40


In OpenEdge 11.6.3 the to facilitate the sizing of -B2, the PROUTIL -C viewB2 output includes the size (in blocks) of each object assigned to each storage pool and a total for the area.  This sizing include all blocks associated with each object - including free blocks.   If the -csoutput Option is used, this additional data are included.
 
For example a "FOR EACH" would not load free blocks, where running a DBANALYS report would load free blocks and this could lead to undersizing the Alternate Buffer Pool.
 
Area "Customer/Order Area":8 - Primary Buffer Pool 

Object Enablement    Size     Type   Object Name 
-----------------  -------- -------  ------------ 
Default                   7  Master   Area.Control-Object:0 
Default                   8   Table   PUB.Customer:2 
Default                  16   Table   PUB.Order:4 
Default                  32   Table   PUB.Order-Line:5 
Default                   8   Index   PUB.Customer.Sales-Rep:16 
Default                   8   Index   PUB.Order.Cust-Order:21 
                   -------- 
                         79 

Monitoring and Managing:

LRU2 enabled/disabled?
PROMON > R&D > 2 Activity Displays > 3 Buffer Cache 
  • "LRU2 replacement policy disabled"
  • "LRU2 replacement policy enabled"

Increase -B2 online

PROUTIL -C INCREASETO executable allows -B2 to be increased online.

Disable LRU2 online
PROMON > R&D > 4 Administrative Functions > 4 Adjust Latch Options > 3. Disable LRU2 alternate buffer pool replacement policy
 
LRU2SKIPS
PROMON > R&D > 4. Administrative Functions > 4. Adjust Latch Options > 5. Adjust LRU2 force skips
 
The change is not written out to the database lg file, but can be confirmed in:
PROMON > R&D > 1. Status Displays > 7. Buffer Cache

Example:
Status: Buffer Cache
Total buffers: 3002 
Hash table size: 887 
Used buffers: 1704 
Empty buffers: 1298 
On lru chain: 3001 
On lru2 chain: 0 
On apw queue: 0 
On ckp queue: 0 
Modified buffers: 6 
Marked for ckp: 0 
Last checkpoint number: 1 
LRU force skips: 50  
LRU2 force skips: 75 <-- lru2skips 
Workaround


 
Notes
References to Other Documentation:

OpenEdge Data Management: Database Administration, Managing Performance, Alternate Buffer Pool

Progress Article(s):

000031841, Sample ABL Code to determine how many buffers available in Alternate Buffer Pool  
000045605, PROMON shows "LRU2 replacement policy disabled" when OS reads GT -B2 value  
000070193, Alternate Buffer requires more blocks than the number of database objects assigned   
 
Attachment 
Last Modified Date9/25/2018 4:23 PM