Kbase P177289: Performance issue after mass data deletion in the database
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
22/11/2010 |
|
Status: Unverified
SYMPTOM(s):
Performance issue after purging large amounts of data in the database
4GL/ABL queries against the tables involved in mass deletion and perform up to 200 times slower than before the mass deletion when no index rebuild or index compaction was performed
Queries use a unique index on the table
No index rebuild or index compaction performed on affected tables after mass record deletion
FACT(s) (Environment):
Various tables had large number of records deleted using 4GL/ABL
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
Bug# OE00187780
CAUSE:
When index entries in a unique index are deleted, they are replaced by placeholders that reserve the entry until the deleting transaction commits.
This is done so that another transaction cannot add the same unique entry and thereby prevent the deleting transaction from rolling back.
These placeholders are no longer needed after the deleting transaction commits and they are cleaned out the next time the index block that contains them is written to disk.
However: if the block is not written to disk again because there are no modifications to it, the placeholders remain .
When read queries traverse the entries in an index bracket, the placeholders are skipped over. When there are a large number of placholders within a bracket, possibly in multiple index blocks (which often occurs when one deletes many rows in a table), this can cause noticeable delays and extra index block reads.
FIX:
Perform an index rebuild or (online) index compaction on all tables which had large amounts of data purged