제품 여행

Index의 capacity에 관한 정보 열람

by 사니조아 posted Apr 26, 2023

Index Capacity 정보

 

들어가며

DBMS의 여러 기능 기능이나 구성 요소들 중에서 가장 중요한 것은 무엇일까요?
Index는 '가장' 중요한은 아니더라도 적어도 '아주 아주' 중요한 요소가 아닐까 생각 합니다.

Index가 없다면 데이터를 쌓아 두기만 할 수 있을 뿐 사실상 관리는 못하는 그런 시스템이 될 테니까요.

자료가 많으면 많을 수록 Index는 더 중요해 집니다.

이렇게 중요한 Index를 분석할 때에도 목적에 부합하는 여러가지 도구와 방법들이 있을 수 있습니다.

이 페이지에서는 그 중에서 Index의 Capaicty에 대한 정보를 리뷰해 보고자 합니다.

기본적인 사용 방법이나 설명은 매뉴얼을 통해 얻을 수 있으므로 여기서 설명은 생략합니다.

 

INDEX CAPACITY 정보 얻기

우선 CUBRID에서는 Index의 Capacity 정보를 다음과 같은 두 가지 방법으로 쉽게(?) 알아 볼 수 있습니다.

1. diagdb tool

-------------------------------------------------------------
BTID: {{0, 5952}, 5953}, idx0 ON dba.tbl, CAPACITY INFORMATION:
Distinct Key Count: 0
Total Value Count: 0
Average Value Count Per Key: 0
Total Page Count: 2
Leaf Page Count: 1
NonLeaf Page Count: 1
Height: 2
Average Key Length: 0
Average Record Length: 0
Total Index Space: 32688 bytes
Used Index Space: 200 bytes
Free Index Space: 32488 bytes
Average Page Free Space: 16244 bytes
Average Page Key Count: 0
-------------------------------------------------------------

 

 2. SHOW INDEX CAPACITY 명령 

Table_name           : 'dba.tbl'        
Index_name           : 'idx0'        
Btid                 : '(0|5952|5953)'        
Num_distinct_key     : 0        
Total_value          : 0        
Avg_num_value_per_key: 0        
Num_leaf_page        : 1        
Num_non_leaf_page    : 1        
Num_total_page       : 2        
Height               : 2        
Avg_key_len          : 0        
Avg_rec_len          : 0        
Total_space          : '31.9K'        
Total_used_space     : '200.0B'        
Total_free_space     : '31.7K'        
Avg_num_page_key     : 0        
Avg_page_free_space  : '15.9K'


위 두가지 수행 결과는 표현되는 키워드와 형식만 약간 다를 뿐 사실 동일한 내용입니다.
 

INDEX CAPACITY 세부 항목

각 항목에 대한 설명을 "SHOW INDEX CAPACITY" 명령의 결과 기준으로 설명하면 아래 표와 같습니다.

 

SHOW INDEX CAPACITY

diagdb 

의 미

비고

Table_name

 

테이블 이름

 

Index_name

 

인덱스 이름

 

Btid

 

BTID 정보 (vol ID, file ID, root_page ID)

 

Num_distinct_key

Distinct Key Count

전체 leaf 페이지에 존재하는 Distinct key 개수

 

Total_value

Total Value Count

인덱스에 저장된 OID 개수

 

Avg_num_value_per_key

Average Value Count Per Key

키 당 OID 값의 평균 개수

((Num_distinct_key > 0) ? (Total_value / Num_distinct_key) : 0)

Num_leaf_page

Leaf Page Count

Non-Leaf 페이지 개수

 

Num_non_leaf_page

NonLeaf Page Count

leaf 페이지 개수

 

Num_total_page

Total Page Count

전체 페이지 개수

 

Height

Height

트리의 높이

 

Avg_key_len

Average Key Length

평균 키 길이

((Num_distinct_key > 0) ? (sum_of_each_key_len / Num_distinct_key) : 0)

Avg_rec_len

Average Record Length

평균 페이지 레코드 길이

((Num_distinct_key > 0) ? (sum_of_each_record_len / Num_distinct_key) : 0)

Total_space

Total Index Space

인덱스에 의해 점유되는 전체 공간

 

Total_used_space

Used Index Space

인덱스의 전체 사용 공간

Total_space - Total_free_space

Total_free_space

Free Index Space

인덱스의 전체 여유 공간

 

Avg_num_page_key

Average Page Key Count

Leaf 페이지 당 평균 키 개수

((Num_leaf_page > 0) ? (Num_distinct_key / Num_leaf_page) : 0)

Avg_page_free_space

Average Page Free Space

전체 페이지 당 평균 여유 공간

((Num_total_page > 0) ? (Total_free_space / Num_total_page) : 0)

 

주1)  v11.2.3 기준입니다.

주2)  각각의 항목은 Overflow Page에 대한 정보를 포함하지 않은 상태로 보여 집니다. 

       다만, Total_value 항목은 Overflow Page의 정보를 포함합니다.

주3)  Overflow Page영역의 정보는 이후 버전에서 포함 될 예정입니다.

 

사용해 보기1

 

우선 자료가 많지 않은 작은 테이블을 가지고 검토를 해 봅시다.

아래와 같이 동일한 구성의 두 테이블읋 준비해서 각각 50건과 500건을 입력합니다.

drop table if exists t1, t2;
create table t1(id int primary key, v1 int, v2 int);
create index idx1 on t1(v1);
create index idx2 on t1(v2);
create table t2 like t1;  

insert into t1 select rownum, rownum % 10, 1 from db_class a, db_class b, db_class c limit 50;
insert into t2 select rownum, rownum % 10, 1 from db_class a, db_class b, db_class c limit 500;

 

아래 명령으로 Capacity 정보를 얻어 봅니다.

;line on
show all indexes capacity  of t1;
show all indexes capacity  of t2;

 

아래에서 왼쪽은 t1 테이블 오른쪽은 t2 테이블에 대한 결과입니다.

=== <Result of SELECT Command in Line 1> ===

<00001> Table_name           : 'dba.t1'
        Index_name           : 'idx1'
        Btid                 : '(0|5696|5697)'
        Num_distinct_key     : 10
        Total_value          : 50
        Avg_num_value_per_key: 5
        Num_leaf_page        : 1
        Num_non_leaf_page    : 0
        Num_total_page       : 1
        Height               : 1
        Avg_key_len          : 4
        Avg_rec_len          : 84
        Total_space          : '16.0K'
        Total_used_space     : '1000.0B'
        Total_free_space     : '15.0K'
        Avg_num_page_key     : 10
        Avg_page_free_space  : '15.0K'
<00002> Table_name           : 'dba.t1'
        Index_name           : 'idx2'
        Btid                 : '(0|5760|5761)'
        Num_distinct_key     : 1
        Total_value          : 50
        Avg_num_value_per_key: 50
        Num_leaf_page        : 1
        Num_non_leaf_page    : 0
        Num_total_page       : 1
        Height               : 1
        Avg_key_len          : 4
        Avg_rec_len          : 804
        Total_space          : '16.0K'
        Total_used_space     : '928.0B'
        Total_free_space     : '15.1K'
        Avg_num_page_key     : 1
        Avg_page_free_space  : '15.1K'
<00003> Table_name           : 'dba.t1'
        Index_name           : 'pk_t1_id'
        Btid                 : '(0|5568|5569)'
        Num_distinct_key     : 50
        Total_value          : 50
        Avg_num_value_per_key: 1
        Num_leaf_page        : 1
        Num_non_leaf_page    : 0
        Num_total_page       : 1
        Height               : 1
        Avg_key_len          : 4
        Avg_rec_len          : 20
        Total_space          : '16.0K'
        Total_used_space     : '1.3K'
        Total_free_space     : '14.7K'
        Avg_num_page_key     : 50
        Avg_page_free_space  : '14.7K'

3 rows selected. (0.004562 sec) Committed. (0.000016 sec)

1 command(s) successfully processed.
 

=== <Result of SELECT Command in Line 1> ===

<00001> Table_name           : 'dba.t2'
        Index_name           : 'idx1'
        Btid                 : '(0|5952|5953)'
        Num_distinct_key     : 10
        Total_value          : 500
        Avg_num_value_per_key: 50
        Num_leaf_page        : 1
        Num_non_leaf_page    : 0
        Num_total_page       : 1
        Height               : 1
        Avg_key_len          : 4
        Avg_rec_len          : 804
        Total_space          : '16.0K'
        Total_used_space     : '8.0K'
        Total_free_space     : '8.0K'
        Avg_num_page_key     : 10
        Avg_page_free_space  : '8.0K'
<00002> Table_name           : 'dba.t2'
        Index_name           : 'idx2'
        Btid                 : '(0|6016|6017)'
        Num_distinct_key     : 1
        Total_value          : 500
        Avg_num_value_per_key: 500
        Num_leaf_page        : 1
        Num_non_leaf_page    : 0
        Num_total_page       : 1
        Height               : 1
        Avg_key_len          : 4
        Avg_rec_len          : 1380
        Total_space          : '16.0K'
        Total_used_space     : '1.5K'
        Total_free_space     : '14.5K'
        Avg_num_page_key     : 1
        Avg_page_free_space  : '14.5K'
<00003> Table_name           : 'dba.t2'
        Index_name           : 'pk_t2_id'
        Btid                 : '(0|5824|5825)'
        Num_distinct_key     : 500
        Total_value          : 500
        Avg_num_value_per_key: 1
        Num_leaf_page        : 1
        Num_non_leaf_page    : 0
        Num_total_page       : 1
        Height               : 1
        Avg_key_len          : 4
        Avg_rec_len          : 20
        Total_space          : '16.0K'
        Total_used_space     : '11.8K'
        Total_free_space     : '4.1K'
        Avg_num_page_key     : 500
        Avg_page_free_space  : '4.1K'

3 rows selected. (0.005187 sec) Committed. (0.000019 sec)

1 command(s) successfully processed.
 

 

    Num_distinct_key와 Total_value를 비교해서 보면 pk_t1_id는 unique 하므로 입력된 레코드 건수와 키의 개수, Total_value의 개수가 동일함을 볼 수 있고,

   idx2는 레코드가 모두 동일한 값을 갖으므로 Total_value의 개수는 레코드 개수와 동일하지만 Num_distinct_key는 항상 1임을 볼 수 있다.

   idx1은 키가 중복되어 Num_distinct_key는 값이 일정 개수 이상으로 늘지 않고 있음을 볼 수 있다.

 

사용해 보기2

이제 데이터의 양을 늘려서 다른 부분들에 대해 검토 해 보겠습니다.

우선 아래와 같은 구문을 이용해서 테이블을 생성하고 데이터를 입력합니다.

drop table if exists tbl;
create table tbl(id int primary key, v1 int, v2 int, v3 int, s char(120));
create index idx1 on tbl(v1, s);
create index idx2 on tbl(v2, s);
create index idx3 on tbl(v3, s);

insert into tbl select rownum, rownum % 5000, rownum % 500, 1, 'cubrid string test' from db_class a, db_class b, db_class c, db_class d limit 100000;
;line on
show all indexes capacity  of tbl;


이 상태에서 아래 명령으로 100건의 레코드를 삭제한 후의 상태와 전체를 모두 삭제한 후의 상태를 확인해 봅니다.

delete from tbl where id%1000 = 1;
show all indexes capacity  of tbl;

delete from tbl;
show all indexes capacity  of tbl;


아래의 표에서 비교해서 볼 수 있습니다.

100000건 입력 후

100건 삭제 후

전체 삭제 후

=== <Result of SELECT Command in Line 1> ===

<00001> Table_name           : 'dba.tbl'
        Index_name           : 'idx1'
        Btid                 : '(0|6208|6209)'
        Num_distinct_key     : 5688
        Total_value          : 100688
        Avg_num_value_per_key: 17
        Num_leaf_page        : 345
        Num_non_leaf_page    : 1
        Num_total_page       : 346
        Height               : 2
        Avg_key_len          : 426
        Avg_rec_len          : 571
        Total_space          : '5.4M'
        Total_used_space     : '3.1M'
        Total_free_space     : '2.2M'
        Avg_num_page_key     : 16
        Avg_page_free_space  : '6.6K'
<00002> Table_name           : 'dba.tbl'
        Index_name           : 'idx2'
        Btid                 : '(0|6272|6273)'
        Num_distinct_key     : 650
        Total_value          : 100150
        Avg_num_value_per_key: 154
        Num_leaf_page        : 76
        Num_non_leaf_page    : 1
        Num_total_page       : 77
        Height               : 2
        Avg_key_len          : 374
        Avg_rec_len          : 920
        Total_space          : '1.2M'
        Total_used_space     : '593.3K'
        Total_free_space     : '635.7K'
        Avg_num_page_key     : 8
        Avg_page_free_space  : '8.3K'
<00003> Table_name           : 'dba.tbl'
        Index_name           : 'idx3'
        Btid                 : '(0|6336|6337)'
        Num_distinct_key     : 1
        Total_value          : 100000
        Avg_num_value_per_key: 100000
        Num_leaf_page        : 1
        Num_non_leaf_page    : 0
        Num_total_page       : 1
        Height               : 1
        Avg_key_len          : 485
        Avg_rec_len          : 1192
        Total_space          : '16.0K'
        Total_used_space     : '1.3K'
        Total_free_space     : '14.7K'
        Avg_num_page_key     : 1
        Avg_page_free_space  : '14.7K'
<00004> Table_name           : 'dba.tbl'
        Index_name           : 'pk_tbl_id'
        Btid                 : '(0|6080|6081)'
        Num_distinct_key     : 100310
        Total_value          : 100310
        Avg_num_value_per_key: 1
        Num_leaf_page        : 156
        Num_non_leaf_page    : 1
        Num_total_page       : 157
        Height               : 2
        Avg_key_len          : 4
        Avg_rec_len          : 12
        Total_space          : '2.4M'
        Total_used_space     : '1.5M'
        Total_free_space     : '926.2K'
        Avg_num_page_key     : 643
        Avg_page_free_space  : '5.9K'

4 rows selected. (6.017251 sec) Committed. (0.000028 sec)

1 command(s) successfully processed.

=== <Result of SELECT Command in Line 1> ===

<00001> Table_name           : 'dba.tbl'
        Index_name           : 'idx1'
        Btid                 : '(0|6208|6209)'
        Num_distinct_key     : 5688
        Total_value          : 100688
        Avg_num_value_per_key: 17
        Num_leaf_page        : 345
        Num_non_leaf_page    : 1
        Num_total_page       : 346
        Height               : 2
        Avg_key_len          : 426
        Avg_rec_len          : 571
        Total_space          : '5.4M'
        Total_used_space     : '3.2M'
        Total_free_space     : '2.2M'
        Avg_num_page_key     : 16
        Avg_page_free_space  : '6.6K'
<00002> Table_name           : 'dba.tbl'
        Index_name           : 'idx2'
        Btid                 : '(0|6272|6273)'
        Num_distinct_key     : 650
        Total_value          : 100150
        Avg_num_value_per_key: 154
        Num_leaf_page        : 76
        Num_non_leaf_page    : 1
        Num_total_page       : 77
        Height               : 2
        Avg_key_len          : 374
        Avg_rec_len          : 921
        Total_space          : '1.2M'
        Total_used_space     : '593.6K'
        Total_free_space     : '635.4K'
        Avg_num_page_key     : 8
        Avg_page_free_space  : '8.3K'
<00003> Table_name           : 'dba.tbl'
        Index_name           : 'idx3'
        Btid                 : '(0|6336|6337)'
        Num_distinct_key     : 1
        Total_value          : 100000
        Avg_num_value_per_key: 100000
        Num_leaf_page        : 1
        Num_non_leaf_page    : 0
        Num_total_page       : 1
        Height               : 1
        Avg_key_len          : 485
        Avg_rec_len          : 1192
        Total_space          : '16.0K'
        Total_used_space     : '1.3K'
        Total_free_space     : '14.7K'
        Avg_num_page_key     : 1
        Avg_page_free_space  : '14.7K'
<00004> Table_name           : 'dba.tbl'
        Index_name           : 'pk_tbl_id'
        Btid                 : '(0|6080|6081)'
        Num_distinct_key     : 100310
        Total_value          : 100310
        Avg_num_value_per_key: 1
        Num_leaf_page        : 156
        Num_non_leaf_page    : 1
        Num_total_page       : 157
        Height               : 2
        Avg_key_len          : 4
        Avg_rec_len          : 12
        Total_space          : '2.4M'
        Total_used_space     : '1.5M'
        Total_free_space     : '925.4K'
        Avg_num_page_key     : 643
        Avg_page_free_space  : '5.9K'

4 rows selected. (0.016022 sec) Committed. (0.000017 sec)

1 command(s) successfully processed.
 

=== <Result of SELECT Command in Line 1> ===

<00001> Table_name           : 'dba.tbl'
        Index_name           : 'idx1'
        Btid                 : '(0|6208|6209)'
        Num_distinct_key     : 489
        Total_value          : 489
        Avg_num_value_per_key: 1
        Num_leaf_page        : 30
        Num_non_leaf_page    : 1
        Num_total_page       : 31
        Height               : 2
        Avg_key_len          : 428
        Avg_rec_len          : 446
        Total_space          : '494.8K'
        Total_used_space     : '217.5K'
        Total_free_space     : '277.2K'
        Avg_num_page_key     : 16
        Avg_page_free_space  : '8.9K'
<00002> Table_name           : 'dba.tbl'
        Index_name           : 'idx2'
        Btid                 : '(0|6272|6273)'
        Num_distinct_key     : 495
        Total_value          : 495
        Avg_num_value_per_key: 1
        Num_leaf_page        : 33
        Num_non_leaf_page    : 1
        Num_total_page       : 34
        Height               : 2
        Avg_key_len          : 422
        Avg_rec_len          : 447
        Total_space          : '542.7K'
        Total_used_space     : '221.3K'
        Total_free_space     : '321.4K'
        Avg_num_page_key     : 15
        Avg_page_free_space  : '9.5K'
<00003> Table_name           : 'dba.tbl'
        Index_name           : 'idx3'
        Btid                 : '(0|6336|6337)'
        Num_distinct_key     : 1
        Total_value          : 431
        Avg_num_value_per_key: 431
        Num_leaf_page        : 1
        Num_non_leaf_page    : 0
        Num_total_page       : 1
        Height               : 1
        Avg_key_len          : 485
        Avg_rec_len          : 520
        Total_space          : '16.0K'
        Total_used_space     : '656.0B'
        Total_free_space     : '15.3K'
        Avg_num_page_key     : 1
        Avg_page_free_space  : '15.3K'
<00004> Table_name           : 'dba.tbl'
        Index_name           : 'pk_tbl_id'
        Btid                 : '(0|6080|6081)'
        Num_distinct_key     : 437
        Total_value          : 437
        Avg_num_value_per_key: 1
        Num_leaf_page        : 4
        Num_non_leaf_page    : 1
        Num_total_page       : 5
        Height               : 2
        Avg_key_len          : 4
        Avg_rec_len          : 19
        Total_space          : '79.8K'
        Total_used_space     : '10.6K'
        Total_free_space     : '69.2K'
        Avg_num_page_key     : 109
        Avg_page_free_space  : '13.8K'

4 rows selected. (0.006074 sec) Committed. (0.000018 sec)

1 command(s) successfully processed.
 

 

 

이 비교 테이블 중에서 PK인 pk_tbl_id에 대해서 우선 살펴 보겠습니다.

우선 Num_distinct_key값이 각각 100000, 99900, 0 으로 표시 되지 않았음을 볼 수 있습니다. 이상하다고 생각되겠지만 이 값들은 사용자가 입력한 정보가 아니라 현재 Index의 상태를 보여주는 용도이기 때문입니다.

우선 처음 100000개를 입력한 상태에서 100310으로 입력건수보다 많은 키가 존재하는 이유는 내부 처리 과정에서 FENCE_KEY와 같은 관리용 키가 생성되었기 때문입니다.

100건을 삭제한 후에도 값이 줄지 않고 있는 이유는 삭제되었다고 해도 Index에 정보가 남아 있는 상태이기 때문입니다. 이런 정보는 VACUUM을 통해서 정리가 됩니다.

전체 삭제 후에도 437개가 있는 것으로 표시되는 것은 VACUUM이 현재 수행 중이어서 정리를 하고 있는 중이기 때문입니다. 아직 전체 정리가 덜 된 상태라고 볼 수 있습니다.

 

이제 idx3 인덱스를 살펴 보겠습니다.

Num_total_page와 같은 페이지 수 정보를 보면 1로 변함이 없습니다. 다른 인덱스들이 데이터 양에 따라 페이지수의 변화가 있는데 비해 idx3는 항상 1개의 페이지만 가지고 있는 것처럼 보입니다.  그 이유는 현재 보여지는 정보에서는  OID Overflow-pages에 대한 정보를 포함하고 있지 않기 때문에 그런 착각을 불러 일으키는 것입니다.

 

마무리

인덱스의 Capacity 정보를 볼 때  혼동 할 만한 부분들에 대해서 간단하게 살펴 보았습니다.

현재 버전에서 아쉬운 점은 OID Overflow pages에 대한 정보가 없다는 것인데 이 부분은 차후 버전에서 개선 될 것입니다.

새 버전에서 정보가 추가되면 다시 변경 사항에 대해 설명 드리겠습니다.