実践!データ圧縮の効果的な活用テクニック
日本オラクル株式会社
以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。 また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことは できません。以下の事項は、マテリアルやコード、機能を提供することをコミットメン ト(確約)するものではないため、購買決定を行う際の判断材料になさらないで下さ い。オラクル製品に関して記載されている機能の開発、リリースおよび時期につい ては、弊社の裁量により決定されます。 OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。文 中の社名、商品名等は各社の商標または登録商標である場合があります。
Agenda
•
マルチコア化とデータベース性能の現状
•
Advanced Compression Option
OLTP表圧縮の機能概要
•
OLTP表圧縮の効果
•
DWH系システムに対する圧縮の効果
•
OLTP表圧縮のチューニング・ポイント
マルチコア化
Intel Xeonプロセッサの場合
Copyright © 2011, Oracle. All rights reserved. 5
5年間で17倍の処理性能の向上
マルチコア化とデータベースの性能
Oracle Directのパフォーマンスクリニックの現状
*データ:Oracle Directが直近で実施したパフォーマンスクリニック http://www.oracle.com/lang/jp/direct/service/pc.html性能ボトルネックの原因の傾向
CPU:9%
ストレージI/O: 43%
非効率なSQL文、索引の設計等 :48%
CPUを追加すれば、性能問題は解決?
CPUがボトルネックだったケースは、わずか
9%(*弊社統計)
マルチコアを使いきることができない
Copyright © 2011, Oracle. All rights reserved. 7
Advanced Compression Option
OLTP表圧縮の機能概要
Advanced Compression Option
圧縮機能一覧
•
最大限のリソース活用とコスト削減を支援する包括的な圧縮
機能(Oracle Database11g~)
1.格納データの圧縮
・Data GuardのREDO転送
・OLTP表の圧縮
・非構造化データ
(SecureFiles)
の圧縮・重複除外
・Data Pumpの圧縮
・RMANの高速圧縮
3.通信データの圧縮
2.バックアップの圧縮
OLTP表圧縮により期待される効果
圧縮率の向上によるパフォーマンス改善
•
OLTP表圧縮でデータ量を縮小し、検索系処理のパフォーマンスを改善
• パフォーマンス改善は、ディスクI/O性能のボトルネックを解消することで実現
• その場合、CPUリソースが余っていることが前提
Copyright © 2011, Oracle. All rights reserved. 9
圧縮率の向上 パフォーマンス改善 OLTP系 バッチ系 より多くのレコードをバッファ・キャッシュ上に 保持可能となり、キャッシュ・ヒット率が向上する効果 ディスクから読込むデータ量が削減されることで、 ディスクI/O時間(SQL処理時間)の大幅な短縮効果
大量データ読込みの高速化
ディスクI/O性能のボトルネックの解消
•
Oracle Databaseの圧縮機能は、H/Wリソースを有効活用
• Oracleは、サーバー側で展開する仕組みでボトルネックを解消非圧縮
Oracleの圧縮機能
ボトルネックOLTP表圧縮
データ圧縮のアルゴリズム
•
INSERT時、ブロック空き領域が内部的に定められた閾値以下になると判
断された場合、 サーバー・プロセスが圧縮
• commit/rollback 状況には依存しない
Copyright © 2011, Oracle. All rights reserved. 11 1,AAAAAAA 1行 INSERT 4行 INSERT 閾値未到達 1行 INSERT 閾値到達 圧縮 閾値 シンボル表 1,AAAAAAA 2,BBBBBBB 3,AAAAAAA 4,BBBBBBB 5,AAAAAAA 1,AAAAAAA 2,BBBBBBB 3,AAAAAAA 4,BBBBBBB 5,AAAAAAA 6,CCCCCC 1,● 2,■ 3,● 4,■ 5,● 6,CCCCCC BBBBBBB AAAAAAA
複数カラムでの圧縮
別カラムで共通する列データ
•
別のカラムでも、共通する列データは圧縮される
• シンボル表の列データを共有するには、データ型が同じである必要有り 閾値に達し、圧縮 共通の列データにマトメて シンボル表に格納 1,AAA,BBB 2,BBB,AAA 3,AAA,CCC 4,AAA,BBB 5,CCC,AAA 6,BBB,DDD 1,●,■ BBB AAA CCC 2,■,● シンボル表 閾値 3,●,▲ 4,●,■ 5,▲,● 6,BBB,DDDデータ型とOLTP表圧縮
CHAR型とVARCHAR2型の違い
•
固定長のCHAR型と可変長のVARCHAR2型を含む表での圧縮
• CHAR型 • 文字列の長さに依存せず、一定サイズで格納 • 定義サイズよりも短い値の文字列は、固定長に合わせて空白を埋め込む • VARCHAR2型 • 必要最低限のサイズで文字列を格納 ※ □は空白の意 13Copyright © 2011, Oracle. All rights reserved.
文字列 CHAR(5) VARCHAR2(5) 圧縮
ABCED ABCDE ABCDE ○
圧縮表の作成方法
Oracle Database 11g Release 2 以降
•
表領域レベル/表レベル/パーティションレベルでの設定が可能
•
表領域レベル
•
表レベル
非圧縮を明示的に指定する場合は、「NOCOMPRESS」
•
Oracle Database 11g R1の表記方法は非推奨
• COMPRESS FOR ALL OPERATIONS => COMPRESS FOR OLTP
• COMPRESS FOR DIRECT_LOAD OPERATIONS => COMPRESS または、 COMPRESS BASIC
create table
TableName
(
column1,column2,..
)
COMPRESS FOR OLTP
;
create tablespace
TablespaceName
datafile '...'
圧縮表の作成方法
Oracle Database 11g Release 2 以降
•
パーティションレベル
• 表全体/親パーティション/サブ・パーティションの単位で設定可能
• 例えば、表全体の設定は「圧縮」にし、特定のパーティションだけ「非圧縮」に設
定する場合は、
表全体で「COMPRESS FOR OLTP」
特定の親パーティションで「NOCOMPRESS」 を指定する
Copyright © 2011, Oracle. All rights reserved. 15
create table
TableName
(
column1,column2,…
)
★
partition by
PartitionType
(
columnM
)
subpartition by
PartitionType
(
columnN
)
(partition
Partition1
values less than (
value1
)
★
(subpartition
SubPartition1
values (
value1-1
)
★
,
subpartition
SubPartition2
values (
value1-2
) ),
partition
Partition2…
));
表全体 親パーティション サブパーティション 優 先 順 位 高 低圧縮表への変更方法
alter table文と表のオンライン再定義
•
既存表を圧縮属性に変更する方法は主に3種類
1.
既存レコードは非圧縮のままで、新規レコードから圧縮する場合
2.
新規レコードだけではなく、既存レコードも圧縮する場合
• ただし、このSQL終了後、索引のRebuildが必要となる • 一定期間の運用後、既に圧縮済みの表の圧縮効率を高める為に、 再圧縮のオペレーションとしても利用可能3.
表のオンライン再定義を使用
• システム無停止で、既存レコードも圧縮可能alter table
TableName
COMPRESS FOR OLTP
;
alter table
TableName
圧縮表への変更方法
表のオンライン再定義のサンプル
Copyright © 2011, Oracle. All rights reserved. 17
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('SH','SALES',DBMS_REDEFINITION.CONS_USE_PK); END;
/
create table SALES_TMP compress for oltp as select * from SALES where 1=2; alter table SALES_TMP add primary key(col1);
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'SH', orig_table => 'SALES', int_table => 'SALES_TMP', col_mapping => NULL, options_flag => DBMS_REDEFINITION.CONS_USE_PK); END; / BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SH','SALES', 'SALES2'); END; / -- このタイミングで必要に応じて、SALES_TMP側に索引を作成(その後、再度SYNC_INTERIM_TABLEの実行を推奨) BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE('SH','SALES', 'SALES2'); END; /
OLTP表圧縮
制限事項
•
以下のオブジェクトはOLTP表圧縮の対象外
• ブロック内でユニークなデータ • 256列以上定義されている表 • 圧縮属性付きでCreate Tableの実行は可能だが、圧縮は行われない • BasicFile LOBのデータ・セグメント • SecureFilesの圧縮/重複排除で対応 • 外部表 • テキスト・ファイルが圧縮された状態でも外部表として定義し、検索可能100%
短
長
小
大
4k
8k
16k
相
対
サ
イ
ズ
100%
列データ長
Cardinality
ブロック・サイズ
圧縮率に影響する3大要素
列データ長、Cardinality、ブロック・サイズ
•
列データ長が長いほど、圧縮効果は高い
• ただし、1ブロック内に重複する列データが2つ格納できない場合は例外•
Cardinality(値の種類)が小さいほど、圧縮効果は高い
•
ブロック・サイズが大きいほど、圧縮効果は高い
Advanced Compression Advisor
Oracle Database 11g Relase 2 以降
•
DBMS_COMPRESSION パッケージ
• DBA権限所有ユーザーでのみ実行可能 • GET_COMPRESSION_RATIO プロシージャ • 事前に圧縮効果を測定するプロシージャ • プロシージャ内部で、実際に圧縮表と非圧縮表を作成 • Enterprise Editionでのみ使用可能 • GET_COMPRESSION_TYPE ファンクション • 指定したブロックの圧縮方法を確認できるファンクションGET_COMPRESSION_RATIO プロシージャ
構文
Copyright © 2011, Oracle. All rights reserved. 21 DBMS_COMPRESSION.GET_COMPRESSION_RATIO ( SCRATCHTBSNAME VARCHAR2 IN OWNNAME VARCHAR2 IN TABNAME VARCHAR2 IN PARTNAME VARCHAR2 IN COMPTYPE NUMBER IN BLKCNT_CMP BINARY_INTEGER OUT BLKCNT_UNCMP BINARY_INTEGER OUT ROW_CMP BINARY_INTEGER OUT ROW_UNCMP BINARY_INTEGER OUT CMP_RATIO NUMBER OUT COMPTYPE_STR VARCHAR2 OUT);
パーティションを限定する場 合は、partnameにパーティ ション名を設定 見積もる圧縮タイプを設定 非圧縮 1 OLTP表圧縮 2
GET_COMPRESSION_RATIO プロシージャ
構文
set serveroutput on declare
SCRATCHTBSNAME VARCHAR2(30) :='USERS'; OWNNAME VARCHAR2(30) :='SH'; TABNAME VARCHAR2(30) :='SALES';
PARTNAME VARCHAR2(30) :='SALES_Q3_2001'; COMPTYPE_FLG NUMBER :=2; SAMPLE_BLKCNT_CMP BINARY_INTEGER; SAMPLE_BLKCNT_UNCMP BINARY_INTEGER; SAMPLE_ROWNUM_PER_BLK_CMP BINARY_INTEGER; SAMPLE_ROWNUM_PER_BLK_UNCMP BINARY_INTEGER; CMP_RATIO NUMBER; COMPTYPE_STR VARCHAR2(100); begin DBMS_COMPRESSION.GET_COMPRESSION_RATIO (SCRATCHTBSNAME,OWNNAME,TABNAME,PARTNAME,COMPTYPE_FLG, SAMPLE_BLKCNT_CMP,SAMPLE_BLKCNT_UNCMP,SAMPLE_ROWNUM_PER_BLK_CMP, SAMPLE_ROWNUM_PER_BLK_UNCMP,CMP_RATIO,COMPTYPE_STR); dbms_output.put_line('---');
dbms_output.put_line('OBJECT_NAME => '|| OWNNAME ||'.'|| TABNAME || ' (PARTITION='|| PARTNAME ||')'); dbms_output.put_line('COMPRESS_RATIO => '|| CMP_RATIO); dbms_output.put_line('---'); dbms_output.put_line('COMPRESSED_TYPE = '||COMPTYPE_STR); dbms_output.put_line('SAMPLE_UNCOMPRESSED_BLOCKS = '||SAMPLE_BLKCNT_UNCMP); dbms_output.put_line('SAMPLE_COMPRESSED_BLOCKS = '||SAMPLE_BLKCNT_CMP); dbms_output.put_line('SAMPLE_UNCOMPRESSED_ROWS_PER_BLK = '||SAMPLE_ROWNUM_PER_BLK_UNCMP); dbms_output.put_line('SAMPLE_COMPRESSED_ROWS_PER_BLK = '||SAMPLE_ROWNUM_PER_BLK_CMP); end; /
GET_COMPRESSION_RATIO プロシージャ
実行結果
• SH.SALESテーブルのSALES_Q3_2001パーティションに対して、OLTP表圧縮を 適用した場合、約2.6倍圧縮される見込みであることを確認 • 約2.6倍圧縮 データ量が約38%(=100/2.6)まで縮小 • SAMPLE_UNCOMPRESSED_BLOCKS : サンプリングしたブロック数 • SAMPLE_COMPRESSED_BLOCKS : サンプリングしたブロック数を圧縮後のブロック数 • SAMPLE_UNCOMPRESSED_ROWS_PER_BLK : サンプリングした1ブロックあたりの行数 • SAMPLE_COMPRESSED_BLOCKS : サンプリングデータを圧縮後の1ブロックあたりの行数Copyright © 2011, Oracle. All rights reserved. 23
---OBJECT_NAME => SH.SALES (PARTITION=SALES_Q3_2001)
COMPRESS_RATIO => 2.6
---COMPRESSED_TYPE = "Compress For OLTP" SAMPLE_UNCOMPRESSED_BLOCKS = 318
SAMPLE_COMPRESSED_BLOCKS = 123 SAMPLE_UNCOMPRESSED_ROWS_PER_BLK = 206 SAMPLE_COMPRESSED_ROWS_PER_BLK = 535
GET_COMPRESSION_TYPE ファンクション
構文
• 指定したROWIDを持つレコードが含まれるブロックの圧縮タイプを確認 • comptypeの値が出力される DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( ownname IN VARCHAR2, tabname IN VARCHAR2, row_id IN ROWID) RETURN NUMBER;GET_COMPRESSION_TYPE ファンクション
実行例
Copyright © 2011, Oracle. All rights reserved. 25
desc SCOTT.TEST
Name Null? Type
--- --- ---COL1 NUMBER
COL2 VARCHAR2(100) select COL1,
case DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT', 'TEST',rowid) when 1 then 'No Compression'
when 2 then 'OLTP Compression' end
from TEST where COL1=100; COL1 CASEDBMS_COMPRES
--- ---100 OLTP Compression
OLTP表圧縮
Oracle Advanced Compression Advisor (9i R2 ~11g R1)
•
アップグレード前に、DBMS_COMPRESSIONパッケージを使用して、
どの程度圧縮されるのかを見積もることが可能
• 以下のサイトから、Create package文をダウンロード+適用 • 注意:Enterprise Editionのみに対応 http://www.oracle.com/technology/products/database /compression/compression-advisor.html ダウンロードOLTP表圧縮
Oracle Advanced Compression Advisor (9i R2 ~11g R1)
•
実行例
• プロシージャ内部で、実際に圧縮表と非圧縮表を作成して各セグメントのサイ
ズを比較する為
• Enterprise Editionでしか使用できない
• 既に圧縮済みの表に対して実行することで、圧縮の効果の確認も可能
Copyright © 2011, Oracle. All rights reserved. 27
SQL> set serveroutput on
SQL> exec DBMS_COMPRESSION.getratio('SH','SALES','OLTP',10);
Sampling table: SH.SALES Sampling percentage: 10% Compression Type: OLTP
【Oracle GRID Center 検証結果】
OLTP表圧縮の性能検証
検証で使用したトランザクション(SQL)
•
カスタムJavaアプリケーションを使用して、以下のJPetStoreのトランザク
ションを実行
Copyright © 2011, Oracle. All rights reserved. 29
1. Signon
SELECT … FROM account …
2. Search Products
SELECT … FROM category … SELECT … FROM product …
3. Select One Product
SELECT … FROM item,product …
4. Check Inventory
SELECT … FROM inventory …
5. Order process
INSERT INTO orders … INSERT INTO orderstatus… INSERT INTO lineitem … UPDATE inventory … COMMIT • トランザクションの比率 • Tx1 : Tx2 = 1 : 9 Transaction1 Transaction2
OLTP表圧縮の性能検証
検証環境
EMC CX4-240
DELL PowerEdge
R710
DB Server DELL PowerEdge R710Intel Xeon E5540(4 Core) x 2 (16Thread) 36GB Memory
OLTP表圧縮の性能検証
検証結果
Copyright © 2011, Oracle. All rights reserved. 31
0
20
40
60
80
100
TPS
(N
or
mal
-MaxTP
S=>10
0)
Small
<--
Normal Data Size
-->
Large
TPS Normal
TPS Compress
OLTP表圧縮の性能検証
CPUオーバーヘッド
0
10
20
30
40
50
60
70
80
90
100
0
100
200
300
400
500
600
CPU
-Us
age
(usr
+sy
s)
Time
Normal
Compress
OLTP表圧縮の性能検証
まとめ
•
OLTP表圧縮の効果
• ディスク容量の削減が可能 • 本検証では、表の合計サイズが1/2へ縮小 • キャッシュ・ヒット率の向上によりスループット向上 • 圧縮により1つのブロックに格納されるレコード数が増加 • バッファ・キャッシュ上に圧縮状態でブロックをキャッシュ • ディスクから読み込む必要のあるブロック数が大幅に減少•
オーバーヘッド
• データの圧縮に若干のオーバーヘッドがあるが、 検索中心の処理でディスクI/O性能がボトルネックの環境では、 スループットの向上が期待できる【Oracle GRID Center 検証結果】
DWH系システムに対する圧縮の効果
大量データの全表検索(Table Full Scan)の高速化
•
DWH系クエリのレスポンス・タイムの大幅削減が可能
• 同じ行数をストレージから読み込む場合でも、
物理的なデータ移動量が減少した効果
• データの総容量の削減だけではなく、クエリ性能の向上も期待できる
Copyright © 2011, Oracle. All rights reserved. 35
【参考】
In-Memory Parallel Queryによる NEC Express5800/A1160上でのData Warehouseシステム全体の性能向上 http://www.nec.co.jp/middle/oracle/gc5.html
約2倍
高速化
DWH系システムに対する圧縮の効果
In-Memory Parallel Executionの適用範囲の拡大
•
圧縮により、メモリ上にキャッシュ可能なレコード数が増加することで、
In-Memory Parallel Executionの適用範囲が拡大大
0 10 20 30 40 50 60 70 80 90 100 0 20 40 60 80 100 120 相 対 レ ス ポ ン ス タ イ ム 非圧縮時のデータサイズ(GB)
非圧縮Direct Path Read 非圧縮In-Memory PQ設定 圧縮In-Memory PQ設定
Copyright © 2011, Oracle. All rights reserved. 37
圧縮に伴うオーバーヘッド
各処理におけるメリット・デメリット
•
圧縮ブロックを読む際は、オーバーヘッド無し
• ブロック単位での圧縮が行われる為、それ以外のブロックへのアクセスは不要 • 展開は行わず、圧縮されたままバッファ・キャッシュ上にキャッシュ SELECT、DELETEは高速化が期待される•
ブロックの圧縮が成功する度、UNDOとRedoを生成
• 読み取り一貫性やトランザクションのロールバックの為に、圧縮前のブロック・ イメージ(UNDO)を保持する必要有り 大部分のINSERT、UPDATEでは追加のオーバーヘッドは発生しないが、ブ ロックを圧縮する際、CPU使用率の増加やUNDOやRedoを生成チューニング・ポイントは?
チューニング・ポイント
圧縮率の向上
•
1ブロック内に、より多くの冗長なデータを格納すること
• より大きなデータ・ブロック・サイズの採用 • データをソートしてからINSERT • パーティション分割 • 非冗長カラムの外出し(特にカラム長が長いもの) • アプリケーションの改修が必要となる為、要検討•
その他
• Direct Path Loadの採用(次頁参照)
• 通常INSERTよりも、Direct Path Loadの方が圧縮率が若干高くなる傾向
○ バッチ処理のように、1つのSQLで大量データを挿入するINSERT
× OLTP処理の1行INSERTに対しては適用しないこと
チューニング・ポイント
性能の向上(1)
•
大量データを扱う処理はParallel実行
• 複数のCPUコアを効率的に活用
• 圧縮によるCPUオーバーヘッドの分散化
•
大量データをINSERTする際は、Direct Path Load
• UNDOとRedoの生成量を大幅に抑制可能
Alter session [enable | force] parallel [ddl | dml | query] parallel n;
11g R2~ : 自動パラレル・チューニング(parallel_degree_policy=auto)
INSERT文に「
/*+ APPEND */
」ヒント句を追加
テキストファイルのデータをロードする場合は、外部表を活用
データベース内の表と同じように、パラレル化も可能
チューニング・ポイント
性能の向上(2)
•
大量データのUPDATE文をCREATE TABLE as SELECT + CASE文へ
• 既存UPDATEの実行計画がTable Full Scanの場合に効果が高い傾向
• ただし、索引を再作成する必要有り
• 大量データのDELETEでも流用可能
Copyright © 2011, Oracle. All rights reserved. 41
update SALES set TAX_PCT = 0.05 where TAX_PCT = 0.03
and TAX_DATE > '01-May-10'; Commit;
create table SALES_NEW nologging parallel compress for oltp
as select ..., case
TAX_PCT=0.03 and TAX_DATE >'01-May-10' then 0.05 else
TAX_PCT end,
...,from SALES;
-- 索引や制約の作成(DBMS_METADATA.GET_DDL等を活用)
alter table SALES rename to SALES_OLD; alter table SALES_NEW to SALES;
チューニング・ポイント
同時並行性
•
1ブロック内に格納される行数が増加することによる同時並行性の低下
• Cache Buffer Chainラッチ待ち
• ある1つのデータ・ブロックに対して、複数ユーザーからのアクセスが集中し
た場合、Cache Buffer Chainラッチ待ちが発生することが有り
• ITL競合
• 同時トランザクション数が多い表や索引において、ブロックのトランザクショ
ン・リストに十分な領域がない場合、ITLの競合が発生することが有り
元々データ量が少ない表は圧縮しない
圧縮対象表の選定方法
•
以下の手順に従い、圧縮対象表を選定することを推奨
1. 256列以上の圧縮されない表を除外 2. データ量が多い上位20%の表を選定 全体の数値の大部分(80%)は、全体を構成する内の一部(20%)の要 素が生み出しているという一般論(パレートの法則)に基づく 3. 選定された各表に対してCompress Advisorを実行し、圧縮効果を測定 4. テスト環境において、負荷検証を実施 Real Application Testing(RAT)等の負荷生成ツールで性能への影響 と表サイズの成長率を確認
5. システム全体のパフォーマンスを考慮し、本番環境で圧縮を設定
※ バッチ処理で大量にデータをUPDATEする表は別途検討
43
OLTP表圧縮の適用ケース
• OLTP表圧縮によりディスクI/Oのボトルネックを解消可能 • CPU使用率は若干上昇するので、CPUボトルネックの環境には不適切 • SELECTやDELETEで圧縮の効果を得やすい傾向 • INSERTやUPDATEが多い場合は、リソース消費の増加に注意 • 数GBや数MBの表を数多く圧縮するよりも、 数百GBや数十GBの表を数個だけ圧縮する方がストレージ削減効果は高いディスクI/Oがボトルネック
検索処理の比率が高い
データ量が大きい表
Oracle
GRID
Center
テクニカルホワイトペーパー公開中
Copyright © 2011, Oracle. All rights reserved. 45