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

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

« Go Back

Information

 
Article Number000070193
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
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.
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/Enhancement NumberDefect PSC00346392
Cause
When the Alternate Buffer Pool has Object Level assignment, the pool picking algorithm incorrectly defaults to the secondary pool once the primary pool is full when free blocks are inserted. The Master Control Object Blocks (one clusters worth) will also be put into the Alternate Buffer Pool once the Primary fills up. This causes the secondary pool to fill with free blocks that should have been put into the primary pool. 

These Free Blocks are only associated with non-Table objects in Type II Areas:
0. Never related to any objects that are assigned at the Area Level to the secondary buffer pool
1. Never table free blocks, unless a 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 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, 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
Upgrade to OpenEdge 11.6.3, 11.7.0 or later where the picking algorithm for Object Level assignments was improved so that free and master object blocks are added to the proper buffer pool when the primary buffer pool is full.

As a consequence of this investigation, the following enhancements were added:

To facilitate the sizing of -B2:

1.   OpenEdge 11.6.3 11.7.0

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 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 would
 
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 
                          
2. In OpenEdge 117 PROMON> R&D > 2,3 (activity, buffer pool) will be 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.
Workaround
Prior OpenEdge 11.6.3 and 11.7, the only way to accurately be able to size the Alternate Buffer Pool -B2 is to have all Storage Areas explicitly assigned to a bufferpool and no 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, allow oversizing of the Secondary Buffer Pool to include current Free Blocks and Master Block Object Blocks of related Index and Lob objects to the tables that have been assigned to the Secondary buffer pool at object level and increase the secondary buffer pool size online as soon as LRU2 is enabled.
 
Notes
Progress Article: 

000078422, A database running with -B2 crashes with error (1040)   
Attachment 
Last Modified Date11/30/2017 8:24 AM