Kbase P23045: Why does idxfix ALL option 3 online seem to grind to a halt?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/15/2005 |
|
Status: Verified
GOAL:
Why does idxfix ALL option 3 online seem to grind to a halt?
GOAL:
Why does idxfix take such a long time to run?
GOAL:
Why is idxfix online so slow?
GOAL:
Can the performance of idxfix online be improved?
FIX:
This Solution discusses running idxfix online as follows:
proutil dbname -C idxfix ALL
Select Option 3: to 1 (Scan records for missing index entries)
and 2 (Scan indexes for invalid index entries) above.
When these options are selected, the entire database is scanned once (as typically done by index rebuild) as well as each index being traversed and the record associated with each index entry being retrieved.
There are m a n y more reads going on in this scenario than, for example, in an index rebuild (idxbuild), as the same record is retrieved once for each index on the table. In addition, because the utility can be run online, these reads are performed with share-locks, so there is probably also a great deal of lock queueing / waiting going on.
There is no sorting used during idxfix, so adding the speed sort parameters -TB and -TM will not speed the process up. It is merely a process that reads records in tables, to check that there are indexes for the records and a read of indexes to check that there are associated fields in records. Note that records will not be fixed during an idxfix, merely reported.
Similarily, if -Bp was added to idxfix at startup, other online users may benefit from it, but not idxfix. Running with -Bp is similar in terms of "nice'ing" a process on UNIX. It causes more accurate utilisation of the database buffer pool LRU algorithm. This in turn could improve performance for other user processes accessing data out of the buffer pool by increasing their buffer hit ratio, if they are also accessing the "interesting" blocks in the buffer pool.
If the idxfix is used online to build indexes, then the time to activate the index is usually very small (related to how many additional index changes may have occurred after the first idxfix started). The time to build the index entries however, for example with word indexes, is directly related to how complex the index is and how many records are involved. It may take a long time if there are a lot of records.
Please note that disabled indexes will not be enabled when idxfix is run online, but the utility will check them. It will build all indexes but not activate disabled indexes. Completing index activation will require an idxfix offline (or idxbuild) of the same index, which will activate the index if no corruption is found.
The performance of idxfix online is affected by other online users or transactions. The process of an online idxfix operation runs SHARE-LOCK, it will constantly wait, grab, and release locks. If there are other transactions requiring same locks, idxfix would have to compete against these and this would cause a performance hit.
The above taken into account, it is worth investigating the following:
1.) What does promon show for status of the index fix connection?
2.) What is the CPU utilitisation show? Is there any activity? Has the opperation hung or just processing a lot of data?
3.) If the idxfix operation has hung, could it be waiting on a lock that has yet to be granted?
To ensure the data integrity ending the process will not mean that idxfix will be ended immediately. It will backout the last idxfix transaction which, should be small. If CTRL+C is issued when idxfix is waiting on a lock for example, the process will not end before idxfix gets that lock. Idxfix would then "seem to grind to a halt".
A 'workaround' could be to refine the choice to 'some' or split the operation up among processes and tables then run idxfix concurrently. It has been suggested that running the online idxcompact beforehand, if index fragmentation is high, will improve the runtime execution. Otherwise, especially in 24 x 7 operations where downtime is precious but is. is necessary to investigate all indexes, run idxcheck on a restored copy of the production database, then feed the results of idxcheck (iow: a subset where index corruption is reported) into an online idxfix, to 'cut down' the workload of the online idxfix operation. Although idxcheck is much slower because of the nature of the checking it does, it has been known to be more thorough in reporting index corruption because it was designed to detect index corruption as sensitively as possible at both record and index block level..