Oracle SEでインデックス圧縮について考える
oracleの運用をしていると問題になることの一つは容量不足。
特に頻繁にupdateされるインデックスの容量増加率は激しい。
EE+Oracle Advanced Compressionを使用すれば色々できるが。。。。
それは私にとっては夢の話なのでw
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 |
圧縮されたサイズも倍近くになっていることがわかる。インサートされても圧縮はされている模様。
データを更新したときのサイズの検証
データ準備
全件プラス1する
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した時と違いがなかった。 インデックスの断片化されたままであった。
効果的なインデックス圧縮を見つけるためのツール
統計情報を参照し、インデックス圧縮することで容量削減可能であるインデックスを表示してくれるツール。
使い方
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]指定した日数より以降に統計情報を取得したテーブル・インデックスのみを対象とする
実行結果
表示されるインデックスはカラムが1つの場合は(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句が使用できないためその際は表ロックがかかってしまう。
- 結論、使いどころが非常に難しいインデックス圧縮。ディスク容量最優先の場合は使用したほうが良いかも。