Kbase 21171: What is the Proper Records Per Block Value For Index Storage Areas
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
2/16/2007 |
|
Status: Verified
GOAL:
What is the optimal record per block setting for a Storage Area containing only indexes
GOAL:
What is the Proper Records Per Block Value For Index Storage Areas
GOAL:
Is there a recommended number of record blocks for a storage area containing only indexes
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
FIX:
Unless there are a billion records or more in a table, use the default. It is one less thing to worry about.
A rowid (a unique identifier for records) is composed of two parts: the dbkey, which identifies a specific database block within a storage area, and a relative record number which identifies a specific record with the block. Together, these two parts form a 32-bit positive integer.
The setting for records per block controls how many bits of a rowid are used for the dbkey and how many for the relative record number. This remains true for both Type I (only available in Progress 9) and Type II (only available in OpenEdge 10, together with Type I) Storage Areas. The remainder of this discussion pertains to both Storage Area types containing Indexes.
Since index blocks do not contain records, the relative record number is irrelevant and only the dbkey is used. Therefore, it might be conclude that the records per block should be set to 1 for best results. This is true, but in fact, it makes very little difference in most cases.
Setting the records per block to 1 for a storage area that will contain only indexes has the following effects:
1. The area can contain more index blocks than it could when using the
default value. For an 8K database block size the default records per block is 64, which allows for a maximum of just under 256 gigabytes or about 33.5 million blocks. With a records per block setting of 1, the area can hold 64 times more, or about 2.1 billion blocks. In most cases, this will not matter because you will not have indexes that become anywhere near this large.
2. The non-leaf blocks of indexes contain compressed index entries that point to index blocks lower in the tree. Because the dbkeys for index blocks use 31 bits for the dbkey (the sign bit is not used), these index blocks will compress /a tiny bit/ better than with the default.
Neither of these benefits is worth the trouble. But what if there is a very large table with long keys (max key length is about 188 bytes per index entry)? How big can the index become?
Since rowids are constrained to 31 bits, there can be at most 2.1 billion records in a single table, stored in an area by itself. Let's estimate how large an index might be in the worst case.
Let's say there are records with 188 byte keys. At the leaf level of the index, assuming no compression occurs (very unlikely), there will be about 40 index entries per index leaf block. Assume for the sake of argument that there are 80 entries in the non-leaf blocks of the index.
For 2.1 billion records, about 53 million leaf blocks are needed in the index. The index will have 5 levels.
Level 5 (the leaf level) of the index will have 53 million blocks. Level 4 of the index will have about 671200 blocks
(53,000,000 / 80 = 662500 (but lets make that 671200))
Level 3 of the index will have about 8390 blocks (671200 / 80 = 8390 )
Level 2 of the index will have about 100 blocks.
The root will have one block. (8390 / 80 = 104.875 (about 100 blocks))
This adds up to about 54 million index blocks, assuming they are tightly packed (utilization is 100%). This is clearly more than will fit in the 33 million blocks you can have with the default setting of records per block. If you have half as many records, you will need about half as many index blocks.
Let's look at what happens if the key length is smaller, say 94 bytes (half of the previous example).
About 80 keys will in each leaf block, and 160 in each non-leaf block. This results in:
27 million blocks at the index leaf level,
168000 blocks at level 4,
2100 blocks at level 3,
26 blocks at level 2,
1 block at the root level.
This adds up to a little over 27 million blocks in the index, again assuming 100% utilization.
With keys of 90 bytes or more and a table with a billion records or more, change the reco.rds per block setting to a smaller value than the default..