Hitachi

Hitachi Advanced Database Setup and Operation Guide

6.3.7 Determining the memory requirement for executing the adbidxrebuild command

When the adbidxrebuild command is executed, the HADB server uses the types of memory described below. You need to determine the memory requirement for each type.

■ Shared memory

  • Process common memory (PROC_IDXRBLDSZ)

  • Real thread private memory (RTHD_IDXRBLDSZ)

■ Process memory

  • Heap memory (HEAP_IDXRBLDSZ)

The following subsections describe the formulas for determining these memory requirements.

Organization of this subsection

(1) Determining the process common memory requirement (for executing the adbidxrebuild command)

Use the following formula to determine the amount of process common memory (PROC_IDXRBLDSZ) needed to execute the adbidxrebuild command.

Formula (kilobytes)

[Figure]
Explanation of variables
#1

Add this value when the index to be processed contains a B-tree index.

#2

Add this value when the index to be processed contains a range index.

#3

Add this value when you use the multi-node function.

#4

If you execute multiple adbidxrebuild commands concurrently, determine the memory requirement for each adbidxrebuild command. Then, add up the total memory requirements.

(a) Determining the variable PROC_MNG

The variable PROC_MNG is used to determine the amount of process common memory used for managing index rebuilding. Use the following formula to determine its value.

Formula (kilobytes) 
PROC_MNG =
    3 + DIC + IOA + SCAN + IDXRBLDBUF_CTL + STS + PAGEALLOC + PROC_AUDINFSZ#
#

Add this value when the audit trail facility is enabled.

Explanation of variables

DIC: Memory used to acquire the definition information of the table to be processed

See the explanation of the variable DIC in (a) Determining the variable PROC_IMPT in (1) Determining the process common memory requirement (for executing the adbimport command) in 6.3.6 Determining the memory requirement for executing the adbimport command.

IOA: Memory used to access the database

Use the following formula to determine its value.

Formula (kilobytes)
IOA = 1,056 + (scan_buff_size + offset_area × 2) × scan_rthd

  • scan_buff_size

    Use the following formula to determine its value:

    value-specified-for-index-rebuild-option-adb_idxrebuild_scan_buff_size × 1,024

  • offset_area

    Use the following formula to determine its value:

    (8 × number-of-columns-comprising-processing-target-index × (scan_buff_size × 1,024

    ÷ (sum-total-of-lengths-of-columns-comprising-processing-target-index + 8))) ÷ 1,024

  • scan_rthd

    Use the following formula to determine the value.

    ↓(value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1) ÷ 2↓
SCAN: Memory needed to output the index record file

Use the following formula to determine its value.

Formula (kilobytes)
SCAN =
   ↑(write_size × scan_rthd × b-tree_index_num)↑

  • write_size

    Value specified for the index rebuild option adb_idxrebuild_dvix_wtbuff_size

  • scan_rthd

    Use the following formula to determine the value.

    ↓(value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1) ÷ 2↓

  • b-tree_index_num

    Number of B-tree indexes to be processed

IDXRBLDBUF_CTL: Buffer control information for rebuilding indexes

Use the following formula to determine its value.

Formula (kilobytes)
IDXRBLDBUF_CTL =
   ↑(248 + BUFBLK) ÷ 1,024↑ × scan_rthd
STS: Memory for saving status information

Use the following formula to determine its value.

Formula (kilobytes)

[Figure]
idx_num

Number of indexes to be processed

b_tree_idx_num

Number of B-tree indexes defined for the table to be processed

range_idx_num

Number of range indexes defined for the table to be processed

scan_rthd

Use the following formula to determine the value.

↓(value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1) ÷ 2↓
text_idx_num

Number of text indexes defined for the table to be processed

imp_sts

Memory for saving status information of adbimport command

If you will be executing the adbidxrebuild command with the --create-temp-file option specified after the adbimport command is interrupted, add the value determined for the variable STS as explained in (a) Determining the variable PROC_IMPT under (1) Determining the process common memory requirement (for executing the adbimport command) in 6.3.6 Determining the memory requirement for executing the adbimport command.

PAGEALLOC: Page allocation control information for rebuilding indexes

Use the following formula to determine its value.

Formula (kilobytes)
PAGEALLOC = dividx_rthd × 310

  • dividx_rthd

    Use the following formula to determine the value.

    value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1
PROC_AUDINFSZ

Determine the value of the variable PROC_AUDINFSZ according to (r) Determining the variable AUDINF under (3) Determining the process common memory requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.

(b) Determining the variable PROC_IDX

The variable PROC_IDX is used to determine the amount of process common memory used to batch-create indexes. Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

Explanation of variables

KEYSZ

Key length of a B-tree index (bytes)

Determine the key length of the index based on 5.8.4 Determining the key length (KEYSZ) of a B-tree index. For variable-length data, use the actual data length. If multiple B-tree indexes have been defined for the processing-target table, use the largest key length among all the B-tree indexes.

scan_rthd

Use the following formula to determine the value:

↓(value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1) ÷ 2↓
rd_buff_size

Value specified for the index rebuild option adb_idxrebuild_dvix_rdbuff_size

ld_buff_size

Value specified for the index rebuild option adb_idxrebuild_dvix_wtbuff_size

dividx_rthd

Use the following formula to determine the value:

value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1
CTRL

  • If the indexed columns are all fixed-length columns

    Substitute 10 bytes.

  • If the indexed columns include variable-length columns

    Substitute 12 bytes.

b_tree_idx_num

Number of B-tree indexes to be processed

text_idx_num

Number of text indexes defined for the table to be processed

(c) Determining the variable PROC_IDXREBUILD

The variable PROC_IDXREBUILD is used to determine the amount of process common memory used to rebuild B-tree indexes. Use the following formula to determine its value.

Formula (kilobytes)

[Figure]
idx_num

Number of B-tree indexes to be rebuilt

PROC_IDXREBUILD_MEM(k)

Use the following formula to determine this value.

Formula (kilobytes)

PROC_IDXREBUILD_MEM(k) =
  ↑(184 + 256 × idx_col_num + (18 + 256 × ↑idx_lv ÷ 16↑) × dividx_rthd
    + KEYSZ + (idx_col_num + 1) × 2 + 4) ÷ 1,024↑

(d) Determining the variable PROC_RNGIDX

The variable PROC_RNGIDX is used to determine the amount of process common memory used to batch-update range indexes. Use the following formula to determine its value.

Formula (kilobytes)

PROC_RNGIDX = ↑64 × rngidx_num ÷ 1,024↑

Explanation of variables

rngidx_num: Number of range indexes to be rebuilt

(e) Determining the variable PROC_DBUPDINF

Use the following formula to determine the value of variable PROC_DBUPDINF.

Formula (kilobytes)

[Figure]

Explanation of variables

DBUPDINF_ENT_NUM

Number of entries in the DB area, table, index, and chunk update information

Use the following formula to determine its value.

Formula (entries)

[Figure]
idx_num

Number of B-tree indexes to be rebuilt

rngidx_num

Number of range indexes to be rebuilt

txtidx_num

Number of text indexes to be rebuilt

chunk_num

Number of chunks in the table in which the indexes to be rebuilt are defined

(f) Determining the variable PROC_RTHDUPDINF

Use the following formula to determine the value of variable PROC_RTHDUPDINF.

Formula (kilobytes)

[Figure]

Explanation of variables

rthd_num

Number of processing real threads

Use the following formula to determine its value.

value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1

To Page Top

(2) Determining the real thread private memory requirement (for executing the adbidxrebuild command)

Use the following formula to determine the real thread private memory requirement (RTHD_IDXRBLDSZ) for executing the adbidxrebuild command.

Formula (kilobytes)

[Figure]
Explanation of variables
#1

If all the processing-target indexes are range indexes, substitute 0.

#2

Add this value when you are rebuilding a B-tree index.

#3

Add this value when a range index is included among the indexes to be rebuilt.

#4

Add this value when a text index is included among the indexes to be rebuilt.

(a) Determining the variable IDXRBLDBUF

The variable IDXRBLDBUF is used to determine the amount of real thread private memory used to rebuild an index. Use the following formula to determine its value.

Formula (kilobytes)

IDXRBLDBUF =
  ↑(RBLD_SQBLK + RBLD_SQIO + RBLD_SQPGE + RBLD_SQHS) ÷ 1,024↑

Explanation of variables

RBLD_SQBLK: Use the following formula to determine its value.

Formula (bytes)

RBLD_SQBLK =
  rbld_blknum × (112 + ↑RBLD_BLKSZ ÷ rbld_pagesize ÷ 64↑ × 24)
RBLD_SQIO: Use the following formula to determine its value.

Formula (bytes)

RBLD_SQIO = 568 × rbld_blknum

  • rbld_blknum

    Value specified for the index rebuild option adb_idxrebuild_buff_blk_num

RBLD_SQPGE: Use the following formula to determine its value.

Formula (bytes)

RBLD_SQPGE =
  (176 + rbld_pagesize + BUFLOG) × (RBLD_BLKSZ ÷ rbld_pagesize)
  × rbld_blknum
RBLD_SQHS: Use the following formula to determine its value.

Formula (bytes)

RBLD_SQHS = 8 × rbld_blknum + 40 × rbld_blknum

  • rbld_blknum

    Value specified for the index rebuild option adb_idxrebuild_buff_blk_num

(b) Determining the variable RTHD_SCAN

The variable RTHD_SCAN is used to determine the amount of real thread private memory to be used for table searches when creating index record files. Use the following formula to determine the value:

Formula (kilobytes)

RTHD_SCAN = MAX( RTHD_EXESQLSZ , RTHD_EXESQLDICSZ )
Note

Determine the values of the variables RTHD_EXESQLSZ and RTHD_EXESQLDICSZ on the assumption that the following SQL statement were executed:

SELECT selection-expression# FROM "name-of-table-to-be-processed"
#

Determine the value on the basis of CHAR(16) columns and as if duplicates were removed from all indexed columns of the index to be processed.

Explanation of the variables

RTHD_EXESQLSZ

See (c) Determining the variable RTHD_EXESQLSZ in (2) Determining the real thread private memory requirement (during normal operation) under 6.3.4 Determining the memory requirement during normal operation.

RTHD_EXESQLDICSZ

See (g) Determining the variable RTHD_EXESQLDICSZ in (2) Determining the real thread private memory requirement (during normal operation) under 6.3.4 Determining the memory requirement during normal operation.

(c) Determining the variable SORTIOBUF

The variable SORTIOBUF is used to determine the amount of real thread private memory used for sort processing. Use the following formula to determine its value.

Formula (kilobytes)
SORTIOBUF = sort_io_buff_size × sort_rthd
Explanation of variables

  • sort_io_buff_size

    Substitute 16.

  • sort_rthd

    Use the following formula to determine the value:

    value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1

(d) Determining the variable SORTBUF

The variable SORTBUF is used to determine the amount of real thread private memory used for sort processing during index rebuilding. Use the following formula to determine its value.

Formula (kilobytes)
SORTBUF = (48 + sort_buff_size × 1,024) × sort_rthd

Explanation of variables

sort_rthd

Use the following formula to determine the value:

value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1
sort_buff_size

Value specified for the index rebuild option adb_idxrebuild_sort_buff_size#

#

A work file is created during sort processing. The formula for the variable sort_buff_size, which minimizes the size of this file, is shown below. Allocate at least the amount of space indicated by the formula. However, if you are running out of memory or if most data items are already arranged in index key order, do not specify more space than is necessary.

Formula

[Figure]

Explanation of variables

REC_SIZE: Use the following formula to determine its value.

Formula

REC_SIZE = (KEYSZ + SYS1) + SYS2
KEYSZ: Index key length of the B-tree indexes defined for the table to be processed

Determine the key length of the index based on 5.8.4 Determining the key length (KEYSZ) of a B-tree index. For variable-length data, use the actual data length. If multiple B-tree indexes have been defined for the processing-target table, use the largest key length among all the B-tree indexes.

SYS1: Assume one of the following values:

  • If the indexed columns of the indexes defined for the table to be processed are fixed-length: 10

  • If any of the indexed columns of the indexes defined for the table to be imported are variable-length: 12

SYS2: Assume one of the following values:

  • If any of the indexes defined for the table to be processed are variable-length multiple-column indexes: (number of indexed columns × 4)

  • For all other indexes: 0

BLK: Use the following formula to determine its value.

Formula

BLK = REC_SIZE + (KEYSZ + 8) + 56
row_num

Number of rows stored in the table to be processed

KEY_INF: Use the following formula to determine its value.

Formula

KEY_INF = REC_SIZE + (KEYSZ + 8) + 28
COL_INFO: Use the following formula to determine its value.

Formula

COL_INFO = 2,112 + (MULTI_KEY_INFO × 32) + (KEYSZ + 8)
MULTI_KEY_INFO: Assume one of the following values:

  • If any of the B-tree indexes defined for the table to be processed are variable-length multiple-column indexes

    (number of indexed columns × 2) + 2

  • In all other cases

    5

(e) Determining the variable RTHD_IDXREC

The variable RTHD_IDXREC is used to determine the amount of real thread private memory used for index rebuilding. Use the following formula to determine its value.

Formula (kilobytes)

RTHD_IDXREC =

↑(386 + 328 × (idx_num - 1) + 32 × max_idx_col_num

+ (64 + buf_size × 1,024) × 2 × idx_num) ÷ 1,024↑

Explanation of variables

  • idx_num

    Number of B-tree indexes to be rebuilt

  • max_idx_col_num

    Maximum number of indexed columns in the B-tree index to be rebuilt

  • buf_size

    Substitute 1,024.

(f) Determining the variable RTHD_IDXREBUILD

The variable RTHD_IDXREBUILD is used to determine the amount of real thread private memory used to rebuild a B-tree index. Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

Explanation of variables

idx_num

Number of B-tree indexes to be rebuilt

RTHD_IDXREBUILD_MEM(k)

Use the following formula to determine its value.

Formula (kilobytes)
RTHD_IDXREBUILD_MEM (k) =
  ↑ (61,458 + ↑MAX (255, KEYSZ) ÷ 2↑ × 2 + 3,070
   + ↑KEYSZ + CTRL ÷ 8↑ × 8 + MAX (255, KEYSZ) + 14
   + ↓8 + page_size × 95 ÷ 100↓ × 2) ÷ 1,024↑
page_size

Page size of the data DB area in which the B-tree index is defined (bytes)

Determine this value based on the explanation of the page size of the data DB area in Table 6‒3: DB area page size under (2) Determining the global buffer page requirement (for starting the HADB server) in 6.3.3 Determining the memory requirement for starting the HADB server.

KEYSZ

Key length of the B-tree index (bytes)

Determine the key length of the index based on 5.8.4 Determining the key length (KEYSZ) of a B-tree index. For variable-length data, use the actual data length.

CTRL

  • When all key lengths in the indexed columns are fixed: 10 bytes

  • When some of the key lengths in the indexed columns are variable: 12 bytes

(g) Determining the variable RTHD_RNGIDX

The variable RTHD_RNGIDX is used to determine the amount of real thread private memory used for batch-updating range indexes. Use the following formula to determine its value.

Formula (kilobytes)
RTHD_RNGIDX = ↑(16 + 128 × rngidx_num) × scan_rthd ÷ 1,024↑
Explanation of variables

  • rngidx_num

    Number of range indexes to be rebuilt

  • scan_rthd

    Use the following formula to determine the value:

    ↓(value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1) ÷ 2↓

(h) Determining the variable RTHD_TXTIDX

The variable RTHD_TXTIDX is used to determine the amount of real thread private memory to be used for batch updating of text indexes. Use the following formula to determine its value.

Formula (kilobytes)

[Figure]

Explanation of variables

idx_num

Number of text indexes defined for the table to be processed

RTHD_TXTIDXBUILD_MEM(k)

Use the following formula to determine its value.

Formula (kilobytes)

[Figure]
buff_blk_size

Substitute 4,096.

buff_blk_num

Value specified for the index rebuild option adb_idxrebuild_buff_blk_num

idx_div_num

Length defined for the indexed column for text index k

txt_sort_buff_size

Value specified for the index rebuild option adb_idxrebuild_txt_buff_size

To Page Top

(3) Determining the heap memory requirement (for executing the adbidxrebuild command)

Use the following formula to determine the heap memory (HEAP_IDXRBLDSZ) required when executing the adbidxrebuild command.

Formula (kilobytes)
HEAP_IDXRBLDSZ = Σ (505 x sort_rthd)#

Explanation of variables

sort_rthd

Use the following formula to determine the value:

value-specified-for-index-rebuild-option-adb_idxrebuild_rthd_num - 1
#

If you execute multiple adbidxrebuild commands concurrently, determine the amount of heap memory used by each adbidxrebuild command. Then, add up the total heap memory requirements.

To Page Top

All Rights Reserved. Copyright (C) 2012, 2020, Hitachi, Ltd.