Feedback
Did this article resolve your question/issue?

   

Article

Alternate Buffer requires more blocks than the number of database objects assigned

« Go Back

Information

 
TitleAlternate Buffer requires more blocks than the number of database objects assigned
URL NameAlternate-Buffer-B2-memory-LRU2-object-level-assignment-000070193
Article Number000115202
EnvironmentProduct: OpenEdge
Version: 10.2B, 11.0.x, 11.1.x, 11.2.x, 11.3.x, 11.4.x, 11.5.x, 11.6.x, 11.7.x, 12.x
OS: All Supported Operating Systems
Other: Database, Alternate Buffer Pool, B2
Question/Problem Description
When using Object Level assignments to the Alternate Buffer Pool (-B2) the number of Buffers reported by PROMON is larger than the collective size of the objects assigned.
In order for LRU2 not to be enabled, the value for -B2 needed is far too high in relation to the objects assigned
LRU2 replacement policy enabled when -B2 is greater than the calculated estimate of Objects assigned
Alternate Buffer Pool increased online with PROUTIL -C increaseto -B2 which disables LRU2
LRU2 replacement is enabled again until eventually the increased value for -B2 assigned cannot be reasonably accounted for by the current size of the tables/indexes/lobs loaded.
Allocating enough memory for the Alternate Buffer Pool in order to avoid LRU2 buffer eviction difficult
LRU2 is enabled after sizing -B2 as outlined in Article  How to size monitor and manage the Alternate Buffer Pool (-B2)  
Steps to Reproduce
Clarifying Information
Alternate Buffer Pool assignments at the Object Level
Type II Storage Area database structure where not all objects are assigned to -B2

Example: Estimate for all Objects Assigned to the Alternate Buffer Pool

IXANALYS: 163 BLOCKS                                                                 
CHANALYS: 1462 BLOCKS (657 RM ; 805 FREE; 0 Index-Delete )                           
TABANALYS:  4MB TOTAL RECORD-SIZE                                                                                                                               
EST MEAN B2: 559 

-B2 600 leads to LRU2 enabled


PROMON > R&D > 2 > 3 (Activity, Buffer Pool)

Alternate Buffer Pool :
Logical reads                         650 <  higher amount of Logical Reads in the buffer pool than expected
Logical writes                          0 
O/S reads                             622 < higher amount of OS Reads in the Buffer Pool than expected
Alternate buffer pool hit ratio:  10 % 
LRU2 replacement policy enabled.
Error Message
Defect NumberDefect PSC00346392
Enhancement Number
Cause
When the Alternate Buffer Pool has Object Level assignment, this causes the secondary pool to fill with free blocks that should have been put into the primary pool. 
  1. The pool picking algorithm incorrectly defaults to the secondary pool once the primary pool is full when free blocks are inserted.
  2. Master Control Object Blocks (one clusters worth) are also be put into the Alternate Buffer Pool once the Primary fills up. 
These Free Blocks are only associated with non-Table objects in Type II Areas:
  • Never related to any objects that are assigned at the Area Level to the secondary buffer pool
  • Never table free blocks, unless tools with block level access such as probkup, chananalys (or dbanalys which has chanalys as part of it) is run which has to read in all blocks
Only when the primary buffer pool is filled (it is usual for LRU to be enabled in production)
a. Index / Lob free blocks defaults to the secondary pool only when the related table’s index /lob are not also assigned at object level in B2
b. Similar to (a) are Master Object blocks 

Once Free blocks become real blocks as they are filled with data, they will find their way back to the Primary Buffer Pool.
Even though the blocks are in the Secondary Buffer Pool instead of the Primary Buffer Pool, data are all accessible. There is no corruption as a result. 

In addition to the above, unrealistic Status: Buffer Cache > Empty buffers counters will be seen when the LRU'ing as these are counted twice when put into the secondary pool that should have been put into the Primary Pool, and eventually make their way back to the Primary Buffer Pool. 
 
Resolution
In OpenEdge 11.6.3, the picking algorithm for Object Level assignments was improved so that free blocks and master object blocks are added to the proper buffer pool when the primary buffer pool is full. 

Since OpenEdge 11.7.0, 11.6.4.006, 11.6.3.032 these changes were reverted and this caused a regression that exhausts the primary buffer pool (-B) and crashes the database. Refer to Article  A database running with -B2 crashes with error (1040)   

As a consequence of this investigation, the following enhancements were added and remain in the product:

To facilitate the sizing of -B2:

1.   OpenEdge 11.6.3 11.7.0PROUTIL -C viewB2 output includes which storage pool it is assigned, the size (in blocks) of each object and a total for the Area.  
  • This sizing includes all blocks associated with each object - including free blocks.   
  • viewB2 with the -csoutput Option, has this additional data
 
For example: a "FOR EACH" would not load free blocks, where a DBANALYS report or PROBKUP, without -Bp would load free blocks
 
Area "Customer/Order Area":8 - Primary Buffer Pool 

Object Enablement    Size     Type   Object Name 
-----------------  -------- -------  ------------ 
Default                   7  Master   Area.Control-Object:0 
Alternate              1320   Table   PUB.Customer:2 
Default                 104   Table   PUB.Order:4 
Default                 176   Table   PUB.Order-Line:5 
Default                  32   Index   PUB.Order.Cust-Order:21 
                   -------- 
                       1639 
                          
2. In OpenEdge 11.7 - PROMON> R&D > 2,3 (activity, buffer pool) is enhanced to provide more detail about the types of blocks accessed in the buffer pools.
 
Alternate Buffer Pool 
Logical reads                         650 
Logical writes                          0 
O/S reads                             622 
O/S writes                              0 
Marked to checkpoint                    0 
Flushed at checkpoint                   0 
Writes deferred                         0 
LRU2 skips                              0 
LRU2 writes                             0 
APW enqueues                            0 
>>
Active Blocks                         601 
Master Blocks                           0 
Index Blocks                           40 
Record Blocks                         138 
Free Blocks                           370 
Sequence Blocks                         0 
Area Blocks                             4 
Object Blocks                           9 
Object List Blocks                      4 
Control Blocks                          0 
<<
Alternate buffer pool hit ratio:  10 % 
LRU2 replacement policy enabled.

As a consequence, depending on the size of the Buffer Pool, this leads to delays in PROMON R&D Menus which has been addressed in OpenEdge 11.7.6 as described in Article:
Workaround
Prior OpenEdge 11.6.3 and Since OpenEdge 11.7.0, the only reliable way to be able to size the Alternate Buffer Pool -B2 accurately, is to have the content of Storage Areas explicitly assigned to a the Alternate Bufferpool and not use object-level assignment.

1. Move all Objects to be assigned to the Secondary Buffer Pool into one or more Type II Storage Areas
2. Assign the Storage Area to the Alternate Buffer Pool
$  proutil -C enableB2 "area name"

Alternatively, when using object-level assignment, allow oversizing of the Secondary Buffer Pool to include current Free Blocks and Master Block Object Blocks of related Index and Lob objects of the tables that have been assigned to the Secondary buffer pool at object level. Then monitor and increase the Alternate Buffer pool size once LRU2 is enabled:

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

PROUTIL -C INCREASETO -B2 <value>

PROMON > R&D > 4 Administrative Functions > 4 Adjust Latch Options > 3. Disable LRU2 alternate buffer pool replacement policy
 
 
Notes
Last Modified Date11/20/2020 6:56 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.