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' 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' 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' 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' 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' 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에 대한 정보가 없다는 것인데 이 부분은 차후 버전에서 개선 될 것입니다.
새 버전에서 정보가 추가되면 다시 변경 사항에 대해 설명 드리겠습니다.
CUBRID Internal: Disk Manager #1: 볼륨 헤더(Volume Header)와 ...
