Skip to content

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