Kbase 19872: How to Determine When to Dump and Load Your Database
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/9/2010 |
|
Status: Verified
GOAL:
How to determine when to dump and load a database
GOAL:
Using dbanalys to decide if it's time to dump and load
GOAL:
What is the Record Scatter Factor reported in tabanalys
GOAL:
What is the Index Factor reported in idxanalys
GOAL:
How to find how much fragmented is a database
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
Dumping and loading your database tends to cause records for the same table to be clustered together in blocks and for those blocks to be contiguous on disk. Sequential access is greatly improved and on reloading, the least scatter will result by loading the data for the table with the smallest average record size first and the table with the largest average record size last.
Two key factors that should therefore be considered when determining if you should dump and load your database, are the index factor and the scatter factor which can both be found by running a dbanalys report against the database:
$ proutil dbname -C dbanalys
The Record Scatter Factor:
The table scatter factor is a numeric representation of the closeness of the records for a table to each other. A value of 1.0 indicates that the records for a table are as close to each other as possible.
The lower the scatter factor, the more likely it is that a block that holds a record for a table will contain other records for the same table.
When multiple records from the same table are read, it is more likely that a buffer will be found. This leads to an extremely general statement that the higher the table scatter factor, the lower the buffer hit rate, therefore the busier the disk drives and the worse the performance.
A scatter factor is a good indicator of the quality of record fragmentation only when it's value is close to one so it is preferable to have a low table scatter factor. Experience has proven that performance gains are found after a dump and load is done when scatter factors are greater than 4.0 to 4.5 on tables with more than 100 records.
Progress 9.x introduced storage areas in the database architecture which allows you to influence the scatter factor of a table by assigning it to its own area and selecting an appropriate records per block. Done wisely, this can eliminate the need for performance related dump and load operations.
The Index Factor:
The index factor is a numeric representation of how much unused space is combined in all the index blocks of the database. It compares the percentage of space used in the index blocks to the most compact the index blocks could become.
With an index factor between:
1.0 - 1.5: The index is in good shape.
1.5 - 2.0: The index may need compaction.
2.0 and higher: The index needs compacting.
If the index were compacted, it would occupy less space in the database and use less memory because each block would be fuller. The number of index levels would probably decrease after compaction. This means that accessing a record would require fewer database reads.
Using idxbuild does not have much effect on compaction because it uses the database blocks on the free chain in the database. As a result the used blocks may not end up being ordered contiguously.
Progress 9.x has an online index compacting utility that lessens the need for performance-related index rebuilds:
$ proutil dbname -C idxcompact Table.Index [percentage]
Another good tool to use (which is currently undocumented) is the idxblockreport. The command is:
$ proutil dbname -C idxblockreport PUB.tablename.indexname
You run this utility as a baseline against those indexes that you may suspect are in need of compacting. Then run an online idxcompact for the index. The performance improvement can be measured by running an intensive report as a baseline, then running this report again after an index compaction exercise.
There's also the efficiency of indexing to be considered, incorrect index usage slows things down in a big way. Check for: deactivated indexes, reports that do full table scans, and specific transactions taking too long to execute.
After dumping and loading your database, baseline your scatter factor which will assist you in future decision .making (eg: performance degradation) as to when it's time to dump and load again..