Performant Index Rebuild Options that were introduced in 10.2B06 are described in the 10.2B06README file and the following Documentation:
Note: OE00218544 Type: Behavior Change Index Rebuild Performance Improvements
- The Sort Block Size -TB maximum value is 64; previously 31 and if not specified, the default is 8
- An additional new parameter, -rusage reports resource statistics including user and system CPU usage, approximations for disk reads and write I/O statistics.
- Four new parameters: -TF, -TMB, -datascanthreads, -mergethreads support tuning the index build operation to enhance performance. Effectively while IDXBUILD remains an offline operation, the time to complete rebuilding indexes is based entirely on the performant capabilities of the machine. Example:
$ proutil <dbname> -C idxbuild activeindexes -SG 16 -TB 64 -TM 32 -TMB 128 -B 16000 -TF 88 -thread 1 -threadnum 6 -mergethreads 4 -datascanthreads 4 -pfactor 90 -rusage
-TF <n>: memory usage factor
- Percentage of system memory to allocate when merging sort buffers when sorting index keys during an threaded index rebuild process. Can be used since OpenEdge 10.2B04, but 10.2B06, 11.1.1 or later are advised where several issues related to the -TF parameter were addressed, in Article: Index rebuild process does not exit after reported as completed on Windows 64-bit
- The default value of -TF is 50 (for 50%) which allows for better in memory sort merge operations. Sort Files are still used when memory is insufficient for this phase. What is the -TF Option used with PROUTIL IDXBUILD?
- Setting -TF 0 results in the pre-10.2B04 behavior which uses only one sort merge buffer, which writes the contents to the file system when it fills.
- When process memory configuration limits do not allow access to the memory requested, unpredictable failure may occur, such as being unable to spawn helper threads or other memory allocation failures. Such an error will cause index rebuild to terminate. It is therefore imperative to set the ulimit values appropriately for the index rebuild process. Example: Index build fails with a Killed message on screen
-TMB : Merge Block SizeFacilitates tuning the size of the blocks used to
merge sorted blocks together, separately from the
Sort Block Size (-TB).
- The default/minimum value for -TMB == -TB value.
- If -TMB is set to a value less than -TB, index rebuild will exit.
-datascanthreads: Data Scan Threads Facilitates the Scan Phase to run as a multi-threaded data scan which spawns the concurrent threads required based to scan record's key-field data associated with the index being rebuilt. The data being scanned, must meet all of the following criteria for a multi-threaded data scan to occur:
- The data area being scanned must be a Type II Area
- Index rebuild must be run with the "sort" option, that is answering "y" when asked if you have enough room for sorting
- No index within the data area being scanned is also being rebuilt
- No index associated with the data area being scanned is a word index
If any requirement is not met, the data scan is performed using the original single threaded mechanism. When not specified, the -datascanthreads value defaults to 0, also indicating the original single threaded mechanism. The data scan phase can be confirmed as running multi-threaded by observing the following messages during the data scan phase of index rebuild:
Processing area 689 : (11463)
Start 4 threads for the area. (14536)
Area 689: Multi-threaded record scan (Type II) complete. Elapsed time: 6.740
Known issues with using Data Scan Threads:
-mergethreads: Merge Threads Facilitates the Sort/Merge phase to spawn concurrent threads when key entries are merged in the sort blocks produced by the data scan phase for each Sort Group.
- There is no restriction on Area or Index Type or datascan threads being used for invoking the -mergethreads feature
- The index rebuild process must be run with the sort option and -thread 1 and -threadnum <n> must also be specified
- When running index rebuild without the sort option, there is no sort/merge phase which negates these performant parameters, as index key-fields are built as they are scanned much like record create/update operations.
- A value of 0 (the default) results in the original, pre-10.2B06, behavior of one thread per index sort group during the sort/merge phase of index rebuild if a threading is enabled: -thread 1 -threadnum <n>
- A value of 1 also results in the original behavior of one thread per index sort group, but the sort/merge is performed by a newly spawned thread rather than by the thread assigned to the individual index sort group.
- Specifying -mergethreads GT 1, allows multiple threads to merge data within an individual index sort group. The number of threads spawned during this phase:
[ the number of merge threads ] times the lesser of the [ -threadnum value | the number of index Sort Groups in use per area ]
The sort/merge phase of index rebuild can be confirmed to be processing with the new merge threads option by observing the following messages during the sort/merge phase if an index rebuild:
Spawning <n> threads for merging of group <n>.
If the -thread parameter is not used in the commandline, but any of the other idxbuild parameters that are inherently thread related such as -threadnum, -datascanthreads, or -mergethreads has a value higher than 1 then threads will be used for the indexbuild.
For both -datascanthreads and -mergethreads, if the requested number of threads can not be spawned, index rebuild exits with an error, for example:
Unable to run with requested thread configuration of 48 threads due to system memory restrictions.
Additional memory required: 100663296 Limit: 134217728 In use: 41036736
Threads requested: Data scan: 0, sort: 8, merge: 5, sort groups: 47
