Hitachi

Hitachi Advanced Database Setup and Operation Guide

6.3.13 Determining the memory requirement for executing the adbmergechunk command

When the adbmergechunk command is executed, the HADB server uses the types of memory described in this subsection. You need to determine the memory requirement for each type.

■ Shared memory

  • Process common memory (PROC_MERGCSZ)

  • Real thread private memory (RTHD_MERCHKSZ)

■ Process memory

  • Heap memory (HEAP_MERCHKSZ)

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 adbmergechunk command)

Use the following formula to determine the amount of process common memory (PROC_MERGCSZ) needed to execute the adbmergechunk 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

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

(a) Determining the variable PROC_MNG

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

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

Add this value when the audit trail facility is enabled.

Explanation of variables

DIC: Memory for acquiring 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 = sql_size + (scan_buff_size + offset_area × 2) × scan_rthd

  • sql_size

    Use the following formula to determine its value:

    32 + 1,024

  • scan_buff_size

    Use the following formula to determine its value:

    value-specified-for-merge-chunk-option-adb_mergechunk_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-merge-chunk-option-adb_mergechunk_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 merge chunk option adb_mergechunk_dvix_wtbuff_size

  • scan_rthd

    Use the following formula to determine the value:

    ↓(value-specified-for-merge-chunk-option-adb_mergechunk_rthd_num - 1) ÷ 2↓

  • b-tree_index_num

    Number of B-tree indexes to be processed

IDXRBLDBUF_CTL: Buffer control information for merging chunks

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-merge-chunk-option-adb_mergechunk_rthd_num - 1) ÷ 2↓
text_idx_num

Number of text indexes defined for the table to be processed

PAGEALLOC: Page allocation control information for merging chunks

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-merge-chunk-option-adb_mergechunk_rthd_num - 1
CHUNK: Chunk management block

Use the following formula to determine its value.

Formula
CHUNK = ↑(8 × (chunk_num × 2) + 30,000) ÷ 1,024↑

  • chunk_num

    Number of chunks created in the table to be processed

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

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

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-merge-chunk-option-adb_mergechunk_rthd_num - 1) ÷ 2↓
rd_buff_size

Value specified for the merge chunk option adb_mergechunk_dvix_rdbuff_size

ld_buff_size

Value specified for the merge chunk option adb_mergechunk_dvix_wtbuff_size

CTRL

  • If the indexed columns are fixed-length columns

    Substitute 10 bytes.

  • If the indexed columns include variable-length columns

    Substitute 12 bytes.

text_idx_num

Number of text indexes defined for the table to be processed

dividx_rthd

Use the following formula to determine the value:

value-specified-for-merge-chunk-option-adb_mergechunk_rthd_num - 1
b_tree_idx_num

Number of B-tree indexes to be processed

(c) Determining the variable PROC_IDXREBUILD

Use the following formula to determine the variable PROC_IDXREBUILD.

Formula (kilobytes)

[Figure]
idx_num

Number of B-tree indexes to be rebuilt

PROC_IDXREBUILD_MEM(k)

Use the following formula to determine its 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

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

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_CHUNK_INFO

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

Formula (kilobytes)
PROC_CHUNK_INFO = ↑(1 + index_num) × 120 × (chunk_num + 1) ÷ 1,024↑
Explanation of variables

index_num: Number of indexes defined for the table to be processed by the merge chunk command

chunk_num: Maximum number of chunks in the table to be processed by the merge chunk command

When the adbmergechunk command is executed, the amount of process common memory determined by the variable PROC_CHUNK_INFO is allocated. When the adbmergechunk command finishes, the allocated process common memory is released.

However, if deletion-pending chunks remain for any of the following reasons, the allocated process common memory is not released. It is released when all delete-pending chunks are deleted.

  • The adbmergechunk command is interrupted after the merge-target chunk is ready but before the merge-source chunks are deleted

    For example, merge-source chunks might remain undeleted if the HADB server has insufficient memory or when the adbcancel command is executed.

  • A merge-chunk target table is already being referenced when the adbmergechunk command is executed with NOWAIT specified in the --purge-chunk option

(f) Determining the variable PROC_DBUPDINF

Use one of the formulas below to determine the value of variable PROC_DBUPDINF. Note that the formula to use differs depending on whether the multi-node function is being used.

Formula when the multi-node function is not used (kilobytes)

[Figure]

Formula when the multi-node function is used (kilobytes)

[Figure]

Explanation of variables

DBUPDINF_ENT_NUM

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

Use one of the formulas below to determine its value. Note that the formula to use differs depending on whether the multi-node function is used.

Formula to be used when the multi-node function is not used (entries)

[Figure]

Formula to be used when the multi-node function is used (entries)

[Figure]
index_num

Number of indexes defined for the table to be processed by the merge chunk command

chunk_num

Number of chunks to be merged

(g) 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

Use the following formula to determine its value.

value-specified-for-merge-chunk-option-adb_mergechunk_rthd_num - 1

To Page Top

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

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

Formula (kilobytes)

[Figure]
Explanation of variables
#1

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

#2

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

#3

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

(a) Determining the variable IDXRBLDBUF

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

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 merge chunk option adb_mergechunk_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 merge chunk option adb_mergechunk_buff_blk_num

(b) Determining the variable RTHD_SCAN

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

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 indexes defined for the table to be processed.

Explanation of 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

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

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-merge-chunk-option-adb_mergechunk_rthd_num - 1

(d) Determining the variable SORTBUF

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

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-merge-chunk-option-adb_mergechunk_rthd_num - 1
sort_buff_size

Value specified for the merge chunk option adb_mergechunk_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 index 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. 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 processed are variable-length: 12

SYS2: Assume one of the following values:

  • If the indexes defined for the table to be processed are multiple-column indexes

    Use the following formula to determine its value:

    (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 the B-tree indexes defined for the table to be processed are variable-length multiple-column indexes

    Use the following formula to determine its value:

    (number of indexed columns × 2) + 2

  • For all other B-tree indexes

    5

(e) Determining the variable RTHD_IDXREC

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

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

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

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↑
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

    Substitute 10 bytes.

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

    Substitute 12 bytes.

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.

(g) Determining the variable RTHD_RNGIDX

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

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-merge-chunk-option-adb_mergechunk_rthd_num - 1) ÷ 2↓

(h) Determining the variable RTHD_TXTIDX

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

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 adb_mergechunk_buff_blk_num merge chunk option

idx_div_num

Length defined for the indexed column for text index k

txt_sort_buff_size

Value specified for the adb_mergechunk_txt_buff_size merge chunk option

To Page Top

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

Use the following formula to determine the heap memory (HEAP_MERCHKSZ) required when executing the adbmergechunk command.

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

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

Explanation of variables

sort_rthd

Use the following formula to determine the value:

value-specified-for-merge-chunk-option-adb_mergechunk_rthd_num - 1

To Page Top

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