PROUTIL -C IDXCHECK index checker was designed to check indexes for corruption as sensitively as possible.
IDXCHECK performs these checks as follows:
- Check that all index entries associated with each record can be found
- Verify that each value in the index is in the associated record
- Validate that all index blocks can be referenced
An appropriate error will be reported if any of these fail. This operation will take time as there are a lot of reads taking place, depending on the number of records and indexes associated with each record.
When there are other processes running on the machine that are load intensive, this will impact the performance of the operation. It is worth checking the CPU utilization beforehand and verifying what other processes are running at the same time before and during an IDXCHECK operation.
The size of the Buffer Pool (-B, -B2) may help but this can only be gauged through testing. There is a 'sweet-point' above which there is no further gain because it depends on how blocks are being paged out. It might help during the phase when the index validates the RM data as opposed to the RM data being validated against the index data. In the first case, it could help if available memory will allow the
entire table and index can to fit into the buffer pool at the time. Cautionary note: increasing -B too high will result in termination with: ** stget: out of storage. (6498)
IDXCHECK is not multi-threaded until OE 12.7,12.8 LTS(
https://docs.progress.com/bundle/openedge-whats-new/page/Whats-New-in-OpenEdge-12.7.html#Run-Multi-threaded-Index-Checks ), but IDXCHECK will take advantage of TYPE II structures when run online.
1. Consider running an IDXCOMPACT before hand to compact the index leafs. This separate operation will initially add to the total time, but future IDXCHECK operations will benefit.
2. Exclude WORD indexes where possible, these do take a lot longer and then running a "blind" idxbuild on these specific indexes as a separate operation.
3. Run parallel IDXCHECK online on areas for example (not anything that would compete with each other)
- First run without locking: IDXCHECK -NL -Bp 32
- Verify specifically on errors from no-lock: IDXCHECK table <tablename> ] -lkwtmo 10 -Bp 32
4. Use a restored copy of production to run periodic IDXCHECK operations on another VM, then feed specific fix operations with the IDXFIX online utility in production while the source of corruption is identified:
5. IDXCHECK is an intensive check and by that nature 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. For example:
Is it expected that IDXCHECK Option 3 detects 1422 issues but takes longer than IDXFIX Option 3?Otherwise, it was determined that the Progress Product is functioning as designed.
An enhancement request has been submitted as an Idea on the Progress Community. To promote the Idea, click on this link:
https://openedge.ideas.aha.io/ideas/OPENEDGE-I-852. Customer feedback is valuable and Idea submissions are monitored by our Product Management team. Enhancement requests are reviewed during the planning phase of each new product release and a list of the enhancements chosen for implementation can be found in the Release Notes documents that accompany each release. Once an Idea is submitted the Progress Software Community will have the opportunity to comment on and vote for the Idea. It's priority will be evaluated as customer demand weighs in through Article Feedback and through vote count in the Ideas Portal.
Update - This feature was introduced on OE 12.7, 12.8 LTS
https://docs.progress.com/bundle/openedge-database-management/page/PROUTIL-IDXCHECK-qualifier.html
