Comments
Description
Transcript
第５章 データベースの物理設計
第５章 データベースの物理設計
本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。
この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合する
使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまたは同
様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。
© Copyright IBM Japan Co., Ltd. 2011
© 2011 IBM Corporation
目次
物理設計とは
物理設計のステップ
– インスタンスの構成とデータベース分割
– 表の分類と表スペース構成
– データ容量の見積もり
– 表スペースの容量見積もり
– ディスク上へのオブジェクトの配置
（参考）物理設計に関連する表編成
2
© 2011 IBM Corporation
物理設計とは
論理設計で決められた表をDB2の表として定義する
– DB2の製品仕様に依存する作業
ハードウェアをどのように使うか
– 物理ディスクへのオブジェクトの配置
– 物理メモリの割り振り
• オペレーティング・システム毎の知識が必要
物理設計を開始する前提:
物理設計を開始する前提:
・サーバーおよびディスクを含むハードウェア構成が、ほぼ決まっていること
・サーバーおよびディスクを含むハードウェア構成が、ほぼ決まっていること
・表の論理設計が終了していること
・表の論理設計が終了していること
3
© 2011 IBM Corporation
物理設計の最終目標
格納すべきデータを格納すること
予測される使われ方の範囲で、安定して動作すること
– 各種要件(アプリケーション、性能、運用等)を最適に実装する
– チェックすべき項目は事前に確認し、トラブルを未然に防ぐ
本来のハードウェア、DB2の性能を発揮すること
充分にテストを実施して、データベース設計が最適に実装されているか検証すること
4
© 2011 IBM Corporation
DB2が管理するオブジェクト
メモリ領域
バッファープール
バッファープール
論理オブジェクト
インスタンス
データベース
データベース
表スペース
表スペース
表スペース
表
表
表
索引
索引
索引
索引
ディスク領域
コンテナ0
5
コンテナ1
コンテナ2
コンテナ3
コンテナ4
コンテナ5
コンテナ6
コンテナ7
© 2011 IBM Corporation
解説：
インスタンス：
データを管理するデータベース・マネージャー(DB2のエンジン)。
データベース：
表、表スペース、索引、その他のオブジェクトの集合。
表スペース：
表のデータを記憶するための論理的な媒体。
コンテナ：
実際にデータを格納する物理的な媒体。
コンテナの実体は、ファイル・システムのディレクトリ、ファイル、または
論理ボリュームなどのローデバイス。
バッファー・プール：
データ・ページ(表の行項目や索引項目が含まれる)を一時的に読み
込んだり、変更したりするための記憶域。
6
表：
論理的に列と行に配列されたデータ。
索引：
効率よく表の行にアクセスするための一式のキー。
© 2011 IBM Corporation
物理設計の指針
パフォーマンスを考えるならば･･･
ディスクI/Oを減らすことができる処理
(例)
・ソート
・データの読み取り
・データの更新
・索引
バッファープールを活用し
データを再利用
7
ディスクI/Oが必ず発生する処理
(例)
・ロギング
・再編成
・バックアップ
・バッチ処理
ディスクI/Oの衝突をなくす
© 2011 IBM Corporation
一般的なシステムの種類
OLTP(オンライン・トランザクション処
DSS(意思決定支援システム)
・更新系
・照会系
・SQL
・SQL
単純
単一行の更新
・アクセス
大量の並行処理が存在
多数のセッションがアクティブ
ほぼメモリ上で動作させ、
応答時間、
トランザクション・レートを
向上させる
8
複雑
大量データの照会
一度に連続した行にアクセス
更新、挿入、削除がほとんどない
ディスクI/Oの速度が処理時間
影響を与えるケースが多い。
多くのディスクにまたがるように
配置し負荷を分散させる
© 2011 IBM Corporation
DB2に必要なディスク容量
DB2に必要なディスク容量は、データベースに保管する必要があるデータ容量に
よって決定
– ユーザー表、索引
– ユーザー表、索引以外
• アクティブ・ログ/アーカイブ・ログ
• インスタンス・ホーム・ディレクトリ
• データベース・ディレクトリー(カタログ表スペース)
• BACKUP, LOADなどのユーティリティー用の領域(一時表スペー
ス)
9
© 2011 IBM Corporation
DB2が使用可能なメモリ量
システムのメイン・メモリ
– 必要なメモリ
• OSが使うメモリ
• ファイル・キャッシュが使うメモリ
• DB2が使用するメモリー領域
– インスタンス単位で上限を設定可能（INSTANCE_MEMORY）
知っておくべきポイント
・使用するメモリ量は、インスタンス単位で上限を設定可能
・自動メモリーチューニング（STMM）を設定している場合は、DB2が負荷に応じ
てチューニングを実施する
10
© 2011 IBM Corporation
（参考）物理設計書に含まれるもの一覧
インスタンス構成
表スペース構成
–インスタンス名
–表スペース名
–サービス名
–表スペースID
–ポート番号
–種類
–ホームディレクトリー
–タイプ
データベース構成
–ページ・サイズ
–データベース名
–エクステント・サイズ
–ローカル・データベース・ディレクトリー
–プリフェッチ・サイズ
レジストリー変数
データベース・マネージャー構成パラメータ
データベース構成パラメータ
–コンテナ
バッファープール構成
–バッファープール名
–表スペースID
–ページ・サイズ
–SIZE(MB)
–拡張記憶域の有無
11
データ容量見積もりシート
© 2011 IBM Corporation
物理設計のステップ
物理設計のステップ
12
© 2011 IBM Corporation
物理設計のステップ
表・索引定義の作成
データ容量の見積もり
インスタンスの構成と
データベース分割
表の分類と
表スペースの構成
表スペース容量の見積もり
当コースでご説明する内容
ディスク上への
オブジェクトの配置
構成パラメータの設定
シェル／コマンドの作成
13
© 2011 IBM Corporation
物理設計のステップ
データ容量の見積もり
インスタンスの構成と
データベース分割
表の分類と
表スペースの構成
表スペース容量の見積もり
ディスク上への
オブジェクトの配置
14
© 2011 IBM Corporation
インスタンス・データベースとは
インスタンス
– DB2のオブジェクトの最も大きな単位
– DB2 の起動／停止の単位
• db2startコマンド／db2stopコマンド
– 同一サーバー上で複数のインスタンスを同時に稼動できる
– 1インスタンスにつき1つのデータベース・マネージャー構成ファイルを使用
して稼動する
データベース
– 一つのインスタンスに複数のデータベースを作成することが可能
– 接続(CONNECT)の対象となる
– バックアップ・リストアの最大単位
15
ポイン
データベースは、DB2としてデータの
論理的な整合性を保証できる単位
© 2011 IBM Corporation
インスタンスの分け方
インスタンス分割の目安
– 運用管理面
• インスタンスごとにデータベース・マネージャーの構成を最適化する
• 起動/停止のタイミングを分けたい（サービス時間、運用時間の違い）
– 可用性
• DB2自身が異常終了する様なトラブル発生時に、影響範囲を小さくしたい
ポイン
1インスタンス1データベースが一般的
データベースが複数あった場合どのインスタンスに含めるか考える
16
© 2011 IBM Corporation
データベースの分け方
データベース分割の目安
– アプリケーション構成（業務内容）
– 同時に処理する要件があるか
• パフォーマンスの観点から一つにすることも検討
• 分割されていると２フェーズ・コミット必要
• JOIN不可(連合DBを使用の場合は可能）
– 運用管理（バックアップ／リストア）の面から検討
• 処理時間
• 運用時間帯
17
© 2011 IBM Corporation
物理設計のステップ
データ容量の見積もり
インスタンスの構成と
データベース分割
表の分類と
表スペースの構成
表スペース容量の見積もり
ディスク上への
オブジェクトの配置
18
© 2011 IBM Corporation
表スペースとは
表スペース
– 表のデータを格納するための論理的な領域媒体
– 実際に表のデータが物理的に格納されるのは、
データベース
表スペースに紐付けられたコンテナ
表スペース
表スペース
– 表スペースはバックアップの最小単位
表
表
LOB
索引
表スペースはなぜ必要か？
– アプリケーションから見ると、表スペースは意識す
る必要ない
コンテナ0
コンテナ3
コンテナ1
コンテナ2
コンテナ4
– データの物理的位置を指定するのに使用するオ
ブジェクト
ポイン
すべての表・索引は表スペースに存在するので、DB2管理者は
表データを物理的にどこに配置するのか制御することが可能。
19
© 2011 IBM Corporation
表スペースとコンテナ
一つの表スペースを１つ以上のコンテナで構成可能
– 表スペース : コンテナ = 1 : N
– データはコンテナ間で平均的に割り振られる
表スペース ts_A
表スペース ts_B
表
表
コンテナ3
コンテナ0
コンテナ1
/dev/rhd0
/dev/rhd1
コンテナ2
/dev/rhd3
コンテナ4
/dev/rhd2
コマンド実行例
create regular tablespace ts_A pagesize 4K managed by database using
(device '/dev/rhd0' 1024, device '/dev/rhd1' 1024, device '/dev/rhd2' 1024, device '/dev/rhd3'
1024);
20
© 2011 IBM Corporation
表スペースとバッファープール
各表スペースは、それぞれ特定の 1 つのバッファー・プールに関連付けられる
– 表スペース : バッファープール = N : 1
バッファー・プール bp_A
バッファー・プール bp_B
表スペース ts_A
表スペース ts_B
表スペース ts_C
表
表
表
コマンド実行例
create bufferpool bp_A immediate size 6000 pagesize 4K;
create regular tablespace ts_A pagesize 4K managed by database using >
(device '/dev/rhd0' 1024) bufferpool bp_A;
21
© 2011 IBM Corporation
一般的な表スペース分割(1/2)
物理設計をするにあたり、業務要件、格納する表のタイプは以下の前提である場合・・・
–
業務要件: マスター表を参照し、業務用の表を更新する。
–
その履歴を別の表へ書き込む。
–
表のタイプ
• マスター表
• トランザクション表
• 履歴表
マスター表
列１
列２
列３
22
参照のみ
参照、更新の両方
データ量が日に日に増加していく表
トランザクション表
列１
列２
列３
履歴表
列１
列２
列３
© 2011 IBM Corporation
一般的な表スペース分割(2/2)
パフォーマンスを考慮し、バッファープールの割り当てを考える
バッファー・プール
専用バッファー・プール
DATA用表スペース
DATA用表スペース
トランザクション表
マスター表
表
表
専用バッファー・プール
列１
列２
列３
列１
列２
列３
バッファー・プール
索引用表スペース
索引用表スペース
LOB用表スペース
索引
索引
LOB
バッファー・プール
DATA用表スペース
表
23
履歴表
列１
列２
列３
© 2011 IBM Corporation
解説: 一般的な表スペース分割
テーブルをグルーピング、表スペースを分ける
– １．できる限りバッファプールに読み込むことが望ましいテーブル
• アプリケーションから共通のデータとして頻繁に参照されるマスタ系データ
– ２．バッファプールには読み込む必要が無いデータ
• アプリケーション活動の履歴的な、一度書いたらほとんど変更／参照され
ない履歴系データ
• データ量が日に日に増加していく
– ３．１と２の中間
• 頻繁に読み書きされるトランザクション系データ
– ４．小さなテーブルはある程度の単位で一つの表スペースにまとめる
– ５．バックアップ取得の頻度で別の表スペースにする
• 大量の更新があるテーブル⇒頻繁なバックアップ取得が必要
• 変更が非常に少ないテーブル⇒頻繁なバックアップ取得が不要
24
© 2011 IBM Corporation
表スペース作成のために決定すること
表スペース作成のために決定すること
1.
2.
3.
4.
5.
6.
7.
8.
保管データのタイプによる表スペースの種別
ユーザー表スペース
REGULAR
LARGE
SYSTEM TEMPORARY
一時表スペース
USER TEMPORARY
ページサイズ
4K
8K
16K
32K
エクステント・サイズ
ファイル管理のタイプによる表スペースの種別
SYSTEM
→
SMS
DATABASE
→
DMS
自動ストレージ機能・自動リサイズ機能の使用
プリフェッチ・サイズ
バッファープール
コンテナ数／コンテナ・サイズ
後で変更可
25
© 2011 IBM Corporation
保管データのタイプによる表スペースの種別の決定
Regular： 通常のデータ、索引用(SMSのデフォルト)
Large： 通常のデータ、索引用(DMSのデフォルト)
Temporary： 一時表スペース
システム一時表スペース
ソート、表の再編成、索引の作成、表の結合などで一時的にデータが入る表スペース
デフォルトのシステム一時表スペースは、TEMPSPACE1(SMS)
ページ・サイズに合せた一時表スペースを作成する
ユーザー一時表スペース
宣言済みグローバル一時表を使用する場合に作成する表スペース
省略時にはデータベース作成の時点で作成されない
ユーザー定義一時表は、ロックとログ記録を回避するので、一時表を活用するアプリ
ケーションは大幅なパフォーマンス改善が見込める
26
© 2011 IBM Corporation
ページ・サイズ
ページ・サイズ: 表または索引内の記憶域のブロック
ページ・サイズを決定するための要素
– 行長のMAXを超えていないか？
– その他のページ・サイズによる制限値を超えていないか？
– 1ページあたりの空きスペースは多くないか？(格納効率)
Page Size
Row Size Limit
Column Count
Limit
Table space size Limit
(Large)
4KB
4005
500
8TB
8KB
8101
1012
16TB
16KB
16293
1012
32TB
32KB
32677
1012
64TB
*DMS表スペースの場合
ポイント
・ページ・サイズ毎に必要なオブジェクトがある
バッファープール／一時表スペース
27
© 2011 IBM Corporation
エクステント
エクステント
–
表スペースにおけるコンテナ内の領域の割り振り単位（ページ）
表スペース作成時に指定
–
作成後は変更が出来ない
データはラウンド・ロビン方式でコンテナに書き込む
DMSのコンテナーで最小限必要なスペース (最初のオブジェクトを作成した時) = 5エクステント + 1
ページ
コンテナ
コンテナ 0
エクステント
コンテナ1
ページ
4K
0 2
1
エクステント
表スペース A
28
3
Extent = 32ページ
(デフォルト)
© 2011 IBM Corporation
SMS表スペース
SMS(System managed space)表スペース
–
オペレーティング・システムのファイル・システム・マネージャーが管理
–
表データと索引、Longデータは全て同じ表スペースを共有
–
管理が容易
–
コンテナはディレクトリー
•
–
ファイルは動的に拡張し、サイズの上限は以下によって決まる
•
•
–
–
29
表、索引は、ファイルとして保管される
コンテナの数
ファイル・システム/ドライブ/ファイルのOSの限界サイズ
コンテナはALTER TABLESPACEで追加不可
•
ファイル・システム/ドライブのサイズは増加可能
•
再定義は表スペース復元時に可能（表スペースのリダイレクション）
一時表スペースに推奨
© 2011 IBM Corporation
DMS表スペース
DMS(Database managed space)表スペース
–
データベース・マネージャーが記憶スペースを管理
–
作成時にスペースを割り当て
–
コンテナはファイル、デバイス
•
–
柔軟なデータ配置
•
•
–
30
表用、索引用、および長形式のデータ用の表スペースを別々に作成することが可能
ディスクのIOを複数の物理ディスクに分散させることが可能
コンテナの追加/削除/拡張/縮小が可能
•
–
デフォルトの設定では、ファイルシステムのキャッシュを使用せず、直接I/Oを使用
データは自動的に再バランス（オプションにより再バランスさせないことも可）
高パフォーマンス
© 2011 IBM Corporation
ファイル管理のタイプによる表スペースの種別
パフォーマンス
– DMSローデバイス≒DMSファイル＞＞SMS
• 表用、索引用、長形式のデータ用の表スペースを別々に作成できる
管理の容易さ
– SMS＞DMS
• SMSは、同じファイルシステムに複数表スペースを作成でき、表スペースご
とに空きスペースの監視を行う必要がない。
– DMSファイル＞DMSローデバイス
• DMSローデバイスは、使用されるローデバイスの数が増えると、管理対象
となる論理ボリューム（LV）が増える。
• DMSファイルでは、1つの大きなファイル・システムを作成し、その中に表ス
ペースを作成すれば、管理対象となる論理ボリューム（LV）が少なくなる。
31
© 2011 IBM Corporation
ファイル管理のタイプによる表スペースの種別
可用性
– DMSローデバイス＞DMSファイル, SMS
• DMSファイル、SMSでは、クラスター・ソフトによるテークオーバー時に、フ
ァイル・システムの整合性チェックが行われるため、テークオーバー時間が
長くなる可能性がある。
• DMSローデバイスでは、ファイル・システムを使用しないので、上記の整合
性チェックは行われない。従って、テークオーバー時間の短縮可能。
32
© 2011 IBM Corporation
自動ストレージ
自動ストレージ・データベース
– 表スペースを作成する際に、そのコンテナーおよび表スペースタイプ（SMS,DMS）が
完全に DB2によって決定されるデータベース
– データベース作成時にストレージ・パスを指定
自動ストレージ・表スペース
– 表スペース作成時、DB2がデータベース作成時に指定したストレージ・パスに自動的
にコンテナを作成する（コンテナの管理はすべてDB2が行う）
• 表スペース作成時にコンテナのリストを指定する必要がない。
– 各ストレージ・パスに作成されるコンテナはひとつのみ
Automatic Storage
Database “Y”
Table
Space “A”
Table
Space “B”
Table
Space “C”
Storage paths on file systems
33
© 2011 IBM Corporation
自動ストレージ
非自動ストレージ・表スペース
– 自動ストレージ機能を使用しない表スペースは個別に管理
– DMSファイルは、自動リサイズ機能を使用することで、
表スペース領域の自動拡張を有効にすることは可能
Non-Automatic Storage
Database “X”
Table
Space “A”
34
Table
Space “B”
Table
Space “C”
© 2011 IBM Corporation
自動ストレージ
メリット
– 表スペースの作成が簡素化される
• あらかじめ定義しておいたストレージ・パスを使用してDB2が自動的にコンテナ
を作成するので、表スペース作成時にコンテナのリストを指定する必要がない。
– コンテナ・パスとして使用する領域を決めておけば、その中でDB2が領域
を割り振ってくれるので、それぞれの表スペースに対して、コンテナ・パス、
サイズを指定する必要がない。
– ストレージ・パスを複数のデータベース、表スペースで共有することができ
る。
• 自動的に複数のストレージ・パスにまたがるようにコンテナが作成される。
– I/Oが分散するようなコンテナ構成に自動的にすることができる。
– ストレージ・パスの追加が可能
• ストレージ・パスが不足する前に、新規のストレージ・パスを追加することができ
る。
– 必要に応じて、新しいストレージ・パスが使用される。
35
© 2011 IBM Corporation
自動ストレージ・データベースの作成
CREATE DATABASEステートメントの新しいオプションAUTOMATIC STORAGEを使用する。
>>-CREATE--+-DATABASE-+--database-name-------------------------->
'-DB-------'
.-AUTOMATIC STORAGE--YES-.
|--+------------------------+----------------------------------->
'-AUTOMATIC STORAGE--NO--'
>--+---------------------------------------------+-------------->
|
.-,---------.
|
|
V
|
|
'-ON----+-path--+-+--+----------------------+-'
'-drive-'
'-DBPATH ON--+-path--+-'
'-drive-'
AUTOMATIC STORAGE { YES | NO }
–
自動ストレージを使用するかどうかを指定
ON
–
AUTOMATIC STORAGE NOの場合
•
データベース・パスを指定
–
AUTOMATIC STORAGE YESの場合
•
ストレージ・パスを指定（複数可）
•
存在するパスの絶対パスでなければならない
DBPATH ON
–
データベース・パスを指定
–
自動ストレージが使用可能で DBPATH ON オプションが指定されていない場合、データベースは ON オプションのリストの最初のパスに作成
される
–
Windows では、パスはドライブ名 (たとえば C:) でなければならない
•
注：Windows の場合、データベース・パスには (DB2_CREATE_DB_ON_PATH レジストリー変数を NO から YES にデフォルトを変
更しない限り)、ドライブ名のみが使用可能です。
–
36
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/t0055491.html
© 2011 IBM Corporation
自動ストレージ・表スペースの作成
自動ストレージ・データベースに対してのみ作成可能
CREATE TABLESPACEステートメントのオプションMANAGED BY AUTOMATIC STORAGEを使用
する。
>>-CREATE--+-----------------------+---------------------------->
+-LARGE-----------------+
+-REGULAR---------------+
| .-SYSTEM-.
|
'-+--------+--TEMPORARY-'
'-USER---'
>--TABLESPACE--tablespace-name---------------------------------->
（中略）
.- MANAGED BY-- AUTOMATIC STORAGE-- | size-attributes |------------------.
>--+------------------------------------------------------------------------+-->
'-MANAGED BY--+-SYSTEM--| system-containers |--------------------------+-'
'-DATABASE--| database-containers |-- | size-attributes|-'
size-attributesを指定すると、ファイルDMS表スペースの自動サイズ変更機能を使用するか
どうか、初期サイズ、増加サイズ、最大サイズを定義可能。
37
© 2011 IBM Corporation
（参考）自動ストレージ・データベースでの初期表スペース
38
SYSCATSPACE
–
自動ストレージ表スペース（ファイルRegular DMS）
–
コンテナは複数パスにストライプされる
–
EXTENTSIZE=4
–
AUTORESIZE=YES
–
INITIALSIZEはDB2が決定する（32MB）
–
INCREASESIZE=AUTOMATIC
–
MAXSIZE=NONE
TEMPSPACE1
–
自動ストレージ表スペース（SMS）
–
コンテナは複数パスにストライプされる
–
EXTENTSIZE=32
USERSPACE1
–
自動ストレージ表スペース（ファイルLarge DMS）
–
コンテナは複数パスにストライプされる
–
EXTENTSIZE=32
–
AUTORESIZE=YES
–
INITIALSIZEはDB2が決定する（32MB）
–
INCREASESIZE=AUTOMATIC
–
MAXSIZE=NONE
© 2011 IBM Corporation
自動ストレージのREGULAR,LARGE表スペース
非自動ストレージと自動ストレージの管理の違い
非自動ストレージ
自動ストレージ
表スペースの作成時にコンテナーを明示的に
提供する必要がある。
表スペースの作成時にコンテナーを提供すること
はできず、DB2 UDB によって自動的に割り当て
および割り振りが行われる。
デフォルトでAUTORESIZE NO
デフォルトでAUTORESIZE YES
INITIALSIZE 文節を使用して表スペースの
初期サイズを指定できない。
INITIALSIZE 文節を使用して表スペースの初期
サイズを指定できる。
ALTER TABLESPACE ステートメント (ADD
、DROP、BEGIN NEW STRIPE SET など)
を使用してコンテナー操作を実行できる。
DB2 UDB がスペース管理を制御するので、コン
テナー操作は実行できない（SQL20318N）。
リダイレクトされたリストア操作を使用して表ス
ペースに関連したコンテナーを再定義できる。
DB2 UDB がスペース管理を制御するので、リダ
イレクトされたリストア操作を使用して表スペース
に関連したコンテナーを再定義することはできな
い（SQL20319N）。
ストレージ・パスの容量は揃える
– それぞれのストレージ・パスの空き容量が異なる場合、同じサイズのコンテナを作成することがで
きない。
39
© 2011 IBM Corporation
自動ストレージの一時表スペース
一時表スペースはSMSとして作成される
非自動ストレージと自動ストレージの管理の違い
非自動ストレージ
自動ストレージ
表スペースの作成時にコンテナーを明示的に
提供する必要がある。
表スペースの作成時にコンテナーを提供すること
はできず、DB2 UDB によって自動的に割り当て
および割り振りが行われる。
リダイレクトされたリストア操作を使用して表
スペースに関連したコンテナーを再定義でき
る。
DB2 UDB がスペース管理を制御するので、リダ
イレクトされたリストア操作を使用して表スペース
に関連したコンテナーを再定義することはできな
い（SQL20319N）。
ストレージ・パスの容量は揃える
–
40
一時表スペースのコンテナは、データベースが開始するたびにストレージ・パスの空き容量によっ
て自動的に再定義されるため、それぞれのストレージ・パスの空き容量は揃えておく
© 2011 IBM Corporation
DMSファイル表スペースの自動サイズ変更
DMSファイル・コンテナのサイズを自動的に拡張
自動サイズ変更を使用しない場合
表スペース
自動サイズ変更を使用した場合
表スペース
SQL0289N発生
自動的に拡張される
41
© 2011 IBM Corporation
DMSファイル表スペースの自動サイズ変更
DMSファイル・コンテナのサイズを自動的に拡張
–
–
–
–
–
–
DMSなのでパフォーマンスがよく、自動的にサイズ拡張するので、SMS同様管理が容易になる
考慮点
– 自動サイズ変更が行われる場合、表に対する挿入処理は、自動サイズ変更が完了するまで待たされる。
–
42
コンテナがいっぱいになる（SQL0289N）前に、追加領域の必要に応じてコンテナを自動的に拡張する
ファイル・コンテナのみ使用可能
• ロー・デバイス・コンテナの自動サイズ変更は不可能
表スペースの最後のレンジにあるコンテナが拡張される
• 自動サイズ変更によってリバランスは発生しない
• それぞれのコンテナは同じ量だけ拡張される
自動ストレージ・表スペースのDMS、自動ストレージ・表スペースでないDMSのいずれに対しても使用可能
自動ストレージ・表スペースでない場合、ユーザーによるコンテナの拡張、削除が可能
• 従来どおり、ALTER TABLESPACEステートメントを使用する
区分化データベース環境でも使用可能
一時表スペースを使用しないREORGの際にも自動サイズ変更が行われる。
• REORG完了後もサイズは拡張されたままで、縮小されない。
© 2011 IBM Corporation
まとめ
ページ・サイズ
指針
レコード長を明確にして、適切なページ・サイズを決定する
1種類のページ・サイズで済むかどうか検討する
– それぞれのページ・サイズ毎にバッファープール、一時表スペースを作るとス
ペースをより多く必要とするため（運用も複雑になる）。
– ページ・サイズを検討するにあたって、ページサイズによる制限値を超えてい
ないこと、格納効率によって判断する。
使用するページ・サイズの種類はなるべく少なくする
※LOBデータの場合はページ・サイズは決めなくても良い
43
© 2011 IBM Corporation
まとめ 表スペース分割
指針
パフォーマンスを考慮し、表スペースの割り当てを検討
–
I/O効率化による分割: 表データ、索引を異なる表スペースに配置
–
業務データの更新頻度による分割
–
基本は、サイズの大きいひとつのバッファープールを使用
•
パフォーマンス・テストで、要件上の応答時間に収まらない場合には、必要に応じてバッファー
プールの分割を検討できるように表スペース分割を検討
I/Oの並列処理を考慮し、データの物理配置に合わせて表スペースを構成
バックアップ等の運用面で検討
–
業務データ種類（バックアップ取得頻度）による分割
ページ・サイズによる制限事項を考慮
44
© 2011 IBM Corporation
まとめ エクステント・サイズの決定
指針
ほとんどの場合デフォルト32から変更する必要はない
多数の小さい表からなる表スペースは、サイズを減らすことも検討
大量の結果行を返す照会を行う表や急激に拡張される表からなる表スペースは、
サイズを増やすことも検討
45
© 2011 IBM Corporation
まとめ ファイル管理のタイプによる表スペースの種別の決定
指針
パフォーマンスを重視する場合は、DMSローデバイス、またはDMSファイル
を使用する
管理の容易さを重視する場合は、自動ストレージ、SMSを検討する
46
© 2011 IBM Corporation
物理設計のステップ
データ容量の見積もり
インスタンスの構成と
データベース分割
表の分類と
表スペースの構成
表スペース容量の見積もり
ディスク上への
オブジェクトの配置
47
© 2011 IBM Corporation
データ容量の見積もりステップ
1. 各表のレコード件数を出す
2. 各表の1行あたりのサイズ(平均行サイズ)を出す
3. レコード件数 * 平均行サイズ = 1テーブルのサイズを出す
4. 索引容量の見積もりを出す
48
© 2011 IBM Corporation
1. レコード件数の見積もり
見積りの前提となる数値、算定根拠を明確にする
– 各テーブルのレコード件数を見積もる上で必要な数値の収集
• コード類、マスター類の件数
• １日あたりの処理件数
• データの保持期間
• データ増加率
．．．etc.
– 各テーブルとテーブルの関係を明確化
ポイント
お客様にAuthorizeされた前提の数値であることが重要
前提に変更があれば、都度再見積り可能なようにワークシートにまとめておく
49
© 2011 IBM Corporation
レコード件数の算出例
お客様との合意事項
– 2年後のデータ量を想定しディスク容量を見積もること
前提となる数値
– 一日あたりのデータ発生件数 10,000件
– データの保持期間 1週間
– データは、保持期間経過後、履歴表へ
– 各テーブルの現在の件数
• マスタ表
100,000件
• トランザクション表
0件
• 履歴表
0件
トランザクション表のレコード件数は？
– 1日のデータ発生件数が10,000件
– データ保持期間1週間 →10,000 * 7 = 70,000件
履歴表のレコード件数は？
– 1日あたりの処理件数 10,000件
– 1年365日
– 2年後のレコード件数 →10,000件 * 365 * 2 = 7,300,000件
50
© 2011 IBM Corporation
2. 平均行サイズの算出
平均行サイズ: 表の一行あたりの長さ
表の列定義から、一行あたりの長さ(バイト)を出す
可変長は、平均のデータ量を算出する
NULL値を許す場合１バイト、可変長の場合4バイトを各該当の列項
目あたり加算
51
© 2011 IBM Corporation
各データ・タイプ毎のサイズ
52
各データ・タイプ毎のサイズ
データ・タイプ
バイト
SMALLINT
2
INTEGER
4
BIGINT
8
DOUBLE
8
DECIMAL(n,m)
(n/2 + 1)
CHARACTER(n)
n
VARCHAR(n)
n+4
LONG VARCHAR
24
GRAPHIC
n*2
VARGRAPHIC
(n*2)+4
LONG VARGRAPHIC
24
DATE
4
TIME
3
TIMESTAMP(n)
(n+1)/2+7の整数部分
© 2011 IBM Corporation
平均行サイズの計算例
マスタ表
VARCHARなので、
平均データ容量にて見積もる
– テーブル名: STAFF
日本語項目
名
物理カラム名
属性
ID
ID
SMALLINT
NULL可 長さ(バイト
)
能
NO
2
•ID
2
•住所
30 + 1 （NULL許可）+ 4 （可
変長）
住所
ADDRESS
VARCHAR
YES
50
所属部門
DEPT
SMALLINT
YES
2
•所属部門コード
2 + 1 （NULL許可）
コード
職種
JOB
CHAR
YES
5
•職種
勤続年数
YEARS
SMALLINT
YES
2
•勤続年数
2 + 1 （NULL許可）
給料
SALARY
DECIMAL
YES
6
5 + 1 （NULL許可）
•給料
4 + 1 （NULL許可
53
© 2011 IBM Corporation
3.
テーブル容量の見積もり
テーブル容量概算：
（平均行サイズ＋10）*レコード件数*安全率
論理レコード長
– 安全率（余裕率)
• オーバーヘッド分
（フラグメンテーションやオーバー・フロー・レコードの有無）
54
© 2011 IBM Corporation
例：テーブル容量の見積もり
テーブル容量概算：
（平均行サイズ＋10）*レコード件数*安全率
＝（56バイト+10）*100,000*1.3
＝8,580,000≒8.2MB
1.3の余裕率は一例であり、必ずしも十分な値ではありません。
表定義やレコード件数がどの程度正確な値であるか等でも異なってきます。
テーブル容量: 8.2 (メガバイト)
55
© 2011 IBM Corporation
4. 索引容量の見積もり
索引容量：
(平均索引キー・サイズ + 11) * 行数 * 安全率
– 基本的な算出方法の考え方はテーブルと同等
– 安全率
• ノンリーフ・ページやフリー・スペースなどのオーバーヘッドのため、安全率は少な
くとも２倍として計算。
– 後に索引が追加されることも考慮し余裕を見ておく
56
© 2011 IBM Corporation
物理設計のステップ
データ容量の見積もり
インスタンスの構成と
データベース分割
表の分類と
表スペースの構成
表スペース容量の見積もり
ディスク上への
オブジェクトの配置
57
© 2011 IBM Corporation
ユーザー表スペースの見積もり
ユーザー表スペース
– 既に決まっているページ・サイズと平均行サイズから、１ページに格納できる行
数を算出
– 予想される行数を格納するために必要となるページ数を算出
• ROUND DOWN(ページ・サイズ/(平均行サイズ + 10)) = １ページあたりの
行数
– ページ・サイズからデータベース・マネージャー用のオーバーヘッド
68バイトを引いて計算
• (レコード件数/１ページあたりの行数) *安全率 = 必要ページ数
– 安全率：オーバーヘッド分、PCTFREEの分や、同じ表スペース内で
REORGする場合なども考慮
58
© 2011 IBM Corporation
例：ユーザー表スペースの見積もり
1ページあたりの行数
＝ROUND DOWN(ページ・サイズ/(平均行サイズ + 10))
＝（4028/（56バイト+10））
＝61（行）
必要ページ数
＝(レコード件数/１ページあたりの行数) *安全率 = 必要ページ数
＝（100,000/61）*1.1
＝1,639（ページ）
必要ページ数: 1,639 (ページ)
59
© 2011 IBM Corporation
一時表スペース・カタログ表スペースの見積もり
一時表スペース
–
一番大きく使用すると思われるケースで検討する
•
•
CREATE INDEX/LOAD/REORGなど最も多く使う処理を目安に見積もる
– 目安は、もっとも大きな表の2～3倍
索引作成時に必要な一時スペースの最大量は、次のようにして見積もることができる
– (average index key size + 9) * number of rows * 3.2
– ここで 3.2 は索引オーバーヘッドの因数、および索引の作成時のソートに必要なスペ
ースの因数です。
カタログ表スペース
–
データベースごとに作成され、表にはデータベース・オブジェクト(例えば、表、ビュー、索引およびパ
ッケージ)の定義についての情報が保管される
60
–
SQLプロシージャなどを使う場合、実行モジュールが含まれる
–
通常200～300MB程度あれば問題はない
–
runstatsをwith distributionオプション付きで実行するとサイズが大きくなる
© 2011 IBM Corporation
(参考)DB2の圧縮機能概要
Table
表の行圧縮 (Row Compression)
Table
複数のアルゴリズムを用いた索引自動圧縮
一時表の自動圧縮
Table
Temp
Order By
Table
Temp
Order By
LOBデータとXMLデータの圧縮
61
© 2011 IBM Corporation
(参考)DB2の圧縮機能
辞書を使った行レベルのデータ圧縮 （V9.1～）
– 辞書には、レコードにある特定のパターンが記録される
– ディスク使用量の削減
– より多くのデータが圧縮された状態でバッファープールに乗るため、バッファープール
ヒット率の向上が期待できる
– ディスクへの読み書き量が削減できるため、特にI/Oネックのシステムにはパフォーマンス
向上の効果がある
名前
部署
給与
都道府県
区・市
郵便番号
Fred
500
10000
東京都
港区
24355
John
500
20000
東京都
港区
24355
Fred
500
10000
東京都
港区
24355
John
500
20000
東京都
港区
24355
…
ディクショナリー
Fred
62
(01)
10000
(02)
John
(01)
20000
(02)
…
01
500
02
東京都, 港区,24355
…
…
© 2011 IBM Corporation
物理設計のステップ
データ容量の見積もり
インスタンスの構成と
データベース分割
表の分類と
表スペースの構成
表スペース容量の見積もり
ディスク上への
オブジェクトの配置
63
© 2011 IBM Corporation
表・索引以外のオブジェクトの配置
表・索引以外のオブジェクト
ログ
アクティブログと
アーカイブログ
カタログ表スペース
一時表スペース
ワーク
バックアップ領域
その他の領域
どの処理を最も優先的に速くしたいかを考えて配置する
ポイント
アクティブ・ログへの書き込み速度は、データベースの更新処理の
パフォーマンスにとって特に重要。
まずはアクティブ・ログの配置をどうするか考えること。
64
© 2011 IBM Corporation
ロギングのしくみ
ログ・ファイル
– データベースへのすべての更新内容を発生順に記録しておくファイル
ログ・データがログ・バッファーからディスクに書き出されるタイミング
– COMMIT時 または ログ・バッファーが一杯になったとき
– ログ・バッファーの中のデータ部分だけが書き出される
非同期
書き出し
データ
バッファープール
変更された
データ
agent
アクティブ・ログの最大サイズ
ログ
バッファー
– 1024GB
ロガー
ログ
コミットしたら
更新内容を
必ず書き出す
– (LOGPRIMARY + LOGSECOND) * LOGFILSIZ * 4KB
65
© 2011 IBM Corporation
一時表スペースの配置
一時表スペースとしてはSMSがお勧め（一般）
– スペースの有効利用
– 多くのクライアントアプリケーションからのソート要求を処理する場合、DMSよ
りパフォーマンスが良い
– 複数（3～4つ）のディレクトリを割り当てる
ページサイズ毎に1つ作成
– 一時表スペースを使用した再編成の場合、テーブルの存在する表スペースの
ページサイズと一時表スペースのページサイズは同じである必要がある。
指針
一時表のI/Oが多く、データ／索引用の表スペースのI/Oと衝突する場合は、
別ディスクに配置する
66
© 2011 IBM Corporation
カタログ表スペースの配置
CREATE DATABASE時に自動的に作成
– デフォルトのページ・サイズは、4KB
– 表スペースタイプはデフォルトを利用する
• 自動ストレージ・データベースではDMSタイプ
非自動ストレージ・データベースではSMSタイプ
の表スペースが作成される
– データベース作成時に、カタログ表スペースのページ・サイズ
を 4KB以外（8,16,32KB）に指定可能
67
© 2011 IBM Corporation
ワーク／バックアップ領域の配置
バックアップしたデータの保管場所
– ディスクへのバックアップの場合、バックアップ元のあるディスクとは別にする
– １つのディスクでは要求が満たせない場合、複数のディスクへのバックアップを
検討
– テープへのバックアップは、TSMがサポートしていれば、複数テープへの同時書
き込みによって高速化が可能
ロード元のデータの保管場所
– ロード元のデータを格納するファイルシステムのパフォーマンスを考慮する
– 一時表スペースとは別ディスクに配置する
• ロード時の入力ファイルと、ソート用の一時表スペースのI/Oが競合
するとロードのパフォーマンスに悪影響が考えられる
68
© 2011 IBM Corporation
参考：その他の領域
SYSTOOLSPACE、SYSTOOLSTMPSPACE
– データベースの自動保守（自動統計収集および再編成）を使用する場合に自動作成さ
れるが、自動保守機能を使用しない場合は、削除しても問題ない
DB2診断情報格納ディレクトリ
– インスタンス・ホーム・ディレクトリ（デフォルト）、または、データベース・マネージャ構成
パラメータDIAGPATHにて指定した場所に格納される
– 管理通知ログ、db2diag.log、ダンプ・ファイル、トラップ・ファイル、コア・ファイル (UNIX
のみ)を格納する
データベース・ディレクトリ
– バッファープール情報、表スペース情報、データベース構成情報、リカバリ履歴ファイ
ル、ログ制御ファイル
ARCHIVE.LOG、RETRIEVE.LOG、USEREXIT.ERR
– USEREXITプログラムによるログ・アーカイブ使用時に出力されるログ
69
© 2011 IBM Corporation
どちらの配置が適切か
パターンA
パターンB
データ用
表スペース1
データ用
表スペース3
データ用
表スペース4
データ用
表スペース2
70
© 2011 IBM Corporation
表スペース配置例
アクティブ
ログ
その他の
領域
バックアップ用
領域
アーカイブ
ログ
データ用
表スペース
C
一時
表スペース
データ用
表スペース
A
LOB用
表スペース
INDEX用
表スペース
C
71
データ用
表スペース
B
INDEX用
表スペース
B
INDEX用
表スペース
A
© 2011 IBM Corporation
解説: 表スペース配置例
様々な要件を考慮しながら、データ配置を決定する間には、様々な妥協が必要になります。
要件に合わせて、プライオリティーをつけながら決定します。
– 妥協その１
• アクティブ・ログは、データ用表スペースと同じディスクに配置すべきではないけれども
、アクティブ・ログのためだけにディスクを1つ確保すると、他の領域が納まりきれない
ので、アクティブ・ログのI/Oとは競合しない、バックアップ領域を同じディスク上に配置
します。ただし、バックアップを、表スペース単位で、またはオンラインで行い、その間
に表へのアクセスがある場合には、ログへの書き込みとバックアップ取得のI/Oが競合
するため、適当ではありません。
– 妥協その２
• 一時表スペースは、データ用表スペースとは別ディスクに配置すべきだが、この具体
例では、一時表スペースのためだけにディスクを確保することができないので、一時表
スペースへのアクセスとの競合がおきない表スペースを同じディスク上に配置します。
バックアップ領域を配置することもできますが、今回は、LOB用表スペースに格納され
ている表は、追加のみで、LOAD、REORGや複雑なSQLの処理が入らないため、一
時表スペースと同じディスク上に配置します。
– 妥協その３
• データ用表スペースAとINDEX用表スペースAは、検索や更新が頻繁なため、他の表
スペースとは分けたいが、この具体例では、Aのためだけにディスクを確保することが
できないので、他の表スペースと同じディスク上でも、できるだけ多くのディスクにまた
がるように表スペースを配置しました。
72
© 2011 IBM Corporation
まとめ ログの配置
指針
アクティブ・ログの2重化を検討する
アクティブ・ログ専用のディスク上に配置する
– ログを配置するディスクを表スペースとは別にする
アーカイブ・ログとアクティブログは別ディスクに配置する
ログは、専用のファイルシステムに配置する
アクティブ・ログに使用するファイルシステムは、ログ容量の約2倍用意する
ログ・アーカイブ機能を使用する場合は、アーカイブ先ディレクトリの数にあわせて
域を確保し、別のディスク配置
73
© 2011 IBM Corporation
まとめ 表スペースの配置
指針
複数の物理ディスクに表スペースを配置する
複数のコンテナを使用する場合は同じサイズ、タイプにする
索引用表スペースは別ディスクに配置する
–
DMSが前提
LOB用表スペースは別ディスクに配置する
–
ファイルDMSが前提
–
LOBデータはバッファープールは使用できない
1つのディスクに複数のコンテナがあってもディスクI/Oの衝突がなければ問題ない
74
© 2011 IBM Corporation
物理設計に関連する表編成
物理設計に関連する表編成
75
© 2011 IBM Corporation
ハイ・パフォーマンスを支えるパーティション表
ひとつの表を複数の区分に分割
古い区分を高速にロールアウト （区分のデタッチ）
既存データはオンライン状態で、新しい区分をロールイン （区分のアタッチ）
区分単位でのアクセス性能向上
各区分は異なる表スペースに配置可能
パーティション表
パーティション表
日付などのレンジで区分に分割し整理する
日付などのレンジで区分に分割し整理する
売上履歴表
過去のデータは
まとめて瞬時に
切り離し
DETACH
Jan
Feb
Mar
Apr
ATTACH
新規データを個
別にLOADして
から区分を取り
付け
Jan
読みたい区分の
みにアクセス
76
© 2011 IBM Corporation
ハイ･パフォーマンスを支える多次元分析機能
多次元クラスタリング（MDC)とは
–
複数属性の値によってデータを分類して自動的に格納する機能
–
単一属性のクラスタでは実現できなかった「2008年9月」の「DB2」の「東京」というような複数の属性をもつ
クラスタ
次元別検索のパフォーマンス向上
データ並べ替えを目的とした再編成不要
削除のパフォーマンスアップ（ブロック削除が可能）
–
索引のサイズが小さい（索引はブロック・ベース（BID））
–
レコードベースの通常の索引も同時に作成可能
サマリー表作成など集
計バッチにも効果大
ブロック
レコード
作成SQL例：
セル
CREATE TABLE MDC1 (
Date DATE,地域 CHAR(10),製品 VARCHAR(10),
年月 generated always as (INTEGER(Date)/100), ... )
製品
ORGANIZE BY DIMENSIONS (年月, 地域, 製品)
次元
列名指定のみで
メンテナンス不要
77
2008年
2008年8月,
東京,
東京,
2008年
2008年9月, 2008年
2008年9月,
DB2
東京,
東京,
東京,
東京,
DB2
DB2
2008年
2008年8月,
大阪,
大阪,
2008年
2008年9月, 2008年
2008年9月, Webspher
大阪,
大阪,
e
大阪,
大阪,
Webspher Webspher
e
e
地域
次元
年月
次元
© 2011 IBM Corporation
Let’s go to Lab4!!
78
© 2011 IBM Corporation