Kbase P3586: Interaction between proutil -C tablemove, before-image and after-imaging
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
30/03/2009 |
|
Status: Unverified
GOAL:
By how much will my BI file grow during a tablemove?
GOAL:
Interaction between proutil -C tablemove and after-imaging
GOAL:
Interaction between proutil -C tablemove and before-image
GOAL:
Why proutil tablemove command causes the BI file to grow?
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
Progress 9.x
FIX:
When Progress moves a table from one storage area to another, it does so in one single transaction, so that if the tablemove operation is interrupted the database can be automatically restored into a consistent state.
During a table move, all the records from the source table are read, equivalent records are created in the destination table, and the source records are deleted. In addition, all the index entries for the source side are deleted and new index entries created for the target side. Aside from that, there are also space allocation/deallocation operations. All these operations are logged in the normal manner.
This means that the tablemove operation is logged both in the before-image and (if enabled) after-image files.
As a consequence the before-image file can grow very large (a number of times larger than the space taken by the table and by the indexes) and after-imaging can cause a high number of after-image extent switches.
Currently there is no easy way to calculate in advance how much space will be required.
The simplest way (but not necessarily convenient) is to make a separate database that contains a copy of a subset of the table to be moved, do the move and see how much log data is generated. Then scale that number up by
the size of the sample in comparison to the actual table.
If the sample is 10% of the actual table then moving it will take pretty close to 10 times what moving the sample did. Or 5% and 20 times.
Allow enough disk space for your before-image; if after-imaging is enabled, make sure that the after-image extents can be backed up, stored and marked as empty quick enough to prevent Progress from filling all after-image extents.
The following is an example which shows a very rough estimation of how much BI growth could be expected:
Say the table is 300 megabytes and contains 100 byte rows (to keep the math simpler). Thus there are 3,000,000 rows.
To create a row in the new area requires a bi note of approximately 200 bytes. To delete a row in the source area requires a bi note of approximately 200 bytes plus creating a placeholder note of approximately 100 bytes.
In addition, space allocation notes will also be needed and notes to update the rm chain. Assume these consume another 100 bytes per row (probably the real number is less).
That all adds up to 600 bytes of bi notes per row.
600 * 3,000,000 is 1,800,000,000 bytes.
This does not include any index entries. The number of notes required for them is dependent on the number of indexes. For each index, an existing entry that points to the source row has to be deleted and a new one that points to the new row in the target area has to be inserted. Each index has 3,000,000 entries. Assume 100 bytes of bi to delete an entry and 100 more to insert a new one. That's 200 * 3,000,000 or 600,000,000 bytes more per index.
Those numbers are just rough estimates, but they are probably in the right ballpark