What performance gain when using IDXBUILD multi-threads
Advantages to use IDXBUILD with option "-threadnum"
What are the index rebuild (idxbuild) enhancements that were introduced in Progress 9.1D07 and OpenEdge 10?
What improvements have been made to the idxbuild utility in Progress 9.1D07 and OpenEdge 10?
How can I make an index rebuild run faster to reduce downtime?
How to improve the performance of idxbuild?
How are Sort Groups (-SG) used in idxbuild?
How are threads used in idxbuild to improve performance?

Progress 9.1D07, OpenEdge 10 index rebuild (IDXBUILD) enhancements 

The IDXBUILD index rebuild utility contained new performance enhancements that include:

• Changing the way CI (cursor index) structures are built and located.
• Changing the format of a key entry inserted into a sort file to use 3 less bytes per entry.
• Simplifying and reducing the time to build a key entry.
• Removing unnecessary format conversions.
• Modifying the algorithm used to scan a database so that free and index blocks are not recycled when a split scan is required
• Improving the algorithm used to parse the .srt file used for sorting groups (-SG).
• A new algorithm for the external sort and merge Phases reduces:
  • Sort and Merge I/O between different indexes by creating more index groups
  • The cost per block I/O operation by having each index group have its own set of input/output buffers and temporary disk files.
  • The number of read/write operations for each block in a temporary file by adding more input buffers.

What performance gain when using IDXBUILD multi-threads

Result of a test with 8 CPUs, a 4.2GB database, 733 tables, 1333 indexes, a .srt file with 5 I/O devices of 2GB each running index rebuild with threading (-threadnum 8): 
Showed a 20% improvement compared to the non-threading model.  For a larger database with a similar layout, the improvement trends higher.

A Multi-Threaded IDXBUILD allows Phase 3 concurrency with Phase 2

The performance of an index rebuild is affected by the following factors:

• The database layout,
• The number of indexes to be rebuilt,
• The I/O devices defined in .srt file,
• The CPU and OS resources.

Index rebuild is intensive in both CPU and IO operations, the IDXBUILD utility is divided into Phases and operations, some of them are IO bound, some are CPU bound where performance improvements can be achieved by allowing a threaded index rebuild for Enterprise database licenses. 

• During Phase 2 where index groups are merged in series, the threading feature allows the main process to assign work to multiple threads during the external merging of multiple index sort groups.  
• Adding threading allows multiple groups to perform this operation concurrently. 
• After the main process create other threads, it also participates in the work of building index trees for groups that have finished the external merging, known as Phase 3
• With threading, Phase 3 can be performed in parallel with Phase 2 as soon as the thread merging that group has completed external merging.

By default, IDXBUILD will use threading (-thread 1) and will set the number of threads (-threadnum) equal to the number of CPU's available on the system. However, if an area has only one index to rebuild or only one index is specified to rebuild, IDXBUILD will run without threads.

For a machine with 12 CPUs, -threadnum 12:

• At most 12 threads will start and work in parallel in Phase 2
• If the number of index groups is equal or more than 12, 12 threads will be all in the work, which will take the most advantage of CPUs.
• If the number of index groups is less than 12, say 5, 5 threads will start and work in parallel
• If the number of index group is only one, it will not take advantage of multiple threads, because there is no other group to be worked with concurrently.

Prior to OpenEdge 10.2B06, the threading during the sort/merge phase of index rebuild uses one thread per index sort group by setting the -thread 1 and -threadnum <n> parameters. Since 10.2B06, specifying -mergethreads allows multiple threads to merge data within an individual index sort group (-SG).  The number of threads spawned during this phase is the number of merge threads times the lesser of the -threadnum value or the number of index sort groups in use per area. For further detail refer to Article

• What are the new Index Rebuild parameters that were introduced in 10.2B06?  

Phase 2, External merging is I/O intensive: 

Imagine an 8-lane highway (-threadnum) with a one lane toll station (I/O device). 

• Temp files are accessed for read and write randomly and intensively.
• When multiple temp files of different index groups reside in the same I/O device, threads compete for I/O resources which will become the bottleneck. 

To solve the problem, this feature allows temp files of different index groups to spread over different sort directories. The new algorithm assigns directories defined in .srt file to index groups by order instead of using the next directory only when the prior sort directories are filled. For this reason, it is recommended that multiple I/O devices are defined in the .srt file.

The multi-volume sort file (.srt)

Since large file support was added for temporary files (9.1D07, 10.1C), this reduces the number of file handles needed by being able to define less sort directories larger than the 2GB limitation.

• However, IDXBUILD threads compete for I/O. The sort and merge Phases of idxbuild experience higher competition for I/O than other stages, and the threaded sort and concurrent merge phase increases this competition. Performance improvements were made to reducing the I/O cost of the external sort and merge, but all effort should be made to spread out the I/O to minimize disk contention as much as possible.
• Therefore, it is still important to have different directories defined on separate I/O devices for temporary index sort files in the .srt file to take advantage of threading by minimizing I/O bottlenecks

 

Internal priority where temporary files are created:

1. If the -SS option is used, then the location specified is used to find the .srt file. 
2. If -SS is not used, the current directory is searched for a .srt file. If found, that file is used. 
3. If -T is specified, then that directory will be used for the temporary files (but only if -SS was not used and there is no .srt file located in the current working directory). 
4. Finally, if none of the above, the current working directory is used as the location of the temporary sort files.

How temporary sort files are created has changed:

• Pre 9.1D07, when 30 directories are configured in the .srt file - 30 files will always be opened even if not all the space are needed.
• Post 9.1D07, the Index Rebuild utility accesses the files in the order in which they are listed in the .srt file. Files for each of the directories are opened before it actually starts the sort process, which results in the message:
  Temporary sort file at pathname used up to nK of disk space.
• There's no point in defining more srt files than sort-groups (-SG) as each SG uses only one sort file (1:1)

For example: If a value of -SG 48 is used, then 48 files will be opened in the first directory of the 30 specified in the .srt file. When the sort-file's size limitation is reached only then a new file will be opened in the next directory, and so on because temp files of different index groups can now be spread over different sort directories with threading.

As an example, imagine rebuilding an index requires 

• 10GB of temporary file space
• The .srt file defines 10 directories with a size limit of 2GB
• 48 sort index groups are defined (-SG 48)

We could potential open 240 files, though not necessarily all files would be opened at the same time:   
     10GB / 2GB = 5
     48 * 5 = 240
If the operating system does not complain about the number of file handles in use at any time, this is not a problem. If, however the first (or only) directory defined in the .srt file was sized at 10GB (or more), then only 48 files (requiring 48 file handles) would need to be opened.

The disadvantage of using more than one set of temporary files it that the idxbuild process must switch between different files during I/O operations of the Phase 1. While the impact of this is relatively small, it is something to be aware of.

Sort-Groups (-SG) and memory requirements

• The -SG parameter was introduced to define the number of index sort groups. 
• Prior to Progress 9.1D07, the number of index groups was hard coded at 8. 
• The minimum value allowed for -SG is 8 and the maximum is 64. 
• If -SG is not specified a default value of 48 is used as internal testing found that this value gave the best results in most cases.
• Larger values of -SG require more memory allocation and file handles. The memory required for each index group can be calculated by using the formula: (-TB) * ((-TM + 1). The value obtained using this formula represents the amount of memory in kilobytes (KB) needed for each index group.
• The larger the -SG value, the fewer the merge/sort operations are needed between different indexes. However, this is only true if the database has many indexes in one or a few areas. 
• When indexes are distributed across many areas, a lower -SG value should be used to avoid unnecessary memory allocation and temporary file creation. 

-SG recommendations

As a rule, where there are no constraints on memory, disk space and the number of active file handles - the value used for -SG should be based on the average number of indexes in one area and the size of those indexes. The following are test case recommendations:

• -SG 64: Database 4.5GB, 733 tables, 1333 indexes defined in one area,
• -SG {8 ..12}: Database 2GB, 20 areas, 315 indexes, where most of the index data (in size) is contained in 17 indexes residing in one area
• -SG 8: Database 10GB, 3 tables in one area, where most of the data (in size) is contained in one table and one index

 Understanding the new -SG Sort Group parameter for IDXBUILD