Kbase P142939: Database engine does not reuse space when creating, deleting or update records
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
06/04/2009 |
|
Status: Verified
SYMPTOM(s):
Database engine does not reuse space when creating, deleting or update records
Space not being re-used for RAW fields storing XML documents
Dbanalys shows that Average Record size multiplied by Total # of records should occupy circa. 2GB of space.
prostrct statistics shows physical storage being used of 6 GB
prostrct statistics shows storage area is growing by around 600MB a week
dump and load does not improve the situation
no difference in Type I or Type II Storage Area architecture
tabanalys shows no real increase in overall data for this area but very high scatter factor 7
Table-history is that records are imported in batches then deleted once processed
Indexes build for area suceeds without error
RM and Free chain rebuilt without error.
No corrupted blocks on the RM chain
No Empty blocks below the high water mark
Chanalys not showing 3910 and 6704 errors.
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
CAUSE:
Record sizes range from 30 bytes (empty file) to up to 8 KB. Although we expect fragmentation (due to record sizes) the fact that scatter is continually increasing in an area that only contains one table, is a hint that space from the deletes isn't being re-used.
With such a disparate range in record sizes, the space allocation algorithm needs to balance performance. With the default Toss Limit set, this allowed for better packing of the smaller records. However, this has the adverse side effect of making the RM chains longer until the smaller records are added. Longer RM chains means longer searching for space for the big records which in this case exhausts the number of times the database engine searches thereyby taking EMPTY blocks from above the High-Water Mark (HWM)
FIX:
The following changes improved the expansion rate in this Storage Area dramatically:
(From a growth of 600MB to 4 MB per week)
- Increasing the -recspacesearchdepth to 10, the HWM stabilized.
- Decrease the Storage Area Create Limit to 32 since the records don't grow this ensures maximum block size availability (marginal effect).
- Increase the Storage Area Toss Limit to a little below the average record size. This in effect reduces the records per block to 1 since the average record size in this case is 5 KB, while still allowing a higher rpb (32) to allow for the smaller records.
The above provides an example of a specific case investigation. It is important to take prostrct statistics and proutil -C dbanalys snapshots prior and post each database structure change.