Kbase P140893: A binary load is taking longer to load records in OpenEdge 10 than in Progress 9
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
08/09/2009 |
|
Status: Verified
SYMPTOM(s):
Loading binary table data in OpenEdge 10, using the proutil <dbname> -C load option, can take significantly longer than a binary load using Progress 9.
Running a proutil <dbname> -C dbanalys on the Progress 9 database after the binary load shows that the database has one block found on the RM chain for that table's area.
Running a proutil <dbname> -C dbanalys on the OpenEdge 10 database shows a significant number of blocks found on the RM chain.
Running promon during the Progress 9 and OpenEdge 10 binary loads shows that OpenEdge 10 has a much larger number of DB reads and DB writes.
example: promon <dbname>
R&D
2. Activity Displays
1. Summary
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.1E04 Service Pack
OpenEdge 10.1C02 Service Pack
Table data is dumped using the proutil <dbname> -C dump option in Progress 9.
Table data is loaded using the proutil <dbname> -C load option in OpenEdge 10
The default values for the create and toss limits in Progress 9 and OpenEdge 10 are as follows:
Create Limit (bytes) Toss Limit (bytes)
---------------------------------- -----------------------------------
DB Blocksize Progress 9 OpenEdge 10 Progress 9 OpenEdge 10
--------------- ------------- ---------------- ------------- ----------------
1024 75 75 150 150
2048 150 150 500 300
4096 300 150 1000 300
8192 600 150 2000 300
CAUSE:
Changes to the default create and toss limits in OpenEdge 10 can cause differences in behavior with regards to record block storage utilization.
Under certain conditions the RM chain can have many more blocks on it in OpenEdge 10, compared to Progress 9, because of the relationship between the OpenEdge 10 default create and toss limits, and the average record size of the data being loaded.
More blocks may be needed in OpenEdge 10 to store the same number of records as in Progress 9 if the default create and toss limit values are used.
The extra overhead of creating more blocks, and added work of searching through and maintaining a large RM chain, are causing the binary load in OpenEdge 10 to take longer to load the same number of records than it did in Progress 9.
FIX:
Change the create and toss limits based on the average record size of the table being loaded to get the most efficient block storage utilization possible. In addition, consider changing the records per block value for the area that the table is being loaded into. This value also affects the block storage utilization.
The following are general recommendations and should tested in a development environment prior to putting into production, to see whether performance improves or not:
Run a dbanalys on the database and note the values for the minimum, maximum, and average record size(s) for the table(s) being loaded using the binary load utility.
Set the records per block for the table's area to appropriate values as outlined in the OpenEdge 10 Database Administration and OpenEdge 10 Getting Started Guides (see references below).
Set the database area toss limit (using the SETAREATOSSLIMIT qualifier of the proutil utility) to just under the average length of the records as reported in the dbanalys output.
Set the database area create limit (using the SETAREACREATELIMIT qualifier of the proutil utility) to the minimum value of 32.
Changing the OpenEdge 10 default create and toss limits to the Progress 9 defaults may not yield comparable binary load performance as seen in Progress 9.
This is due to changes in the block header size of a record block. These changes affect the amount of available space in a block, which can be less in OpenEdge 10 than in Progress 9.
In Progress 9, the block header size + overhead = 20 bytes.
In OpenEdge 10, the block header size + overhead = 20 bytes for a Type I area.
For Type II areas, the block header size + overhead is either 68 or 84 bytes, depending on the block type.
To achieve similar performance for a binary load in OpenEdge 10, it may be necessary lower the create limit to be able to fit the same number of records in a block as in Progress 9.