Dmitri Levin (Customer) asked a question.

Best Practices for Storage Area Configuration?

This topic has been discussed thousands of times, but opinions are changing over the years. I’d like to know what the consensus is today. Below is my vision. Please correct me if I am mistaken.

 

1. Use 8K as a database block size.

 

Paul Koufalis explained:

https://blog.wss.com/basics-db-storage-01

"We generally find that bigger blocks result in slightly better performance so if performance is critical we often go with 8k. There are, however, arguments* for matching the DB block size to the file system block size, since file system operations occur at that size.

 

* There is a rare and esoteric problem called a torn page which slightly favors matching the database and file system block sizes. However, in my [Paul] nearly thirty years as an OpenEdge DBA, I have suspected that torn pages were a potential explanation for a database corruption once…maybe twice."

 

A few notes about the torn pages:

Blocks in the type 2 storage areas use checksums. There is a misconception that crash recovery can repair the torn pages but in fact Progress cannot recover blocks identified in error message 14410:

Checksum validation of dbkey %j block type %i in area %i does not match data. Expected: %i received %i. (14410)

 

There are many articles in the knowledge base that address error 14410; however, in most of these cases, the issue does not involve torn pages. If you get this error for a running database or when you run offline utilities, it’s NOT the torn pages. Scenario of torn pages: server is crashed, you try to restart a database but it failed with error 14410 /during/ crash recovery. Torn pages would imply that you have lost your database. But I am not aware of any such cases. In all likelihood, they are extremely rare - if they even exist at all.

 

Torn pages can be easily recovered provided replication is enabled for a database. You can simply use the dd command to copy a block from one database to another. 

 

2. Choice of storage area type: always SAT2.

 

Obvious choice.

 

3. Each area must contain the objects of only one type.

 

Reasons:

Utilities scan the areas in search of an object of a specific type.

For each area we should specify the records per block (RPB) and blocks per cluster (BPC) settings which may vary depending on the object types.

 

4. Put the largest tables into their own areas.

 

The goal is to make shared area(s) small. The smaller shared area the faster the utilities like idxfix. The utilities scan the entire area. They could scan the chain of data clusters belonging to an object specified for the given utility, but they do not do so. In essence, they use "turbo-scanning": the utilities scan all clusters within the area but read only one block from each cluster, except in cases where the cluster belongs to the object.

 

An adequate number of personal areas for the tables is provided if there are about 10 such areas, or if the size of the smallest one is roughly equal to the size of the shared area (the size of remaining tables). 10 tables + 10 index areas + shared areas – the list of areas would be short enough for people to keep it under control.

 

5. Put the large LOBs into their own areas.

 

Large LOB objects are the ones whose size is compatible to the sizes of the personal areas for tables. For example, 10 largest tables in the customer's database range in size from 51 to 326 GB. The largest LOB fields are 54, 67 and 166 GB. I would allocate dedicated areas for these LOB fields.

 

This choice does not affect performance neither of databases nor of any utilities. I have no arguments to justify why large LOB fields should also be given the personal areas. This is more a matter of aesthetics.

 

6. For each table area, create a corresponding index area.

 

Truncate an index area before rebuilding indexes for the tables located in the table area.

 

7. Set records per block (RPB) for table/LOB areas to 256.

 

Even for the personal areas where the ratio of DbBlockSize to MeanRecSize essentially lower.

Even for the LOB fields where the most record sizes are larger than db block size.

To control space management use only toss/create limits rather than RPB.

This piece of advice seems to run counter to established tradition.

 

Let’s assume the mean record size allows to create only 48 records per block. But you can set RPB either to 32 or to 64. With RPB 32 you will leave unused 1/3 of block size (16 of 48). It amy also trigger the growth of the RM chain. Obviously RPB 32 would be a mistake. RPB 64 can create the feeling that you are in control of the space usage but it’s the toss/create limits that rule it. We will get exactly the same results using any RBP higher than 64. So we can just set RPB to 256 without taking into account the average record size.

 

Gus Bjorklund in Space: The Final Frontier

"If the block is not on the RM chain, has unused record slots, and has at least as much free space as the free space constant, it is added to the RM chain. If the block has more than 4 unused slots, it is added to the front of the chain. Otherwise it is added to the end of the chain."

 

If we want RM chains to remain short, it is better to add blocks to the front of the chain. This is why each data block should have at least 4 free slots.

 

8. Set records per block (RPB) for index areas to 1.

 

Progress Community Archive: setting cluster size - best approach? 

https://community-archive.progress.com/forums/00018/03675.html

Richard Banville: 

"Another common practice is to set the RPB to 1 for an area that is only to contain indexes. The reasoning here is that index space allocation doesn’t care about RPB. This is true. If you choose such a setting, just make sure you never put a table in that same area or you will be storing one record per block!"

 

9. Set blocks per cluster (BPC) for table/LOB areas to 512 and 64 for index areas.

 

Progress Community Archive: setting cluster size - best approach? 

Gus Bjoklund: 

"My advice for choosing the allocation cluster size value is: Always use 8."

"There is very little advantage to using a larger allocation cluster size. I have not seen any actual performance advantages for larger cluster sizes even though in theory there could be."

 

Large cluster size might be helpful for utilities that are using "turbo-scanning".

 

Richard Banville: 

"When in doubt about which cluster size to choose, pick 64 block clusters, especially for indexes."

"There are circumstances when inserting an index entry can cause a block split. When the block split occurs, the storage engine must ensure that it has enough free space to split the maximum number index block. This maximum is the maximum b-tree depth which is currently 16. This means that on certain index split operations, 2 clusters may need to be allocated in order for a single entry to be added."

 

It’s highly unlikely there are Progress databases on the third planet from the Sun with the large tables whose indexes have b-tree depth higher than 6. To move to the next index level, the size of the table must increase approximately by a thousand times. Setting the cluster size to 64 is not mandatory for real index areas.

 

Should the BPC setting be reduced for shared areas? For a shared area containing a set of small-to-medium-sized tables, which is the better choice: BPC 8 or 64? Is the loss of two gigabytes of disk space across, let’s say, a thousand empty tables a problem - especially when the database size exceeds, say, a terabyte? Databases on disk often contain far more unused space than the amount that would be lost on empty objects when using a large cluster size.


  • Dmitri Levin (Customer)

    10. Set the toss/create limits to control space management.

     

    Unfortunately, Progress does not provide any information that can help us to choose the values of the toss/create limits. The closest related statistics is the space allocation activity (_ActSpace) but it’s available only at the database level, rather than being broken down by areas or objects. Choosing the toss/create limits is, in essence, a shot in the dark. But we must honestly acknowledge that these limits with their default values are already controlling the space management algorithms in our databases. Default values of the toss/create limits are often not the optimal, but they do work nonetheless.

     

    There are obvious hints:

    If the records are fragmented, we need to increase the create limit.

    If RM chain is long (longer than one cluster size) then we should consider to increase a toss limit. A good starting point is to set a toss limit equal to 1-2 average record size.

     

    For each object in SAT2 it is possible to estimate the average amount of unused space within a data block. This metric can also serve as a guideline for choosing toss limit.

     

    When max record size is larger than db block size and especially for the LOB fields we should take into account the algorithms that Progress uses for large records (for the records larger than 70% of block size). But these algorithms are undocumented.

     

    One more issue with toss/create limits – they are not included in a data definition dump (.df files). We are forced to dump the toss/create limits from old database as an additional step to load them later into a new database.

     

  • Rob Fitzpatrick (White Star Software)

    "Set records per block (RPB) for index areas to 1."

    This is obsolete guidance which applies only to Type 1 areas. And no one should be creating new Type 1 areas or assigning new objects to existing ones.

     

    Using RPB 1 maximizes the area's available block-addressing bits per dbkey, to ensure the area can grow to be as large as is permitted by the address space (2^31 blocks), which is the limiting factor for maximum area size for Type 1.

     

    For a Type 2 area, the limiting factor for area size is maximum extent size * maximum extent count (1 TB * 1024 = 1 PB). Type 2 areas have 64-bit addresses (in 10.1B+), of which 63 are available for addressing. But even in the worst case (a 1 PB area), we only need 37 or 38 block-addressing bits per address, depending on 8KB or 4 KB block size. So we would hit the size limit imposed by extents long before we run out of block-addressing bits. We would have 17 to 18 bits per dbkey left over that can never be used, and even that is if we're using RPB 256 (e.g. 63 - 8 - 38 = 17).

     

    Unfortunately the database does not let us create index-only areas. There is no mechanism for creating an area-level filter by object type (though there is a request for this: https://openedge.ideas.aha.io/ideas/OPENEDGE-I-507). So the danger still remains that while the vigilant DBA is away, someone else could apply a schema change that adds a fast-growing table to an index area. If it has RPB 1, as RichB noted, the result will be very fast growth and poor caching performance. If RPB is 256, the harm is minimized.

     

    Long story short: use RPB 256 for index-only areas. And monitor on a regular basis for mixed-use areas. in 11.7+, prostrct statistics is good for this purpose and runs much fast than dbanalys in large databases.

  • Rob Fitzpatrick (White Star Software)

    "Should the BPC setting be reduced for shared areas? For a shared area containing a set of small-to-medium-sized tables, which is the better choice: BPC 8 or 64? Is the loss of two gigabytes of disk space across, let’s say, a thousand empty tables a problem - especially when the database size exceeds, say, a terabyte? Databases on disk often contain far more unused space than the amount that would be lost on empty objects when using a large cluster size."

     

    Why waste space when you don't have to?

     

    Block size: 8 KB

    Tables in area: 1000

    Every storage object consumes one or more clusters.

    Minimum area size with BPC 8: 8 * 8 KB * 1000 = 64 MB

    Minimum area size with BPC 64: 64 * 8 KB * 1000 = 512 MB

    Minimum area size with BPC 512: 512 * 8 KB * 1000 = 4 GB

     

    Bigger databases mean longer backups, longer utility scans, longer restores, higher cloud storage costs. These numbers multiply further with multiple databases per environment and multiple environments (prod, DR, UAT, test, dev, etc.).

  • Rob Fitzpatrick (White Star Software)

    Use 8K as a database block size.

     

    I agree. I always use 8 KB, regardless of OS or file system cluster size.

  • Dmitri Levin (Customer)

    Hi Rob,

     

    Thank you for your comments!

     

    > Long story short: use RPB 256 for index-only areas.

     

    How can we tag an area so that our monitoring programs understand it is intended exclusively for indices? Simply including a keyword in the area’s name will not protect against human error. Setting RPB to 1 is simply a flag, the meaning of which is easy to interpret.

     

    > And monitor on a regular basis for mixed-use areas. in 11.7+, prostrct statistics is good for this purpose and runs much fast than dbanalys in large databases.

     

    It is recommended to collect _TableStat/_IndexStat statistics on a regular, daily basis using a monitoring tool. Upon startup, this tool should check the current state of the database schema. Each time a snapshot is taken, the tool can verify the database schema's timestamp. If the timestamp has changed, the tool can identify exactly which object was modified or created. Furthermore, if an object has been created in an inappropriate area, the tool can issue a warning. By the way, this functionality is already partially implemented.

     

    > Why waste space when you don't have to?

     

    Which BPC you recommend for a shared area?

     

    There will be approximately 1,000 tables in the shared area, 404 of which are empty. The total size of all tables in the area is 230 GB. I don’t see an issue with 2 GB being wasted. The blocks of large and medium-sized tables are wasting much more disk space.

     

    > Bigger databases mean longer backups, 

     

    The database size is approximately 3 TB. An additional 2-4 GB of disk space occupied by empty tables will have no noticeable impact on backup execution time.

     

    > longer utility scans,

     

    The larger BPC the faster utility scans. It's a direct correlation.

     

    From the perspective of disk space efficiency, the most effective solution would be to place all small tables (smaller than the cluster size) in the SAT1 area. However, the application might later begin to use of some of these tables, in which case we would need to move them to a more appropriate area. My approach is to treat such small tables (including empty ones) as medium-sized tables—anticipating what they might become in the future.