Setup Level II Storage Guide

[Rob Fitzpatrick]

RMBLK:
0040 numdir:       0x02               2
0041 freedir:      0xfd               253
0042 free:         0x1f8c             8076
0044 entry #  0:   0x1fef             8175
0046 entry #  1:   0x1fd4             8148

"numdir" and "freedir" are 1 byte fixed fields.
Offset directory is a variable structure. Each entry uses 2 bytes.

BTW, dbrpr seems to be updated in V12.8: "entry #".

Yes. What I refer to as "the RM header" is numdir, freedir, and free.

Dbrpr was enhanced in 12.8, to show more details of the RM header and the row directory. Rowids are new, along with some new base conversion. They also fixed one or two bugs. E.g. the phantom partitionId field between objectId and objectType is gone.

sports, first record block of Customer, excerpt:
12.7:

0010 bkCheckSum:   0xa5da             -23078
     bkHeaderSize: 0x0040             64
     objectId:     0x0002             2
     partitionId:  0x0000             0
     objectType:   0x0001             1
     bkObjDbkey:   0x0000000000000100 256

RMBLK:
     numdir:    25
     freedir:   7
     free:      156
     dir:
        [  0] Offset: 0x0fd0 Size: 46    Table number: 2  Table schema version: 0
        [  1] Offset: 0x0f2d Size: 161   Table number: 2  Table schema version: 0
        [  2] Offset: 0x0e88 Size: 163   Table number: 2  Table schema version: 0
        [  3] Offset: 0x0dd7 Size: 175   Table number: 2  Table schema version: 0

12.8:

0010 bkCheckSum:   0xa5da             -23078
     bkHeaderSize: 0x0040             64
     objectId:     0x0002             2
     objectType:   0x0001             1
     bkObjDbkey:   0x0000000000000100 256

RMBLK:
0040 numdir:       0x19               25
0041 freedir:      0x07               7
0042 free:         0x009c             156
0044 entry #  0:   0x0fd0             4048
0046 entry #  1:   0x0f2d             3885
0048 entry #  2:   0x0e88             3720
004a entry #  3:   0x0dd7             3543

     Dir:
   [  0] Recid: 384 Offset: 4048 Size:    46 Table number: 2  Table schema version: 0
   [  1] Recid: 385 Offset: 3885 Size:   161 Table number: 2  Table schema version: 0
   [  2] Recid: 386 Offset: 3720 Size:   163 Table number: 2  Table schema version: 0
   [  3] Recid: 387 Offset: 3543 Size:   175 Table number: 2  Table schema version: 0

There are also changes in the data presentation of fragment details (menu 13, option 1). Here are 12.8 and 12.7 side by side:

12.8:                                                                                        12.7:
Dir:                                                                                         dir:                                                                        
   [  0] Recid: 384 Offset: 4048 Size:    46 Table number: 2  Table schema version: 0           [  0] Offset: 0x0fd0 Size: 46    Table number: 2  Table schema version: 0
                    0x002e     Fragement Size: 46                                                        0  0xe7  SKIPTBL [ 0:  3] 0x0000     0                          
                 0  0xe7 0x00 0x02 SKIPTBL Size: 2 Entries: 1                                            5  0xfa  VECTOR Size: 0                                         
                 3      [1:3] 0x0027    39 (44)                                                                       REC_TBLVERS:     0                                 
           1     5  0xfa 0x00 0x0b TABLE_INFO_VECTOR Size: 11 Begins...                                               REC_TBLNUM:      2                                 
                 8      [1:8]     0 0x00 "."            REC_TBLVERS: 0                                                REC_TBLFLDCOUNT: 17                                
                 9      [2:9]     1 0x01 0x02 ".."            REC_TBLNUM: 2                                           Spare            1: 0xfd  VUNKNOWN                 
                11      [3:11]     1 0x01 0x11 ".."            REC_TBLFLDCOUNT: 17                                    Spare            2: 0xfd  VUNKNOWN                 
                13      [4:13]     1 0xfd  VUNKNOWN      Spare                                                        Spare            3: 0xfd  VUNKNOWN                 
                14      [5:14]     1 0xfd  VUNKNOWN      Spare                                                        Spare            4: 0xfd  VUNKNOWN                 
                15      [6:15]     1 0xfd  VUNKNOWN      Spare                                                        Spare            5: 0xfd  VUNKNOWN                 
                16      [7:16]     1 0xfd  VUNKNOWN      Spare                                           8      0    ""                                                  
                17      [8:17]     1 0xfd  VUNKNOWN      Spare                                           9      1 0x02 "."                                               
                18  0xff  ENDREC                                                                        11      1 0x11 "."                                               
           2    19      0 0x00 "."                                                                      13  0xfd  VUNKNOWN                                               
           3    20      0 0x00 "."                                                                      14  0xfd  VUNKNOWN                                               
           4    21      0 0x00 "."                                                                      15  0xfd  VUNKNOWN                                               
           5    22      0 0x00 "."                                                                      16  0xfd  VUNKNOWN                                               
           6    23      0 0x00 "."                                                                      17  0xfd  VUNKNOWN                                               
           7    24      0 0x00 "."                                                                      18  0xff  ENDREC                                                 
           8    25      3 0x03 0x55 0x53 0x41 ".USA"                                                    19      0    ""                                                  
           9    29      0 0x00 "."                                                                      20      0    ""                                                  
          10    30      0 0x00 "."                                                                      21      0    ""                                                  
          11    31      0 0x00 "."                                                                      22      0    ""                                                  
          12    32      0 0x00 "."                                                                      23      0    ""                                                  
          13    33      3 0x03 0x80 0x15 0x00 "...."                                                    24      0    ""                                                  
          14    37      0 0x00 "."                                                                      25      3 0x55 0x53 0x41 "USA"                                   
          15    38      5 0x05 0x4e 0x65 0x74 0x33 0x30 ".Net30"                                        29      0    ""                                                  
          16    44      0 0x00 "."                                                                      30      0    ""                                                  
          17    45      0 0x00 "."                                                                      31      0    ""                                                  
                                                                                                        32      0    ""                                                  
                                                                                                        33      3 0x80 0x15 0x00 "..."                                   
                                                                                                        37      0    ""                                                  
                                                                                                        38      5 0x4e 0x65 0x74 0x33 0x30 "Net30"                       
                                                                                                        44      0    ""                                                  
                                                                                                        45      0    ""

 

[Rob Fitzpatrick]

Some remaining bugs in option 13:

bad parsing of EH block as Type 2:

Customer/Order Area (8)  Extent 1  Block 1
-----------
0000 bk_dbkey:     0x00000000         0
     bk_type:      0xfe               254 (Extent Header Block)
     bk_frchn:     0x7f               127 (NOCHN)
     bk_incr:      0x0001             1
     bk_nextf:     0x00000000         0
     bk_updctr:    0x00000000         0

0010 bkCheckSum:   0x10b8             4280
     bkHeaderSize: 0x0040             64
     objectId:     0x0000             0
     objectType:   0x0000             0
     bkObjDbkey:   0x681b50af681b50af 7501678318611812527
0020 bkDbkey:      0x681b50e26839d90f 7501678537657145615
     bkNextf:      0x0000000000000000 0
0030 bkLastBiNote: 0x0000000000000000 0
     partitionId:  0x0000             0
     bk_incr_HIGH: 0x00               0
     Reserved:     0x00               0
     Reserved:     0x00000000         0

0040 transactionId: 0x00000000        0
     serialNumber: 0x0000000000000000 0

incorrect parsing; phantom extended block header:

Customer/Order Area (8)  Extent 2  Block 16
-----------
0000 bk_dbkey:     0x000003c0         960
     bk_type:      0x03               3 (Data Block)
     bk_frchn:     0x7f               127 (NOCHN)
     bk_incr:      0x0001             1
     bk_nextf:     0x00000000         0
     bk_updctr:    0x00000021         33

0010 bkCheckSum:   0x0776             1910
     bkHeaderSize: 0x0040             64                   <--- short header
     objectId:     0x0004             4
     objectType:   0x0001             1
     bkObjDbkey:   0x0000000000000300 768
0020 bkDbkey:      0x00000000000003c0 960
     bkNextf:      0x0000000000000000 0
0030 bkLastBiNote: 0x0000000000000000 0
     partitionId:  0x0000             0
     bk_incr_HIGH: 0x00               0
     Reserved:     0x00               0
     Reserved:     0x00000000         0

0040 nextCluster:  0x0f8a0fc608670020 1119724800573898784  <--- garbage data
     prevCluster:  0x0eae0ee50f1c0f53 1057799339442442067  <--- garbage data

incorrect failure to parse extended block header:

Customer/Order Area (8)  Extent 3  Block 3
-----------
0000 bk_dbkey:     0x00000400         1024
     bk_type:      0x0c               12 (Object Block)
     bk_frchn:     0x7f               127 (NOCHN)
     bk_incr:      0x0001             1
     bk_nextf:     0x00000000         0
     bk_updctr:    0x00000001         1

0010 bkCheckSum:   0x6ab4             27316
     bkHeaderSize: 0x004c             76                   <--- extended header
     objectId:     0x0015             21
     objectType:   0x0002             2
     bkObjDbkey:   0x0000000000000400 1024
0020 bkDbkey:      0x0000000000000400 1024
     bkNextf:      0x0000000000000000 0
0030 bkLastBiNote: 0x0000000000000000 0
     partitionId:  0x0000             0
     bk_incr_HIGH: 0x00               0
     Reserved:     0x00               0
     Reserved:     0x00000000         0
                                                           <--- missing transactionId/serialNumber
OBJBLK:

again:

Customer/Order Area (8)  Extent 3  Block 10
-----------
0000 bk_dbkey:     0x000004e0         1248
     bk_type:      0x04               4 (Free Block)
     bk_frchn:     0x00               0 (FREECHN)
     bk_incr:      0x0001             1
     bk_nextf:     0x00000000         0
     bk_updctr:    0x00000000         0

0010 bkCheckSum:   0xf1d4             -3628
     bkHeaderSize: 0x0050             80                   <--- extended header
     objectId:     0x0015             21
     objectType:   0x0002             2
     bkObjDbkey:   0x0000000000000400 1024
0020 bkDbkey:      0x00000000000004e0 1248
     bkNextf:      0x0000000000000000 0
0030 bkLastBiNote: 0x0000000000000000 0
     partitionId:  0x0000             0
     bk_incr_HIGH: 0x00               0
     Reserved:     0x00               0
     Reserved:     0x00000000         0
                                                           <--- missing nextCluster/prevCluster

                     DISPLAY BLOCK MENU

They seem to use a naive calculation for finding cluster-boundary blocks, e.g.:

• if (dbkey / RPB) modulo clustersize = 0: cluster start block
• if (dbkey / RPB) modulo clustersize = clustersize - 1: cluster end block

This is true when the area has exactly one extent, but not in the general case.

 

[Rob Fitzpatrick]

They seem to use a naive calculation for finding cluster-boundary blocks, e.g.:

• if (dbkey / RPB) modulo clustersize = 0: cluster start block
• if (dbkey / RPB) modulo clustersize = clustersize - 1: cluster end block

This is true when the area has exactly one extent, but not in the general case.

Sorry, I'm writing faster than I'm thinking. That's the correct calculation.

I think they are comparing physical block offsets to the clustersize, which gets thrown off by EH blocks in extent 2 and beyond.

 

[George Potemkin]

May I ask? If we would be able to change the space management algorithms... When new fragment is added to a first block on RM chain and now the free space of the block is less than the toss limit, would you remove the block from the chain immediately or let the next record create to do that? I would prefer the second (lazy) version - there is a (little) chance that some of the records in the block will be later deleted or will decrease in size thereby freeing more space in block to keep the block on RM chain. Following the principle: "do nothing if you can". But Progress does not use this approach. What would be your choice?

 

[Rob Fitzpatrick]

May I ask? If we would be able to change the space management algorithms... When new fragment is added to a first block on RM chain and now the free space of the block is less than the toss limit, would you remove the block from the chain immediately or let the next record create to do that? I would prefer the second (lazy) version - there is a (little) chance that some of the records in the block will be later deleted or will decrease in size thereby freeing more space in block to keep the block on RM chain. Following the principle: "do nothing if you can". But Progress does not use this approach. What would be your choice?

My choice would be to let it happen on the next create. It may not make much difference if record sizes are quite uniform, or always larger than the toss limit. But for tables with smaller records or more variability in the record sizes, it may be that another record the same size the one just created would not fit. But that next record might end up being smaller, enough so that it would fit in the remaining space and still leave at least the create limit available.

 

[George Potemkin]

The bkLastBiNote field is not used, but is there any rumor as to what it was intended for?
Block (the bunch of records) vs an address of some recovery note?

 

[George Potemkin]

But that next record might end up being smaller, enough so that it would fit in the remaining space and still leave at least the create limit available.

I missed this point. Thanks!

> if record sizes are quite uniform

LOBs are quite opposite!

 

[Rob Fitzpatrick]

The bkLastBiNote field is not used, but is there any rumor as to what it was intended for?
Block (the bunch of records) vs an address of some recovery note?

I never did figure that out. It is eight bytes, so it could be a block address. I guess it's a question for Gus or Rich, or someone else who was on the engine team in the v9 days. I'm not sure what it's purpose would be.

It seems like several ideas that got past some review stage in the planning of Type 2 storage didn't get implemented, e.g. block types 8, 10, 11, and 18. I guess bkLastBiNote was another.

 

[JLovegren]

A table called "wrkfile" strikes me as something that might be worth discussing. Is this a table that contains temporary data? Do you write a bunch of things there, do something, and then eventually delete all of that data? If you do, then that would be something that could benefit quite a lot from being in a dedicated storage area. Depending on how any eventual purges get done and how much data there is you might even want to consider using "proutil -C truncate area" rather than having 4gl code delete data.

Assuming, of course, that "wrkfile" implies what I suggest that it might imply.

That's a good assumption. wrkfiles are created to temporarily hold data for running reports. The system was designed for v9, at which time Actuate could be bundled with Progress. Actuate was SQL and, at the time, was too slow so we used preprocessors in 4GL to create the data and then Actuate to display it. Nowadays, we use pdfinclude for our reports. There are some reports, however, that I still run the preprocessor using the wrkfile and pdfinclude to present the output. While the reports are not too complicated, they relate to accounting/finance and therefore a lot of hoops to jump through in order to comply with company rules related to modifying the calculations. (I know I should do it at some point). Every report will delete the previous report's records before creating new ones(per user). It is not ideal, every year I delete all wrkfile data that is left over. Reports not run frequently or by users no longer employed.

 

[Rob Fitzpatrick]

The typical topics for Holy Wars:
How to lay out the database objects per areas?
What RPB/CLS values to use for the areas?
When you need and how to change the toss/create limits for the tables/lobs?

It would be interesting to see the experts' current understanding. What old myths can be declared dead? Are there any topics for debate?

Objects to areas
I have a loose collection of unwritten rules in my head about assignment of objects to areas. I should write a blog post about that at some point.

In no particular order:

• There should be no application objects in the schema area.
• All data storage areas should contain objects of only one type: tables or indexes or LOBs.
  • Unfortunately, the platform does not give us a way to enforce this, so we must monitor vigilantly. This is a needed feature.
• All non-empty objects should be assigned to Type 2 storage areas.
  • It is acceptable to assign all completely empty objects to a Type 1 "Empty <object type>" area, provided you are willing and able to manage and monitor this. If not, put them into the Type 2 multi-object area for their object type.
• There should be an area for each object type for the small- to medium-sized objects that don't warrant their own area. E.g.:
  • "Data Area" for small tables
  • "Index Area" for the indexes of those tables
• I don't have real-world experience with LOB columns, so I can't give useful advice here. LOBs are inherently large, so it doesn't make sense to me to have an area for miscellaneous LOBs, other than perhaps as a default area for that area type, to keep application LOBs out of the schema area. I think it is best for each LOB to have its own area, especially as management and tooling for LOBs is minimal at best.
• There should be a default Type 2 area for each object type, to help keep the schema area clean.
  • E.g.:
    

    proutil s2k20 -C setobjectdefaultarea "Data Area" table
    proutil s2k20 -C setobjectdefaultarea "Index Area" index
    proutil s2k20 -C setobjectdefaultarea "LOB Area" lob
    proutil s2k20 -C dispobjectdefaultarea all
    Database Objects        Area Number     Area Name
    -------------------------------------------------
    Tables                        10        Data Area
    Indexes                       20        Index Area
    LOBs                          30        LOB Area

• There should be an area for each very large/fast-growing table. "Large" is up to the DBA's taste.
• There should be one area for each such table, for its indexes.
• There should be an area for any non-large tables that have word indexes defined. Their indexes do not need a separate table.
  • This is to keep these tables out of multi-table areas, which would single-thread the data-scan/key-build phase of proutil idxbuild even when the -datascanthreads parameter is used. It may matter if your index rebuilds take a long time.
• Don't create a huge number of areas, or a huge number of extents per area. This consumes more file handles and makes monitoring more of a pain.

RPB / Cluster size

RPB

• Table areas
  • Don't worry about "conserving" or "maximizing" available rowids. You might see this mentioned in old talks or KB articles. This was a concern for Type 1 areas, but not for Type 2. RPB (area records per block) doesn't impact maximum area size for Type 2.
  • For a multi-table area, use 256. If you have a table that isn't very large that you think should have a special RPB value, move it to its own area. E.g. you might want to put a very small, very heavily read table into its own area with RPB 1 to minimize buffer latch contention, if you have evidence that you have this problem.
  • I tend to base RPB for single-table areas on mean record size. There could be an argument to be made to simply use 256 for all table areas. The counter-argument would be that this could lead to excessive record fragmentation in tables where the application tends to update/grow the record significantly, and it is long enough after the create that the block is full (either logically or physically), so the only remaining space is the small create limit. This depends on knowledge of the application and tables, so it can't easily be expressed as a general rule.
• Index areas
  • RPB is only a concern for the case where a table is mistakenly assigned to an index area. Use 256.
    • The old guidance was to use RPB 1, to conserve dbkeys. Don't do this.
• LOB areas
  • LOBs are large, so my intuition says to use RPB 1. I'd like to hear otherwise if I am wrong.
  • Again, monitor these areas to ensure they only contain the objects they should. It would be nice to be able to disqualify an area from further object assignments, to guarantee that an area intended for one object remains so. This is another feature we don't have.

Cluster size

• Table areas
  • Use 8 for small-object areas and 512 for large-object areas.
• Index areas
  • Same.
  • I have read the justification for using 64, where a block split in a leaf block in an index with many levels propagates all the way up the the root block and we want to have lots of free blocks available for that so we don't have to wait for space allocation. To me, this feels like the "torn page" argument against 8 KB block size on file systems with 4 KB clusters. Possible in theory, but not a problem I'm worried about until I see evidence of it in the real world.
• LOB areas
  • Use 512.

Create and toss limits

I think you (@George Potemkin) and Dmitri Levin are the experts here, so you won't learn anything new from me.

I'd say this level of tuning is reserved for the DBAs who are very conscientious and hands-on, and who manage large very large databases. In my days of managing customer databases, I didn't resort to tuning these parameters.

I think the current wisdom (for tables large enough for this to matter) is to set the toss limit as a function of the template size, e.g. equal to it, or 10% larger. Right now I don't have a strong opinion to defend. Changing toss limit may materially affect RM chain length over time. But to me the question is, what are the effects of long RM chains?

I see value in tuning the toss and create limits in the particular case where you value record fragment density. For example, you have a table where the records are consistently small, but still large enough that blocks fill physically (too little free space), not logically (no row directory entries), and the records are not updated (or, are updated without growing). Keeping the toss limit high lets the blocks stay on the RM chain longer, and thus eligible for creates. And setting the create limit to the minimum of 32 maximizes space for creates.

An aside: here is another very niche optimization for such a table. Ensure, via D&L if necessary, that it has a table number below 128. The record versioning metadata in the primary fragment of each record stores the table number, and it is encoded as an integer. This means values of 1 to 127 occupy 2 bytes (1-byte field length and 1-byte field value), while higher table numbers occupy 3 bytes. If you have, say, an actual RPB of 200 for a table, you could be storing its table number 200 times per record block. So there would be 200 fewer bytes of free space in the block if this was table number 128 instead of table number 127. Maybe I think about this stuff too much.

As for LOBs, I know there are special cases in the space-management algorithms for record blocks, though I don't know what all of them are. I know, for instance, that for very large records (e.g. larger than the block) the create limit is ignored, to maximize how much of the record fits in the first fragments. Though I have an academic interest in LOBs, there is a part of me that hopes I'm not forced to learn a lot more about them. There is something to be said about the simplicity of just managing tables and indexes.

 

[Rob Fitzpatrick]

The character fields are the main contributors to the record's sizes. Table may have many (huge) number of the character fields. Each field has its own distribution of the sizes. If we would put each field in its own table we would get the tables with the absolutely different mean sizes and the deviations. But in fact they belong to one table. That is why the concept of the mean size is meaningless. At least we don't talk about the mean record size per database. The "mean" property will work more or less only when the deviation ratio is close to 1. In other cases it would worth to know the distribution of the character fields.

This is an excellent point. It would be very useful to have information about field-level distribution of field lengths. I suppose we can get a list of the worst offenders with a dbtool SQL width scan.

 

[Rob Fitzpatrick]

BTW, online dbanalys could dump the _TableStat/_IndexStat/_LobStat to create the statistics since last db startup. Would it hard to do for Progress development? Would it help us to analysis the data usage?

Great idea. Lots of developers/DBAs have spent lots of hours extending and contextualizing PSC's tool output to make it more useful. There is so much more they could do. As a community, we would be much better off if they did it. A rising tide lifts all boats, as they say.

Also, in this day of GB or TB of RAM on servers, we should be able to rely on having all of the CRUD stats. I think it is time to retire the default of 50 for -*rangesize, and have the broker manage this automagically: set the ranges to match the schema (or with a small buffer for growth), and increase them as additions are made. It should at least be an option.

What a grumpy old man!

Well, at least you aren't the only one.

 

[George Potemkin]

I should write a blog post about that at some point.

Or a presentation for then next Progress Conference! I’m glad that now there is a modern guidance about object assignment that we can refer to.

> LOBs are inherently large, so it doesn't make sense to me to have an area for miscellaneous LOBs

1. LOBs are the records whose minimal size is ten size /smaller/ than the minimal size of table records.
2. I did not check many applications but I saw enough LOBs that have rather large number of the extremely small records together with large number of the large records. Dbanlys report is based on an assumption that all records are similar like the bricks. That is why their mean size is the only characteristic that we are allowed to know. Standard deviation is skipped. The "bricks" can be sometimes "broken" so dbanlys reports the min and max values as well to see the exceptions. Even the real world of table records does not always match this assumption. The world of the LOB records is absolutely different in the most cases.

Example of LOBs in real databases. OpenEdge V11.7.12

                                         ---    LOB Size   ---
Table                 LOBs     Size      Min      Max     Mean
table:969  blob:5  1462381     5.0G     2.0B   754.4K     3.6K
table:1303 blob:15     578    24.8K     1.0B   411.0B    43.9B
table:1278 blob:12      25   101.2K   123.0B    43.3K     4.0K
table:1278 blob:10      15    40.0B     1.0B    12.0B     2.7B
table:1278 blob:11      25    11.8K     1.0B   770.0B   485.0B
table:1278 blob:13      26   204.3K   301.0B    14.6K     7.9K
table:1193 blob:6   453188    10.0G     2.0B     1.3M    23.0K
table:1194 blob:7   438878     9.4G     1.0B     1.3M    22.6K
table:1234 blob:9       16    13.2K    48.0B     3.5K   842.1B
--------------------------------------------------------------
Subtotals:         2355132    24.4G    48.0B     1.3M    10.9K

Let's take table:1193 blob:6
Records with the sizes of 2 bytes and with megabyte sizes belong to the same BLOB.
About 400,000 of 453,188 records have the size less than 8K (dbblocksize).
380,000 records are less than a half of db block size.
80,000 records are less than 15 bytes (min possible record size). RPB 256 is too small for them.
RPB 1 is a right choice only for 12% of the records.


There are two sub-algorithms of space management: the documented one for small records and the undocumented algorithm for records larger than dbblocksize - 120. There are the LOB fields where the "large" word is only a part of field type name. Their max size is less than 32000 bytes - less than the limit for the table records. There are the LOB fields where the most of the records use more than one block. Let’s assume we need to create a record with 1MB size. It will use about 128 db blocks. Should we check the blocks on RM chain with free space about 300 bytes (the default toss limit) to put there a fragment of the records? Certainly, not! May be the blocks that are half empty? We could but Progress itself will ignore the blocks on RM chain if they have less than 70% of free space. The best choice would be the free blocks. We can do it! We can set the toss limit at its max value. That is why I would create two areas – the one for rather small LOB records where Progress will use the well-known documented algorithm to manage the space usage in the data blocks and the second one for the LOBs where the mean record is higher than db block size. Not for the benefits in performance or the better disk space usage. Only due to the different space management algorithms that Progress will use for the small and large LOBs.

The side effect of undocumented space management algorithm - another application, OpenEdge V12.2

                                                       ---    LOB Size      ---
Table     Records LOB    LOBs    Blocks          Size  Min    Max          Mean
table:189    1044 blob:9  894 1,982,144 6,176,992,522  325 77,457,151 6,909,388


       Reads  Updates Creates  Deletes
Table  10.97  0.085   0.00002  0
Lob     0.028 0       0.028    0.028
Index  11.02
OS     22.47 (LOB OS Reads)

Mean size of LOB records is 844 db blocks.
Application did not delete the records of the tables and almost did not create the new ones.
The records are mainly updated including the updates of the BLOB field.
Each time Progress deletes all fragments of old LOB records and then creates new ones. The same it will do for CLOB fields even if only one its character is changed/added/deleted. The size of recovery notes is about twice higher than LOB size (more than 14 MB of BI writes per one update of a mean LOB record).

If a record/LOB is smaller than dbblocksize - 120 then Progress will use the different algorithm – with much smaller footprint in BI activity.

> LOBs are large, so my intuition says to use RPB 1. I'd like to hear otherwise if I am wrong.

1) the LOBs can have the large mean size but still have the huge number of the extremely short records. If the min size of LOB field is small then we should not expect it’s an exception just for a couple of records.

2) There is "a rule of 4 unused slots" described in Gus’ monograph. It defines where Progress will add a block to the RM chain – to its head or to a tail. If RPB is less 4 then data block will never have 4 unused slots. So the blocks will be always added to the chain’s tail even if their space is 100% free. Progress treats the recid holders as the used slots. So even RPB 4 could be not enough to add some empty blocks to the head of RM chain.

I didn’t test if RPB 8 will make any difference in the number of recovery notes generated by the changes of RM chain. But I would choose at least RPB 8 for the LOB areas. But my real choice is RPB 256 due to point 1 above.

Each change of LOB record means the huge changes of its RM change. It’s shame that Progress does not provide the same statistics as in _ActSpace but individually per each chain. Though we can get this statistics based on the AI scans – it’s not so hard to do. Without such statistics we can’t blame the space management algorithms for their inefficiently. It’s very sad that the fix is simple – Progress developers can do this with a snap of their fingers. Tables and LOBs in the current versions can use the Free chains as they still do in type 1 storage areas. The show stopper – the free chains are empty in the type 2 areas because their supplies were cut off. When we update or delete a LOB record that consists of 100 db blocks then we get 99 absolutely free blocks and one almost free block that stores only a recid holder. But Progress adds all of them to a chain of the /partially/ filled blocks (a.k.a. RM chain) instead of Free chain. The blocks on RM chain can be filled partially. These blocks will be moved from the head to the tail of the chain over and over creating unnecessary BI activity. Progress, free the Free Chains!

 

[George Potemkin]

It would be very useful to have information about field-level distribution of field lengths. I suppose we can get a list of the worst offenders with a dbtool SQL width scan.

What is the maximum size that can be expected from a field named “ship-city”? For example, the alternate name of Bangkok is 168 character long and it’s mentioned in Guinness Book of Records.

Field size statistics from a database somewhere in America ;-)

Size Cnt
2332  7
1058  1
1053  1
1051  1
1047  1
 486  2
...
  26 77956

Obviously, an application is parsing the shipping address and sometimes it assigns an incorrect value to the “ship-city” field. But if we just looks in dbanalys we would not be able to explain why the sizes of some records are abnormally large.

 

[George Potemkin]

Index areas

• RPB is only a concern for the case where a table is mistakenly assigned to an index area. Use 256.
  • The old guidance was to use RPB 1, to conserve dbkeys. Don't do this.

Holy war started! ;-)

Arguments to use RPB 1 for the index areas:
1) It's the only way to mark an area to be used for indexes only. We can write a program that will issue a warning if it finds the objects of the other types than the indexes;
2) If someone puts a table in area with RPB 1 and the table is growing then the faster is growing HWM of the area the sooner we will notice the mistake. And we can fix the issue while the table is not yet too large. The pain is good when it helps to heal.

 

[Rob Fitzpatrick]

It's the only way to mark an area to be used for indexes only.

Unfortunately, Progress doesn't provide a useful way to document structure. Structure file comments are useless as they are not written into the database. So, for example, they are discarded after a prostrct list. It is yet another limitation with structure management.

One solution is to put your structure in git and check in the full structure file each time it changes. That would let you document your intentions, so you have a record of why each change was made. But that is separate from the database and would rely on a manual process which could be skipped or forgotten.

Another possible approach is to create an application table with one record per area. You could write information about the intended usage of each area into the corresponding record. Then the DBA could perform a join of _Area and that table, and determine whether the current assignments break any rules. And if someone forgot to update the application table after an _Area record was added, that could be noticed programmatically.

If someone puts a table in area with RPB 1 and the table is growing then the faster is growing HWM of the area the sooner we will notice the mistake.

I would argue that if you rely on noticing rapid area growth to know that your assignments are wrong, then you aren't monitoring your area assignments effectively, and that's the real underlying problem to be solved.

It can be very helpful to have a naming convention for areas. E.g.:

Multi-object areas:
"Data": tables only, many objects
"Index": indexes only, multiple objects
"LOB": LOBs only, multiple objects
"Data_wordidx": only smaller tables that have one or more word indexes

Single-table areas:
"<table name>_data": only table <table name>
"<table name>_index": only the indexes of <table name>

Empty-object areas:
"Empty_data": only tables with zero records
"Empty_index": only the indexes of empty tables

Such a scheme would allow you to write a monitoring program to enforce your business rules for your structure. E.g.:

• If the name is "Data", it should contain no indexes or LOBs.
• If the name is "Index", it should contain no tables or LOBs.
• If the name is "LOB", it should contain no tables or indexes.
• If a table has one or more word indexes, its area should be "<table name>_data" or "Data_wordidx".
• If the name is "Data_wordidx", it should contain no indexes or LOBs.
• If the name is "<table name>_data", it should only contain table <table name>.
• If the name is "<table name>_index", it should contain all of the indexes of table <table name>.
• If the name is "Empty_data", it only contains tables with zero records.
• All empty tables are assigned to "Empty_data".
• If the name is "Empty_index", it only contains indexes of empty tables.
• All indexes of empty tables are assigned to "Empty_index".
• All application data storage area names should match the naming convention.
• etc.

This program wouldn't be hard to write, and it would be another tool in the DBA's toolbox for quality control.