Understanding OpenEdge Index Compacting | OmegaServe

Index Compacting

Indexes are structures that hold keys that allow fast access to record data. The index structure is typically about 10% of the total table size. Ideally heavily accessed tables will have the entire index structure cached in memory.

The index structure is held in a B-Tree format. A B-Tree allows traversing the tree to find the location of a key that matches the request. This is done quickly and typically only needs to access 3 or 4 database blocks to find the match. From there the application can fetch the row based on the ROWID. Conversely, if there was no index, hundreds or thousands of database blocks would need to be accessed to find the desired row.

The Index holds the entire range of values in the table. For example, if there is an index on a field called “Name”. The index would contain all possible values for the names in the table.

When all the index blocks are completely full, the next new entry will create an index block split. Before a new key can be inserted into the block, the block is split into 2 different blocks and the parent block gets the new pointer to the new block.

Once this is complete, the new key can be inserted into the appropriate block. This is a common occurrence when adding new data. Over time while rows are being created to the table this leads many index block splits which results in poor index utilization.

Use Dbanalys or Ixanalys to find an index block utilization.
proutil dbname -C dbanalys

In the above example there are many indexes on the tr_hist table with some of them having 3 levels indexes and 4 level indexes.

The tr_hist table has 47,118,632 rows in it. This is stored in 3,524,450 4 KB database blocks, or about 13 rows per block. Conversely the tr_addr_eff index uses 20,482 4 KB database blocks, which means it can store 2,300 keys in each block.

The tr_part_eff index is a 4-level index consuming 74,469 4 KB database blocks, but the index size is only 148.7 MB in size. 74,469 x 4096 = 305 MB. This means that most of the index blocks are half filled. Compacting this index would accomplish two things.

1. The index B-Tree would move from a 4-level index to a 3-level index. Benchmarks have proven that there is a 20% performance gain when using a 3-level index vs a 4-level index for the same data.
2. With full index blocks, the likelihood of a future index lookup would be in the same block is much higher than with ½ full blocks. If the index was completely full it would take 38,068 blocks, which would put 1,237 keys in each block, whereas it stands now, it only holds 632 keys per block.
   

How to Index Compact

• Run online
  
• Run with After Imaging Enabled
  
• Run with OE Replication Enabled
  

It is run one index at a time
proutil dbname -C idxcompact table.index [utilization]

The index compact is going to read the entire index into memory and work on the index block by block to make changes. During this time, there will be much more Before Image activity, After Image activity, and OE Replication activity. It is best to first test it on a test copy of your database to see how long it takes to run, then decide when to run it on production, either at night or on a weekend, if it takes a long time to run.

Recommendations

There is not much value to compacting small indexes as their utilization will decrease rapidly as there are not many index blocks associated with the index. A good rule of thumb is to look at indexes that are more than 50 MB in size. If these larger sized indexes have a utilization of less than 80%, it is worth compacting them. The default compaction level for index compact is 80%, which is too low, especially for large indexes. It is better to use 90% - 95%. Do not be tempted to use 100%, as this will cause many index splits, which can be a performance killer, when rows are being added to the table.

Conclusion

Maintaining database indexes is just one of the many activities required to have a well performance application environment. Regular monitoring and periodic maintenance is required. Stale systems have the risk to running into problems like poor performance or unplanned outages. This article is addressing one aspect of the required maintence. If in doubt, ask a professional

OpenEdge System Health Check

Is your Progress OpenEdge database running optimally? Could there be an unknown issue that could potentially damage or bring down your production database?