After Image (AI) extents are switching rapidly when adding 15,000 new records to a table.
Environment
RL_CXREM represents a 'remove index entry' note.
Command: proutil dbname -C dbanalys > filename
All Supported Operating Systems
OpenEdge 10.1x
Question/Problem Description
After Image (AI) extents are switching rapidly when adding 15,000 new records to a table.
1,500 AI log files created (totaling 21GB) during rapid ai switch interval
A scan of the ai files using the command: rfutil dbname -C aimage scan verbose -a <ai file name> shows a large amount of code = RL_CXREM
Output from aimage scan command shows that 98% of the notes within the ai files are referencing index areas that are associated with the records that were deleted.
Trid: <number> area = <area> dbkey = <dbkey> update counter = <number> (12529)
Trid: 1831818283 area = 64 dbkey = 164718080 update counter = 1919 (12529)
Index Delete Chain Analysis portion of the dbanalys output is showing 0 blocks for most index objects.
Index Delete Chain Analysis portion of the dbanalys output is showing a large number of blocks for a few index objects (608, 616, 627 and 619)
Example Index Delete Chain Analysis: 53912 block(s) found in the Index Delete chain of Index object 627
Index objects that contain large amount of blocks on the Index Delete Chain are Unique indexes.
The unique indexes are associated with the 70 million records that were deleted recently
Clarifying Information
Recently deleted 70 million records across 3 different tables.
Cause
When records are deleted, index-trees are not rebuilt. The entire B-tree is left intact. During a transaction, when a record is deleted, if there is a unique index, a delete place holder is put in the place of the RECID for the deleted record. Index blocks that contain these deleted place holders appear on the index delete chain. Every time an index entry is about to be added, the index delete chain will be checked. If there are blocks on the chain, one block will be removed and processed for deleted entries. Over time as records are being added to the table, index blocks containing these deleted place holders will be processed and written out to disk.
The addition of 15,000 records to the table (that contained index delete blocks), resulted in the processing of a large subset of these index delete blocks to disk. The processing of these index delete blocks resulted in significant growth and increase in the number of AI (After Image) files being used for the period when the new records were being added to the table.
Resolution
If you are deleting/archiving a lot of records from a table (that contains a unique index), you now know that you will have delete place holders associated with each of these deleted records. These delete place holders reside in blocks on the index delete chain. The blocks on the index delete chain will need to be processed. The goal is to minimize the impact of processing these blocks.
You have two options:
1. You can either process a subset of these blocks over time as records are added to the table.
This option requires minimal involvement from you except to realize that bigger transactions will require more AI (After Image) and BI (Before Image) space due to processing these index delete place holders. We reuse space within the bi file by making use of clusters. The AI file does not however make use of clusters. This means that you may need to monitor and prepare your AI files for growth and excessive switching as a result of processing these index delete blocks.
2. You can use idxbuild, idxcompact, idxmove or truncate area to process the entire index delete chain for a unique index.
The following is a list of some considerations involved with some of these options:
A "proutil -C idxbuild" does not make use of the AI or BI file and therefore can be ran offline against the unique indexes to address this issue.
Idxcompact and idxmove does make use of the BI and AI files. If you execute these commands against a database with After Imaging enabled, you may need to monitor and prepare your AI files for growth and excessive switching as a result of processing these index delete blocks.
If you want to use "proutil -C idxcompact" online, the recommendation would be that you get to 10.1C04 and later or 10.2A01 or later, due to a known issue that resulted in the index delete chain being processed in a large transaction scope. This resulted in excessive bi file growth due to our not being able to reuse bi clusters when running this utility.Статистика: Добавлено dmi — 21 ноя 2013, 01:32
]]>