order of execution?

H

HTX

New Member
Goodday to you all,

I was planning to rebuild my indexes en restructure my database in order to gain more performance and reclaim diskspace/database space.
Over multiple multivolume databases the variable extents are considerably larger than the fixed ones also are there some issues with extent locations that could be improved.

The plan currently is to backup the database to tape with probkup.
procopy the database to a temp structure
prodel the original database otherwise not enough diskspace for idxrebuild.
idxrebuild the temp database,
create a new .st file accordingly to the new disklayout
procopy the temp in to the new database
prodel the temp database

This should work and get the objected results.

The thing I was wandering about is the order of execution of the idxrebuild i could restructer the database first and at the end rebuild its indexes?

Is this in any way better faster worse etc etc.
If any of you would provide some comments I would be very thankful.

Best regards,
Arjan van der Meer
 
This may not be what you want to hear but I think you would be much better off doing a dump & reload.

You don't specify what version of Progress you're running but if it is v9 or better then you would also probably benefit tremendously from rethinking your storage areas. See:

http://www.greenfieldtech.com/articles/storage_areas.shtml

for some ideas on that subject.

One more thing -- not having enough disk space for an index rebuild without deleting the source database suggests that you are managing your resources with an eye towards optimizing disk space. Generally speaking you sacrifice performance when you do this. Disk subsystems have two major capacity metrics -- storage space efficiency and IO operation throughput. When you optimize one you de-optimize the other. You can generally either have a fast disk subsystem with lots of extra space or a full disk subsystem that is slow.

Bean counters are usually only aware of space and tone deaf to arguments regarding IO throughput. One way to convince them that you are not "wasting" space is to specify that you need (mirrored) space for 1) the production database, 2) a disk backup (staging for the tape backup) 3) a recovery area 4) a dump area (2x db size), 5) index rebuild area 6) -T area and 7) after image logs going back X months. Plus anything else your application might need. (Distinct development and test environments are also needed but those are usually a distinct server...) That sounds like a lot but if you have it you will find that tasks like dumping and loading and rebuilding indexes are much simpler. You'll also be in a much improved position for crash recovery as it will not be necessary to destroy that crashed database in order to restore (if the recovery fails you still have the crashed db to work with, and sometimes you need data that is only in the crashed db...)
 
thanks 4 the reply.

The progress version is 8.3D and we have our reasons not to update to V9. It isnt relevant to the question I asked. The current application that uses Progress is custom made and planned to be phased out before the end of the year.

But before the end of the year it is likely that the database wil grow even more and that the ugly extents we have now wil be a concern.

The "space problem" is that we have enough space for 1 database copy or 1 rebuild of the index.

The plan I made was to overcome this problem and make optmial use of all available filesystems.

The goal is to gain better performance through the index rebuild and beter disk usage through reorganising the database extents over different filesystems.

The question was in what order to perform the several commands io to get the objected goal
 
You must log in or register to reply here.
Back
Top