Comments
Transcript
DWH設計ガイド 2章．区分化によるアクセス効率の向上 第 ビジネス・ユニットの名前
ビジネス・ユニットの名前 DWH設計ガイド 第2章．区分化によるアクセス効率の向上 PSU_temp_0522 ＜第１．１０版 ２００８年 １２月＞ 本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。 この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合する 使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまたは同 様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 2009/3/178/3/05 この文書のデータの利用または公開には、 最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 内容 第1章．大規模DBへの挑戦 第2章．区分化によるアクセス効率の向上 – パーティションDB – パーティション表 – マルチディメンション・クラスタリング（MDC）表 – ハイブリッド構成 第3章．圧縮によるストレージ格納効率の向上 第4章．大規模ワークロードの管理（WLM） PSU_temp_0522 2 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 第2章．区分化によるアクセス効率の向上 ３．マルチディメンション・クラスタリング（MDC）表 – 多次元クラスタリング（MDC）表の概要 • MDC表のブロック索引 • MDCの効果例 • MDC表による削除処理の高速化 • レコード索引のクリーンアップ • MDC ロールアウト パフォーマンスデータ – MDC表の設計 • • • • 設計の流れ 次元キーの選択 設計時のポイント 高速なデータ投入 – 考慮点 PSU_temp_0522 3 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 多次元クラスタリング（MDC）表の概要 多次元クラスタリング（MDC）は、複数属性の値でデータを分類して、自動的に格納する機能 – 単一属性のクラスターでは実現できなかった「2008年10月」の「DB2」の「東京」というような複数の属 性をもつクラスター • 「2008年10月」、「DB2」、「東京」にあたる列は、次元列と呼ばれる クエリー要求に対する高いパフォーマンスを提供 – 次元別検索のパフォーマンス向上 • 同じ値のレコードは同じセルに存在するため、範囲を指定した検索などのように比較的多量の連続したアクセスに有 効（ブロック・ベース（BID)の索引を使用した照会処理） – 削除のパフォーマンスアップ（ブロック削除が可能） セル データ並べ替えを目的とした再編成不要 ブロック (エクステント） レコード 4 MDC表の作成例： CREATE TABLE TB_MDC ( 年月 CHAR(7), 地域 CHAR(10), 製品 VARCHAR(10) PSU_temp_0522 ) ORGANIZE BY DIMENSIONS (年月, 地域, 製品) 2008年 2008年11月 11月 東京 2008年 2008年10月 10月 2008年 2008年10月 10月WebSphere 東京 東京 DB2 DB2 地域 dimension 2008年 2008年11月 11月 大阪 2008年10月 10月 WebSphere 2008年 2008年10月 10月2008年 大阪 大阪 DB2 DB2 製品 dimension © Copyright 年月 dimension IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表のブロック索引(1/2) ブロック索引は、各ブロックへのポインター（BID）を保持する索引 – レコード索引と比べて、索引のサイズがとても小さい – 照会処理時の索引スキャンの負荷が小さい – INSERT処理、DELETE処理時、索引のメンテナンス負荷が小さい CREATE CREATE CREATE CREATE TABLE TB_REGULAR (年月 CHAR(7), 地域 CHAR(10), 製品 VARCHAR(10)) INDEX RIDX_NENGETSU ON TB_NORMAL(年月) INDEX RIDX_CHIIKI ON TB_NORMAL(地域) INDEX RIDX_SEIHIN ON TB_NORMAL(製品) CREATE TABLE TB_MDC (年月 CHAR(7), 地域 CHAR(10), 製品 VARCHAR(10)) ORGANIZE BY DIMENSIONS (年月, 地域, 製品) Regular table and recod index MDC table and block index 索引のポインターは、ブロック単位の ため、ポインターの数が少ない = row = block PSU_temp_0522 5 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表のブロック索引(2/2) 2種類のブロック索引が、表の作成時に自動作成される – 次元ブロック索引 • MDC表の各次元に対して1つずつ作成される – 複合ブロック索引 • 1つのMDC表に対して1つ作成され、各次元に含まれる全ての列をキーに持つ CREATE TABLE TB_MDC (年月 CHAR(7), 地域 CHAR(10), 製品 VARCHAR(10)) ORGANIZE BY DIMENSIONS (年月, 地域, 製品) 年月の次元ブロック索引、地域の次元ブロック索引、製品の次元ブロック 索引、年月＆地域＆製品の複合ブロック索引という計 4つのブロック索 引が作成される。 MDC表に、レコード索引を定義することも可能 PSU_temp_0522 6 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDCの効果例 MDC使用中のお客様へのサーベイ結果 検索パフォーマンス – 大部分が平均３倍の性能向上 – 検索スピードアップの最大は10倍、30倍、100倍、2000倍 PSU_temp_0522 Source:DB2 IM Technical Conference 2005, D09, MDC Performance – Customer Examples and Experience 7 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表による削除処理の高速化 次元キーのみをWHERE条件に指定する削除処理では、セル単位のデータ消しこ みが可能（MDCのロールアウト） – ロールアウトでは、行単位でのログを記録しないため、ログが削減される – DELETE完了通知のタイミングは、レコード索引のメンテナンス方法によって違いがある • レコード索引のメンテナンス方法は、2つのタイプから選択可能（次頁参照） ロールアウトの設定方法 – DB2_MDC_ROLLOUTレジストリ変数、もしくはCURRENT MDC ROLLOUT MODE 特 殊レジスターで行なう MDC ロールアウトのSQL例： DELETE FROM TB_MDC WHERE 年月=‘2008-10’ and 製品=‘DB2’ この例では、2008年10月のDB2に 合致する 2つのセルが削除される。 MDC ロールアウトの設定例 （レジストリ変数による設定） db2set DB2_MDC_ROLLOUT=IMMEDIATE （特殊レジスターによる設定） SET CURRENT MDC ROLLOUT MODE DEFERRED CLEANUP 2008年 2008年11月 11月 東京 2008年 2008年10月 10月 2008年 2008年10月 10月WebSphere 東京 東京 DB2 DB2 2008年 2008年11月 11月 大阪 2008年10月 10月 WebSphere 2008年 2008年10月 10月2008年 大阪 大阪 DB2 DB2 PSU_temp_0522 8 ※設定値の意味については、次頁参照 地域 dimension 製品 dimension © Copyright 年月 dimension IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 レコード索引のクリーンアップ レコード索引のメンテナンスは、2つのクリーンアップのタイプから選択できる。 – MDC 即時(Immediate)索引クリーンアップ・ロールアウト（デフォルト, V8.2.2~） • レコード索引のメンテナンスがすべて完了してからDELETE完了通知を戻す – 各索引のキー削除のために、行をスキャンする – 索引のロギングには、通常のDELETE処理と違いはない – 一時点で処理される索引は、1つ db2set DB2_MDC_ROLLOUT=IMMEDIATE SET CURRENT MDC ROLLOUT MODE IMMEDIATE CLEANUP – MDC 据え置き(Deferred)索引クリーンアップ・ロールアウト • レコード索引のメンテナンスは、DELETE完了通知後にバックグランドにて実行する (V9.5∼） – 索引のロギングは、索引ページ単位のため、ログ量が減少する – メンテナンスのため、索引ごとに非同期バックグラウンド・プロセスが起動されるため、クリーン アップの処理時間が削減される db2set DB2_MDC_ROLLOUT=DEFER PSU_temp_0522 9 SET CURRENT MDC ROLLOUT MODE DEFERRED CLEANUP © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC ロールアウト パフォーマンスデータ 1,100万行 (134,260 pages), 16K page, 16 extent size, 4 nodes, 8 RID indexes Delete response tim e (in percentage) for different rollout options 120.00% 100.00% Delete (no rollout) Percentage 80.00% Immediate rollout 60.00% Deferred rollout including async cleanup 40.00% Deferred rollout not including async cleanup 20.00% 0.00% 0.3 1.5 3.0 30.0 97.0 PSU_temp_0522 Percentage Delete in Table 10 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表の設計（設計の流れ） 設計の流れ – MDC表の設計では、表への照会・更新・削除のアクセス要件を元に次元キーの 選定を行い、続いて、ストレージの格納効率を考慮した適切なページ・サイズ、 エクステント・サイズを決定する。 1. 次元キーを選択する。 まとまった参照、削除がある列を候補とする 2. 次元キーのレコード長から、表のページ・サイズを決定する。 通常表の物理設計と同じく、表の最大サイズやページあたりの格納効率を考慮 して、ページ・サイズを決定する 3. 表全体のレコード件数を元に、セル密度を考慮したエクステント・サ イズを決定する。 PSU_temp_0522 11 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表の設計（次元キーの選択） 次元キーの選択 – 特定の値（例えば、日付、地域、製品単位など）で、まとめて参照や削除を行う場合、その 属性でのクラスタリングを行なう。 ★次元キーの候補となる列 大規模履歴表、売上明細表における日付列、顧客表の 地域など まとまった次元単位の参照がある まとまった次元単位の削除がある 大規模履歴表における日付単位の消しこみなどで は、セル単位の高速削除が可能 ★次元設定のポイント ブロック索引による照会は、比較的多量の連続 した照会に有効 検索述部、GROUP BYにて頻繁に指定される列は有力候補 1エクステントを満たすのに十分な重複値のある列を選択 ユニーク性のあまり低くない列を次元キーに設定したい場合 には、生成列との併用も検討する PSU_temp_0522 12 同じ値のレコードは同じセルに存在するため、範囲を 指定した検索などのように比較的多量の連続した 照会に有効 次元キーを、レコード索引に指定する際には、索引の第一列 に指定するのは避ける ユニーク性の高い列を選択したり、極端に多くの 数の次元列を指定すると、ディスクの浪費につ ながる （I/Oの効率も下がる） 生成列を使用することで、ユニーク性を落とすこ とが可能 カーディナリティーの低い列が第一列だとアクセ スプランが不安定になりやすい © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表の設計（設計のポイント） エクステント・サイズを決定する際のポイント – 次元キーの値にバリエーションが多いデータでは、セル密度が低くなりやすいので気をつける。 著しい低下を防ぐためには、エクステント・サイズを縮小することを考える。 – ただし、エクステント・サイズを小さくしすぎると、いくつかの弊害もあるため注意する。 • 主な弊害 バックアップのスループット低下 ブロック検索によるRelation Scanのスループット低下 – バックアップやブロック検索によるScan時にはエクステント単位でI/O要求が出されるため、エ クステントの単位が小さい場合、I/Oのブロック・サイズもまた小さくなってしまう。 • たとえば、最小のエクステントである4KBページｘ２をエクステント・サイズとした場合、その表スペースに 対するＢａｃｋｕｐのブロック・サイズは8KBとなり、バックアップのスループットが低下しやすい。 • エクステント・サイズの決定においては、セル密度および上記スループットのトレードオフとなる。 – I/O効率の観点からは、エクステント・サイズはRAIDストライプ・サイズと同等か整数倍である ことが望ましい。 • （参考）IBMストレージのストライプ・サイズは、DS4700で8KB∼512KB、DS6000/8000では256KB PSU_temp_0522 13 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表の設計（設計のポイント） あまり多くの列を次元キーに選択するのは避ける – 次元キー値のバリエーションが増えるため、セル密度が低下する 時系列の大規模履歴表などには、年月（または年月日）を次元としたMDCを検討 してみる 比較的ユニーク性の低い列を使用した条件しか指定できない検索に検討してみる – 従来のレコード・インデックスでは、ユニーク性が低く、不向きとされていた列 – ユニーク性があまり低くはない列では逆効果になるケースもあるので、生成列との併用も 検討する。 （例）次元キーに生成列を指定したMDC表の作成例 CREATE TABLE TMDC (年月日 DATE,地域 CHAR(10),製品 VARCHAR(10), 個数 SMALLINT 年月 generated always as (INTEGER(年月日)/100), ... ) ORGANIZE BY (年月, 地域, 製品) PSU_temp_0522 14 年月日（DATE型）を照会する処理が実行される際に、DB2は生成列 （年月）に定義されたブロック索引を使用するように動作してくれる © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表に対して高速なデータ投入を実現するためのポイント – 高速なデータ投入のためには、投入データが次元キーでソートされている ことが重要 • 「（参考）MDC表へのINSERT処理の流れ」のように、MDC表へのINSERT時に は該当するブロックの取り出しが必要となる。 • 次元キーに同じ値を持つレコードを同一ブロックに格納するため、同じブロックへ のINSERTを一括して行うと、余分なDISK I/Oが最小化される。 • また、INSERT/IMPORTで投入する場合、並列処理によってトータルのスルー プットを向上させる手法も有効 – ブロックや索引ページの取り出しを待つ時間が処理時間の大半を占めるため、1並列の みではCPUやDISKの性能を有効活用できない • 性能改善のポイント 投入データを次元列でソートする 投入データを分割し、並列で投入することを検討する RID索引へのキー追加もスループット低下の原因になるため、できる限りブロック 索引への移行を検討する PSU_temp_0522 15 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表に対して高速なデータ投入を実現するためのポイント MDC表に対するINSERT処理で多重度を増加させた場合の傾向 – 多重度1ではDISK transferが500程度しか出ておらず、DISK性能が使い切られていない。 – 1JOB辺りのスループットは若干低下しつつも、多重度10までINSERT性能が向上してい る。 多重度を増やした際の傾向 2,000 3,000 INSERT/秒 INSERT/秒 / 1JOB Disk transfer/s 1,600 2,500 レコード/秒 1,400 2,000 1,200 1,000 1,500 800 1,000 600 400 Disk transfer/sec 1,800 500 200 0 0 1 2 4 6 8 10 JOB多重度 PSU_temp_0522 16 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表に対して高速なデータ投入を実現するためのポイント （参考）MDC表へのINSERT処理の流れ INSERT INTO SJ10 VALUES (‘XXX’,’XXX’,’XXXX) DB2バッファー・プール ブロック索引 MDC表 ① ③ ② 複合ブロック索引から挿入 対象のブロックを特定 空きページをバッファープール に展開して新規レコードを挿入 各ブロックから、空きスペースの 管理レコードを取得して、空き ページを特定。 RID索引 (計器ID＋検針日等) MDC表へのINSERTの流れ ④ RID索引がある場合、索引 にエントリーを追加 PSU_temp_0522 既にデータで一杯のページ 一部データが格納されているページ まだデータが格納されていないページ 17 ① 複合ブロック索引から挿入対象のブ ロックを特定 ② それぞれのブロックから、空きスペー スの管理レコードを取得して、空き ページを特定 ③ 空きページをバッファープールに展開 してレコードを挿入 ④ RID索引がある場合、索引にエントリー を追加 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表の設計（設計のポイント） 次元キー以外の検索条件列には、レコード索引を作成する – 範囲検索のような、照会処理による大量のデータ読み込みには、ブロック索引を活用する – 定義される レコード索引の数が増えすぎると、INSERT処理/DELETE処理時に行われる 索引メンテナンスの負荷が上がるので注意する • レコード索引を定義する際には、第一列に指定しない – カーディナリティーの低い列が第一列だとアクセス・プランが不安定になりやすい 顧客ID 名前 18 生年月日 最終購入日 001 伊知郎 東京都 1952/01/01 2008/04/29 002 史子 東京都 1974/07/14 2008/09/24 003 士郎 東京都 1980/04/03 2008/09/01 004 悟朗 千葉 1964/06/27 2007/12/20 005 陸 名古屋 1984/12/24 2008/08/31 006 奈々子 大阪 1967/11/24 2008/09/12 007 鉢太 大阪 1977/05/04 2008/09/25 ユニーク・キーなど、カーディナリティーの 高い列には、レコード索引を作成する PSU_temp_0522 出身地 都道府県のように、検索条件の候補で、ある程度の重複 値が見込める列は、ディメンション・キーの候補とする （=ブロック索引が作成される） © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 MDC表の設計（設計のポイント） MDC表のロールアウトを活用したいのであれば、削除処理のWHERE条件で指 定する列は、次元キーのみとする – 指定列がディメンション・キーのみの場合も、特定の条件下では、ロールアウトは使用され ないので注意する • 表にDATA CAPTURE CHANGES属性が定義されている • SQLステートメントで、FETCH FIRST n ROWSが指定されている 等 レコード索引のメンテナンス・クリーンアップは、基本的には、「MDC 据え置き (Deferred)索引クリーンアップ・ロールアウト」を使用すればよい。 – 以下の場合には、「MDC 即時(Immediate)索引クリーンアップ・ロールアウト」を検討する。 – DELETE処理が完了した時点で、索引データの削除完了を保証したい場合 – 同じく、索引表スペースに Disk I/Oが発生させたくない場合 PSU_temp_0522 19 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 考慮点 MDC表へのLOADを実行する場合には、以下に気をつける – オプションに関する制約 • SAVECOUNT オプションはサポートされない • ANYORDERオプションが有効となって実行される – ANYORDERオプションを入力ファイル順に、表へデータを投入するためのオプション。 – このオプションを指定しない場合には、暗黙的に有効とされる – 未ソートデータではよりパフォーマンスが劣化しやすい • LOADユーティリティは、満杯にならないページをバッファーに保持するため、未ソート のデータを投入した場合、満杯にならないままバッファー上に保持され続けるページが 増加しやすいため。 • 特に、大半のデータで満杯になるページが発生しないデータで顕著に影響する。 （性能改善のポイント） PSU_temp_0522 20 投入データを次元キーでソートする LOADのData-Bufferを十分に確保する LOADで性能が出ない場合、IMPORT/INSERTによるデータ投入も検討する （データの特性によってはLOADよりパフォーマンスが改善される場合もある） © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 参考：MDC表による挿入処理の高速化(1/2) MDC表では、INSERT処理時に取得するロックの単位をブロックレベルに することが可能 – トランザクションが複数ある場合、同じセルに大量INSERT処理する際の同時稼動性の向 上 – ロック取得数の最小化 – ロックを取得・解放する回数が少ないため、CPU負荷の低減 ※INSERT 操作にはブロックレベルのロックしか実行されない ブロック INSERT処理 の性能向上 X セル レコード ALTER TABLE ... LOCKSIZE BLOCKINSERT ※デフォルトは、ROW PSU_temp_0522 21 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。 ビジネス・ユニットの名前 参考： MDC表による挿入処理の高速化(2/2) ROW（デフォルト） MDC表のBLOCKINSERT – トランザクションは異なっていても同じブロック へデータが挿入される – トランザクションごとに異なるブロックへデータ が挿入される – 挿入処理はシリアライズされる – 挿入処理は同時並行で処理される Appl1 INSERT トランザクションは、異なる行に ロックを取得するが、挿入先とな るブロックは同じとなる。 Appl2 INSERT トランザクションごとに、異なるブ ロックにブロック・ロックを取得する。 Appl1 INSERT Appl2 INSERT PSU_temp_0522 22 © Copyright IBM Japan Systems Engineering Co., Ltd. 2008 この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。