Building a Large-Scale DRDB Index#
Overview#
Indexing involves reading, sorting, and storing data.
Therefore, running indexes in parallel can actually degrade performance due to excessive disk I/O and buffer misses.
By configuring the properties as shown below, you can reduce I/O and buffer misses, optimize the sorting process, and ultimately shorten the build time for large-scale disk-based indexes.
Solution#
Altibase 6.5.1 ~ Altibase 7.1#
1. BUFFER_AREA_SIZE (unit: bytes) = (The bigger, the better.)
2. SORT_AREA_SIZE (unit: bytes) = (Number of physical cores in the system * 20MB)
3. DISK_INDEX_BUILD_MERGE_PAGE_COUNT (unit: number of pages) = (1% of BUFFER_AREA_SIZE is recommended, but you should consider DISK_INDEX_BUILD_MERGE_PAGE_COUNT [see Consideration #3 below].)
4. INDEX_BUILD_THREAD_COUNT (unit: number of cores) = (Number of physical cores in the hardware)
Considerations
1. BUFFER_AREA_SIZE
- The larger the value, the longer the STARTUP time will be.
2. SORT_AREA_SIZE
- When building a single index, memory usage is at least the size of SORT_AREA_SIZE. If two indexes are built in parallel, memory usage doubles to SORT_AREA_SIZE * 2.
- SORT_AREA_SIZE is also a shared property with disk temp tables, so changing it affects the operation of disk temp tables.
3. DISK_INDEX_BUILD_MERGE_PAGE_COUNT
-
The unit of the property is the number of pages, while the unit of BUFFER_AREA_SIZE is bytes.
-
If the property value is large but the index size is small, performance may actually degrade.
- Index build performance may degrade if the following conditions are met.
- DISK_INDEX_BUILD_MERGE_PAGE_COUNT > ( Index key length * number of RECORD ) / SORT_AREA_SIZE
Altibase 7.3 ~#
1. BUFFER_AREA_SIZE (unit: bytes) = (The bigger, the better.)
2. DISK_INDEX_BUILD_SORT_AREA_SIZE (unit: bytes) = (Number of physical cores in the system * 20MB)
3. DISK_INDEX_BUILD_MERGE_PAGE_COUNT (unit: number of pages) = (1% of BUFFER_AREA_SIZE)
4. INDEX_BUILD_THREAD_COUNT (unit: number of cores) = (Number of physical cores in the hardware)
Considerations
1. BUFFER_AREA_SIZE
- The larger the value, the longer the STARTUP time will be.
2. DISK_INDEX_BUILD_SORT_AREA_SIZE
- When building a single index, at least the amount of memory equal to DISK_INDEX_BUILD_SORT_AREA_SIZE is used. If two indexes are built in parallel, memory usage doubles to DISK_INDEX_BUILD_SORT_AREA_SIZE * 2.
(SORT_AREA_SIZE is not used.)
3. DISK_INDEX_BUILD_MERGE_PAGE_COUNT
- The unit of the property is the number of pages, while the unit of BUFFER_AREA_SIZE is bytes.
- The following issue does not occur in Altibase 7.3 or later.
If the property value is large but the index size is small, performance may actually degrade.
- Index build performance may degrade if the following conditions are met.
- DISK_INDEX_BUILD_MERGE_PAGE_COUNT > ( Index key length * number of RECORD ) / SORT_AREA_SIZE