読者です 読者をやめる 読者になる 読者になる

kenken0807_DBメモ

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

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

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

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

SEでできる圧縮

  1. RMAN バックアップセットの圧縮(compress option)
  2. インデックスの圧縮(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した時と違いがなかった。 インデックスの断片化されたままであった。

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

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]指定した日数より以降に統計情報を取得したテーブル・インデックスのみを対象とする

実行結果

表示されるインデックスはカラムが1つの場合は(NUMROWS/DISTINCT100) < 50%
マルチカラムインデックスの場合は最初のカラムが(NUMROWS/DISTINCT
100) < 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句が使用できないためその際は表ロックがかかってしまう。
  • 結論、使いどころが非常に難しいインデックス圧縮。ディスク容量最優先の場合は使用したほうが良いかも。