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章．区分化によるアクセス効率の向上
１．パーティションDB
– パーティションDB（DPF）の概要
• シェアード・ナッシング・アーキテクチャー
• データの分散
– パーティションDBの設計
•
•
•
•
分散キーの選択
均一なデータ分散
結合の種類
Join時のデータ移動の最小化
• 表の配置
• 大量データの更新
• 大量データの抽出
– 考慮点
PSU_temp_0522
3
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDB（DPF）の概要
ハッシングによるデータ分散
– シェアード・ナッシング・アーキテクチャー
大規模DBの実現
Interconnect
– SQL、ユーティリティの
高速並列処理
– 巨大な表、表スペース、
バッファープール
ハッシングによるデータ分散
– 複数サーバーによる
インスタンス構成
Database Partitioning
Feature（V8/V9）
表 T1
表 T1
表 T1
DB
パーティショﾝ1
DB
パーティショﾝ2
表 T1
InfoSphere Warehouse （V9.5）
– InfoSphere Balanced Warehouse
に採用されている
（初期導入／DB構成済み）
PSU_temp_0522
4
DB
パーティショﾝ0
DB
パーティショﾝ3
CREATE TABLE T1 ( COL1 INT, COL2 CHAR(3) )
DISTRIBUTE BY (COL1)
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
シェアード・ナッシング・アーキテクチャー(1/2)
各DBパーティションは、独立した処理エンジン、資源を保持
– 各DBパーティションが別々に処理エンジンを持ち、データやログ、ロック、キャッシュな
ど全てを別々に管理
– 各DBパーティションがお互いに影響を及ぼさないため、高い性能、拡張性を実現可能
高速通信マネージャー
DBパーティション
データ
ログ
データ
ログ
データ
ログ
データ
ログ
データベース
PSU_temp_0522
5
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
シェアード・ナッシング・アーキテクチャー(2/2)
アプリケーションにはひとつのDBイメージ
– データの配置を意識せず、どのパーティションからでも処理可能
– アプリケーションからリクエストされたクエリーは自動的に各DBパーティションに送信され、
並列に処理
SELECT
COUNT(*)
FROM T1
各DBパーティション
に処理要求を配信
し、結果をマージ
コーディネーター・
エージェント
PSU_temp_0522
6
サブ・エージェント
データ
ログ
データ
ログ
データ
ログ
データ
ログ
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
データの分散
Customer
cust_id
10
20
30
40
50
CREATE TABLE CUSTOMER(
cust_id INT,
name VARCHAR(80),
gender CHAR(5))
DISTRIBUTE BY(cust_id)
IN TBSP01;
cust_idを分散
キーとして選択
指定した表スペース
の分散配置通りに表
も分散される
10
name
中村
東
山村
古川
堀部
gender
F
M
M
M
F
分散キーの値から得られ
るハッシュ値と分散マップ
によって対象のパーティ
ションが決定する
Hash(cust_id)
分散マップ
中村
30
0
1
2
3
4
5
6
7
…
4095
0
1
2
0
1
2
0
1
2
0
CUSTOMER
F
山村
M
20
東
7
DBパーティション0
DBパーティション0
M
M
50
PSU_temp_0522
古川
40
DBパーティション1
DBパーティション1
堀部
F
DBパーティション2
DBパーティション2
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDBの設計（分散キーの選択）
分散キーの選択
– 分散キーを適切に選択することで、パーティション間に均一に負荷が分散され、最適なパ
フォーマンスを得ることができる
★分散キーの候補となる列
カーディナリティ（値のばらつき）の高い列
各パーティションへ格納される行の均一化
○： 取引番号、顧客番号など
×： 有効フラグ、性別
各区分への均等なデータアクセスの発生
特定の値にアクセスが集中しないような列
○： 取引番号、顧客番号など
×： 取引日付など
結合処理を最も効率よく行うことができる
結合列として使われる列
★分散キーの選択時の考慮点
多くの列を指定しない
ハッシングのコスト軽減のため
プライマリー・キー、ユニーク索引には、必ず分散キー
を含める
分散キー選択の際の制約
LONG列は使用不可
PSU_temp_0522
8
分散キーの定義変更は不可
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDBの設計（均一なデータ分散）
設計時のポイント
– 均一なデータ分散
•
•
選択した分散キーのカーディナリティが低かった場合など、パーティション間でデータにばらつきが発生すると、パ
フォーマンス、CPU、メモリーリソースに悪影響を及ぼす
分散キー候補が他にない場合は、REDISTRIBUTE DATABSE PARTITION GROUPコマンドのUNIFORMオプ
ションにより、データの均一化が可能
Interconnect
対象データ量が多いため、処理
時間、CPU、メモリーリソースが
他と比較して大きくなる
不均一なデータ分散
表 T1
PSU_temp_0522
9
DB
パーティショﾝ0
表 T1
表 T1
表 T1
DB
パーティショﾝ1
DB
パーティショﾝ2
DB
パーティショﾝ3
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDBの設計（結合の種類）
外部表、内部表のデータの移動有無による結合の種類
– ユーザー側で明示的に結合の種類を選択することはできない
– オプティマイザにより、分散キー、Joinキー、表の大きさにより最適な結合が選択される
– DPF特有の４つの結合
•
•
•
•
詳細は「参考」を参照
コロケーテッド結合
外部表のブロードキャスト結合
外部表の指示結合
内部表、外部表の指示結合
– 通常のJoinはデータ移動後に実施される
• Nested Loop Join、Merge Join、Hash Joinなど
外部表
内部表
備考
コロケーテッド結合
データ移動なし
データ移動なし
最も効率的な結合
「分散キー ＝ Joinキー」
外部表の
ブロードキャスト結合
全パーティション
にコピー
データ移動なし
外部表が小さなマスター表である場合などに選
択されやすい
外部表の指示結合
Joinキーで再度
ハッシュ
データ移動なし
外部表は
「分散キー ≠ Joinキー」
内部表、外部表の指示結合
Joinキーで再度
ハッシュ
Joinキーで再度
ハッシュ
パーティション間をデータが飛び交う結合
内部表、外部表ともに
「分散キー ≠ Joinキー」
PSU_temp_0522
10
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDBの設計（Join時のデータ移動の最小化）
設計時のポイント
– Join時のデータ移動の最小化
• Join時にパーティション内でJoin処理が完結するように設計する
– 例） CUST表、SALES表のJoin
• データ移動が発生しないケース
– 分散キー、Joinキーが共にCUST_IDとなるJoin
• データ移動するケース
– Joinキーが分散キーと異なるJoin
【データ移動が発生しないケース】
【データ移動するケース】
CUST : dk (CUST_ID)
1
3
11
パーティション1
1
2
SALES : dk (CUST_ID)
1
3
2
1
2
PSU_temp_0522
CUST : dk (CUST_ID)
パーティション2
再分配され
ずにJoin処
理が可能
3
2
SALES : dk (other_ID)
1
3
1
2
2
パーティション1
CUST_IDに
より再分配
される
パーティション2
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDBの設計（表の配置）
設計時のポイント
– 表の配置
•
他の表で使用されている分散キーを含まないような小さなマスター表は分散配置させない
– 例） ﾏｽﾀｰ表、ﾄﾗﾝｻﾞｸｼｮﾝ表のJoin
① ﾏｽﾀｰ表をﾊｯｼﾝｸﾞすることなく、ﾄﾗﾝｻﾞｸｼｮﾝ表の存在するﾊﾟｰﾃｨｼｮﾝへBroadcast
（表をコピーするイメージ）
② 各ﾊﾟｰﾃｨｼｮﾝで結合を実施
さらなる改善策（MQT利用）
①ﾏｽﾀｰ表を
各ﾊﾟｰﾃｨｼｮﾝ
にBroadcast
（表のｺﾋﾟｰ）
【ﾏｽﾀｰ表のBroadcast】
ﾏｽﾀｰ表
1 234
ﾏｽﾀｰ表
ﾏｽﾀｰ表
ﾏｽﾀｰ表
JOIN
JOIN
JOIN
JOIN
ﾄﾗﾝｻﾞｸｼｮﾝ表
1
1
1
PSU_temp_0522
ﾊﾟｰﾃｨｼｮﾝ1
12
3
3
ﾊﾟｰﾃｨｼｮﾝ2
4
ﾊﾟｰﾃｨｼｮﾝ3
2
MQTを利用することで、Select文発行時
MQTを利用することで、Select文発行時
にﾏｽﾀｰ表をBroadcastすることなく、既に
にﾏｽﾀｰ表をBroadcastすることなく、既に
レプリカされているMQTを利用させる。
レプリカされているMQTを利用させる。
②各ﾊﾟｰﾃｨｼｮ
ﾝで結合
2
ﾊﾟｰﾃｨｼｮﾝ4
CREATE TABLE R_ﾏｽﾀｰ表 AS (
SELECT ・・・ FROM ﾏｽﾀｰ表
)
DATA INITIALLY DEFFERRED
REFRESH IMMEDIATE
IN TBSP01 REPLICATED;
複製する表は小さく、更新頻度の低いも
複製する表は小さく、更新頻度の低いも
のを推奨
のを推奨
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDBの設計（大量データの更新）
設計時のポイント
– 大量データの更新処理（その１）
• 複数のパーティションに同時に仕事をさせること
– １行INSERTの繰り返しなど、複数パーティションを同時に仕事をさせることが難しい場合には、複数JOBの並列
実行も検討
– または、ひとつのSQLが各パーティションで並列処理されるように工夫する
• 例: INSERT_UPDATEモードで実行するIMPORT
詳細は後述
– IMPORT実行をしているパーティションから各パーティションへシーケンシャル処理される
import from file of del insert_update into tbl;
詳細は後述
• 代替策: 一時表へLOADしてからMERGE
load from file of del replace into temp_t;
merge into tbl a using temp_t b on a.pkey=b.pkey
when matched then update set col01=b.col01
when not matched then
insert (pkey,col01) values (b.pkey,b.col01);
PSU_temp_0522
13
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDBの設計
INSERT_UPDATEモードで実行するIMPORT
INSERTやUPDATEは入力データ1行毎に順番に処理される
コーディネーターと各ターゲットとの間で多くの回数送受信が
発生する
Import
Import from
from file
file
of
of del
del
insert_update
insert_update into
into tbl;
tbl;
一時点では、一つのパーティションがINSERT/UPDATEを
行っているだけであり、スケーラビリティの得られない形態
0
Update
-803
Insert
part0
part0
part1
part1
part2
part2
HASH
part3
part3
WRITE
Insert
PSU_temp_0522
14
Update
Sqlcode -803
Sqlcode 0
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDBの設計
一時表へLOADしてからMERGEを実行する方法
すべての処理が全パーティションで並列実行
アプリケーションとDBの間、DBパーティション間の送受信フ
ローを最小にすることができる
1.
1. Load
Load from
from file
file of
of del
del
replace
replace into
into temp_t;
temp_t;
LOAD
temp_t
part0
part0
15
LOAD
temp_t
MERGE
MERGE
PSU_temp_0522
2.
2. Merge
Merge into
into tbl
tbl
using
using table_tmp
table_tmp
tbl
part1
part1
LOAD
temp_t
part2
part2
temp_t
MERGE
MERGE
tbl
LOAD
tbl
part3
part3
tbl
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDBの設計（大量データの更新）
設計時のポイント
– 大量データの更新処理（その２）
• 区分化された表間でデータを並列に高速コピーする
– カーソルLoadの「DATABASE」オプションと、「FETCH_PARALLELISM」オプションを利用
– 区分化マップが同じ場合には、複数パーティションより並列にデータ抽出し、データをハッシュすることなく複数
パーティションで並列にLoadする（ただし、WHERE条件を指定すると、データはハッシュされてしまう）
• 例: 本番DBの明細表から検証DBの明細履歴表にデータを高速にコピーする
– 「DATABASE」オプションに別のDB名を指定することでDB間の高速データコピーが可能
>
同じDB名を指定すればDB内での表間の高速コピーが可能
connect to 検証DB;
declare c1 cursor database 本番DB user dpf95 using dpf95
for select * from 明細表 ;
load from c1 of cursor insert into 明細履歴表 fetch_parallelism yes;
PSU_temp_0522
16
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
パーティションDBの設計（大量データの抽出）
設計時のポイント
– 大量データの抽出処理
• 複数のパーティションに同時に仕事をさせること
– Exportユーティリティでは、最後にコーディネータパーティションでファイルに出力する処理がネックになりやすい
– 大量のデータがパーティション間を流れる
• 代替策: db2_allを使用して複数パーティションでローカルにファイルを出力する（AIXの例）
db2_all ¥"¥￨¥￨" ¥
db2 connect to sample; ¥
db2 'export to /data/emp.00## of del ¥
select * from employee ¥
where dbpartitionnum (empno) = current dbpartitionnum' ¥
"
– db2_allの「”」は、「##」の部分をデータベース・パーティション番号に置換させる接頭部シーケンス
– db2_allの「||」は、バックグラウンドでコマンドを並列に実行させる接頭部シーケンス
（＊）db2_allの接頭部シーケンスには、kshに文字列として認識させるために「¥」をつけている
PSU_temp_0522
17
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
考慮点
カタログ・パーティションの重要性
– システム・カタログ表が含まれるパーティションが「カタログ・パーティション」
– CREATE DATABASEしたパーティション
– 全ての接続はカタログ・パーティションにも接続される
– カタログ・パーティションがDownすると全接続が使用不可になる
インスタンス・オーナーのHOME
– 各パーティションからNFSなどを利用して共有する必要がある
• NFSサーバーがDownすると処理の継続はできなくなる
• カタログ・パーティションとNFSサーバーは同一とすることを推奨
リモート・コマンドを実行できるようにする
– すべてのデータベース・パーティションに対し、リモート・コマンドを実行する権限が必要
– インスタンス・オーナーのHOMEに.rhostsを用意する（sshの利用も可能）
PSU_temp_0522
18
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
考慮点
コーディネータ・パーティション
– SQL発行のために接続したパーティションが「コーディネータ・パーティション」
– どのパーティションもコーディネータになり得る
– コーディネータ・パーティションに負荷がかかる場合（多くの同時トランザクションや検索の
アンサーセットが多いときなど）は、複数のコーディネータ・パーティションを用意
分散キーの設計
詳細は前述
– データを均一に分散させるために分散キーの選択が重要
PSU_temp_0522
19
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
参考：パーティションDBの設計（結合の種類）
コロケーテッド結合
select *
from cust c,sales s
where c.cust_id=s.cust_id
1
1
2
2
3
1
1
2
2
3
パーティション0
CUST表とSALES表の分散キーは共
にCUST_ID
２つの表の結合列もCUST_ID
結合は各パーティション内の行同士
のみで実施される
q1
1
1
3
1
1
3
q1の読み取り
結果のリターン
q1
2
2
2
2
CUST : dk (CUST_ID)
1
3
2
SALES : dk (CUST_ID)
1
3
2
1
2
パーティション1
PSU_temp_0522
20
パーティション2
CUST表のスキャン
SALES表のスキャン
結合処理
q1への挿入
CUST表のスキャン
SALES表のスキャン
結合処理
q1への挿入
パーティション1
パーティション2
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
参考：パーティションDBの設計（結合の種類）
外部表の
ブロードキャスト結合
２つの表の結合列は、共に
CUST_ID以外の列
結合は各パーティション内だけでは
実現できないため、各パーティション
へCUST表の内容をBroadcast
q1
CUST : dk (CUST_ID)
2
3
SALES : dk (CUST_ID)
1
3
1
2
2
パーティション1
PSU_temp_0522
21
パーティション2
1
1
2
2
3
③結合結果
を受け取る
パーティション0
CUST表とSALES表の分散キーは
共にCUST_ID
1
1
1
2
2
3
select *
from cust c,sales s
where c.other_id=s.other_id
1
2
q2
1
2
3
1
2
3
q1の読み取り
結果のリターン
②SALES表
のﾊﾟｰﾃｨｼｮﾝ
で結合
SALES表のスキャン
q2の読み取り
1 2
結合処理
3
q1へ挿入
2
CUST表のスキャン
q2へ挿入
（Broadcast）
パーティション1
1
q1
1
2
1
2
SALES表のスキャン
q2の読み取り
1 2
結合処理
3
q1へ挿入
1 2
3
3
q2 q2
3
CUST表のスキャン
q2へ挿入
（Broadcast）
q2
パーティション2
①CUST表
CUST表を
全ﾊﾟｰﾃｨｼｮﾝ
を全ﾊﾟｰﾃｨ
ｼｮﾝに送る
に送る
© Copyright
IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
参考：パーティションDBの設計（結合の種類）
1
1
2
2
3
select *
from cust c,sales s
where c.cust_id=s.cust_id
外部表の指示結合
1
1
2
2
3
③結合結果
を受け取る
パーティション0
CUST表の分散キーはCUST_ID
SALES表の分散キーはother_ID
２つの表の結合列は、共にCUST_ID
q1
結合処理のため、SALES表の各行を
CUST_IDの値でハッシングし、適切な
パーティションへ送信
CUST : dk (CUST_ID)
1
3
2
SALES : dk (other_ID)
1
3
1
2
2
パーティション1
PSU_temp_0522
22
パーティション2
1
3
q2
1
1
3
1
1
3
q1の読み取り
結果のリターン
②CUST表
のﾊﾟｰﾃｨｼｮﾝ
で結合
CUST表のスキャン
q2の読み取り
1
結合処理
3 1
q1へ挿入
q1
2
2
2
2
CUST表のスキャン
q2の読み取り
2
2
結合処理
q1へ挿入
2
SALES表のスキャン
CUST_IDでHASH
q2へ挿入
（Directed）
パーティション1
2
1
q2 q2
SALES表のスキャン
CUST_IDでHASH
q2へ挿入
（Directed）
q2
①SALES表を パーティション2
ﾊｯｼﾝｸﾞして各
ﾊﾟｰﾃｨｼｮﾝに送る
© Copyright IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
ビジネス・ユニットの名前
参考：パーティションDBの設計（結合の種類）
内部表と外部表の
指示結合
1
1
2
2
3
select *
from cust c,sales s
where c.cust_id=s.cust_id
1
1
2
2
3
③結合結果
を受け取る
パーティション0
CUST表とSALES表の分散キーは共に
other_ID
２つの表の結合列は、共にCUST_ID
結合処理のため、２つの表の各行を
CUST_IDの値でハッシングし、適切な
パーティションへ送信
q1
CUST : dk (other_ID)
1
2
SALES : dk (other_ID)
1
3
1
2
2
パーティション1
PSU_temp_0522
23
1
3
3
パーティション2
q3
1
q2
1
1
3
1
1
3
q2の読み取り
q3の読み取り
結合処理
q1へ挿入
q1の読み取り
結果のリターン
②ｷｭｰのデータ
を読み取って
結合
q1
2
2
2
2
q2の読み取り
q3の読み取り
結合処理
q1へ挿入
SALES表のスキャン
CUST_IDでHASH
q3へ挿入
q3
2
q3
1
SALES表のスキャン
CUST_IDでHASH
q3へ挿入
CUST表のスキャン
CUST_IDでHASH
q2へ挿入
2
3
q2
q2
CUST表のスキャン
CUST_IDでHASH
q2へ挿入
2
q3
q2
①2表ともﾊｯｼﾝｸﾞ
パーティション2
して各ﾊﾟｰﾃｨｼｮﾝ
© Copyrightに送る
IBM Japan Systems Engineering Co., Ltd. 2008
この文書のデータの利用または公開には、最終ページに記載されている制限事項が適用されます。
パーティション1