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:
- 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.
- 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.
- 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 40In
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.
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
To avoid LRU2 enabledThe Alternate Buffer pool will require more blocks than the number of database objects assigned, when online utilities with Block Level Access, as opposed to block requests for specific objects are used. These need to be run with Private Buffers (-Bp)
- probkup online dbname <backupvolume> -Bp 64
- proutil dbname -C dbanalys -Bp 64
Consider increasing the database startup parameter -Bpmax 64 (default). The private buffer pool (-Bp) is an isolated portion of public buffer pool (-B), it is also limited to no more than 25% of the primary buffer pool (-B) value. Client sessions may also be making use of using private buffers (-Bp, NumSeqBuffers)
For further detail refer to Article
Alternate Buffer requires more blocks than the number of database objects assigned Monitoring and Managing B2:
LRU2 enabled/disabled?
PROMON > R&D > 2 Activity Displays > 3 Buffer Cache
- "LRU2 replacement policy disabled"
- "LRU2 replacement policy enabled"
Increase -B2 onlinePROUTIL -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
