Oracle SEでインデックス圧縮について考える

oracleの運用をしていると問題になることの一つは容量不足。
特に頻繁にupdateされるインデックスの容量増加率は激しい。
EE+Oracle Advanced Compressionを使用すれば色々できるが。。。。
それは私にとっては夢の話なのでｗ

oracle SEで可能な圧縮について考え、検証した結果のまとめ。
環境はoracle 11.2.0.4。

SEでできる圧縮

RMAN バックアップセットの圧縮（compress option）
インデックスの圧縮（compress option）

今回はインデックスの圧縮について検証。

インデックス圧縮はカーディナリティの低いインデックスに対して効果を発揮する。重複した値をデータブロックに格納しないようにするようだ。

まず、基本的なインデックス圧縮する方法は

create index INDEX_NAME on TABLE_NAME(COLUMN_NAME) compress;

仕組みについてはマニュアル参照

索引と索引構成表

準備

TCOMPテーブル・・インデックス圧縮するテーブル
NOTCOMPテーブル・・インデックス圧縮しないテーブル

create table tcomp (p int primary key,id int ,id2 int, id100 int, id1000 int, id10000 int ,id100000 int,id1000000 int,id2000000 int,id3000000 int,id4000000 int,id4500000 int);
insert into tcomp select level,level,mod(level,2),mod(level,100),mod(level,1000),mod(level,10000),mod(level,100000),mod(level,1000000),mod(level,2000000),mod(level,3000000),mod(level,4000000),mod(level,4500000) from dual connect by level<=5000000;
create index tcomp_uni on tcomp(id) compress;
create index tcomp_2 on tcomp(id2) compress;
create index tcomp_100 on tcomp(id100) compress;
create index tcomp_1000 on tcomp(id1000) compress;
create index tcomp_10000 on tcomp(id10000) compress;
create index tcomp_100000 on tcomp(id100000) compress;
create index tcomp_1000000 on tcomp(id1000000) compress;
create index tcomp_2000000 on tcomp(id2000000) compress;
create index tcomp_3000000 on tcomp(id3000000) compress;
create index tcomp_4000000 on tcomp(id4000000) compress;
create index tcomp_4500000 on tcomp(id4500000) compress;

create table notcomp (p int primary key,id int ,id2 int, id100 int, id1000 int, id10000 int ,id100000 int,id1000000 int,id2000000 int,id3000000 int,id4000000 int,id4500000 int);
insert into notcomp select * from tcomp;
create index notcomp_uni on notcomp(id) ;
create index notcomp_2 on notcomp(id2) ;
create index notcomp_100 on notcomp(id100) ;
create index notcomp_1000 on notcomp(id1000) ;
create index notcomp_10000 on notcomp(id10000) ;
create index notcomp_100000 on notcomp(id100000) ;
create index notcomp_1000000 on notcomp(id1000000) ;
create index notcomp_2000000 on notcomp(id2000000) ;
create index notcomp_3000000 on notcomp(id3000000) ;
create index notcomp_4000000 on notcomp(id4000000) ;
create index notcomp_4500000 on notcomp(id4500000) ;

こんな感じになる

SQL> select INDEX_NAME,num_rows,DISTINCT_KEYS,trunc((DISTINCT_KEYS/5000000)*100,5) as percentage from user_indexes where INDEX_NAME like '%TCOMP_%';

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS PERCENTAGE
------------------------------ ---------- ------------- ----------
TCOMP_2                           5000000             2     .00004
TCOMP_100                         5000000           100       .002
TCOMP_1000                        5000000          1000        .02
TCOMP_10000                       5000000         10000         .2
TCOMP_100000                      5000000        100000          2
TCOMP_1000000                     5000000       1000000         20
TCOMP_2000000                     5000000       2000000         40
TCOMP_3000000                     5000000       3000000         60
TCOMP_4000000                     5000000       4000000         80
TCOMP_4500000                     5000000       4500000         90
TCOMP_UNI                         5000000       5000000        100

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS PERCENTAGE
------------------------------ ---------- ------------- ----------
NOTCOMP_2                         5000000             2     .00004
NOTCOMP_100                       5000000           100       .002
NOTCOMP_1000                      5000000          1000        .02
NOTCOMP_10000                     5000000         10000         .2
NOTCOMP_100000                    5000000        100000          2
NOTCOMP_1000000                   5000000       1000000         20
NOTCOMP_2000000                   5000000       2000000         40
NOTCOMP_3000000                   5000000       3000000         60
NOTCOMP_4000000                   5000000       4000000         80
NOTCOMP_4500000                   5000000       4500000         90
NOTCOMP_UNI                       5000000       5000000        100

セグメントサイズを確認

select segment_name, bytes from user_segments where segment_name like 'TCOMP_%' or segment_name like 'NOTCOMP_%' order by 1;

インデックス	TCOMP(byte)	NOTCOMP(byte)	差分(byte)
2	65011712	83886080	18874368
100	65011712	83886080	18874368
1000	65011712	92274688	27262976
10000	65011712	92274688	27262976
100000	66060288	92274688	26214400
1000000	83886080	100663296	16777216
2000000	92274688	100663296	8388608
3000000	109051904	100663296	-8388608
4000000	125829120	100663296	-25165824
4500000	125829120	100663296	-25165824
PK(5000000)	134217728	100663296	-33554432

データの偏りもあるが、全体件数の40～50％を下回るカーディナリティの場合はcompressが有効と思われる。
60%を超えるカーディナリティーの場合圧縮したほうがデータ容量が増えてしまっていることがわかる。

等価比較の実行速度を検証

アクセス	TCOMP	NOTCOMP
where id2=1	00:00:58.19	00:00:56.06
where id100=1	00:00:01.33	00:00:01.33
where id1000=1	00:00:00.15	00:00:00.15
where id10000=1	00:00:00.02	00:00:00.03
where id100000=1	00:00:00.01	00:00:00.01
where id1000000=1	00:00:00.01	00:00:00.01
where id2000000=1	00:00:00.01	00:00:00.01
where id3000000=1	00:00:00.01	00:00:00.01
where id4000000=1	00:00:00.01	00:00:00.01
where id4500000=1	00:00:00.01	00:00:00.01
where id=1	00:00:00.01	00:00:00.01

多少のcompressのほうがオーバヘッドがある分遅いがほとんど実行速度は大きくは変わらない

インデックスレンジスキャン（全件取得）の実行速度と読み込みブロック数を検証

アクセス	TCOMP(実行時間)	NOTCOMP(実行時間)	TCOMP(consistent gets)	NOTCOMP(consistent gets)
where id2 < 2	00:00:00.50	00:00:00.39	7683	9414
where id100 < 100	00:00:00.50	00:00:00.37	7683	9761
where id1000 < 1000	00:00:00.50	00:00:00.38	7685	10386
where id10000 < 10000	00:00:00.50	00:00:00.38	7685	10449
where id100000 < 100000	00:00:00.50	00:00:00.38	7835	11085
where id1000000 < 1000000	00:00:00.50	00:00:00.38	9218	11134
where id2000000 < 2000000	00:00:00.53	00:00:00.37	10875	11426
where id3000000 < 3000000	00:00:00.57	00:00:00.37	12554	11563
where id4000000 < 4000000	00:00:00.57	00:00:00.37	14225	11563
where id4500000 < 4500000	00:00:00.57	00:00:00.37	15057	11634

圧縮率が高い場合はconsistent getsは非圧縮よりも小さくなるが、実行速度はやはり圧縮したものを解凍するため CPU時間がかかり遅くなっているものと考えられる。

データを追加したときのサイズの検証

データ準備

データをそのまま倍にする

insert into tcomp select P+5000000,id,id2,id100,id1000,id10000,id100000,id1000000,id2000000,id3000000,id4000000,id4500000 from tcomp;
経過: 00:10:40.36
insert into notcomp select P+5000000,id,id2,id100,id1000,id10000,id100000,id1000000,id2000000,id3000000,id4000000,id4500000 from notcomp;
経過: 00:07:53.01

insertは非圧縮したほうが早い。

サイズ結果

インデックス	TCOMP(byte)	NOTCOMP(byte)	差分(byte)
2	159383552	201326592	41943040
100	142606336	218103808	75497472
1000	184549376	243269632	58720256
10000	218103808	260046848	41943040
100000	134217728	184549376	50331648
1000000	159383552	184549376	25165824
2000000	184549376	201326592	16777216
3000000	209715200	206569472	-3145728
4000000	243269632	201326592	-41943040
4500000	251658240	201326592	-50331648
PK(5000000)	268435456	209715200	-58720256

圧縮されたサイズも倍近くになっていることがわかる。インサートされても圧縮はされている模様。

データを更新したときのサイズの検証

データ準備

全件プラス１する

update tcomp set id=id+1,id2=id2+1,id100=id100+1,id1000=id1000+1,id10000=id10000+1,id100000=id100000+1,id1000000=id1000000+1,id2000000=id2000000+1,id3000000=id3000000+1,id4000000=id4000000+1,id4500000=id4500000+1;
経過: 01:11:24.10
update notcomp set id=id+1,id2=id2+1,id100=id100+1,id1000=id1000+1,id10000=id10000+1,id100000=id100000+1,id1000000=id1000000+1,id2000000=id2000000+1,id3000000=id3000000+1,id4000000=id4000000+1,id4500000=id4500000+1;
経過: 01:17:44.47

update実行の時間は圧縮したほうが若干早かった。

サイズ結果

インデックス	TCOMP(byte)	NOTCOMP(byte)	差分(byte)
2	293601280	377487360	83886080
100	285212672	417333248	132120576
1000	352321536	478150656	125829120
10000	402653184	494927872	92274688
100000	260046848	373293056	83886080
1000000	310378496	402653184	25165824
2000000	360710144	385875968	25165824
3000000	394264576	394264576	0
4000000	393216000	402653184	9437184
4500000	394264576	411041792	16777216
PK(5000000)	394264576	411041792	16777216

不思議な結果になった。ほとんどのインデックスサイズが非圧縮インデックスのほうが大きくなっている。

データを削除したときのサイズの検証

データ準備

insertで増やした5000000件削除する

delete from tcomp where P > 5000000;
経過: 00:13:27.78
delete from notcomp where P > 5000000;
経過: 00:07:18.22

delete実行の時間は非圧縮したほうが圧倒的に早かった。

サイズ結果

インデックス	TCOMP(byte)	NOTCOMP(byte)	差分(byte)
2	293601280	377487360	83886080
100	285212672	417333248	132120576
1000	352321536	478150656	125829120
10000	402653184	494927872	92274688
100000	260046848	373293056	83886080
1000000	310378496	402653184	25165824
2000000	360710144	385875968	25165824
3000000	394264576	394264576	0
4000000	393216000	402653184	9437184
4500000	394264576	411041792	16777216
PK(5000000)	394264576	411041792	16777216

deleteのサイズは先ほどupdateした時と違いがなかった。インデックスの断片化されたままであった。

効果的なインデックス圧縮を見つけるためのツール

<a href="https://github.com/kenken0807/oracle-indexcompress-checker">kenken0807/oracle-indexcompress-checker</a>github.com

統計情報を参照し、インデックス圧縮することで容量削減可能であるインデックスを表示してくれるツール。

使い方

perlなので以下を入れてあげる

cpanm install DBD::Oracle
cpanm install Term::ANSIColor

実行

perl oracle-indexcompress-checker.pl --user orauser --password orauser --sid orcl --host localhost --lastday 1

オプション

[OPTIONS]
--user USERNAME[default none]
--password PASSWORD[default none]
--host HOSTNAME[default localhost]
--table TABLENAME[default none]　特定のテーブルのインデックスの場合指定
--sid SID[default none]
--port LISTENER PORT[default 1521]
--lastday [default 7]指定した日数より以降に統計情報を取得したテーブル・インデックスのみを対象とする

実行結果

表示されるインデックスはカラムが１つの場合は(NUMROWS/DISTINCT100) < 50%
マルチカラムインデックスの場合は最初のカラムが(NUMROWS/DISTINCT100) < 50%または(NUMROWS/INDEX_DISTINCT*100) < 50%

=====================================================================================================
TABLE ANALYZED:2015/07/17 12:53:53  INDEX ANALYZED: 2015/07/17 12:54:10
TABLENM: NOTCOMP
INDEXNM: NOTCOMP_10000
NUMROWS: 10000000  INDEX_DISTINCT: 10000  FULL COMPRESS %: 0.1   BYTES: 310378496
----------------------------------------------------------------------------------------------------
COLPOSI: 1     COLNAME: ID10000                 DISTINCT:      10000   %:   0.10
REBUILD_SQL: ALTER INDEX NOTCOMP_10000 rebuild compress 1;
SIZE_SQL:    SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME='NOTCOMP_10000';

各項目について

TABLE ANALYZED: テーブルの統計情報取得日時
INDEX ANALYZED: インデックスの統計情報取得日時
TABLENM: テーブル名
INDEXNM: インデックス名
NUMROWS: テーブルのレコード件数
INDEX_DISTINCT: インデックスのカーディナリティ
FULL COMPRESS %: (INDEX_DISTINCT/NUMROWS)*100・・この値が50％未満の場合はcompressすることで容量の削減が可能
BYTES: インデックスのバイト数
COLPOSI: インデックスのカラム順序
COLNAME: カラム名称
DISTINCT: カラムのカーディナリティ　　　　
%: 対象カラムのカーディナリティ(NUMROWS/DISTINCT)*100
REBUILD_SQL: compressするためのSQL文
SIZE_SQL: サイズを確認するためのSQL文

確認ツールを使ってマルチカラムインデックスのサイズの違いを見る

ツールを使用し、以下表示を確認。

=====================================================================================================
TABLE ANALYZED:2015/07/17 22:02:09  INDEX ANALYZED: 2015/07/17 16:09:40
TABLENM: MULTI_TABLE
INDEXNM: IDX_MULTI_TABLE
NUMROWS: 3917488  INDEX_DISTINCT: 3944260  FULL COMPRESS %: 100.68 BYTES: 159383552
----------------------------------------------------------------------------------------------------
COLPOSI: 1     COLNAME: ID                      DISTINCT:      33196   %:   0.85
COLPOSI: 2     COLNAME: TYPE                    DISTINCT:          2   %:   0.00
COLPOSI: 3     COLNAME: FROM_DATE               DISTINCT:         29   %:   0.00
COLPOSI: 4     COLNAME: SORT_NO                 DISTINCT:          8   %:   0.00
COLPOSI: 5     COLNAME: TYPE2                   DISTINCT:          3   %:   0.00
REBUILD_SQL: ALTER INDEX IDX_MULTI_TABLE rebuild compress 5;
SIZE_SQL:    SELECT SEGMENT_NAME,BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME='IDX_MULTI_TABLE ';

マルチカラムインデックスの場合はcompressのあとに数値をつけると、先頭から数値分のカラムのみ圧縮する。

ALTER INDEX IDX_MULTI_TABLE rebuild compress 2;

とすると、IDとTYPEのみ圧縮対象となる。

すべてのパターンを試してみた。

compress	byte
nocompress	159383552
compress 5	192937984
compress 4	192937984
compress 3	109051904
compress 2	125829120
compress 1	134217728

↑の場合はcompress 3が最適となる。判断はどのカラムまでが重複が多いのか確認しないとわからないので難しい。

distinctした結果以下であった。

compress	distinct rows
nocompress	3944260
compress 5	3944260
compress 4	3944260
compress 3	945950
compress 2	46840
compress 1	33437

まとめ

インデックス圧縮は全体の行に対して40％以下のカーディナリティの場合に容量削減に効果がある。
select処理での読み込みブロック数は減らすことができる（ディスクアクセスは早くなりそう）。
CPUでの処理時間を多く消費するためCPUTIMEは増える。
既存のインデックスに対して圧縮する場合rebuild compressとなるが、SEではonline句が使用できないためその際は表ロックがかかってしまう。
結論、使いどころが非常に難しいインデックス圧縮。ディスク容量最優先の場合は使用したほうが良いかも。

kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

Oracle SEでインデックス圧縮について考える

SEでできる圧縮

準備

セグメントサイズを確認

等価比較の実行速度を検証

インデックスレンジスキャン（全件取得）の実行速度と読み込みブロック数を検証

データを追加したときのサイズの検証

データ準備

サイズ結果

データを更新したときのサイズの検証

データ準備

サイズ結果

データを削除したときのサイズの検証

データ準備

サイズ結果

効果的なインデックス圧縮を見つけるためのツール

使い方

実行

オプション

実行結果

各項目について

確認ツールを使ってマルチカラムインデックスのサイズの違いを見る

まとめ