データベース物理設計 DB2 UDB (PC&Unix) データベース物理設計 1-2 ＜第１．０１版＞２００３年７月
by user
Comments
Transcript
データベース物理設計 DB2 UDB (PC&Unix) データベース物理設計 1-2 ＜第１．０１版＞２００３年７月
DB2 UDB (PC&Unix) データベース物理設計 データベース物理設計 お断り：当資料は、DB2 UDB V7.2、およびV8.1（UNIX,PC) をベースに作成されています。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 1-2 ＜第１．０１版＞２００３年７月 DB2 UDB (PC&Unix) データベース物理設計 データベース物理設計 目次 物理設計の流れ 物理設計作業開始にあたって ①テーブル／索引定義の作成 ②データ容量の見積り ③データベースの分割とインスタンスの構成 ④テーブルの分類とテーブルスペースの構成 ⑤その他のオブジェクトの配置 ⑥ディスク上へのテーブルスペースの配置 ⑦構成パラメータの設定 ⑧シェル／コマンドの作成 物理設計の評価 UDB物理設計の経験則 ESSにおけるDB設計ベンチマーク ESSにおけるDB設計例 AIX 32bit環境の制限 AIX特有の設定(DB2_MMAP_READ/WRITE） プラットフォーム別32bitデータベース共有メモリの制限 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 3-4 P.5 P.7 P.9 P.47 P.62 P.69 P.85 P.95 P.105 P.115 P.117 P.120 P.125 P.127 P.135 P.141 P.145 DB2 UDB (PC&Unix) データベース物理設計 物理設計の流れ ①テーブル・インデックス 定義の作成 ②データ容量の見積もり ③データベースの分割と インスタンスの構成 ・テーブルの分割／参照整合や制約／属性、長さの決定 ・主キー（１次インデックス） ・検索やジョインのパターンによって２次インデックス ・データ項目、長さ、インデックスなどは既に決まっている前提 ・ページ・サイズの決定 ・表、（索引）サイズの見積もり ・バックアップ単位であるデータベースの分割を検討 ・インスタンスの分割も検討 ④テーブルの分類 テーブルスペースの構成 ・種類別にテーブルを分類し、テーブルスペースを決める ⑤その他のオブジェクトの 配置 ・ログ、一時表、カタログなどの配置 ・バックアップ用、ワークスペースの確保 ⑥ディスク上への テーブルスペースの配置 ・⑤によってテーブルに使用できるディスクが確定 ・物理ディスク上へのテーブルスペースの配置を決める。 ・コンテナーの設計 ・⑥の物理設計にあわせた構成パラメーターの変更 ⑦構成パラメータの設定 ⑧シェル／コマンドの作成 ・これまでに設計したオブジェクトを作成するコマンドおよびシェルを 作成 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 物理設計の流れ データベースの物理設計を行う場合、ディスクおよびサーバーを含むハードウェア構成が決まっている必要があります。また、 テーブルの論理設計も既に終了していることが前提です。 テーブルの論理設計を実際のハードウェア上にどのように構築するかを決定することが、「物理設計」ということになります。 純粋な論理設計では、まだどのデータベース製品を使うかにはあまり依存しません。しかし、どのようにディスク上に配置し、メモ リを割り振るかを決定する物理設計はデータベース製品に特化した作業になります。 ①DBMSに特化しない論理設計では、カラムの属性・長さなどが決まりません。物理設計の最初にまずこれらのDBMSに特化した テーブル定義を決定します。例えば、データは日付なのですが、格納方法はDATEにするかTIMESTAMP,CHARまたはVARCHAR にするかなどの選択肢があります。 ②データ容量を見積もります。論理設計によってできあがったテーブルレイアウトとレコード数から容量を見積もり、必要となるディ スク容量を計算します。 ④次にそれらのテーブルを幾つかのグループに分類します。そしてそれらのテーブルを配置するテーブルスペースの定義を決め ていきます。 ⑤DBサーバーで使用できる物理ディスクのうちテーブル・インデックス以外で使用するディスクを計画します。 ⑥⑤の後に残った物理ディスクはテーブルとインデックスで使用できることになります。これらのディスクを使用してテーブルス ペースの配置を決定します。 ⑦物理設計に関連した構成パラメータを設定します。バッファプールやテーブルスペースの構成も必要です。 ⑧最後に行うのは、これらの設計に基づいたファイルシステムや論理ボリュームなどを作成するシェル・スクリプトの作成と、これ らの上にテーブルスペースおよびテーブル、インデックスを作成するDDLの作成です。 データベースの物理設計は基本的には以上の作業を行い、ドキュメントを作成し、実際のハードウェア上に構築するところで終了 します。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 5-6 DB2 UDB (PC&Unix) データベース物理設計 物理設計作業開始にあたって 論理データベース設計作業が完了していることが前提 論理データベース仕様の後からの変更は困難 アプリケーション開発作業に多大な手戻りを発生させる可能性がある 論理設計で決めた表をDB2データベースの表として定義 作成したデータベース仕様をシステム上の物理記憶域にマッピング DB2 UDBの製品仕様に依存する作業であるため、DB2 UDBの製品知識が必要 使用するオペレーティング・システム毎の知識も要求される データベース設計の最終目標 時代の変化に伴う多様な要求の出現に対応できる安定したデータベースを構築する 各種要件（アプリケーション、性能、運用等）を最適に実装するデータベースを検討する 運用設計、障害回復設計とも同期を取りながら進めて行く パフォーマンス・チューニングや運用の効率化のため適宜物理設計の見直しも必要 充分にテストを実施して、データベース設計が最適に実装されているかを検証する必要がある (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 物理設計作業開始にあたって 物理設計作業開始にあたっては、論理データベース設計作業がきちんと完了していることが必要です。論理設計完了後の変更 は、アプリケーション開発作業に多大な手戻りを発生させる可能性があります。 物理設計作業は 表の論理設計で決められた表を、DB2 UDBのデータベースの表として、定義することです。 実際に、物理記憶域のどこに、どのように表スペースや表、索引といった各オブジェクトを配置するかのマッピングを行ないま す。 論理設計で作られた論理モデルの｢実装｣を行なうことになります。 DB2 UDBの製品仕様に依存する作業であるため、DB2 UDBの製品知識が必要とされます。 また、ファイル・システムなど、使用するオペレーティング・システム毎の知識が要求されることもあります。 データベースの設計の目標となるのは、自分の環境を、理解しやすくしかも将来の拡張の基礎となるよう表現したものを作ること です。また、データの一貫性や整合性を保ちやすいデータベース設計が望ましいと言えます。そのためには、設計の段階で冗長 性を少なくし、データベースの更新時に生じ得る異常をなくす必要があります。 また、アプリケーション要件や、性能、運用要件等を最適に実装するものでなければいけません。 運用設計、障害回復設計とも同期を取りながら進めて行く必要があります。 データベースの設計は、一度で終了するものではありません。多くの場合、何度かやり直すことが必要になります。パフォーマン ス・チューニングや運用の効率化のため適宜物理設計の見直しも必要になるのです。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 7-8 DB2 UDB (PC&Unix) データベース物理設計 ①テーブル／索引定義の作成 DBMSに特化したテーブル／索引定義を決定 列の設計 データ・タイプと長さの決定 主キーと外部キー 制約 固有制約 参照制約 表検査制約 トリガー 索引（インデックス）設計 主キー（１次索引） 照会のパターンにより２次索引を検討 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ①テーブル／索引定義の作成 テーブル、および索引定義の作成にあたっては、事前にDBMSに特化した仕様を考慮して、様々な項目を決定する必要がありま す。 ページサイズは、データの容量やディスク容量、DB2 UDBの制限値を考慮して、適切なページ・サイズを決定します。 主に以下のような点について、DBMSに特化したテーブル定義を決定していきます。 列の設計（データ・タイプと長さなど） 主キーと外部キー 制約 固有制約 参照制約 表検査制約 トリガー 索引設計（１次、２次） 変更により適用業務に大きな影響を与えるものとして、例えば以下のものがあります。 データ・タイプの変更 データの長さの変更 NOT NULLの指定の変更 以下のものは、変更があっても適用業務に影響が出ないよう、それぞれ、回避の方法はあります。 データベース名の変更 CATALOG DATABASEで別名設定 表名の変更 RENAME TABLEで表名の変更、視点の作成により対応 列名の変更 視点の作成により対応 列の順序変更 視点の作成により対応 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 9-10 DB2 UDB (PC&Unix) データベース物理設計 列の設計 列のデータ・タイプと長さの決定 データ長やとり得る値の制限値により、最適なデータ・タイプを選択する 開発言語環境による生産性の観点での考慮なども必要 文字列（CHAR/VARCHAR/GRAPHIC/VARGRAPHIC） 可変長 or 固定長？ ⇒ 原則、固定長を使用することを推奨 可変長（VARCHAR）は固定長（CHAR)に比べて4バイト分余計に必要 可変長は、該当列の位置を認識するためにCPU負荷が増加 固定長は定義した長さ分の領域を必ず使用する（圧縮を採用した場合はこの限りでない） GRAPHICはホスト系（EBCDIC）との互換のために使用 日付／時刻形式（DATE/TIME/TIMESTAMP） CHARで保持するより、格納サイズが小さい YEAR , MONTH , DAY などの日付計算、時間計算関数が使用可能。DB2が計算・値のチェックを行える 数値（SMALLINT/INTEGER/FLOAT/DOUBLE/DECIMAL） 算術に使用するのであれば、通常は数値型。最大取り得る値によって、データ・タイプを選択する CHARで保持するより、格納サイズが小さい 小数点以下がある場合はDECIMALを検討 LONG型（LONG VARCHAR/LONG VARGRAPHIC/CLOB/DBCLOB/BLOB） LONG VARCHARは文字列というよりLONG型である。（ページ内にデータが格納されない） LONG VARCHAR、LONG VARGRAPHICについてはdescriptorのための２０バイトが行データ中にとられる。 上限値にほとんど差異はない。LONG VARCHAR,LONG VARGRAPHICよりも、VARCHAR,VARGRAPHICを使用す る。 LOBについても、他の表データとは別の場所に保管され、各列の情報（ポインター）のみを他データと共に持つ データが4KB以下の場合、LONGはなるべく使用しない (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 列の設計 数値・文字列（固定長・可変長）・日付などのうちどれを選択するか、データ長や、とり得る値の制限値により適したデータ・タイプを 選択します。 また、開発言語環境によって扱い易いデータ・タイプであるのか、生産性の観点などからの考慮も必要です。 文字列 可変長か固定長か決定する必要がありますが、まずは固定長を検討します。 可変長の場合は、長さとオフセット情報を入れる領域が列あたり4バイト分余計に必要になります。 可変長の場合は、該当列の位置は先頭からたどらなければならないため、CPUの負荷が該当列の位置がわかっている固定 長よりも余計にかかります。 列長の差が大きい（列あたり平均２０バイト以上）時には可変長を採用することで、DISKスペースは削減されます。 GRAPHICはダブルバイト文字列のためのものですが、主にホスト系（EBCDIC）環境のDBとの互換のために使用されます。 日付／時刻のデータ 日付計算、時間計算、関数の使用が可能になるように、DATE/TIME/TIMESTAMPを使用してください。また、その方が、 CHARデータタイプとして格納するよりもDISKスペースは軽減されます。 数値 算術に使用するのであれば、通常は数値型で格納すべきです。該当の項目の最大取り得る値によって、データ・タイプを選択 します。また、文字列で格納するよりもDISKスペースは軽減されます。 LONG型 LONGタイプは表データ・ページに実際の列のデータは含まれません。別の表オブジェクトとして表スペースに格納されます。 行データ中にはそれらの列の２０バイトの記述子（descriptor）は含まれます。 LONGタイプのデータをLONG専用の表スペースに格納させることも、CREATE TABLE時の指定で可能です。 ４KB以下の文字データについては、上述のような特異な扱いを避けるためにもLONGタイプは使用しないようにするなど、デー タ長の制限値により、適したデータタイプを選択してください。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 11-12 DB2 UDB (PC&Unix) データベース物理設計 列の設計（続き） 参考：DB2 UDB がサポートするデータ･タイプ 日付/時刻 外部データ 符号付数値 ストリング DATALINK タイム・ スタンプ 時刻 TIME 正確な値 日付 概算値 DATE TIMESTAMP 浮動 小数点数 文字 可変長 バイナリ グラフィック BLOB 単精度 可変長 固定長 固定長 可変長 REAL 倍精度 DOUBLE GRAPHIC CHAR VARCHAR CLOB VARGRAPHIC DBCLOB ２進整数 １０進 パック １６ビット ３２ビット ６４ビット SMALLINT INTEGER BIGINT DECIMAL (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 列の設計（続き） 参考：DB2 UDB がサポートするデータ･タイプ ・文字タイプ データタイプ 説明 制限値 CHAR（ｎ） ｎバイトの固定長文字列 １＜＝ ｎ ＜＝ ２５４ VARCHAR（ｎ） 最大ｎバイトの可変長文字列 １＜＝ ｎ ＜＝ ３２６７２ LONG VARCHAR（ｎ） 長可変長文字列 最大 ３２７００バイト GRAPHIC（ｎ） ｎ文字の固定長漢字ストリング １＜＝ ｎ ＜＝ １２７ VARGRAPHIC（ｎ） 最大ｎ文字の可変長漢字ストリング １＜＝ ｎ ＜＝ １６３３６ LONG VARGRAPHIC 長可変長漢字ストリング 最大 １６３５０文字 データタイプ 説明 制限値 SMALLINT 短精度整数 −３２７６８ ∼ ＋３２７６７ INTEGER 長精度整数 BIGINT ６４ビット整数 REAL 単精度浮動小数点数 DOUBLE、FLOAT 倍精度浮動小数点数 DECIMAL（ｍ、ｎ）、NUMERIC １０進数（精度桁数、小数点以下桁数） −２，１４７，４８３，６４８ ∼ ＋２，１４７，４８３，６４７ −９，２２３，３７２，０３６，８５４，７７５，８０８ ∼ ＋９，２２３，３７２，０３６，８５４，７７５，８０７ −３．４０２E＋３８ ∼ −１．１７５E−３７ もしくは １．１７５E−３７ ∼ ３．４０２E＋３８ −１．７９７６９E＋３０８ ∼ −２．２２５E−３０７ もしくは ２．２２５E−３０７ ∼ １．７９７６９E＋３０８ １＜＝ ｍ ＜＝３１、０＜＝ｎ＜＝ｍ データタイプ 説明 制限値 DATE 日付 ０００１−０１−０１ ∼ ９９９９−１２−３１ TIME 時刻 TIMESTAMP タイム・スタンプ ００：００：００ ∼ ２４：００：００ ０００１−０１−０１−００．００．００．００００００ ∼ ９９９９−１２−３１−２４．００．００．００００００ ・数値タイプ ・日付／時刻タイプ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 13-14 DB2 UDB (PC&Unix) データベース物理設計 列の設計（続き） 参考：DB2 UDB がサポートするデータ･タイプ ・ラージ・オブジェクト データタイプ 説明 制限値 CLOB（ｎ K|M|G） 文字ラージ・オブジェクトストリング ２，１４７，４８３，６４７バイト DBCLOB（ｎ K|M|G） ２バイト文字ラージ・オブジェクト １，０７３，７４１，８２３文字 BLOB（ｎ K|M|G） ２進ラージ・オブジェクト ２，１４７，４８３，６４７バイト ・ラージ・オブジェクトのディスクリプタ LOBの最大長 LOB Descriptor長 LOBの最大長 LOB Descriptor長 １，０２４ ７２ １３４，０００，０００ ２００ ８，１９２ ９６ ５３６，０００，０００ ２２４ ６５，５３６ １２０ １，０７０，０００，０００ ２５６ ５２４，０００ １４４ １，４７０，０００，０００ ２８０ ４，１９０，０００ １６８ ２，１４７，４８３，６４７ ３１６ ・DATALINK データタイプ 説明 カプセル化された値の属性 DATALINK カプセル化された値で、データベース以外の場所に保管されている ファイルへのデータベースからの論理参照 リンクタイプ、方式、ファイル・サーバ名、ファイル・パス、アク セス制御トークン、コメント (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 15-16 DB2 UDB (PC&Unix) データベース物理設計 列の設計（続き） その他設計上の考慮点 NOT NULLの指定 NULL可能にした場合、列毎に１バイトのNULLフラグが必要であり、CPU負荷が増加する NULL標識変数を準備しなければならず、プログラムが煩雑になる 比較、結合、UNIONの処理がある列はデータ・タイプを揃える データ変換負荷の軽減 パフォーマンスを考慮した列の順序 可変長列は、全ての固定長列の後ろに配置 (DB2 UDBでは自動的に) 更新される列は可能であれば近くに並べる 適用業務には列の指定順序は影響しない（順序にこだわる場合、視点(View)で対応も可） VARCHAR列：適当なデータ・タイプかどうか要検討 読み取りに２ステップ要：長さ→データ VARCHAR列への変更：長くなった場合、Tombstoneが発生する場合あり 更新がある場合、データのフラグメンテーションを招く ⇒ REORG運用が必要となる可能性が高い 列の自動生成 生成列（GENERATED COLUMN）の利用 指定されたルールに従い、列の値が動的に生成される列 列の値を事前に加工して入れておくことにより、SQL実行時のパフォーマンスを向上させる 識別列（IDENTITY COLUMN）の利用 DB2が列の値として、固有の数値を生成する列 固有の値を取得するために別途テーブルを用意したり、MAX関数を使用して取得したりする必要がない 行をユニークに識別可能な、列の値を事前に入れておくことにより、識別列を使用した照会処理が可能 シーケンス・オブジェクトの利用も検討 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 列の設計（続き） 列にNULLデータが入る可能性がなければ、以下の理由からNOT NULLを指定してください。 NOT NULLでない場合 列毎に1バイトのNULLフラグが必要 NULLか否か調べるCPU負荷の増加 NULLフラグによるDISKスペースの増加 プログラムではNULL標識変数の準備が必要 比較、結合、UNIONの処理がある列は、列同士のデータタイプをそろえることでデータ変換負荷を軽減させることができます。 パフォーマンスを考慮した場合、更新される列は近くに並べて下さい。 更新時のログは、先頭の更新列から、最後の更新列まで取得される為、更新列がばらけているとログデータが増加します。 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10 update testtab set c5=50 ・・・・・・・・・ ログは、c5のみ update testtab set c3=20, c9=90 ・・・ログはc3∼c9まで ただし、可変長列でその列長が変更されるような更新がおこなわれた場合、行全体（新旧）がログとして取得されます。 可変長列は、全ての固定長列の後ろに配列されます。 DB2 UDBは、データの保存時に固定長列、可変長列、LONG VARCHARポインター(20バイト)の順で自動的に格納していま す。 LL VARCHAR INTEGER CHAR LL 固定長のinteger列を得るためにはその前のVARCHAR列からたどらなければならない CHAR INTEGER LL VARCHAR integer列は行の先頭からの相対位置が変わらないので直接得ることができる LL (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 17-18 DB2 UDB (PC&Unix) データベース物理設計 解説： 列の設計（続き） VARCHAR/VARGRAPHIC列を使用する場合には、可変長であるメリットとデメリットを考慮した上で、使用して下さい。 可変長列は、2種類の情報を持っています。 データの長さ データ 可変長データを読み込む場合、まずデータの長さを読み取り、次にデータをその長さ分読み取るという2段階を経るため、パ フォーマンスに影響が出ます。 また、可変長データに変更が発生した場合、元データより長くなると同じページに収まらなくなってしまう可能性があります。 その場合、移動先の情報を持ったTombstoneが元のデータ位置に残されます。 その為、そのレコードを取得するために、ページを2段階経なければならなくなる可能性があります。 C1 C3 C2Len BPS header C2 Data 3 OS0OS1OS2 Record 2 Record 1 Rec 0 新しいページ Tombstone 更新がある場合、データのフラグメンテーションを招き、 REORG運用が必要となる可能性が高くなります。 VARCHAR列を使用するのが望ましい場合： データの長さの範囲がまちまちで、ほとんどの列は短い データの長さの範囲がまちまちで、全ての範囲でほぼ均等に分布 VARCHAR列を使うべきでない場合： データの長さの範囲はまちまちだが、ほとんどの列は範囲の上の方にある VARCHAR列にすることによって、ディスク・スペースが余計にかかり、パフォーマンスが低下するケース (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 参考：生成列（GENERATED列） 生成列とは、各行の値を挿入操作または更新操作からではなく、定義した式によって定めることができる列です。更新トリガーお よび挿入トリガーを組み合わせて使用すると同様のことが行えますが、生成列を使用すると、派生した値が式と一貫したものであ ることを保証できます。 表で特定の式や述部を頻繁に使用することがわかっている場合、生成列を使用してあらかじめ値を生成しておくことにより照会の 際のパフォーマンスを向上させることが可能です。 表で生成列を作成するには、ALTER TABLEまたはCREATE TABLE時にGENERATED ALWAYS AS 文節を使用して、列の値を定 義する式を含んだ列を指定します。 生成列には検査制約やユニーク索引/基本キーが使用できない、生成列を持つ表に対してRENAME TABLE ができない等の制約 事項があるので使用にあたっては考慮が必要です。 生成列の例 "c1" および "c2" という通常の 2 つの列と、表の通常の列から派生した "c3" および "c4" という 2 つの生成された列の 入った表を作成します。 C1 > C2 の時は1 それ以外は 0 C1 + C2 CREATE TABLE T1(c1 INT, c2 DOUBLE, c3 DOUBLE GENERATED ALWAYS AS (c1 + c2), c4 GENERATED ALWAYS AS (CASE WHEN c1 > c2 THEN 1 ELSE 0 END) ); t1表 C1 C2 C3 C4 C2 C3 C4 t1表 INSERT t1 (C1, C2) VALUES (10, 3) INSERT C1 10 3 13 10 + 3 = 13 10 > 3 なので1 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 19-20 1 データベース マネージャーが 値を生成 DB2 UDB (PC&Unix) データベース物理設計 解説： 参考：IDENTITYE列とSEQUENCE IDENTITY列（識別列）は生成列の中の一つで、表の各行に対して固有な基本キー値を自動的に生成します。 識別列では、アプリケーションがデータベースの外に独自のカウンターを生成する際に生じる、並行性およびパフォーマンス 上の問題を回避することが可能です。 固有な基本キーを自動生成する際に識別列を使用しない場合には、単一行の表にカウンターを保管するのが一般的な設計 方法です。各トランザクションはこの表をロックして、数を増分してからトランザクションをコミットして、カウンターのロックを解除 します。しかし、残念ながら、この設計では、カウンターを増分できるのは一度に 1 つのトランザクションのみです。一方、識別 列を使用して基本キーを自動的に生成すると、アプリケーションでより高度なレベルの並行性を実現できます。 SEQUENCT（シーケンス）とは、値の自動生成を可能にするデータベース・オブジェクトです。 シーケンスを使用すると、固有キー値を生成することが可能です。IDENTITY列と同様 アプリケーションはシーケンスを使用す ることで、データベースの外部に固有カウンターを生成したことによって発生する可能性のある、並列性およびパフォーマンス の問題を回避することができます。 識別列属性とは異なり、シーケンスは特定の表列に関連付けられていないデータベースオブジェクトです。 シーケンス・オブジェクトはどのアプリケーションでも使用できるため、NEXTVALおよびPREVALの二つの値を返す式が定めら れています。 t1表 C1 IDENTITY列 IDENTITY列 C2 C3 t1表 SEQUENCE C1 C4 C2 create table t1 (c1 int generated always as identity (start with 10, increment by 2), c2 char(10), c3 double, c4 int) 列内で固有な値を自動生成 NEXTVAL C3 C4 SEQUENCE オブジェクト insert into t1 (c1,c2) values (nextval for seq1, 100) シーケンス・オブジェクト内で固有な値を自動生成 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 21-22 DB2 UDB (PC&Unix) データベース物理設計 主キーと外部キー 主キー（Primary Key） 主キーは表の保全性を保証する 格納されるデータの値は、ユニークでなければならない NOT NULL指定必須 主キーを付与するとユニーク索引（１次索引） は自動的に作成される（固有制約） 参照制約を使用しない場合は基本キーではなく、 ＜部門表＞ 別途ユニーク索引を定義しても可 外部キー（Foreign Key） 部門番号 部門名 １００ 営業部 ２００ 電算部 ３００ 総務部 ＜社員表＞ 社員番号 社員名 部門番号 A111 田中 １００ B222 山田 ２００ C333 鈴木 ２００ 主キー 親表と従属表の間の親子関係を示す働きをする 外部キー 別の表の主キーが、その表のデータ項目になっている時、そのキーは外部キーとなりうる 結合操作の結合列になる ⇒ 索引の候補 外部キーは参照の整合性を保証するために、主キーに存在しない値を持ってはいけない（参照制約） 参照制約を使用しない場合、あくまでも論理的なものであって、物理定義する必要はない 定義時には、CONSTRAINTにより制約名をつける 管理が容易 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 主キーと外部キー 主キー 表の固有キーとは、それぞれの値が固有の行を識別する、１つの列または複数の列の順序集合のことです。たとえば、社員 番号の列の各値は一人の社員だけを指すものなので、これを固有キーとして定義することができます。二人の社員が同じ社 員番号を共有することはできません。 表の主キーは、１つの表上に定義された固有キーの１つですが、その表上で1番目に重要なキーとして選択されたものです。 １つの表上には１つだけの基本キーが可能で、そのエンティティ内で行を唯一無二のものとして識別できるデータ項目です。 主キーはエンティティの保全性を保証するために、ユニークであり、空白値は許されません。 外部キー 表の外部キーは、親表の固有キーまたは主キーを参照する、表内の１つの列または1組の列のことです。 外部キーは表（親表）と表（従属表）の間の参照の整合性を保証するために、基本キーに存在しない値を持ってはならず、結 合操作時には結合列になります。 参照制約を使用しないのであれば、親表と従属表の間の親子関係を示すあくまでも論理的な意味合いのものであり、主キーと外 部キーの物理的な設定は必須ではありません。 主キー、外部キーの定義時には、CONSTRAINTにより制約名をつけたほうが管理しやすくなります。 主キーの定義 CREATE TABLE 親表名 （主キー列名 ・・・ NOT NULL, ・・・・・・・, ・・・・・・・, CONSTRAINT 制約名 PRIMARY KEY（主キー列名)) 外部キーの定義 CREATE TABLE 従属表名 （・・・・・・・, 外部キー列名 ・・・ NOT NULL, ・・・・・・・, CONSTRAINT 制約名 FOREIGN KEY（外部キー列名) REFERENCES 親表名 ON DELETE 規則) (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 23-24 DB2 UDB (PC&Unix) データベース物理設計 制約 データの保護や、データ間の相互関係の定義をDBMSに行わせる アプリケーション・ロジックとしてでコードを作成する必要がない 事前に制約を使用するか否かの方針決めが必要 使用する場合は、制約違反エラーのハンドリング・ロジックが必要 データ格納時にDB2により厳格にチェックされる バッチによる大量の更新時や特にテスト環境でのテスト・データ作成時に格納順番やデータの値を考慮する必要あり ３種類の制約を提供 固有？ 固有制約 表の 1 つまたは複数の列に重複する値を指定することを禁止する規則 表検査制約 1 20 col2 1 A1 INSERT col3 col3 < 20？ 10 表の各行の1つ以上の列について可能な値を指定する規則 col1 col2 A1 INSERT 参照制約 外部キーの値が親キーの値として現れる場合、または外部キーの構成 要素の一部がヌル値の場合のみ有効とする規則 'A3' INSERT （情報制約） DB2による制約情報チェックの適用/非適用の設定が可能な制約（V8） col1 1 col2 A1 c1 A1 A2 親表に存在する？ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 制約 制約を利用すると、データの保護や、データ間の相互関係の定義をデータベース・システムに行わせることが可能です。これによ り、アプリケーションでコードを作成し、これらの規則を施行する必要がなくなります。 事前に制約を使用するか否かの方針決めを最初に確定させる必要があり、使用するのであれば、制約違反エラーのハンドリン グ・ロジックは必要となります。 データ格納時にDB2により厳格にチェックされるため、バッチによる大量の更新時や特にテスト環境でのテスト・データ作成時に格 納順番やデータの値を考慮するが必要あります。例えば、参照制約が定義されている表については、データのINSERTは、必ず親 表を先にする必要があります。 DB2は以下の種類の制約を提供しています。 固有制約 表の 1 つまたは複数の列に重複する値を指定することを禁止する規則 表検査制約 表の各行の1つ以上の列について可能な値を指定する規則のことです。 参照制約 外部キーの値が親キーの値として現れる場合、または外部キーの構成要素の一部がヌル値の場合のみ有効とする規則 （情報制約） V8から、情報制約（Informational Constraint）と呼ばれる新しいタイプの制約により、DB2による制約情報チェックの適用/ 非適用が設定可能になりました。 制約情報を非適用とすることにより、ビジネス・アプリケーションのロジックによってチェックされ、この場合、データベース・ マネージャーによってチェックされることはありません。また、オプティマイザーによる制約の利用を活動化、または非活動 化させる指定も可能です。 下記のオプションを CREATE または ALTER TABLE で指定します。 ENFORCED： DB によって更新操作時、常に制約をチェックさせる。 NOT ENFORCED：DB に制約をチェックさせない。また、SET INTEGRITY を使用しても、チェックされません。この場合、実 際に制約に違反するデータが入る可能性あるため、アプリケーションでのチェックする必要があります。 ENABLE QUERY OPTIMIZATION： オプティマイザーのQuery Rewriteを活動化する。 DISABLE QUERY OPTIMIZATION：オプティマイザーのQuery Rewriteを非活動化する。 制約は、CREATE TABLE文およびALTER TABLE文を使用して、表の列に対して定義します。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 25-26 DB2 UDB (PC&Unix) データベース物理設計 解説： 制約 固有制約の例 表t1 には、列 (col1, col2, col3) があり、col1 の各データは表で固有でなくてはならない場合、例えば次のように定義します。 create table t1 (col1 int not null, col2 char(10) not null, col3 int, constraint t1_uniq UNIQUE (col1)) このcol1列に重複した値をINSERTすると、SQL0803Nのエラーとなります。 表検査制約の例 t1のcol3には、かならず20未満でなくてはならない場合は、以下のように表を定義します。 create table t1 (col1 int not null, col2 char(10) not null, col3 int, constraint col3check CHECK (col3 < 20)); col3に20以上の値をINSERTしようとすると、SQL0545Nのエラーになります。 情報制約の例 上記で定義した表検査制約と同等ですが、アプリケーションで保証することとし、DB2はチェックしません。 create table t1 (col1 int not null, col2 char(10) not null, col3 int, constraint col3check CHECK (col3 < 20) NOT ENFORCED ENABLE QUERY OPTIMIZATION); (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 27-28 DB2 UDB (PC&Unix) データベース物理設計 参照制約 参照制約とは 参照の整合性を保持するためのしくみ CASCADE ：波及的 RESTRICT ：制限付き NO ACTION ：制限付き（デフォルト） SET NULL ：空白値化 参照制約の動作 主キーの削除 CASCADE ：主キーを持つ行を削除し、対応する外部キーの行も削除 RESTRICT（NO ACTION）：対応する外部キーを持つ行がない時のみ、基本キーの行を削除 SET NULL ：主本キーを持つ行を削除し、対応する外部キーを空白値化 主キーの更新 RESTRICT（NO ACTION）：対応する外部キーを持つ行がない時のみ、主キーを更新 主キーの挿入 外部キーには影響しない 外部キーの削除 主キーには影響しない 外部キーの更新 対応する主キーがなければ、その外部キーの値での更新はできない 外部キーの挿入 対応する主キーがなければ、その外部キーをもつ行の挿入はできない（RESTRICT） (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 参照制約 参照制約とは参照の整合性を保持するためのもので、表と表の間、または表内部で必要な関係を定義することができます。その 関係においては、表の中の特定の属性や列の値が別の表や列にも存在していることが必要となります。 データベースの中に参照制約を作成すると、参照の整合性が保持されるようになり、照会をより効率的に処理するために、最適 化プログラムがこれらの特殊な関係の情報を活用できるようになります。 参照制約はオプションであり、 CREATE TABLE ステートメントおよび ALTER TABLE ステートメントで定義することができます。参 照制約は、データベース・マネージャーによって、 INSERT、UPDATE、DELETE、ALTER TABLE ADD CONSTRAINT、および SET CONSTRAINTS ステートメントの実行の過程で課せられます。 挿入規則 外部キーのヌル値以外の挿入値が親表の親キーの何らかの値と一致していなければならない。 更新規則 参照制約の定義時に指定され、選択項目には、NO ACTIONとRESTRICTがあります。 更新規則は親の行または従属表の行が更新されるときに適用されます。 親行の場合、親キーの列の値が更新されると、次のようになります。 従属表の行がキーの元の値と一致し、しかも更新規則がRESTRICTである場合には、その更新は拒否されます。 更新ステートメントの完了時に（トリガー後を除く）、従属表の行に対応する親キーがなく、しかも更新規則がNO ACTION である場合には、その更新は拒否されます。 削除規則 参照制約の定義時に指定され、選択項目には、NO ACTION、RESTRICT、CASCADE、SET NULLがあります。 削除規則は親表の行が削除されるときに適用されます。 親表の行が削除の対象になっており、参照制約の従属表の中にその行の従属行があると、次のようになります。 RESTRICTまたはNO ACTIONの場合、エラーとなり行は削除されません。 CASCADEの場合、削除規則は、従属表にある（削除対象の親行の）従属行へ伝送します。 SET NULLの場合、従属表にある（削除対象の親行の）従属行の外部キーのうち、ヌル値可の各列はヌル値に設定され ます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 29-30 DB2 UDB (PC&Unix) データベース物理設計 参照制約 （例） 参照制約の例 主キーの列が’A01’の行を削除するDELETE文が実行された場合 参照制約の削除規則の指定により、外部キーに対する処理が決まる 外部キー 従業員表 従業員番号 氏名 部門番号 １００１ ２３４５ ３５６７ ｘｘｘｘ ｘｘｘｘ xxxx A01 B01 A01 部門表 主キー 部門番号 部門名 部長社員番号 A０１ A０２ B０１ C０１ xxxx xxxx xxxx xxxx 3333 4444 5555 6666 CASCADE ： 対応する外部キーの値を持つ行をDELETE RESTRICT ： 対応する外部キーの値がある場合、DELETEがエラーとなる SET NULL ： 対応する外部キーの値をNULL値にする DELETE A01 xxxx 3333 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 参照制約 （例） DELETE文が主キーのある表に対して実行された場合、DELETE文の条件に指定された主キーの値に合致する外部キーを持つ 従属表の行は、参照制約の削除規則の指定により、処理方法が決定される。 CASCADE ： 対応する外部キーの値を持つ行をDELETE RESTRICT または NO ACTION： 対応する外部キーの値がある場合、DELETEがエラーとなる SET NULL ： 対応する外部キーの値をNULL値にする 参照制約の例 表t1 の列col2 は表t2 の列c1の既存の値と一致していなくてはならず、t2の行が削除される場合は、t1のその行を参照する 行も削除される場合は、次のようになります。 create table t2 (c1 char(10) not null primary key) create table t1 (col1 int not null, col2 char(10) not null, col3 int, constraint t1_fk FOREIGN KEY (col2) REFERENCES t2 (c1) ON DELETE CASCADE) このcol2列にt2に存在しない値をINSERTするとSQL0530Nのエラーになります。 t2から行を削除すると、その行を参照していたt1の行も削除されます。 insert into t1 values (1, 'A1', 10) insert into t1 values (1, 'A2', 20) DB20000I SQL コマンドが正常に終了しました。 delete from t2 where c1 = 'A1' DB20000I SQL コマンドが正常に終了しました。 select * from t1 COL1 COL2 COL3 ----------- ---------- ----------1 A2 20 1 レコードが選択されました。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 31-32 DB2 UDB (PC&Unix) データベース物理設計 トリガー トリガーとは データの変更処理の際、予め定義された処理を自動的にDBMSが行う 表に対する更新処理をきっかけに処理を起動させる必要がある場合に使用する アプリケーション・ロジックでコードを作成する必要がない T1表 T2表 c1 20 INSERT 30 16 TRIGGER! c1 2001-07-08-11.35.02.786000 2001-07-08-11.35.02.826000 2001-07-08-11.35.02.866000 c2 20 25 22 if (new.c1 > 10) then set a = (select sum(c1)/count(c1) from t1); insert into t2 values (CURRENT TIMESTAMP, a); else signal SQLSTATE '75001' (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： トリガー トリガーとは、指定した表に対するDELETE、INSERT、UPDATE操作によって引き起こされる処置の集まり（トリガーアクション）を定 義するものです。 例えば、次のようなことが可能です。 入力データの妥当性検査を実行すること 新しく挿入した行の値を自動的に生成すること 相互参照のために他の表から読み込むこと 監査証跡のために他の表に書き込むこと 電子メール・メッセージによるアラートをサポートすること トリガーを使用するメリットとして、業務規則を実施する論理をデータベースの中に組み込むことができ、表を使用するアプリケー ション側で業務規則を実施する必要がなくなるということが挙げられます。すべての表に対して実施する論理を集中管理すること によって、論理が変わってもアプリケーション・プログラムを変更する必要がないために、保守が容易になります。 トリガーは、データ保全規則を実施するために、参照制約および検査制約と共に使用することができます。また、他の表を更新し たり、挿入行または更新行の値を自動的に生成または変換したり、警告をだすなどのタスクを実行する関数を呼び出したりする場 合にも使用できます。 トリガーには、前トリガーと後トリガーの2種類があります。後トリガー の場合は、トリガー・アクションを各行ごとに 1 回実行する か、ステートメント全体で 1 回実行するかをさらに定義できます。 前トリガー の場合は、必ず各行ごとにトリガー・アクションを実行 します。 トリガーはCREATE TRIGGER ステートメントで定義されます。 トリガーの例 CREATE TRIGGER TRIG1AFTER INSERT ON EMPLOYEE FOR EACH ROW MODE DB2SQL UPDATE COMPANY_STATS SET NOWEMP=NOWEMP+1; (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 33-34 DB2 UDB (PC&Unix) データベース物理設計 テーブル定義に関するその他考慮点 1行のレコードがページをまたがることはできない Longを除く 最適なページ・サイズの決定（4KB/8KB/16KB/32KB） 行の削除によるスペース解放はない 削除してもDiskの使用率は変わらない 再利用はされる APPENDモードの指定により再利用させないことも可能 REORGなどの運用が必要 ページの空き領域の設定 予め、ページ内にフリー・スペースを残す設定 設定が必要か否かの検討が必要 離れたページにデータが挿入されると、パフォーマンスに影響を与える クラスター索引を持つテーブル 可変長列の更新があるテーブル ALTER TABLEによるPCTFREE指定 LOAD、REORG時に指定されたフリー・スペースを確保 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： テーブル定義に関するその他考慮点 1行のレコードが複数のページにまたがることはできません。 1レコードの長さがページ内に収まらない場合は、ページサイズを大きくして下さい。 4KB → 8KB → 16KB → 32KB データの削除が行われたとき、そのレコードのあった場所は解放されません。(再利用はされます。)削除のフラグが立つのみで す。Diskの使用率は変わりません。 削除されたスペースを解放させるためには、reorg tableを実行して下さい。 APPENDモード INSERT時にテーブル内の空きページを検索することなく、最終ページにレコードを追加する機能。（APPENDモード ON） レコードが単調増加していく特性のテーブルにINSERTする場合、パフォーマンスが向上しますが、DELETEによる削除レコード の空き領域は再利用されないため、別途、テーブル自体の再作成（もしくは、0件）、または、再編成などの運用により、テーブ ル容量を適正に保つ仕組みの検討が必要とななります。 クラスターインデックスのあるテーブルには、適用不可。 ページの空き領域(PCTFREE） 離れたページにデータが挿入されると、パフォーマンスに影響を与える為、予め、ページ内にフリー・スペースを残す様、設定 することができます。 ALTER TABLE 表名 PCTFREE 数値 PCTFREE：0∼99 (デフォルト:-1) データのロード、およびREORG TABLE時に、指定されたサイズのフリー・スペースをページ内に残します。 PCTFREEの設定が必要な例 クラスターインデックスを持つテーブル（データの挿入時に、索引順とデータの並び順を同じにするようにデータを格納しようと 試みる）に対して、業務上の観点から、データの追加、削除処理の頻度にも留意し、ページの空き領域（ＰＣＴＦＲＥＥ）の設定 の検討が必要です。データの挿入が多い場合、索引順序にデータを配置しようとしても、ページ内に収まらない場合がありま す。 また、可変長の属性の列項目を持つテーブルにおいて、その列項目に対して更新がある場合、空き領域を設けることを検討 する。 オンライン中の更新がない、または、クラスターインデックスを持たない列項目の属性が固定長のみで定義されているテーブ ルについては、空き容量は特に必要ありません。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 35-36 DB2 UDB (PC&Unix) データベース物理設計 索引の設計 索引の目的 照会処理の処理効率を高める アクセス・パスにおける索引の使用による効率のよいデータへのアクセス 行のユニーク性を維持する ユニーク索引 データの並び順を索引順に維持することにより、データ・アクセスの効率を向上させる クラスター索引 設計手順 パフォーマンス改善を目的とし、繰り返し行う必要がある 索引候補の検討 索引数の検討 索引候補の取捨選択 索引の物理定義と検証 索引が有効に利用され最適なアクセスパスになっているか 意図した索引を使用しているか メンテナンス負荷を軽減するため、使用されていない場合にはDROP SYSCAT.PACKAGES（静的SQL）、または、EXPLAINツールで確認 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 索引の設計 表に作成する索引は、本の索引と同様の機能を果たします。 索引の第一の目的は、データをアクセスする際の処理効率を向上させることです。余計な入出力をすることなく、最短の方法で目 的のデータにたどりつくには、索引は非常に有効です。 ユニーク索引を作成した際には、索引のキー列のユニーク性を保証する機能を使用可能です。 クラスター索引 クラスター索引を作成すると、データの挿入時に、索引順とデータの並び順を同じにするようにデータを格納しようと試みま す。 データをインデックスの列項目の値順に読み込む場合、Ｉ／Ｏ回数が軽減され、処理効率が向上します。 データが格納されるページに、空きスペースを準備する必要がある。 インデックスの列項目の値が更新される（更新があった場合、索引順に再格納は行わないため、再編成の必要性を検討する 必要がある。）場合や、検索結果が常に１件となる照会処理が頻繁に行われる場合は、作成してもメリットはありません。 索引の設計手順 パフォーマンス改善を目的とし、内部設計から統合テストの局面まで、繰り返し行う必要がある 索引候補の検討 主キーや外部キーなどは、データの意味から索引候補として決定可能であるため、外部設計後に可能な作業です。一 方、その他の２次索引については、具体的なSQL文を元にアクセス・プランを検討し、候補の洗い出しを行います。 索引数の検討 索引数が増えると、索引のメンテナンス負荷が高くなり、処理効率が低下します。従って、トランザクションの内容により、 索引数を制限して作成する必要があります。 索引候補の取捨選択 どの列に索引を付与するか、最適なアクセス・プランを検討し、本当に必要と思われる索引を選択します。 索引の物理定義と検証 索引が有効に活用されているかを確認し、使用されていない場合には、DROPする必要があります。索引が存在することによ るメンテナンス負荷を軽減するためです。静的SQLプログラムについては、SYSCAT.PACKAGESで確認できます。また、動的 SQLプログラムについては、EXPLAINツールで確認します。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 37-38 DB2 UDB (PC&Unix) データベース物理設計 索引候補の検討 ユニーク索引が必要か ユニーク性の維持が必要な場合： 参照の整合性が必要な場合： ユニーク索引 主キー CREATE TABLE実行時に、自動的に主キーに対する昇順のユニーク索引が作成される 索引名 ： SQL+タイムスタンプ+番号 索引スキーマ： SYSIBM CONSTRAINTで制約名つけると管理が容易 外部キーに索引をつける 結合列になる可能性が高い列に索引があると、処理効率は良い 条件句(WHERE句に現れる述語)の中で頻繁に使用される列を検討 結合列 探索条件の列 ANDで結ばれた等号述語 範囲指定の述語（ＢＥＴＷＥＥＮ，不等号述語） ソート列（DISTINCT、ORDER BY、GROUP BYで指定された列） 索引のみのアクセスを目的とした索引 INCLUDE列つきのユニーク索引 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 索引候補の検討 基本的な索引候補 まず、ユニーク索引が必要かどうかを検討します。ユニーク性を維持しなければならない列が存在するのであれば、ユニーク 索引が必要です。 主キーの必要性を検討します。他の表の列と整合性を保たなければならない、マスターとなる列が存在するのであれば、表 に主キーを設定します。基本キーの設定は、表の作成（ＣＲＥＡＴＥ ＴＡＢＬＥ）時に指定するか、または、表の変更（ＡＬＴＥＲ ＴＡＢＬＥ）で指定します。 外部キーがある場合、検索条件の結合列となる可能性が高いため、索引の候補になります。 さらに、その他の２次索引候補を検討します。 候補になる列は、条件節での登場回数が多い列です。 また、ソートの対象となる列も候補になります。 FOREIGN KEY（外部キー）が定義されている列項目 レコードの探索条件として、「=」述部に指定されることの最も多い列項目、もしくは、最初のキーとしての個別の値が最も多い 列 表を結合するときに使用するすべての列 INCLUDE列つきのユニーク索引 ユニーク索引の列として、ユニークではない列を含むことが可能 目的： ・索引のみのアクセスによるパフォーマンス向上 冗長な索引を作成しない 表にアクセスすることなく、索引のみで照会処理要求を満たすことができます。これをindex-only accessといいます。 INCLUDE列を指定してユニーク索引を作成することにより、データページのアクセス頻度が軽減されます。 索引キーの一部の列については、ユニーク性を保持する ユニークではない列については、ユニーク性の検査が発生しない 作成方法： CREATE UNIQUE INDEX 索引名 ON 表名 （列名） INCLUDE (列名） 複数列の指定が可能（索引列数：１６列 最大キー長：１０２４バイト） ユニークではない列については、索引順（ASC，DESC）の指定は無効 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 39-40 DB2 UDB (PC&Unix) データベース物理設計 ＜参考＞INCLUDE列つきのユニーク索引：使用例 処理するＳＱＬステートメントの例：（employee_idに主キーがある） SELECT employee_id, mgr_id FROM my_employee WHERE employee_id = 78379 ; INCLUDE列を使用しない例：２つの索引を作成・維持する必要あり １．表の作成 CREATE TABLE my_employee ( employee_id integer not null, mgr_id integer, phone_no integer, hire_date date, PRIMARY KEY (employee_id) ); - DB2は主キーには自動的にユニーク索引を作成する ２．索引のみのアクセスのために、索引を作成する CREATE INDEX col_index ON my_employee (employee_id, mgr_id) ; INCLUDE列を使用する例：１つの索引だけで INDEX ONLY ACCESS １．表の作成 CREATE TABLE my_employee ( employee_id integer not null, mgr_id integer, phone_no integer, hire_date date) ; ２．INCLUDE列つき索引の作成 CREATE UNIQUE INDEX my_index on my_employee (employee_id) INCLUDE (mgr_id) ; ３．主キーの作成 ALTER TABLE my_employee add PRIMARY KEY (employee_id); - 既存の索引が主キーになる (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 索引候補の取捨選択 索引の作成を避けた方がよい列 可変長列 索引のメンテナンスの負荷が高い 統計情報のCOLCARDの値が小さい（重複値の多い）列 （例） フラグ（0 or 1）や区分など SYSCA.COLUMNSのCOLCARD列： ユニークな値の数 オプティマイザ-が索引を選択しない ごく小さい表の列 アクセス・パスの決定時に、索引が有効とみなされず、表スキャンになる可能性が高い 複合列索引の考慮点 複合列索引の全ての列が等号で使用されるものは有効 索引列は、最も頻繁に等号で指定される列か、最もユニーク性の高い列を順に指定する 最初の索引列で結果行を大幅に絞り込める索引は、利用されやすい 完全にマッチングする索引を優先する （例）索引１（col1,col2,col3) と 索引２（col1,col2）がある場合で、条件がcol1=x and col2=x であれば索引２を優先 ・索引２はFULLKEYCARDが有効であり、かつ、キー長が短いのでバッファーヒット率が高い 統計情報でFULLKEYCARDが大きいものは有効 SYSCAT.INDEXES（FULLKEYCARD）：列全体でユニークな値の数 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 41-42 DB2 UDB (PC&Unix) データベース物理設計 索引数の検討 索引数の目安：表あたり５個以下が望ましい むやみに索引を作成することは、ディスクを無駄に消費し、負荷を増やすことになる オンラインでの更新処理環境： １−２個 照会のみの環境： ５個以上作成してもよい オンラインの更新処理と照会処理の混在した環境： ２−５個 更新処理時には索引のメンテナンスが必要となり、負荷が発生する INSERT処理では、索引列の追加処理が発生 DELETE処理では、索引列の削除処理が発生 索引列に対するUPDATE処理では、索引列の変更処理 索引のスプリット処理 行のランダムな追加を予想し、事前にページにフリースペースを確保しておく（PCTFREE） その他の負荷 クラスター索引がある表へのINSERTは、索引順を極力保持するようにデータを格納する ディスク・スペース使用量の増加 LOAD、REORG時の索引の再作成の負荷 索引の追加によりプログラムのPREPARE時間が増加 静的SQLではＢＩＮＤ時、動的SQLでは実行時の時間が増加する 検討すべきアクセスパスの組み合わせが増加する (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 索引数の検討 索引数が増えると、照会のパフォーマンスは向上する可能性がある一方で、索引のメンテナンス負荷が高くなり、更新の際の処 理効率が低下します。従って、トランザクションの内容により、索引数を制限して作成する必要があります。 複数のインデックスを作成する前には、ディスク・スペースや処理時間への影響も留意し検討する必要があります。 索引のスプリット処理 索引のリーフ・ページが満杯になった時点で、さらにそのページにデータが格納される必要があったとき、索引ページは分割さ れ、２つのリーフ・ページになります。分割されるデータの割合は、満杯になった索引ページが索引構造内でどの場所にあった かにより異なります。 索引ページのフリースペース（PCTFREE） 表にランダムにデータをＩＮＳＥＲＴするようなアプリケーションにより、索引のリーフページが頻繁にスプリットされてしまうのを 防ぐために有効です。 CREATE INDEX ステートメントのオプションです。また、LOAD時にMODIFIED BY INDEXFREESPACE=xにより、再指定するこ とも可能です。 フリースペースが確保されるタイミングはLOADおよびREORG時です。 以下の場合には、フリースペースは必要ありません。 INSERT,DELETEがない 索引キーの更新がなく、固定長列のみなので更新による行のネスティングが発生しない 照会のみである LOADに関する考慮点 ＬＯＡＤの前に索引作成を行っておいたほうが、ＬＯＡＤ後に索引を作成した場合と比較すると、合計時間が短くてすみます。ま た、事前にユニーク索引を作成しておいた場合には、ＬＯＡＤ時にユニーク性の検査が行われます。 ＬＯＡＤの前に索引を作成しておく場合には、索引を作成するための一時領域を確保しておく必要があります。メモリー領域と しては、SORTHEAP DB CFGに設定された容量のソート領域が使用されます。また、ディスク領域としては、一時表スペース が使用されます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 43-44 DB2 UDB (PC&Unix) データベース物理設計 その他の検討事項 索引用の表スペース 索引用の表スペースを作成する（DMSのみ） 表データとは別の物理ディスクに配置することによる並列Ｉ／Ｏが期待できる 索引だけ早いディスクに格納することができる 索引用の表スペースに対するバッファープールを作成する 索引をメモリー上に保持し、バッファーヒットさせたい場合 索引用の表スペースはＤＭＳファイル表スペースにすることも検討？ ＡＩＸファイルシステムのキャッシュが使用可能 索引が有効利用され、最適なアクセスパスを得るために 索引順を維持する クラスター索引により索引順を維持する REORGにより定期的に索引順を維持する ユニーク索引の列による１件検索の場合には、クラスター率が低くても問題はない ＲＵＮＳＴＡＴＳの実行による統計情報更新とBIND実行 実行タイミング 表のデータがLOADされ、適切な索引が作成された時 表のデータがREORGされた時やアプリケーションをBINDする時 表および索引のデータの10%−20%がUPDATE/DELETE/INSERTされた時 現時点の統計情報に更新することにより、現状で最適なアクセス・パスが選択される（動的ＳＱＬの場合） RUNSTATS実行後、BINDを実行する（静的SQLの場合） (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： その他の検討事項 策引データを策引用の表スペースに格納するためには、表の作成（ＣＲＥＡＴＥ ＴＡＢＬＥ）時に、その表に対して作成された索引 のデータをどの表スペースに格納するかを明示的に指定します。 CREATE TABLE 表名 (COL1 INTEGER, .......) IN 表の表スペース名 ＩＮＤＥＸ ＩＮ 索引の表スペース名 索引が作成されている索引用の表スペースをDROPすることはできません。DROP時に、関連するデータベース・オブジェクトが存 在するためにDROPが不可能である旨のエラー･メッセージが戻されます。この場合、索引を全てDROP後に、表スペースをDROP するか、または、DROP TABLESPACE文で、関連する表スペースを全て指定することにより、表スペースをDROPすることが可能 です。 RUNSTATSは、表のデータが大きく変化した場合に、統計情報を最新の状態に更新するために実行します。動的SQLは、動的に ＢＩＮＤを実行するため、ＲＵＮＳＴＡＴＳで統計情報を変更することによりアクセス・パスが変わる可能性があります。 静的SQLの場合は、RUNSTATS実行後、より最適なアクセス・パスを得るには、BINDを実行する必要があります。 ＲＵＮＳＴＡＴＳはＡＮＤ ＩＮＤＥＸＥＳオプションつきで実行します。また、必要に応じて、WITH DISTRIBUTIONオプションつきで実行 し、非一様分布統計情報を収集してみます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 45-46 DB2 UDB (PC&Unix) データベース物理設計 ②データ容量の見積り 見積りの基礎となる前提の数値、算定根拠を明確にする 各テーブルのレコード件数を見積もる上で必要な数値の収集 コード類、マスター類の件数、１日あたりの処理件数 データの保持期間、データ増加率 ．．．etc. お客様にAuthorizeされた前提の数値であることが重要 前提に変更があれば、都度再見積り可能なようにワークシートにまとめておく 見積りの対象 テーブル ユーザー表データ 長形式フィールドのデータ ／ラージ・オブジェクト (LOB) データ 索引 後にパフォーマンスチューニングを行う際に索引が追加されることも考慮し余裕を見る 表スペース ページ・サイズの決定 システム・カタログ ユーザー表スペース 一時表スペース（システム／ユーザー） ログ・ファイル・スペース あくまでも見積りであり、テスト環境のDBでデータを格納し確認 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ②データ容量の見積り 論理設計のアウトプットであるテーブルをを作成した場合に、どれだけのディスク容量が必要となるかを見積もる必要があります。 この容量見積もりは、テーブルに含まれるデータだけではなく、索引のデータも含まれます。後段階でパフォーマンスチューニング を行う際にインデックスが追加されることも考慮し、索引を作成するテーブルスペースの容量には余裕を持たせる必要がありま す。 見積り作業を開始するにあたっては、テーブルや索引の定義が決定していることは言うまでもありませんが、見積りの基礎となる 前提の数値（コード類、マスター類の件数、１日あたりの処理件数、データの保持期間、データ増加率．．．etc）や算定根拠を明確 にする必要があります。 各テーブルのレコード件数を見積もる上で必要な数値の収集は、お客様にAuthorizeされた前提の数値であることが重要です。 前提に変更があれば、都度再見積りが必要となりますので、再見積りが容易になるようにワークシートにまとめておきましょう。 データ容量見積りの対象には、以下のようなオブジェクトがあります。 テーブル ユーザー表データ 長形式フィールドのデータ ／ラージ・オブジェクト (LOB) データ 索引 後にパフォーマンスチューニングを行う際に索引が追加されることも考慮し余裕を見る 表スペース ページ・サイズの決定 システム・カタログ ユーザー表スペース 一時表スペース（システム／ユーザー） ログ・ファイル・スペース（ログ・ファイル・スペースの詳細は後述） ログ情報には以下の内容が含まれています。 SQL ステートメント 変更された全てのデータ コミット / ロールバック データベース・オブジェクトのサイズは、正確に見積もることができません。サイズの見積もりを難しくする原因は、ディスクのフラ グメント化によって発生するオーバーヘッド、フリー・スペース、および可変長列の使用などです。これは、列タイプや行の長さが広 い範囲で異なる可能性があるためです。まずデータベースのサイズを見積もってから、テスト・データベースを作成し、それに標準 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 的なデータを入れてみてください。 47-48 DB2 UDB (PC&Unix) データベース物理設計 テーブル／索引の容量見積り テーブル容量概算：（論理レコード長＋10）×レコード件数×安全率 論理レコード長 各データ項目のデータ・タイプ、桁数から各項目の物理サイズを算出。可変長の場合は、平均長を採用。 NULL値を許す場合１バイト、可変長の場合4バイトを各該当の列項目あたり加算 すべてを合計したのが1行あたりの論理レコード長 レコード件数 保存期間、データ増加率も加味 安全率（余裕率） PCTFREEの割合やAPPENDモードであるかも考慮する オーバーヘッド分（フラグメンテーションやオーバー・フロー・レコードの有無） Long列データは他の表データとは別のところに保管 LONG VARCHAR、LONG VARGRAPHICは24バイト、LOBは各列の情報(ポインターなど)のみを他データと共に持つ 必要なディスク容量 表スペースのページ・サイズを決定し、１ページあたりの行数から必要ページ数を導く（後述） 索引容量：(平均索引キー・サイズ＋９）×行数×安全率 基本的な算出方法の考え方はテーブルと同等 安全率 ノンリーフ・ページやフリー・スペースなどのオーバーヘッドのため、少なくとも２倍は必要 索引の作成時のソートに必要な一時スペース分を加味すると、３．２倍と言われている 後にパフォーマンスチューニングで索引が追加されることも考慮し余裕を見ておく (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： テーブル／索引の容量見積り ここで、レコード長を計算する場合、ヌルが可の場合1バイト、可変長の場合4バイトを加える必要があります。 論理レコード長は、各データ項目のデータ・タイプ、桁数から各項目の物理サイズを算出して、可変長の場合は、平均長を採用し ます。NULL値を許す場合１バイト、可変長の場合4バイトを各該当の列項目あたり加算し、これらをすべて合計したのが１行のレ コード長となります。 レコード件数は、保存期間、データ増加率も加味します。また、安全率としてPCTFREEの割合やAPPENDモードであるかの考慮 や、オーバーヘッド分（フラグメンテーションやオーバー・フロー・レコードの有無）も考慮します。 索引のキー長も算出方法は基本的にはテーブルと同等です。 後段階でパフォーマンスチューニングを行う際に索引が追加されることも考慮し、索引を作成するテーブルスペースの容量には余 裕を持たせる必要があります。 最終的に必要なディスク容量は、表スペースのページ・サイズを決定し、1行の長さから１ページあたりの行数を算出し、全データ 件数をその１ページあたりの行数で割ることにより、必要ページ数を導きます。（後述） 各データ・タイプ毎のサイズ データ・タイプ バイト INTEGER 4 SMALLINT 2 DOUBLE 8 DECIMAL 精度数/2＋1の整数部分 CHARACTER(n) n VARCHAR(n) n＋4 LONG VARCHAR 24 GRAPHIC(n) n×2 VARGRAPHIC(n) (n×2)＋4 LONG VARGRAPHIC 24 DATE 4 TIME 3 TIMESTAMP 10 LOB 最大ｻｲｽﾞ(ﾊﾞｲﾄ) LOB Descriptor ｻｲｽﾞ 1K 72 8K 96 64K 120 512K 144 4M 168 128M 200 512M 224 1G 256 1.5G 280 2G 316 ヌル値可能な列の場合、1バイト多くなります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 49-50 DB2 UDB (PC&Unix) データベース物理設計 解説： テーブル／索引の容量見積り LONG列データ long varchar, long vargraphic のデータです。 ファイル名は、SQLxxxx.LFです。 32KB + 4KB(アロケーションとフリースペース情報) 32KBのデータエリアの中は 512×2の累乗のセグメントにわかれています。 512、1024、2048、・・・、32KB LOBデータ SQLXXXX.LB 1024*2の累乗のセグメントずつ増えます。 1024、2048、・・・、64MB SQLXXXX.LBA アロケーションとフリー・スペース情報 64GB毎に4KBページ 1個 + 8MB毎に4KBページ1個。 COMPACTパラメータ(CREATE TABLE XXXX) LOBデータの将来の更新のために予めフリースペースを確保しないようにする。 LOBデータをより小さいセグメントに分割させます。 （パフォーマンスは悪くなりますが,ディスクスペースは少なくなります) not compactはスペースを確保します（デフォルト） LOBデータをひとつのセグメントの中に連続してとります 例) create table blob (col0 clob(10m) compact) 2500バイト/行 × 1000 行 load compact−3MB 2048＋1024バイトずつとります not compact -4MB 4096バイトずつとります (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 51-52 DB2 UDB (PC&Unix) データベース物理設計 参考：ＮULLとシステム・デフォルト値のデータ圧縮 目的 V8からの新機能 格納スペースの節約 新しいレコードフォーマットでNULL値の場合には実際のデータページを節約 NULL値でないSYSTEM Default値の場合にも実際のデータをページを節約 実際の領域を確保しない 結果として1 page上に入る行数を増やすことが可能 １page 255行の制約は緩和されたわけではない 圧縮指定方法 CREATE/ALTER TABLEでCompressの指定 (Table level) VALUES COMPRESS指定 NULLsおよび0-length varying-length data type (varchar, vargraphics, long varchar, long vargraphic, BLOB, CLOB, DBCLOB) は Insert/Update時にDisk上に保管されない (Column level) Compress system default指定 numerical 0s, blanksが圧縮可能 考慮点 内部的にフォーマットが変更されているため、条件によっては、容量が増大する カラムのオフセット情報等で各列２バイト確保される 圧縮したことにより、オーバーフローが発生する可能性があるので、メリットがあるかを充分検討する必要がある (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 参考：ＮULLとシステム・デフォルト値のデータ圧縮 New vs Old レコードフォーマット Data Type Byte count (new row format) ROW OVERHEAD Byte count (old row format). If column is nullable, add 1 more byte to the shown list 2 0 INTEGER SMALLINT BIGINT REAL DOUBLE DECIMAL 6 4 10 6 10 The integral part of (p/2)+3, where p is the precision 4 2 8 4 8 The integral part of (p/2)+1, where p is the precision CHAR(n) VARCHAR(n) LONG VARCHAR n+2 n+2 n n+4 GRAPHIC(n) VARGRAPHIC(n) n*2+2 (n*2)+2 22 24 n*2 (n*2)+4 LONG VARGRAPHIC 22 24 DATE TIME TIMESTAMP DATALINK(n) Maximum LOB length 1024 6 5 12 4 3 10 n+52 n+54 70 72 Maximum LOB length 8192 94 96 Maximum LOB length 65536 118 120 Maximum LOB length 524000 142 144 Maximum LOB length 4190000 166 168 Maximum LOB length 134000000 198 200 Maximum LOB length 536000000 222 224 Maximum LOB length 1070000000 254 256 Maximum LOB length 1470000000 278 280 Maximum LOB length 2147483647 314 316 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 53-54 DB2 UDB (PC&Unix) データベース物理設計 ページ・サイズの決定 テーブルを格納するページサイズを決定する データの行長・カラム数の制限 テーブル容量の制限 64/128/256/512GB(ページサイズ4K/8K/16K/32K) 格納効率も考慮する必要がある １ページには最大255行まで格納できる 使用可能なディスク容量 アプリケーションの特性（OLTP or DSS？）や管理面も考慮する ページ・サイズによる制限値 ページ・サイズ 行長 カラム数 テーブル容量 行数/ページ 4 KB 4005 Byte 500 64 GB 255 行 8 KB 8101 Byte 1012 128 GB 255 行 16 KB 16293 Byte 1012 256 GB 255 行 32 KB 32677 Byte 1012 512 GB 255 行 テーブルの分割も検討する UNION ALL VIEWに対する参照（V6,V7でも可） UNION ALL VIEWに対する更新、INSERTも可（V8） (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ページ・サイズの決定 ページ・サイズ テーブルに必要となるディスク容量を計算する時に、どのページサイズを使用するかが非常に重要な要素になります。 DB2 UDB V7、V8では、4K/8K/16K/32Kバイトの４種類のページサイズをサポートします。 1行のレコードが複数のページにまたがることはできません。 1レコードの長さがページ内に収まらない場合は、ページサイズを大きくして下さい。 テーブルの最大容量の制限 テーブルの最大容量は64/128/256/512GBバイト(それぞれのページサイズは4K/8K/16K/32Kバイト) 格納効率 １ページには最大255行まで格納できるという制限 １行のサイズが100バイトのテーブルがあった場合、32KBページでは最大約 (32000 ÷ 100) 320行を1ページに格納でき るはずだが、この制限によって（320 - 250）約70行分のデータ領域にはデータが格納されず使われない無駄な領域になり ます。 例えば、レコードサイズがLOB(Large Object:BLOB,CLOB,DBCLOB,LONG VARCHAR)を含まない5000バイトのテーブル があった場合、4KBページではテーブルを作成することができません。5000バイトの長さを持つテーブルを作成するために は、少なくとも8Kバイトのページサイズを使用する必要があります。しかしこの場合、8Kバイトページのうち5000バイトにし かデータが書かれない為、残りの3000バイトは使用されない無駄な領域になります。このようなケースの場合、16KBペー ジという選択肢もあります。16KBページには、5000バイト長のレコードは3レコード入ります。残りは1000バイトとなり、使用 されないスペースを抑えることができます。 使用可能なディスク容量との兼ね合い考慮する。 行のランダム読み取り および 書き込みを実行するOLTPアプリケーションは、不必要な行に使用するバッファーページを少な くするために小さいページサイズを使用するようにしてください。 一度に多くの連続した行にアクセスするDSSアプリケーションは、指定された数の行を読み取るのに必要な入出力要求の数を 減らすように大きなページサイズを使用するようにしてください。 異なるページ・サイズによる考慮点 バッファプール、一時表スペースはページサイズ毎に必要 USEオプションを使用した再編成では、 一時表スペースは表スペースと同じページサイズである必要がある 拡張ストレージは大きなページサイズに合わせる バックアップを異なるページサイズに復元することは出来ません (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 55-56 DB2 UDB (PC&Unix) データベース物理設計 参考：UNION ALL VIEWによるテーブル分割 UNION ALLビューとは 複数のの結果表を組み合わせて新たな結果表として定義されたView UNION ALLを指定すると結果は該当表のすべての行から構成される テーブル分割の目的 テーブルスペースサイズの制限による分割 履歴データ等のレンジ・パーティションを実現 データ削除を分割されたテーブル単位のIMPORT REPLACEなどで実行できる REORGなどの運用をテーブル毎に個別に行える VIEW定義の方法 CREATE VIEW文中のSELECT文で、WHERE文節によって値を制限 このVIEWに対するINSERTは不可 表に対するCONSTRAINTによって値を制限 INSERTを使うためにはCONSTRAINTが必要（V8で機能拡張） (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 参考：UNION ALL VIEWによるテーブル分割 UNION ALLビューの例 CREATE TABLE Q1(order DATE,month SMALLINT,item VARCHAR(10)); ALTER TABLE Q1 ADD CONSTRAINT q1ckc1 CHECK (MONTH BETWEEN 1 AND 3); ORDER MONTH ITEM 2003-01-01 1 ｘｘｘｘ テーブル Q1 2003-02-01 2 ｘｘｘｘ 2003-03-01 3 xxxx CREATE TABLE Q2(order DATE, month SMALLINT,item VARCHAR(10)); ALTER TABLE Q2 ADD CONSTRAINT q2ckc1 CHECK (MONTH BETWEEN 4 AND 6); ORDER MONTH ITEM 2003-04-01 4 ｘｘｘｘ テーブル Q2 2003-05-01 5 ｘｘｘｘ 2003-06-01 6 xxxx CREATE TABLE Q3(order DATE, month SMALLINT,item VARCHAR(10)); ALTER TABLE Q3 ADD CONSTRAINT q3ckc1 CHECK (MONTH BETWEEN 7 AND 9); ORDER MONTH ITEM 2003-07-01 7 ｘｘｘｘ テーブル Q3 2003-08-01 8 ｘｘｘｘ 2003-09-01 9 xxxx CREATE TABLE Q4(order DATE, month SMALLINT,item VARCHAR(10)); ALTER TABLE Q4 ADD CONSTRAINT q4ckc1 CHECK (MONTH BETWEEN 10 AND 12); ORDER 2003-10-01 2003-11-01 2003-12-01 MONTH 10 11 12 ITEM ｘｘｘｘ ｘｘｘｘ xxxx テーブル Q4 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 57-58 CREATE VIEW VIEW_YEAR(order, month,item) AS SELECT * FROM Q1 UNION ALL SELECT * FROM Q2 UNION ALL SELECT * FROM Q3 UNION ALL SELECT * FROM Q4; ビュー VIEW_YEAR ORDER 2003-01-01 MONTH ITEM 1 ｘｘｘｘ 2003-02-01 2 ｘｘｘｘ 2003-03-01 3 ｘｘｘｘ 2003-04-01 4 xxxx 2003-05-01 5 ｘｘｘｘ 2003-06-01 6 ｘｘｘｘ 2003-07-01 7 ｘｘｘｘ 2003-08-01 8 ｘｘｘｘ 2003-09-01 2003-10-01 9 10 ｘｘｘｘ ｘｘｘｘ 2003-11-01 11 ｘｘｘｘ 2003-12-01 12 ｘｘｘｘ DB2 UDB (PC&Unix) データベース物理設計 参考：UNION ALL VIEWによるテーブル分割（続き） UNION ALL VIEW 使用上の注意点 アクセス・パスを充分確認した上で使用する VIEWへのSELECTを表へのSELECTに変換するのはオプティマイザーである。 オプティマイザーが解らない場合は、表を特定できない。その場合、全ての表にアクセスしてしまう 表を特定できない例 照会の条件が、CHECK制約やVIEW定義のWHERE条件に合致しない CHECK制約にMONTHなどの関数を使用．．．等 オプティマイザーはVIEW中のSQL文を展開してから評価するので、展開後のSQL文が長くな る可能性がある ステートメント・ヒープ、アプリケーション・ヒープがより多く必要 SQL文の長さの制限(64K)を超える可能性がある 長すぎるSQL文でオプティマイザーがあきらめて、単純なアクセスパスに走る可能性がある UNION ALL VIEWへの更新系処理はオーバーヘッドが生じる 各テーブルへのチェック処理 更新処理は、なるべく実体のテーブルに対して直接処理を行う (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 参考：UNION ALL VIEWによるテーブル分割（続き） オプティマイザ-によって表が特定される例 オプティマイザ-によって表が特定されない例 Create Table Q1_2(Order Date,month smallint,item varchar(10)); alter table q1_2 add constraint q1chck2 check (month between 1 and 3); Create Table Q1_1(Order Date,month smallint,item varchar(10)); alter table q1_1 add constraint q1chck1 check (MONTH(order) between 1 and 3); Create Table Q2_2(Order Date,month smallint,item varchar(10)); alter table q2_2 add constraint q2chck2 check (month between 4 and 6); Create Table Q2_1(Order Date,month smallint,item varchar(10)); alter table q2_1 add constraint q2chck1 check (MONTH(order) between 4 and 6); Create Table Q3_2(Order Date,month smallint,item varchar(10)); alter table q3_2 add constraint q3chck2 check (month between 7 and 9); Create Table Q3_1(Order Date,month smallint,item varchar(10)); alter table q3_1 add constraint q3chck1 check (MONTH(order) between 7 and 9); Create Table Q4_2(Order Date,month smallint,item varchar(10)); alter table q4_2 add constraint q4chck2 check (month between 10 and 12); Create Table Q4_1(Order Date,month smallint,item varchar(10)); alter table q4_1 add constraint q4chck1 check (MONTH(order) between 10 and 12); Create view VIEW_YEAR2(order,month,item) as select * from Q1_2 union all select * from Q2_2 union all select * from Q3_2 union all select * from Q4_2; Create view VIEW_YEAR1(order,month,item) as select * from Q1_1 union all select * from Q2_1 union all select * from Q3_1 union all select * from Q4_1; Original Statement: -----------------select * from view_year2 where month = 1 Original Statement: -----------------select * from view_year1 where month = 1 Optimized Statement: ------------------SELECT Q1."ORDER" AS "ORDER", Q1."MONTH" AS "MONTH", Q1."ITEM" AS "ITEM" FROM ADMINISTRATOR.Q1_2 AS Q1 WHERE (Q1."MONTH" = 1) Optimized Statement: ------------------SELECT Q9.$C0 AS "ORDER", Q9.$C1 AS "MONTH", Q9.$C2 AS "ITEM" FROM (SELECT Q1."ORDER", Q1."MONTH", Q1."ITEM" FROM ADMINISTRATOR.Q1_1 AS Q1 WHERE (Q1."MONTH" = 1) UNION ALL SELECT Q3."ORDER", Q3."MONTH", Q3."ITEM" FROM ADMINISTRATOR.Q2_1 AS Q3 WHERE (Q3."MONTH" = 1) UNION ALL SELECT Q5."ORDER", Q5."MONTH", Q5."ITEM" FROM ADMINISTRATOR.Q3_1 AS Q5 WHERE (Q5."MONTH" = 1) UNION ALL SELECT Q7."ORDER", Q7."MONTH", Q7."ITEM" FROM ADMINISTRATOR.Q4_1 AS Q7 WHERE (Q7."MONTH" = 1) ) AS Q9 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 59-60 DB2 UDB (PC&Unix) データベース物理設計 表スペースの容量見積り システム・カタログ表スペース データベース作成時、デフォルトで約3．5MB ユーザー表スーペース SMS表スペースは、DMS表スペースに比べ、オーバーヘッドが少ない テーブル／索引を格納するページ・サイズの決定（4K/8K/16K/32K） 各テーブル容量から必要ページ数を算出 ROUND DOWN(ページ・サイズ/(論理レコード長)) = １ページあたりの行数 (レコード件数/１ページあたりの行数) * 安全率 = 必要ページ数 安全率：オーバーヘッド分、PCTFREEの分や、同じ表スペース内でREORGする場合なども考慮 一時表スペース 一番大きく使用すると思われるケースで検討する システム一時表スペース JoinやSortを行うアプリケーションやREORG、LOADなどの運用次第で大きく変わる SMS表スペースの使用が望ましい DMSは、一時表の作成時に多くのオーバーヘッドがある SMS ではディスク・スペースを動的に割り当てるが、DMS では事前に割り当てられてしまう。 ユーザー一時表スペース 宣言済み一時表を使用する場合 デフォルトの ユーザー一時表スペースはないため、作成する必要がある 一時表スペースの見積りは最終的には事前に入念なテストを行って確認する (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 表スペースの容量見積り データベースが作成されると、システム・カタログ表も作成されます。システム表は、データベース・オブジェクトと特権がデータ ベースに追加されるたびに増加します。最初の時点では、約 3.5 MB のディスク・スペースが使用されます。 カタログ表に割り当てられるスペースの量は、表スペースのタイプとカタログ表が含まれる表スペースのエクステント・サイズに よって異なります。たとえば、エクステント・サイズが 32 の DMS 表スペースを使用した場合、最初の時点ではカタログ表に 20 MB のスペースが割り振られます。 DMS表スペース - 必要な最小ページ値 表スペースの作成 表スペース メタ データ コンテナー・タグ 1 ページ/コンテナー 表スペースのヘッダー 1 エクステント スペース・マップ 1 エクステント オブジェクト表データ 1 エクステント 3 エクステント + 1 ページ 表(オブジェクト)の作成 エクステント・マップ 1 エクステント データのエクステント 1 エクステント コンテナーで最小限必要なスペース = 5 エクステント + 1 ページ (最初のオブジェクトを作成した時) EXTENTSIZE = 32 ページなら、 5*32＋1＝161ページがコンテナーに必要 表スペースの名前を指定しない場合、REORGしようとする表を含む表スペースにその表の作業コピーを保管します。 一時表スペースを使って表を再編成する場合、 一時表スペースのページ・サイズは表のページ・サイズと一致しなければなりま せん。 宣言済み一時表は、独自のユーザー一時表スペース・タイプの中にのみ作成されます。デフォルトのユーザー一時表スペースは ありません。 一時表スペースの必要なスペースの量は照会および戻される表のサイズや照会アプリケーション、REORG、LOADなどの運用に 依存するため、正確に見積もることは難しいため、事前にテストで確認することが必要です。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 61-62 DB2 UDB (PC&Unix) データベース物理設計 ③データベースの分割とインスタンスの構成 インスタンス DB2のオブジェクトの最も大きな単位 論理的なデータベース・マネージャー環境 Unix環境 インスタンス データベース db2syscプロセスを中心としたプロセス群 Windows環境 表スペース(SMS) 「DB2 - インスタンス名」 サービス 全てのオブジェクトが含まれる DB2 UDBの起動／停止の単位 表 索引 表スペース(DMS) 表 LOB db2startコマンド／db2stopコマンド １マシンに複数インスタンスの作成が可能 db2icrtコマンドで作成 表スペース(DMS) データベース 有機的にまとめられた表スペース、表や索引の集まり 一つのインスタンスに複数のデータベースを作成する ことが可能 索引 表スペース(DMS) LOB create database コマンド 接続(CONNECT)の対象となる バックアップ・リストアの最大単位 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ③データベースの分割とインスタンスの構成 インスタンス 一つのデータベース・マネージャー構成ファイルを使用して稼動する、データベース・マネージャー環境のことです。 インスタンスは、DB2 UDBを構成するオブジェクト群の中で、最も大きな単位です。 db2startで起動し、db2stopで停止するのは、このインスタンスです。(管理サーバーの場合、db2admin start/db2admin stop) データベースのエンジンともいえる、db2syscプロセスが立ち上がる単位ということもできます。 インスタンスは、一台のマシン上に、複数、持たせることができます。しかし、一つのアプリケーション環境から扱えるのは、一 つのインスタンス環境のみです。 db2startで起動されるインスタンス、およびアプリケーション環境で使用するインスタンスは、以下で決まります。 環境変数 DB2INSTANCE に指定されているインスタンスが現行インスタンスとして使用されます。 PC環境では、環境変数｢DB2INSTANCE｣が設定されていない場合があります。 その場合、レジストリー変数｢DB2INSTDEF｣に設定されているインスタンス(デフォルトではDB2)が現行インスタンスとして 使用されます。 DB2INSTDEFは、グローバル・レベルのレジストリー変数です。 これを変更するには以下を実行します。 db2set db2instdef=新インスタンス名 -g データベース DB2 UDBのデータベースには、様々なオブジェクトが含まれますが、ユーザーから見るときには、表や索引の集合体と言える でしょう。 一つのインスタンス上に、複数のデータベースを作成することが可能です。 データベースは、接続・運用上および許可の単位です。 connectコマンドで接続するデータベースの名前を省略した場合、デフォルトでは、DB2DBDFTレジストリー変数に設定されて いるデータベース名が使用されます。 connect toでデータベースに最初に接続する時には、ログ・ファイルやバッファープールのアロケーションなど初期作業が行な われますので、それ以降の接続に比べて時間がかかります。 最初の接続に時間をかけたくない場合には、db2start後にactivate databaseコマンドで初期作業を行なわせて下さい。 データベースは、バックアップ取得や、CONNECT特権の単位でもあります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 63-64 DB2 UDB (PC&Unix) データベース物理設計 インスタンス構成 インスタンスの分け方 アプリケーション構成 開発環境用と本番環境用 運用管理面 管理者の権限(SYSADM、SYSCTRL、SYSMAINT)を持つユーザーを分けたいとき インスタンスごとにデータベース・マネージャーの構成を最適化する 起動/停止のタイミングを分けたい（サービス時間、運用時間の違い） 可用性 エンジン動作部分に影響を与える様なトラブル発生時に、影響範囲を小さくしたい その他考慮点 UNIX環境：作成するインスタンスのビット単位（64ビット／32ビット）指定 同居させる他のプロダクトの要件にも注意 V７環境とV8環境間で、異なるアドレッシングビット数のクライアント＆サーバーは直接接続不可 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： インスタンス構成 インスタンスを分ける例としては以下の様な場合があります。 開発用と本番用 管理者の権限(SYSADM、SYSCTRL、SYSMAINT)を持つユーザーを分けたいとき DBMSのエンジン動作をコントロールしたい場合 起動/停止のタイミングを分けたい（運用、サービス時間） 可用性の観点で、エンジン動作部分に影響を与える様なトラブル発生時に、その影響をできるだけ受けさせたくない ： など １つのマシンに複数インスタンスを作成することが可能ですが、インスタンスごとに、追加のシステム・リソース (仮想メモリーとディ スク・スペース) が必要になります。また、追加インスタンスを管理するためにさらに管理が必要になります。 インスタンス作成の際は、ビット単位（64ビット／32ビット）を指定しますが、同じマシン上で稼動する他のソフトウェアの稼動要件も 考慮する必要があります。（例えば、32ビットカーネルしか対応していないS/Wなど） また、V７環境とV8環境間で、異なるアドレッシングビット数のクライアント＆サーバーは直接接続は出来ないので注意が必要で す。DB2 UDBのV7の32ビット・インスタンス環境と接続する場合、V8でも32ビット・インスタンスにあわせる必要が生じます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 65-66 DB2 UDB (PC&Unix) データベース物理設計 データベース作成の考慮点 データベースの分割の目安 アプリケーション構成（業務内容） 同時に処理する要件があるか パフォーマンスの観点から一つにすることも検討 分割されていると２フェーズ・コミット必要 JOIN不可(連合DBを使用の場合は可能） 運用管理（バックアップ／リストア）の面から検討 処理時間 運用時間帯 可用性 テスト環境ではフェーズやチーム単位で分割 コード・セット別 Unicodeデータベース 分割する場合、レプリケーション機能の検討も必要か 作成時のCOLLATE USING句指定 日本語環境でデータを五十音順に照合したい場合はIDENTITYを指定 デフォルトはSYSTEM（辞書順） 作成後は変更不可 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： データベース作成の考慮点 データベースの分け方 まずアプリケーションの構成から決定します。業務が全く異なる場合は、別データベースにするべきですが、それぞれに属す る表同士に何らかの関連があって同時に処理する必要がある場合は、パフォーマンスの観点からも同じデータベースにしてし まうことも検討します。 バックアップ/リストアといった運用管理の面からも検討します。 複数に分けた場合、１つがダウンしても別のデータベースは使用可能ですので、可用性という点では優位です。 このほかに、データベースを分ける例としては以下の様な場合があります。 開発用と本番用 共用する表を持たない異なるシステムの場合（表をJOINしたい場合は通常分けない） Unicodeデータベースなど、コードセットの異なるDBが必要･･･ など CHAR、VARCHAR、LONG VARCHARの照合(ソート、比較など)においては、値の重み付けを考慮する必要があります。 重み付けは、CREATE DATABASEのCOLLATE USING句で指定する事ができます。 SYSTEM(デフォルト)：現行のテリトリーに基づいた照合順序 (辞書順) 例：a(X'61')、A(X'41')、b(X'62')、B(X'42')、c(X'63')、C(X'43')、・・・ COMPATIBIRITY：DB2 V2の照合順序に同じ (電話帳順) 例：A、a、B、b、C、c・・・ IDENTITY：バイト単位で比較 (文字コード順) 例：A、B、C、・・・、a、b、c・・・ 日本語などのダブルバイトの文字については、1バイトずつに分割して照合が行われます。 SYSTEMの場合の日本語例： ヂ(X'8361')、ア(X'8341')、ッ(X'8362')、ィ(X'8342')、ツ(X'8363')、イ(X'8343')、・・・ COMPATIBIRITYの場合の日本語例： ア(X'8341')、ヂ(X'8361')、ィ(X'8342')、ッ(X'8362')イ(X'8343')、ツ(X'8363')、・・・ IDENTITYの場合の日本語例： ァ(X'8340')、ア(X'8341')、ィ(X'8342')、イ(X'8343')、・・・、チ(X'8360')、ヂ(X'8361')、ッ(X'8362')、ツ(X'8363')、ヅ(X'8364')、・・・ 日本語環境では五十音順にデータを照合したい場合には、COLLATE USING句にIDENTITYを指定してデータベースを作成する 事をお薦めします。 COLLATE USINGに指定した値は、一旦データベースを作成した後に変更する事はできませんので注意して下さい。 GRAPHICタイプのデータについては、COLLATE USING句の指定に関わらず、文字コード順に照合が行われます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 67-68 DB2 UDB (PC&Unix) データベース物理設計 ④テーブルの分類とテーブルスペースの構成 表（テーブル）スペース 表のデータを格納するための論理的な領域媒体 実際にデータを格納する物理的な媒体をコンテナーという コンテナーの実体はファイルシステムのディレクトリ、ファイル、または論理ボリュームなどのローデバイス 一つの表スペースを１つ以上のコンテナーで構成 データベース内に作成される表スペース システム・カタログ表スペース，システム/ユーザー一時表スペース，ユーザーデータ用表スペース テーブルスペースはバックアップの最小単位 データベース 表スペース 表 バッファープール 表スペース バッファープール 索引 コンテナー０ 表 コンテナー３ コンテナー４ コンテナー１ コンテナー２ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ④テーブルの分類とテーブルスペースの構成 表スペースは、表のデータを記憶するための論理的な領域媒体です。 実際に表のデータが物理的に格納されるのは、表スペースに紐付けされたコンテナーになります。 コンテナーは、表スペースのタイプにより、ディレクトリーやファイル、デバイスといった形態を取ります。 一つの表スペースに複数のコンテナーを割り当てることができます。 データはコンテナー間で平均的に割り振られます。 一つのコンテナーは、一つの表スペースにしか属することはできません。 表スペースへのデータの書き出しは、エクステントと呼ばれる割り当て単位に行われます。(デフォルトは32ページ) (詳細は後述) create databaseを実行すると、デフォルトでは３つのSMS表スペースが自動的に生成されます。 SYSCATSPACE：システム・カタログ表スペース システム・カタログ表、およびその索引が入っている表スペースです。 データベース作成時に作られ、一旦作られた後は、変更や削除することができません。 TEMPSPACE1：システム一時表スペース JoinやSort時に一時的にデータが入る表スペースです。 USERSPACE1：ユーザー・データ用表スペース ユーザーのデータや索引が入れられる表スペースです。 表スペースを指定せずにcreate tableを実行すると、他のユーザー・データ用表スペースが無い場合は、デフォルトで、こ の表スペースが使われます。 既に他のユーザー・データ用表スペースが作られている場合には、最初に作られた表スペースが使われます。 表スペース単位でバックアップ/リストアを行うことも可能です。 ロールフォワード回復不可能な場合(循環式ロギング、またはキャプチャー用ロギング)は、データベース単位のバックアップのみ 可能です。 データベース構成パラメータ：logretain=noまたはcapture かつ userexit=off ロールフォワード回復可能な場合(アーカイブ式ロギング)は、表スペース単位のバックアップ/リストアが可能です。 データベース構成パラメータ：logretain=recovery または userexit=on (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 69-70 DB2 UDB (PC&Unix) データベース物理設計 ④テーブルの分類とテーブルスペースの構成（続き） 適切な表スペースを選択する ファイル管理のタイプによる表スペースの種別 SMS：オペレーティング・システムのファイル・システムによるファイル管理 DMS：データベース・マネージャーによるファイル管理 保管データのタイプによる表スペースの種別 Regular：通常のデータ、索引用 Long：LOB、LONGデータ用 Temporary：一時表用 システム一時表スペース ユーザー一時表スペース 表スペースのページ・サイズ 4KB，8KB，16KB，32KB 4KB以外の表スペース作成の場合、事前に同じページ・サイズのバッファープールと一時表スペースを作成する (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ④テーブルの分類とテーブルスペースの構成（続き） ファイル管理のタイプにより、以下の２種類の表スペースに分類することができます。(詳細は後述) SMS(System Managed Storage) 各オペレーティング・システムのファイル・システムがファイルを管理します。 コンテナーとしては、ディレクトリーを設定します。 DMS(Database Managed Storage) データベース・マネージャーがファイルを管理します。 コンテナーとしては、ファイル、またはデバイスを設定します。 保管データのタイプにより、以下の3種類に表スペースを分類することができます。 Regular：通常のデータ、索引用 データと索引を同じ表スペースに入れることもできますし、別表スペースに分けることも可能（DMS）です。 Long：LOB、LONGデータ用(DMSのみ作成可能) LONG VARCHR、LONG VARGRAPHIC、BLOB、CLOB、DBCLOB専用の表スペースです。 これらのデータは、データと一緒にRegular表スペースに共存させることも可能です。 Temporary：一時表用 システム一時表スペース JoinやSortで一時的にデータが入る表スペースです。 一時表スペースは、データベース上最低一つは必要です。一つしかない時には、削除しようとするとエラーとなります。 4KB以外のページを持つ表スペースがある場合、そのページに合せた一時表スペースも作っておいて下さい。 ユーザー一時表スペース 省略時にはデータベース作成の時点で作成されません。Global Temporary Tableが使用する表スペースです。 4KB以外のページを持つ表スペース（表）がある場合、そのページに合せた一時表スペースも作っておいて下さい。 Global Temporary Table(ユーザー定義一時表) アプリケーションは、データベースに接続している間、一時的な表を作成して使用できます。 一時表を定義するには、DECLARE GLOBAL TEMPORARY TABLE ステートメントを使用します。 ユーザー定義一時表が保持されるのは、 アプリケーションがデータベースから切断されるまでの間だけです。 アプリケーショ ンが終了したりデータベースから切断されたりすると、 表の中のデータはすべて削除され、表は暗黙的に除去されます。 この表の記述は、システム・カタログには現れません。 したがって、この表を他のアプリケーションのために保持したり、 他の アプリケーションと共用したりすることはできません。 ユーザー定義一時表は、ロックとログ記録を回避するので、一時表を活用するアプリケーションは大幅なパフォーマンス改善 を見込めます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 71-72 DB2 UDB (PC&Unix) データベース物理設計 ④テーブルの分類とテーブルスペースの構成（続き） 表スペースの分割の指針 パフォーマンスを考慮し、バッファープールの割り当てを検討 I/Oの並列処理を考慮し、データの物理配置に合わせて表スペースを構成 バックアップの単位、LOADの並行処理等の運用面で検討 テーブルをグルーピング、表スペースを分ける １．できる限りバッファプールに読み込むことが望ましいテーブル 頻繁に読み書きされるトランザクション系データ ２．バッファプールには読み込む必要が無いデータ アプリケーション活動の履歴的な、一度書いたらほとんど変更／参照されないデータ ３．１と２の中間 ４．小さなテーブルはある程度の単位で一つの表スペースにまとめる ５．バックアップ取得の頻度で別の表スペースにする 大量の更新があるテーブル⇒頻繁なバックアップ取得必要 変更が非常に少ないテーブル⇒頻繁なバックアップ取得不要 LOADを複数同時に実行するには、別々の表スペースにする（特にV7） V7では、LOAD実行中はテーブルスペースをQuiesce（静止）状態にするため、該当の表のみ ならず、同一表スペース上の他の表にもアクセス不可 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ④テーブルの分類とテーブルスペースの構成（続き） 典型的な分け方は以下のようになります。 １．頻繁に読み書きされるトランザクション系データ ２．アプリケーション活動の履歴的な、一度書いたらほとんど変更されないデータ ３．１と２の中間 分類１のテーブルは、アプリケーションのパフォーマンスに大きな影響を与えます。ディスク資源とメモリ資源を十分に割り当て る必要があります。 分類２のテーブルは、容量的には大きくなりますが、一度書いた情報をあまり読まないため、メモリ資源は低く抑えることがで きます。このタイプのテーブルを全件検索するようなSQLが実行された場合、ディスクのアクセスが非常に多くなるので、パ フォーマンスが常時必要な分類１のテーブルとは異なる物理ディスクを割り当てることが理想的です。できればディスクへつな がるSCSIやファイバーチャネルなどのインターフェースも別であることが理想的と言えます。 分類３は、メモリ資源をそれなりに与える必要がありますが、優先順位では分類１より下になります。 表スペースの分け方には、以下のようなケースもあります。 頻繁に使用される表、それもアクセスされるデータがほぼ決まっている場合には、大き目のバッファープールを持った表ス ペースを割り当てます。 このときには、データの表スペースと、索引の表スペースとを分けるのもひとつの方法です。(DMSの場合) 大きな表に、ランダムにデータ・アクセスする場合には、小さいバッファープールを持った表スペースを割り当てます。 たまにしか使用されないアプリケーションからアクセスされる表には、小さいバッファープールを持った表スペースを割り当 てます。このような表は、まとめて一つの表スペースに入れるのもひとつの方法です。 参照制約，トリガーなど関連のあるテーブル同士は一つの表スペースにまとめます。 小さな表は全て同じ表スペースにまとめます。 バックアップを取得する単位で表スペースを分けます。 表スペース単位のバックアップは時間とリソースの節約になります。 大量の変更がある表スペースは頻繁にバックアップを取ります 変更が非常に少ない表スペースは時折バックアップを取ります。 V７では、LOAD実行時、テーブルスペースをQuiesce（静止）状態にし、表スペースと表スペース内の表すべてに対して Z-LOCK(超排他)を取得するため、LOADの並行処理を可能にするには表スペースを分ける必要があります。（V8では、LOAD中 も表に照会アクセスが可能であり、同じ表スペースの表については、アクセスが自由できます。） (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 73-74 DB2 UDB (PC&Unix) データベース物理設計 DB2の表スペースの特徴 SMS(System Managed Storage)表スペース オペレーティング・システムのファイル・システム・マネージャーが管理 表データと索引、Longデータは全て同じ表スペースを共有 管理が容易 コンテナーはディレクトリー ファイルは動的に拡張し、サイズの上限は以下によって決まる コンテナーの数 ファイル・システム/ドライブ/ファイルのOSの限界サイズ コンテナーは動的に追加不可 ファイル・システム/ドライブのサイズは増加可能 再定義は表スペース復元時に可能（表スペースのリダイレクション） 各コンテナーサイズは同じ大きさに データベース作成時にデフォルトで作成される 一時表スペースに推奨 CREATE DB DB1 on /database （データベース作成例） /database/owner/NODE0000/SQL00001/ SQLT0000.0/ システムカタログ SYSCATSPACE SQLT0001.0/ 一時表スペース TEMPSPAC E1 SQLT0002.0/ ユーザー表用 USERSPACE1 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： DB2の表スペースの特徴 SMSの特徴 SMSでは、表スペースはファイルシステムのディレクトリに相当し、表や索引はそれぞれ別々のファイルになります。SMS表ス ペース（ディレクトリ）を作成したファイルシステムが許す限り、表に対してデータを追加することができます。つまり、表スペー スの大きさはファイルシステムの大きさに依存します。 SMSでは表や索引およびLOBやLONG VARCHARなどの長形式のデータを含むロング形式のデータを全て同じ表スペースに 格納する必要があります。 複数のディスクにIOを分散させる為に、１つの表スペースに対してコンテナーを複数定義することがSMSでも可能ですが、コン テナーの追加や削除を行うことはできません。表スペースを作成するときのコンテナー定義を変更する為には、一度削除して 再作成する必要があります。 複数コンテナーによってSMS表スペースを作成した場合、どれかのファイルシステムが一杯になると表スペースはそれ以上の データを追加することができなくなります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 75-76 DB2 UDB (PC&Unix) データベース物理設計 DB2の表スペースの特徴 DMS(Database Managed Storage)表スペース データベース・マネージャーが記憶スペースを管理 作成時にスペースを割り当て コンテナーはファイル、デバイス ファイルの操作に対してはファイル・システムI/Oを使用 ロー・デバイスの操作に対しては直接I/Oを使用 柔軟なデータ配置 表用、索引用、および長形式のデータ用の表スペースを別々に作成することが可能 ディスクのIOを複数の物理ディスクに分散させることが可能 コンテナの追加/削除/拡張/縮小が可能 データは自動的に再バランス（オプションにより再バランスさせないことも可） 高パフォーマンス CREATE T ABLE ... in REGULAR1 index in INDEX1 long in LONG1 テーブル REGULAR1 インデックス INDEX1 長形式 LONG1 データ、 インデッ クス、 長形式を別々に配置するDMSの例 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： DB2の表スペースの特徴 DMSの特徴 DMSでは、表スペースは大きな１つのファイルまたはローデバイスに相当します。中をどのように使われているのかはOSから は確認できません。表スペースを作成する際に、あらかじめ必要な大きさを定義する必要があります。DMSではさらに２つのタ イプがあり、１つはDMSファイルともう一つはローデバイスです。 DMSファイルの場合は、ファイルシステム上に１つの大きなファイルが作成されますが、ローデバイスの場合はファイルシステ ムを経由せずにＤＢ２が直接IOを行います。 DMSでは、表用、索引用、および長形式のデータ用の表スペースを別々に作成することが可能です。それによって、ディスク のIOを複数の物理ディスクに分散させることが可能になります。 DMSでも複数のコンテナーを１つの表スペースに対して定義でき、さらに動的に追加することが可能です。表スペースを作成 後にコンテナーの大きさを変更することもでき、DB2 UDB V.8では小さくすることも可能になりました。 複数コンテナーからDMS表スペースを構成する場合、どれかのコンテナーが一杯になっても空いているコンテナーを探して書 き込もうとします。全てのコンテナーが一杯になった場合、それ以上データの追加はできません。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 77-78 DB2 UDB (PC&Unix) データベース物理設計 DMSとSMSの違い パフォーマンス DMSローデバイス＞DMSファイル＞＞SMS 表用、索引用、長形式のデータ用の表スペースを別々に作成できる Solaris環境では、パフォーマンス重視のデータにはロー・デバイスを含む DMS 表スペースを使用することを推奨 管理の容易さ SMS＞DMS SMSは、同じファイルシステムに複数表スペースを作成でき、表スペースごとに空きスペースの監視を行う必要ない 領域のアロケーション DMS SMS SMS:ページ単位 DMS:エクステント単位 データの挿入 既にア ロケーシ ョンされている データの挿入 ファイルシステムが空いているがアロケーションは挿入時 SMSとDMSのアロケーションの違い (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： DMSとSMSの違い ３つの表スペースを比較した場合、通常パフォーマンスは以下のようになります。 DMSローデバイス＞DMSファイル＞SMS ローデバイスとファイルDMSの差はそれほど大きくありませんが、DMSとSMSではパフォーマンスに大きな差が出る場合がありま す。 Solaris(TM) では、パフォーマンス重視のワークロード用にはロー・デバイスを含む DMS 表スペースを使用することが強く勧めら れています。 DMSはSMSに比べて、表用、索引用、長形式のデータ用の表スペースを別々に作成できるため、それぞれのディスクIOを分散さ せることが可能です。これによってディスクIOを効率化し、パフォーマンスを向上することが可能です。 また、DMSではデータが挿入された時にエクステント単位でデータが書き込まれます。またそのエクステントを書き込むのは既に 容量を確保して作成されたDMSファイルまたはローデバイスの中に書き込みます。この時にDMSファイルやローデバイス自身の 大きさは変わりません。 それに対してSMSでは、データの挿入はデフォルトではページ単位で書き込まれます。この動作はdb2empfaコマンドによってエク ステント単位に変更できますが、エクステントを書き込む度に、表および索引などに相当するファイルの大きさを拡張していく必要 があります。この動作によってSMSはDMSに比べて特に大量データの挿入に関してパフォーマンスが劣ります。 db2empfa - 複数ページ・ファイル割り振りの使用可能化コマンド データベースの複数ページ・ファイル割り振りを使用可能にします。 SMS 表スペースでの複数ページ・ファイル割り振りを使用 可能にすると、ディスク・スペースは、一度に 1 ページではなく、1 エクステントに割り振られます。 管理が容易なSMS SMSはパフォーマンス的にはDMSに劣ると言われていますが、同じファイルシステムに複数の表スペースを作成でき、表ス ペースごとに空きスペースの監視を行う必要がなく、管理が容易なため、開発系システムやパフォーマンスを必要としないテ スト系システムでよく使用されます。 SMSを本番システムに使用してはいけないわけではなく、パフォーマンスがそれほど必要ではなく、運用手順を簡易化する必 要性を優先する場合は、本番でSMSを使用するケースもあります。 Sun ClusterやHA/CMPなどのHA(High Availability)機能によって、ＤＢサーバーをテイクオーバーする場合、ファイルシステムを使 用すると、fsck(ファイルシステムのチェック)に時間がかかり、テイクオーバー時間が長くなるために、ローデバイスが多く使われて います。 DMSのコンテナーで最小限必要なスペース (最初のオブジェクトを作成した時)＝５エクステント+1ページ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 79-80 DB2 UDB (PC&Unix) データベース物理設計 参考：エクステント エクステント 表スペースにおけるコンテナー内の領域の割り振り単位（ページ） DMSは一度にエクステント全体をアロケートし、その後でエクステント内のページを使用 SMSはextent_szの値になるまで一度に１ページずつアロケートする 表スペース作成時に指定 データベース・レベルではDFT_Extent_SZを定義、表スペースではEXTENTSIZE を定義 作成後は変更が出来ない データはラウンド・ロビン方式でコンテナーに書き込む コンテナー 0 0 コンテナー1 4K 2 1 3 エクステント Extent = 32ページ (デフォルト) 表スペース B (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 参考：エクステント 表スペースへのデータの書き出しは、エクステントと呼ばれる割り当て単位（ページ）に行われます。(デフォルトは32ページ) エクステント・サイズは、表スペース作成時に設定（extent size）し、それ以降変更することができません。作成時に指定しない場 合、データベースの設定(DFT_Extent_SZ)を使用して表スペースを作成します。 DMSは一度にエクステント全体をアロケートし、その後でエクステント内のページを使用します。 エクステントがいっぱいになると、次のコンテナーに行き、そこでまた、エクステントをアロケートします。 これが繰り返されます。 SMSはextent_szの値になるまで一度に１ページずつアロケートします。 extent_szに達すると、次のコンテナーに行き、ページをアロケートします。 これが繰り返されます。 SMS表スペースでも、一度にエクステントをアロケートすることも可能です。そのためには、db2empfaユーティリティーを使ってdb cfg flagの｢マルチ・ページ・ファイル・アロケーション可｣をYESに設定して下さい。 コンテナーへの書き出しはラウンドロビン方式で行われます。 エクステントサイズは表の大きさに応じて決めてください。 急激に大きくなっていく大きな表に対しては大きくします。 一つの表スペース内に多くの小さな表があるときには小さくします。（もしくはSMSを使用します。） (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 81-82 DB2 UDB (PC&Unix) データベース物理設計 ブランク・ページです ブランク・ページです 83-84 DB2 UDB (PC&Unix) データベース物理設計 ⑤その他のオブジェクトの配置 どの処理を最も優先的に速くしたいかを考えて配置する ログ アクティブログとアーカイブログ 一時表スペース ソートに使用 再編成・ロード・インデックス作成時に大量に使用 カタログ表スペース 通常はそれほど大きくならないので、デフォルトのSMSでも可 ワーク/バックアップ領域など ロード元やバックアップ先などのファイルシステムのパフォーマンスが必要な場合 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ⑤その他のオブジェクトの配置 テーブルを格納する表スペースをディスク上に配置するためには、それ以外のオブジェクトをどのように配置するかを決定する必 要があります。 テーブル以外のオブジェクトをどのように配置するかによって、データベースのパフォーマンスに大きく影響を与えます。 ログへの書き込み速度はデータベースの更新処理のパフォーマンスにとって非常に重要です。 また、一時表スペースのパフォーマンスによって、LOADやインデックス作成、再編成などの処理の時間が大きく変わってきます。 バックアップの時間が非常に重要な場合などは、バックアップ先のI/Oを高速化する必要があります。 また、ロード先の表スペースのI/Oは非常にチューニングされていても、入力ファイルのI/Oが遅いのでは、ロード処理自体は遅く なってしまいます。 これらのオブジェクトをどのように配置するかという点はシステム全体のパフォーマンスにとって非常に重要になります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 85-86 DB2 UDB (PC&Unix) データベース物理設計 ログの配置 アクティブログに対する書き込み速度はアプリケーションのレスポンス に直接影響する アクティブログは安全性を考えると、２重化した方がよい （アクティブログの障害＝データベースの障害） である。 AIXでは、UDBの２重ログを使用した場合、高レートトランザクションのパフォーマンスが悪くな る障害が存在 問題番号IY40954→V7 FP9 / V8 FP2では未対応 UDBの２重ログは両方に書き出して終了なので、どちらか１つのディスクが遅い場合、ログへ の書き込みは遅くなる。 AIXの場合現時点では、OSのミラーリングを推奨 非同期 データ 書き出し ディスク容量が許すのであれば バッファプール アクティブログ専用のディスク上に配置する ESSの場合ランクも別の方が理想的 SCSI / Fibreなどのチャネルもできれば別 変更され たデータ RAID5ではなく、ミラーリングを使用する ストライピングによって書き込みを速くする データの 更新処理 コミットしたら更 新内容を 必ず書き出す ログ (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ログの配置 ログはDB2にとって重要なオブジェクトです。特にアクティブログは非常に重要で、アクティブログが損傷したことはデータベース自 身が損傷したことを意味します。 つまり、アクティブログが壊れたら、データベースが壊れたのと同じです。 何故、アクティブログがそれほど重要なのでしょうか。更新処理を行う時のDB2の動作を考えてみます。 データベースが更新された場合、バッファプールと呼ばれるキャッシュのデータが更新されますが、ディスクにはすぐには書き出さ れません（このようなデータが含まれるページをダーティーページと呼びます）。その更新処理がコミットされると、必ずログに書き 出されます。 この状態でもしDBサーバーがクラッシュした場合、次にデータベースが使われる前に、クラッシュリカバリーという処理を行う必要 があります。クラッシュリカバリーによって、更新されたはずなのにディスクに反映されていない更新を、ログを読みながらディスク へ反映します。 ここで、重要な点はログがなければクラッシュ前にどのような更新処理が行われていたのかを知ることができないということです。 DB2はこのクラッシュリカバリーによって、突然のDB2の異常停止などの時でもデータベースに格納されているデータの論理的な 整合性を保証しています。つまり、ログが壊れるということはDB2がデータの整合性を保証できないということを意味しています。 ログの重要性を理解したところで、そのI/Oパフォーマンスが非常に重要であることも気が付かれたと思います。更新処理がコミッ トされたら必ずログに書き出される訳ですから、ログへの書き出しが終わらないと次の処理へ進むことができません。 更新処理のボトルネックがログのI/Oにならないようにする為には、他のI/Oとの衝突をできる限り避ける必要があります。他の I/Oとはテーブルやインデックスのデータが含まれるディスクへのI/Oが含まれます。つまり、ログはテーブルやインデックスなどと は全く別のディスクに配置することが推奨されます。ESSのような１つのアレイが大きな場合でも、できる限り全く別のディスク上に 配置し、SCSIやファイバーチャネルなどのインターフェースも別にすることが推奨されます。 また、書き込みに対して多少不利なRAID5よりもできればミラーリング（RAID1）などが可能で有れば選択するようにしてください。 さらにディスクに余裕がある場合には、ストライピングなどによって高速化できれば更新処理にとって良い結果が期待できます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 87-88 DB2 UDB (PC&Unix) データベース物理設計 ログの配置 アーカイブログとアクティブログは別のディスクに配置する USEREXITによってコピーする先（アーカイブログ・ディレクトリ）とコピー元（アクティブログ・ディ レクトリ）は別にした方がパフォーマンス的に有利 障害時の危険分散を考えても別の方がよい アクティブログとアーカイブログが壊れた場合、バックアップからのリストアーはできても、最新状態へのロールフォ ワードができなくなる。 大量更新を行うバッチ系処理の場合(Importなど) ログに対するキャッシュに相当するログバッファー(LOGBUFSZ)を大きくする 高レートのトランザクション処理の場合 MINCOMMITをデフォルト1より大きくする この指定を行った場合、コミットされたら必ずログには書かれるということを100%は保証できな くなる点に注意 ログをローデバイスに配置すると、取り扱いが難しくなる ２重ログが使えない USEREXITに何らかの問題があって、ログのコピーに失敗した場合、手動によるコピーが不可 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ログの配置 アクティブログの重要性を説明してきましたが、DB2にはもう一つアーカイブログというログがあります。通常、USEREXITによって、 アクティブログがアーカイブされた時に、アクティブログとは別のディレクトリ（またはテープのような別のメディア）にコピーして保存 します。このログファイルをコピーする処理も重要です。 コピー先のログファイルは、コピー元やデータベース自身の障害発生時に復元の為に必要になるので、コピー元とは全く別のディ スクやメディアに保存する必要があります。また、このUSEREXITはデータベースが使われている時に動作しますので、もし同じ ディスクにコピー先を配置してしまうと、コピー先の書き込みI/Oの為にアクティブログ自身のI/Oへ影響を与える可能性がありま す。データベースのパフォーマンスを維持するためには、できる限りこのような影響は排除する必要があります。 １つの作業単位（UOW）によって大量のデータを更新するような場合、コミットとコミットの間隔が非常に長くなります。このような場 合、データベース構成パラメータのログバッファー（LOGBUFSZ）を大きくするとパフォーマンスに好影響を与えます。このような更 新処理の場合、ログバッファが小さいと（デフォルトでは32KB）、更新処理がコミットされていなくても、ログを頻繁にディスクに書き 出さなければならなくなる為に、パフォーマンスに悪影響があります。 また、高レートのトランザクション処理が要求された場合、非常に短い時間に多くのコミット処理を行う必要があります。ここで１つ１ つのコミットに対して必ずディスクへの書き出しを行っていたらパフォーマンスが悪くなる場合があります。このようなケースでは、 MINCOMMIT データベース構成パラメータを１より大きくすることによって、複数のコミットによってログをディスクに書き出すことが できます。 このMINCOMMITパラメータを1より大きくすることは、パフォーマンスには好影響を与えますが、DB2のログに対して、コミットされ たら必ず書き出されることが保証できなくなってしまいます。このため、最大でクラッシュ直前の１秒程度の更新データが失われる 可能性があります。（MINCOMMITが１より大きく、コミットの数がMINCOMMITに達していない場合でも、１秒経過した場合はログを ディスクへ書き出します。） ログをローデバイスに配置する機能をDB2はサポートしていますが、アーカイブログ形式を使用していてロールフォワード処理が 必要な本番データベース環境では、あまりお勧めできる機能ではありません。 DB2の２重ログの機能はローデバイスで使用することはできません。 USEREXITによって通常ログをコピーしますが、ローデバイスを使用して、USEREXITに何らかの問題があってログファイルのコ ピーに失敗し、活動ログのデバイス中に特定のログファイルが残ってしまったようなケースでは、手動でコピーすることができませ ん。 障害時の復旧作業は、通常時とは異なり、多種の問題が発生している可能性があり、手動で復旧できないということは、安全性 が低いとも言えます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 89-90 DB2 UDB (PC&Unix) データベース物理設計 一時表スペースの定義 SMS or DMS 一時表スペースとしてはSMSがお勧め（一般） スペースの有効利用 多くのクライアントアプリケーションからのソート要求を処理する場合、DMSよりパフォーマンスが良い 複数のコンテナーを作成して、パフォーマンス向上を計る ファイルシステム上に作成するため、ファイルシステムの制限（ラージイネーブル、ulimit）に注意が必要 一時表スペースとしてのDMS（例外） Solarisでは一時表スペースを使用した再編成、ロードやインデックス作成時などの大量データの処理の場合パ フォーマンス的に有利な場合がある（ケース・バイ・ケースなので、ベンチマーク・テストが必要） HAのテイクオーバー時間を短くために、fsckの必要になるファイルシステムの代わりにローデバイスDMSを選択する 場合がある ページサイズ毎に１つ作成 一時表スペースを使用した再編成の場合、テーブルの存在する表スペースのページサイズと 一時表スペースのページサイズは同じである必要がある。 ソート時に使用する一時表スペースは、格納できるページサイズを選択する。 異なるページサイズを持つ別々の一時表は、SMSとして同じファイルシステムに配置するとスペースの有効利用がで きる 後からでも比較的簡単に作成し直せる表スペース (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 一時表スペースの定義 SMSかDMSの選択 一時表スペースに関しては、DMSよりSMSの方が勧められています。一時表スペースの使われ方は、ソートやジョインに必要なと きに一時的にシステム一時表が作成され、データが格納されます。つまり、表の作成・削除が内部で行われています。表作成時 にSMSではファイルが作成され、使用後に削除されます。DMSでは表スペース内の空き領域を管理する「スペースマップ」や、各 表に対してディスクのどの部分を使用しているのかを管理する「エクステントマップ」をメンテナンスする必要があります。多くの ユーザーがそれほど大きくないソートやジョイン用の一時表を作成する場合、この２つのマップのメンテナンス作業が競合し、パ フォーマンスが劣化する場合があるため、DMSは一時表スペースには勧められていません。 例外として、Solarisで比較的大量なデータを一時表スペースに書き出すような場合、インデックスの作成やロードなどの場合、 DMSの一時表の方がパフォーマンス的に有利な場合があります。 ただし、Solarisでは必ず一時表スペースはDMSにしなければならないという訳ではありません。使用方法によってはSMSが適当 なケースも存在します。 一時表スペースでは通常の表スペースとは、データを書き込む単位が異なります。通常の表スペースにおいて、DMSではデータ はエクステント単位に書き込まれ、SMSではページ単位に書き込まれます。SMSではページ単位で書き込む上に、ファイルシステ ム上で、ファイルのサイズを大きくしていく必要があり、これがDMSに比べてオーバーヘッドになり得ます。db2empfaというコマンド があり、SMSでもエクステント単位でディスクに書き出すことが可能になるため、通常の表スペースでは効果がありますが、一時表 スペースでは、必要な容量を一度にアロケーションするため、db2empfaの効果は期待できません。 データベース中に複数のページサイズが存在する場合、一時表スペースをページサイズ毎に作成されることをお勧めします。こ れは一時表スペースを使用した再編成を行う場合には必須です。再編成を行うテーブルが含まれるテーブルスペースのページサ イズと同じページサイズを持つ一時表スペースがないと一時表スペースを使用した再編成は行えません。 複数の一時表スペースを定義する場合、一時表スペースがSMSであれば、同じファイルシステムに配置することが可能になりま す。これによって使用率が低いディスクエリアを共有し、ディスクスペースの有効利用が可能になります。 もちろん、複数の再編成を並行で実行する必要があるような場合は、別々のディスクに配置する方が、ディスクI/Oが衝突せずパ フォーマンス的に有利です。 一時表スペースは比較的簡単に作成し直せる唯一の表スペースです。本番稼働を始めた後でも、データのExport/Loadなどの移 行などが必要ではないためです。ただし、一時表スペースを作成し直す場合、データベースに最低限１つの一時表スペースを残さ ないとエラーになります。新しい一時表スペースを作成後に、古い一時表スペースを削除すればこのエラーは回避できます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 91-92 DB2 UDB (PC&Unix) データベース物理設計 ワーク／バックアップ領域など バックアップ時間の短縮が必要な場合、バックアップ領域のパフォーマ ンスを考慮する ディスクへのバックアップの場合、バックアップ元のあるディスクとは別にする １つのディスクでは要求が満たせない場合、複数のディスクへのバックアップを検討 テープへのバックアップは、TSMがサポートしていれば、複数テープへの同時書き込みによっ て高速化が可能 ロードの処理時間短縮が必要な場合 ロード元のデータを格納するファイルシステムのパフォーマンスを考慮する AIXでは、以下のパラメータがパフォーマンスに影響する場合がある vmtuneのminpgahead,maxpgahead（ファイルシステムの先読み） 一時表スペースと同居できるか ロード時の入力ファイルと、ソート用の一時表スペースのI/Oが競合するとロードのパフォーマ ンスに悪影響が考えられる 通常は、再編成時にはロードやバックアップなどの処理は行わない。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 93-94 DB2 UDB (PC&Unix) データベース物理設計 ⑥ディスク上へのテーブルスペースの配置 複数の物理ディスクに表スペースを配置し、DISK I/Oを分散させる １つの表スペースを複数ディスクに配置 理想は同じサイズのコンテナー 索引は別の表スペースに配置 長形式は別の表スペースに配置 基本：Prefetch Size = Extent Size×コンテナー 数 表スペース２ (Prefetch Size = Extent Size × 3) 表スペース１ (Prefetch Size = Extent Size × 4) (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 複数の物理ディスクに表スペースを配置 パフォーマンスのためには、各表スペースに対して、６個∼１０個以上の物理ディスク上に別々のコンテナーを作成し、I/Oを分散 することが理想的である（と言われています） 複数のコンテナーに配置する場合には、表スペースに対して以下の設定を行い、プリフェッチャーによる並列I/Oを使用可能にす る必要があります。 Prefetch Size = Extent Size×コンテナー数 この設定によって、複数のプリフェッチャー・プロセスを使い、データをバッファプールに読み出すことができるようになります。 データはラウンドロビンで書き込まれている為、各コンテナーのサイズは同じであることが理想的です。それによって、データの分 散度合いのばらつきを無くし、ディスクI/Oが特定のコンテナーにできるだけ集中しないようにします。 DB2_PARALLEL_IOを指定した場合は、特にPrefetch Sizeは重要になります。パラレルI/Oを指定するような場合は、通常ESSなど のRAIDディスクが使われており、コンテナーが１つでも実際は内部では複数のディスクが使われています。このような状態では、 コンテナー数は先読みに対しては意味を持たず、DB2はPrefetch SizeがExtent Sizeの何倍になっているかによって、プリフェッ チャーを起動し先読みを行います。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 95-96 DB2 UDB (PC&Unix) データベース物理設計 複数の物理ディスクに表スペースを配置（２） RAID5などアレイの場合は以下の構成を行う DB2_STRIPED_CONTAINERS =OFF V8からはデフォルトがON 表スペースを作成する前に指定する RAID stripes data storage =ON RAID stripes data storage DB2_PARALLEL_IO PrefetchSize と ExtentSizeの違いを見て、I/Oを並列に行うかを決定する DMSに対して有効 SMSでは効果が測定できなかった V7.2で動作が少し変更。V7.1では１つのコンテナーである必要があったがV7.2では複数のコンテナーでも可能 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 複数の物理ディスクに表スペースを配置（２） DB2_STRIPED_CONTAINERS DB2_STRIPED_CONTAINERS レジストリー変数は、V.6 FixPak7以降およびV.7で、使用可能になっています。この変数を使用 して、表スペースにおいて、エクステントのバウンダリーが一致するように指定します。現在は、DMS表スペースのコンテナを 作成する時に、１ページのタグがコンテナの最初に格納されます。残りのページがデータの格納に使用可能なスペースにな り、データはエクステント・サイズ単位のブロックにグループ化されて格納されます。 ＲＡＩＤディスクを使用する時、ＲＡＩＤのストライプサイズと同じか、その数倍と同じエクステントのサイズで表スペースを作成す ることをお勧めします。しかし、１ページのコンテナ・タグのために、エクステントはＲＡＩＤのストライプと一致せず，Ｉ／Ｏ要求の 際に、より多くの物理ディスクにアクセスする必要が出てきます。 DMS表スペースのコンテナは、タグが、タグ専用のフル・エクステントに格納されるように作成することもできます。これにより、 上記の問題を避けることもできますが、余分な１エクステントが必要にもなります。どんなDMSコンテナ （CREATE TABLESPACE でも ALTER TABLESPACEによって追加されたものでも)でも、新規のコンテナは、タグを入れる１つのフル・エ クステントを持ちます。既存コンテナは変更されません。 このように、コンテナを作成するためには、 DB2_STRIPED_CONTAINERSレジストリー変数をONに設定する必要があります。 さらにインスタンスを停止し、再始動します。 V.8ではデフォルトがONになっています。 DB2_PARALLEL_IO PrefetchSizeとExtentSizeの違いを見て、DB2は並列I/Oを行うかどうかを判断します。 DMSに対して有効でしたが、SMSでは効果が測定できませんでした。 V7.2で動作が少し変更され、V7.1では１つのコンテナーである必要がありましたがV7.2では複数のコンテナーでも可能になっ ています。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 97-98 DB2 UDB (PC&Unix) データベース物理設計 索引は別の表スペースに配置 DMSが前提 索引は表とは全く異なるI/Oが発生する 表データと索引のアクセスが競合するのを防ぐために、 できれば、別のディスク上に別の表スペースを作成する 表スペース for INDEX (Prefetch Size = Extent Size × 3) 表スペース for TABLE (Prefetch Size = Extent Size × 4) (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 長形式は別の表スペースに配置 DMSが前提 長形式のデータにはバッファプールは有効ではない ファイルDMSに配置することによって、ファイルキャッシュを有効にする ことができる LOBとして格納するデータは、通常それほど頻繁に読み書きが発生しないので、キャッシュが 効かなくても影響が少ないケースも多い (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 99-100 DB2 UDB (PC&Unix) データベース物理設計 ディスクへの配置例（１） 小規模DB(Solaris / 表13GB：2*HDD / 索引3GB : 1*HDD) Backup先はDISK->TAPE Archive Log/Backupはディスク容量不足のため、ストライプ System TS2 TS1 索引 表 System TS1 INDEX TS2 INDEX Catalog LOG その他 TMP用 TS1 TMP用 TS2 Archive LOG1 Catalog LOG その他 TMP用 TS3 Backup DISK1 Backup DISK2 矢印はミラーリングを表す 点線で囲まれた部分はストライピング １ボリューム9.1GB TS:Tablespace (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ディスクへの配置例（１） （注意） この例では、DMS一時表スペースが有利な結果が出ていますが、これは全てのケースで該当する訳ではありません。 Solarisでも、一時表スペースにDMSを使ったためにパフォーマンストラブルが発生したケースも報告されています。 小規模DB(UDB V6.1 / Solaris / 表13GB：2*HDD / 索引3GB : 1*HDD)の例です。 バックアップと再編成の時間を短くすることが最優先であったため、 バックアップは一度ディスクに書き出してから、後からテープに吸い上げる 再編成は、一時表スペースをDMSにしたところ、4時間から2時間40分に減ったため、DMS一時表を選択 Archive Log/Backupはディスク容量不足のため、ストライプ ディスクへバックアップを行ったあと、テープに吸い上げたらバックアップは消えてもよいため、速度を最優先し、ストライプを選 択 再編成は一時表スペースを使うことを前提に、一時表スペース用にも３つのHDDを使用 当初はSMSで考えていたが、DMS一時表が再編成には有利だったため、DMSに変更 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 101-102 DB2 UDB (PC&Unix) データベース物理設計 ディスクへの配置例（２） DB 100GB程度 (AIX / S80 / ESS(SCSI)) ログは表・索引とは別ランクに配置 表と索引はそれぞれ４つのコンテナーに配置 バックアップをディスクに作成してからテープに保存 各ランクは16GB毎にhdiskを構成 各ランクの端数は10GB程度の予備領域(hdisk7,14,21,28,35,42,49,56) 下記図にて構成されていないディスクは、テストなど別の目的に使用 1 RANK 1 15 RANK 3 3 4 5 6 7 16 30 8 9 44 11 12 13 14 RANK 2 17 18 19 20 21 22 23 24 25 26 27 28 RANK 4 DB GROUP 3 31 32 33 34 35 36 37 38 39 40 41 42 RANK 6 FILE DB GROUP 2 43 10 LOG FILE 29 RANK 5 2 DB GROUP 1 45 RANK 7 46 47 48 49 50 51 52 DB GROUP 4 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 103-104 53 54 55 56 RANK 8 DB2 UDB (PC&Unix) データベース物理設計 ⑦構成パラメータの設定 バッファプールの構成 表スペースのディスクに対する入出力のキャッシュ LOBに対してバッファプールは効果が無い プリフェッチャー（NUM_IOSERVERS）を十分に構成する このパラメータは必要より大きく構成しても、ほんの少しメモリを消費するだけで他の悪影響は 考えられない 物理ディスクの数＋２ 程度が推奨 ページクリーナー（NUM_IOCLEANERS）も十分に構成する このパラメータは大きく構成し過ぎると、CPUの負荷を必要以上に大きくする可能性がある CPUの数を初期値に、最大で物理ディスク数迄 エージェント・ プロセス バッファプール Page Cleaner PreFetcher データベース接続 テーブルスペース クライアント アプリケーシ ョン (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ⑦構成パラメータの設定 パフォーマンスに最も影響を与えるパラメータがバッファプールと言われています。UDBが使用するメモリー領域の中で通常、最も 大量にメモリーを使用します。 バッファプールはテーブル及びインデックス・データのキャッシュに相当します。 バッファプールはデータベースが活動化時（通常最初の接続が行われた時）にアロケーションされ、非活動化時（最後の接続が切 断された時）に解放されます。 バッファプールのみを大きくしても、物理ディスクのIOを行うIOサーバーとページクリーナーが少なければ、効果が上がらない可能 性があります。 IOサーバーの初期値は、テーブル及びインデックスを配置している物理ディスクの数+2（物理ディスク数はRAID5ディスクの場合、 パリティ・スペアを除く）と一般的に言われています。 ページクリーナーはCPUの数から物理ディスクの数の間で調整する。初期値はCPUの数（物理ディスク数はRAID5ディスクの場 合、パリティ・スペアを除く）。 RAID5等ストライピングされたディスクをテーブルスペースに使用する場合、IOサーバー及びページクリーナーは通常ディスクと同 様に調整する必要がありますが、さらにdb2setコマンドで設定する環境変数：db2_parallel_ioに＊（全てのテーブルスペース）また 特定のテーブルスペースIDを指定する必要があります。 バッファプールのサイズは、スナップショットによってバッファヒット率（（論理読出数−物理読出数）／論理読出数×１００）を計算し て通常評価します。通常80%から90%以上が目安ですが、システムの性格によってはそれ以下でも問題がない場合もあります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 105-106 DB2 UDB (PC&Unix) データベース物理設計 バッファプールと関連するプロセス（プリフェッチャー） プリフェッチャーは先読みを行う時に使用される page page page page page page page page バッファプール page page page page Prefetcher Prefetcher Prefetcher Extent Extent Extent Prefetcher Prefetcher Prefetcher 必要のないプリフェッチャーは 使用されないだけ Prefetch単位 PrefetchSize = ExtentSize×３ 表スペース (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： バッファプールと関連するプロセス（プリフェッチャー） バッファプールにテーブルスペースからデータを読み込む時に、小さなデータを読み込むのであれば、通常はエージェントが直接 読み出して、バッファプールに格納します。しかし、連続したデータを読み込むような場合は、DB2が判断し、プリフェッチ （Pre-Fetch：先読み）を行います。このプリフェッチは、プリフェッチャーと呼ばれるプロセスによって行われます。プリフェッチャー の数はデータベース毎に指定できます。データベース構成パラメータ：NUM_IOSERVERSです。 プリフェッチャーが効率よく動作するためにはもう一つ構成が必要です。それはテーブルスペースの属性である、PrefetchSizeで す。各プリフェッチャーはそれぞれ１回の読み込みで、ExtentSize分のページを読み込みます。PrefetchSizeはExtentSizeの何倍 になっているかによって、幾つのプリフェッチャーが必要なのかが決まります。ここで重要なのは、PrefetchSizeはExtentSizeの倍 数である必要があるという点です。何倍かは、コンテナー数倍を基本とします。 上記の図の例で考えてみましょう。テーブルスペースは、物理ディスク３つから構成されています。テーブルスペースのExtentSize はデフォルトの32ページとします。この場合、このテーブルスペースの最適なPrefetchSizeは、ExtentSize×テーブルスペースを構 成する物理ディスク数となり、96ページということになります。 この構成によって、プリフェッチャーはこのテーブルスペースを先読みする際には、３つ使用され、それぞれのディスクを別々のプ リフェッチャー・プロセスがIOし、ディスクIOの効率化を図ります。 この際、テーブルスペースは、各ディスクに１つずつ合計３つのコンテナーから構成する必要があります。OSのストライピングに よって複数のディスクから単一のコンテナーを構成することも可能ですが、DB2に複数ディスクから構成されていることを明確に し、ディスクの入出力をDB2によって効率化する為にも、OSのストライピングよりDB2のストライピングの方が推奨されます。 最近の物理ディスクは、飛躍的に速度が速くなっているので、PrefetchSizeは必ずしもExtentSize×物理ディスク数でなくても、そ の倍程度でも効果があることが確認されています。物理ディスク装置へのSCSI またはファイバーチャネルの速度などにもよるの で、一概には言えませんが、倍、3倍、4倍程度を試してみて効果があればそのPrefetchSizeが、最もパフォーマンス的に優れてい るということになります。 プリフェッチャーの構成（NUM_IOSERVERS）は、大きすぎても使われないプロセスが起動されているだけなので、多少のメモリを余 分に消費するのみのため、多少多めに構成しても、ほとんど問題がありません。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 107-108 DB2 UDB (PC&Unix) データベース物理設計 バッファプールと関連するプロセス（ページクリーナー） ページクリーナーは以下の時に呼び出される ページ変更しきい値（chngpgs_thresh）に達したとき ソフトチェックポイント エージェント・ プロセス バッファプール Page Cleaner PreFetcher データベース接続 テーブルスペース クライア ント アプリケーシ ョン (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： バッファプールと関連するプロセス（ページクリーナー） これに対して、バッファプールからテーブルスペースに対してデータを書き出すプロセスを、ページ・クリーナーと呼びます。プリ フェッチャーと同様に、データベース構成パラメータ：NUM_IOCLEANERSによって構成します。 ページ・クリーナーがプリフェッチャーと異なる点は、多く構成しすぎると、CPUへのオーバーヘッドになる可能性があるという点で す。ページ・クリーナーは幾つかの事象によって起動されますが、ページ・クリーナーが起動される時は、構成されているクリー ナーが全て起動されます。必要な数よりも多く構成されていた場合、多くのクリーナーが起動されることになり、それがCPUの負荷 を必要以上に高める可能性があります。 ページ・クリーナーの初期値がCPUの数と言われているのはこのためです。プリフェッチャーの数は物理ディスクの数＋２程度と 言われています。 このように、バッファプールはそれだけを大きくしても、バッファプール−ディスク間のデータのやりとりを行うプリフェッチャーおよび ページ・クリーナーをテーブルスペースのPrefetchSizeおよびExtentSizeと共に正しく構成しないと、ディスクへの入出力を効率化 し、パフォーマンス向上を図ることは難しくなります。 ページ・クリーナーが呼び出されるタイミング ページ・クリーナーが呼び出されるタイミングに影響を与えるパラメータが以下の構成パラメータです。 ページ変更しきい値（chngpgs_thresh） softmaxおよびlogfilsiz ページ変更しきい値は、バッファプール中のこのパラメータで指定した比率がダーティページ（データが更新され、バッファプール 上では更新されているが、ディスクには反映されていないページのこと）になったら、ディスクにダーティページを書き出そうとしま す。 また、logfilsizとsoftmaxによって、ソフト・チェックポイントを発生させ、ダーティページをディスクに書き出すことも可能です。ソフト・ チェックポイントの正確な意味を考えると多少混乱するかもしれませんが、大体、ログが切り替わるタイミングでソフト・チェックポイ ントによって、ダーティページをディスクに書き出すと考えてよいでしょう。Softmaxはデフォルトでは100%で、logfilsizと同じですが、 softmaxを小さくするかlogfilsiz自身を小さくすることによって、頻繁に発生させることができます。 このダーティページの量は、クラッシュ・リカバリーと呼ばれるDB2によるリカバリー処理に大きな影響を与えます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 109-110 DB2 UDB (PC&Unix) データベース物理設計 その他最低限構成すべきパラメータ ログ関連 logfilsiz,logprimary,logsecond ロック関連 locklst,maxlocks ソート関連 sortheap,sheapthres エージェント関連 maxappls,maxagents,num_pool_agents,num_init_agents その他 applheapsz,dbheap,intra_parallel よくわからない場合は、パフォーマンス構成ウィザードで初期値を設定 あくまでも、参考程度 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： その他最低限構成すべきパラメータ ログ関連 logfilsiz,logprimary,logsecond ログは、最大のトランザクション（通常大量更新を行うバッチジョブなど）で使用されるログ容量をスナップショットより測定 し、その数値を元に（データ件数などの増加率などを含めて）見積り、LOGFILSIZ,LOGPRIMARY,LOGSECONDパラメータ を調整します。 ログ容量＝LOGFILSIZ×（LOGPRIMARY＋LOGSECOND）×4096（バイト） ファイルサイズ（LOGFILSIZ）をあまり大きくすると、ログ・ファイルの切り替えの際にディスクIOの負荷が大きくなり、あまり 小さくすると頻繁にログ・ファイルが切り替えられるために（ログ・ファイル切り替え時には、バッファプール中の更新されて ディスクに書かれていないダーティ・ページが、ディスクに書き出される）、バッファプールが有効に利用されない場合があ ります。 活動ログに使用するファイルシステムは、ログ容量の約２倍用意する必要があります。これは、１時点で大量のログが使 用された場合にアロケーションされる、次のログファイルの為の領域です。ディスクに余裕がある場合は、64GB(V.7の最大 ログ32GBの倍)用意しておくと活動ログのファイルシステムが不足することが無くなります。ローデバイスのログは、管理が 難しいので使用しないことを推奨します。 ロック関連 locklst,maxlocks ロックの為のメモリー領域(locklist)は、クライアント数や１つの更新単位（１つのCOMMITまでにINSERT,UPDATE,DELETE によって更新される行の数）にあわせて調整する必要があります。 Locklistが不足していると、ロックエスカレーション（通常ロックは行単位ですが、ロック用メモリーが不足した場合、テーブ ル単位のロックに自動的に変わること）が発生し、長時間のロック待ちやデッドロックなどが発生する可能性があります。 但し、通常夜間などに実行される大量更新の為のバッチジョブ等に関しては、ロックエスカレーションが問題にならない場 合もあります。 スナップショットの、locklist使用量およびLock Escalationの頻度などによって調整します。通常Lock Escalationは、オンラ イン系トランザクションではできるだけ発生させないように、メモリーサイズを大きくします。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 111-112 DB2 UDB (PC&Unix) データベース物理設計 解説： その他最低限構成すべきパラメータ ソート関連 sortheap,sheapthres ORDER BYを使用したSQLなどで使用するソート（分類）用のメモリー領域(SORTHEAP) ここで定義したメモリー領域では容量不足の場合、一時表スペースがソートのために使用されます。この場合、ディスク上 でソートを行うので、メモリー上の場合よりパフォーマンスが悪化する可能性があります。 各接続に対してアロケーションされるSORTHEAPを大きくした場合、インスタンス全体で使用できるソートヒープを制限する DBM構成パラメータSHEAPTHRESも調整する必要があります。SHEAPTHRESの理想的なサイズは、インスタンス配下の （SORTHEAP×同時接続数）です。 エージェント関連 maxappls,maxagents,num_pool_agents,num_init_agents クライアントからＤＢサーバーに接続すると、サーバープロセスのバックエンドでエージェントが起動されます。Unixではプ ロセス、PCではスレッドです。 エージェントの起動はサーバーへの負荷が非常に高い為、あらかじめサーバー上にエージェントを起動しておく num_initagentsパラメータ（db2start時に起動）、または１回使用されたエージェントを解放せずに次回の接続時に再利用す るnum_poolagentsパラメータによって調整することができます。 エージェントはデータベースの非活動化によっては解放されません。num_initagentsおよびnum_poolagentsによって保持さ れているエージェントはdb2stopによって解放されます。 WAS等のアプリケーションサーバーを使用し、接続プールなどの機能によって接続数が固定されている場合、パフォーマ ンスにあまり影響を与えませんが、接続・切断を繰り返すようなアプリケーションが存在する場合は、応答時間およびサー バー負荷に大きな影響を与えるため、調整する必要があります。 初期値の目安は、num_poolagentsは日々のピーク時接続数または平均接続数、num_initagentsは通常時最低接続数。 UDBではSMPサーバーのCPUを効果的に利用する為、intra_parallelの機能がありますが、これをONにした場合、エージェ ントの数は接続数×並列度まで増加することになります。Intra_parallelはクライアント数が多いオンライントランザクション 系のサーバーでは使用しないことが推奨されています。 エージェントによって使用されるメモリー容量を見積もる場合、最低限1.5MB／接続、大きい場合（複雑なSQL等を実行す る場合）では32MB／接続まで実メモリーの空き容量を見積もる必要があります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： その他最低限構成すべきパラメータ applheapsz 複雑なSQL文の実行 より複雑なSQL文を処理する場合、アプリケーション・ヒープ（applheapsz）およびステートメント・ヒープ（stmtheap）が不足する 可能性があります。 特に、アプリケーション・ヒープは、Websphere Application ServerのPrepared Statement Cacheのように１つの接続上でス テートメントを複数同時に実行するような場合に、大きくする必要があります。ただし、applheapszはデフォルトの128ページで は不足するケースがよくありますが、512または1024ページを指定すれば、かなり複雑なSQL文を処理でき、ほとんどのケース では十分なはずです。 アプリケーション・ヒープ不足 もし、あなたが担当しているシステムで、applheapszに10MBを超えるような値（2500ページ以上）にしないと、「アプリケーショ ン・ヒープ不足です」というエラーが発生する場合、そこで動作しているアプリケーション・プログラムが宣言して使った資源（ス テートメント・ハンドルなど）を確実にクローズしているかを確認してください。 そのような接続上にゴミを残すようなアプリケーションを動かしている場合、applheapszを極端に大きな値にしなければ、動か している途中でエラーが発生します。 applheapszを大きくするということは、その接続数分だけメモリ上にゴミが残るということになります。そのようなシステムでは、 長時間の連続稼働によって、メモリの使用量が増加し、システムが不安定になる場合があります。 そのようなケースでは、アプリケーション・ヒープを増やして対応せずに、アプリケーション・プログラムを直して、接続上にゴミ を残さないようにしてください。 dbheap バッファプールを大きくするとデータベースヒープが不足する場合があります。この場合、データベースヒープも大きくする必要 があります。 データベース・ヒープにはログ・バッファー、カタログ・キャッシュ（V8では別）が含まれます。これらを大きくする場合もデータ ベース・ヒープを大きくする必要があります。 intra_parallel このパラメータは情報系のシステムの為のものなので、OLTP系のシステムではデフォルトのOFFのままにし、YESに設定しな いようにしてください。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 113-114 DB2 UDB (PC&Unix) データベース物理設計 ⑧シェル／コマンドの作成 論理ボリューム，ファイルシステムの作成、権限の変更 OS上の操作でDB2が使用する資源を準備する AIX Volume Group(VG) Physical Volume(PV) Veritas Disk Group(DG) subdisk Logical Volume(LV) volume plex(ストライプ時など) WindowsではファイルDMSが一般的 <- MSCSではローデバイスが使用できないことも要因 データベース作成 データベースのホームディレクトリー ログの配置変更(newlogpath) バッファプール・表スペース作成 prefetchsize , extentsizeを正しく設定 表・索引作成 その他 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ⑧シェル／コマンドの作成 論理ボリューム・ファイルシステム作成、権限の変更 AIXの例 論理ボリューム作成 ローデバイス用論理ボリュームの権限変更 ファイルシステム作成 mklv -y lv_name vg_name pp数 hdiskxx chown user:group /dev/rlv_name crfs -v jfs -m /mount_point -l lv_name -A yes データベース作成例（/databaseをDBのホーム・ディレクトリとして作成） db2 create database DB_NAME on /database バッファプール作成例(100MB) db2 create BP01 size 25000 pagesize 4K not extended storage 表スペース作成例(DMS Raw Device) db2 "create tablespace TBS_NAME managed by database using (device '/dev/rlv_name1' 1G,device '/dev/rlv_name2' 1G) extentsize 32 prefetchsize 64 bufferpool bp01" (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 115-116 DB2 UDB (PC&Unix) データベース物理設計 物理設計の評価 幾つかのI/O負荷の高い処理によってベンチマークを行う iostat(veritasの場合、vxstat)を１秒間隔で監視する 10MB/秒∼20MB/秒の速さが出ていれば、通常はそれほど問題にはならない 最近のHDDでは単体で20MB/秒∼30MB/秒の速度をもっている シーケンシャルなread/writeでは特に速度がでるはず。ランダムなI/Oではそれだけの速度は困難。例えば、Loadの Buildフェーズでインデックスを作成する場合など ESSの場合、readであれば60MB/秒∼70MB/秒程度は出る可能性がある 本来書き込むはずのディスク以外にI/Oが発生していないかどうかも見ることができる 書くと予想されていなかった一時表スペースの使用などを見ることもできる。ただし、複数の処理を並行に実行した場 合の監視では見分けることが難しい。 他の余計なI/Oに影響されていないかも確認する ページングによるディスクI/Oの影響は非常に大きい vmstatによって確認する。ページインが数秒間連続で10以上発生していたら要注意 ファイルキャッシュとメモリーの奪い合いがあった場合、インパクトが大きい ファイルキャッシュを使う為にページアウトが頻発するケース AIXではvmtuneのmaxperm,minpermによってOSによるファイルキャッシュへの使用を制限する DB2がファイルキャッシュを使用する場合、DB2が原因でファイルキャッシュが大きくなり、DB2がページアウトされて いる可能性もある (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： 物理設計の評価 物理設計は過去の事例などと比較することによって評価できます。 例１）２００１年当時最新H/Wによるベンチマーク例 H/W RS/6000モデル S80（CPU 6way メモリ 4GB） ESS 4ランク（１０５GB × ４） Ｓ／Ｗ AIX V4.3.3 UDB V7.1（+FP1） 表 20GB （141バイト、１６列） 表スペース 8K Page ４ランクにまたがって作成 DMS・Raw Device Buffer Pool ８００MB，アーカイブ・ログ取得 処理量 備考 8GB／時間 索引*3 （2.2MB／秒） BACKUP 16分 75GB／時間 TO ESS DISK （20.8MB／秒） RESTORE 17分 70GB／時間 FROM ESS DISK （19.4MB／秒） 例２）２０００年当時H/Wによるベンチマーク例 H/W Sun GP7000S Model45(メモリー：2GB) マルチディスクパック装置D1000 (HDD 9.1GB * 8)*2(Mirror) DLTオートローダ装置(XSLDL128A) S/W Solaris 2.6 UDB V6.1 表 13GB / 索引 3GB REORG TMP=DMS(r):2h40m / SMS BACKUP(to DISK) Offline :20m (13.3MB/sec) / Online RESTORE(from DISK) 40m (6.7MB/sec) DISK -> TAPE 2h (2.2MB/sec) TAPE -> DISK 20m (13.3MB/sec) LOAD 処理時間 2時間30分 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 117-118 :4h :25m (10.6MB/sec) DB2 UDB (PC&Unix) データベース物理設計 物理設計の評価（私の場合） 目を閉じて時間帯ごとに、ディスクのアクセス・ランプがどのように点灯 するかを想像する 例えば、夜のほんの短い時間だけ使われるディスクがあった場合、昼間はアクセスランプは つかない パフォーマンスを最もよくしたい時間帯に、できるだけ多くのディスクのアクセス・ランプが点灯 するように配置を考える 運用シナリオの例 午前０時 バックアップの開始 データベースを読み込み、バックアップ先のディスクまたはテープに書き出す、集中的なI/O 午前３時 バッチジョブ開始 入力ファイルの読み込みと更新される表と索引の表スペースおよびログへの書き込みが集中的に発生 一部の表にはロードを実行。入力ファイルの読み込みと、ロード対象の表・索引、ソートの為の一時表スペースおよ び、コピーファイルへのI/Oが発生 午前９時 ユーザーが出勤して、使用を開始した時にCPU負荷が上昇 日中 表・索引・ログへのランダムな小さなI/Oが発生 夕方 一部のユーザーによって、分析を行う負荷の高いSQL処理。表スキャンや一時表スペースを使ったソートが発生。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 UDB物理設計の経験則 ここで書かれている内容は、全ての場所で最適とは言えないことに注 意してください 内容を理解せずに、書かれている方法を適用 しないでください 結果は保証しません (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 119-120 DB2 UDB (PC&Unix) データベース物理設計 UDB物理設計の経験則①（必ずしも真ではない） バッファプールはデータの5%が理想的である バッファプールはとりあえず1GB程度から始める 1GBを超えないバッファプールであれば、どのプラットフォームでもOSの制限などを超える可 能性が低い バッファプールを含むDB共有メモリの制限（1.75GB(V7),2GB(V8) 32bitAIX）はインスタンス単位と考える 実際にはデータベース単位の制限だが、大規模DBが複数に分かれているのであればインス タンスも通常は分割した方が、障害時の可用性、安定性、運用の柔軟性が高くなる (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： UDB物理設計の経験則①（必ずしも真ではない） ここに書いてある内容は、必ずしも全てのケースで通用する訳ではありません。H/Wやプラットフォーム、アプリケーションの特性 などによって真ではないケースもありますので、実際の環境に適用するには、十分に検討してください。 バッファプールはデータの5%という数字は、あまりにも経験的な数字で、データ量が多い場合は設定がもともとできない スナップショットを用いたパフォーマンステストなどが行えないようなケースでは当初の設定値として（設定可能であれば）使用して もよい。 バッファプールの合計サイズを1GB程度かそれ以下に押さえることによって、OSの制限などに達する可能性を減らすことができる ため、安全性の高い設定と言えます。1GBを超えて、各OSの制限までを設定するときは、それぞれのプラットフォームや使ってい るオプションなどによって、制限値に達していないかどうかを確認する必要があります。 もちろん、64bit環境であればバッファプールなどの制限は非常に大きくなるので、1GBに押さえる必要はありません。 「多くのUDBサーバーを設計したSEさんの、良い結果を生む勘違い」 データベース共有メモリーの制限は実際にはデータベース単位であるが、インスタンス単位と考えてもよい。 データベースが別々なのであれば、インスタンスも分けた方が運用が容易になり、他のＤＢによって不安定になる可能性も減る UDBに詳しい（と言われている）人が近くにいたら聞いてみると面白いかもしれません。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 121-122 DB2 UDB (PC&Unix) データベース物理設計 UDB物理設計の経験則②（必ずしも真ではない） 表スペースは表と索引を分ける 表と索引用のバッファプールは同じ方が、有効利用できる 別にすると、索引のページをバッファプール中に保持しやすくなる AIX上ではユーザー・データを格納する表スペースはRaw Deviceで定 義する Raw Deviceの場合、DB2_MMAP_READ / DB2_MMAP_WRITEはOFFにして、バッファプールに 使えるメモリを１セグメント確保する 一時表スペースはSMSで定義する ESSなど１つのアレイディスクに対して ４つ程度のコンテナーを定義する または 4GBのコンテナーをたくさん作成する (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： UDB物理設計の経験則②（必ずしも真ではない） ここに書いてある内容は、必ずしも全てのケースで通用する訳ではありません。H/Wやプラットフォーム、アプリケーションの特性 などによって真ではないケースもありますので、実際の環境に適用するには、十分に検討してください。 表と索引は表スペースを分けた方がよい バッファプールを分けるかどうかは好みが分かれる 同じバッファプールを使うと、バッファプール内のページはほとんど埋まるため、バッファプールの有効利用ができる 異なるバッファプールを使うと、表データのページが読み込まれても、索引ページをバッファプール上にできるだけ残しておくこ とが可能になる AIXではRaw Device を使用して、MMAPをOFFにすると余計なことを考慮しなくてもよい SMSやDMS FileなどのJFSを使っていた場合、選択が難しくなる。但し、サイズが小さいカタログや、すぐに削除してしまう一時表 スペースではほとんど問題にならない 一時表スペースはSMS Solaris以外では、一時表スペースにはSMSが推奨されています。ただし、SolarisでもDMSよりSMSが適当なケースもあります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 123-124 DB2 UDB (PC&Unix) データベース物理設計 ESSにおけるDB設計ベンチマーク ESSの基本構成 ESSのランク内(8パック) = ディスク×6 Data Data Data Data Data Data Parity Spare 表定義 Column name -------------------C1 C2 C3 C4 C5 Type schema --------SYSIBM SYSIBM SYSIBM SYSIBM SYSIBM Type name Length Scale Nulls ------------------ -------- ----- ----INTEGER 4 0 No CHARACTER 10 0 No CHARACTER 128 0 Yes CHARACTER 128 0 Yes CHARACTER 128 0 Yes 変更した構成 NUM_IOSERVERS NUM_IOCLEANERS IBMDEFAULTBP LOGBUFSZ DBHEAP 一時表スペースによる比較 主キー 24 4 25000 4096 6000 DB Server 一時表スペース③ 一時表スペース④ 一時表スペース① 一時表スペース② コンテナー SMS コンテナー SMS コンテナー SMS コンテナー SMS コンテナー DMS Raw コンテナー SMS コンテナー DMS Raw コンテナー SMS コンテナー DMS Raw コンテナー SMS コンテナー DMS Raw p270(CPU * 1) Memory 1GB 注意： このテストでは、一時表スペースを明示 的に指定した再編成の処理速度比較 のため、同じページサイズの一時表ス ペースを４つ作成しました。 通常に使用するデータベースではこの ような構成は行いません。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ESSにおけるDB設計ベンチマーク ESS上にデータベース・オブジェクトを配置する場合の考慮点を見いだすために、幾つかの配置を行ってパフォーマンステストを行 いました。 今回のテスト結果からは以下のようになりましたが、全てのケースで同様の結果になるという保証はありませんのでご注意くださ い。 一時表スペース比較（再編成による） SMSの一時表スペースがDMSに比べてパフォーマンスが良い（SMS:48,49sec / DMS:71sec） コンテナーを複数に分けても速度に対する効果はあまり無い(47.8sec,49.1sec,49.3sec) ESS上にSMSコンテナーを配置する場合、i-node競合の観点から複数のディレクトリ（１ランクに４つ程度）に配置することが、 redbook:"DB2 UDB V7.1 Performance Tuning Guide(SG24-6012) 3.2.9"などでも推奨されています。 しかし、一時表スペースの場合は、同じi-nodeを持つファイル（表オブジェクトに相当）を複数エージェントによって同時にアクセス することによる競合が無いため複数コンテナーに配置しても速度に変化が無かったと予想されます。 ESS上にDMSコンテナーを１つ配置するケース１と４つ配置するケース２のLoadによる比較 処理時間はどちらも49秒前後で同じですが、load phaseにおけるiostatのtm_actがケース１の95%程度に比べ、ケース２では70%程 度と低く、まだ30%程度の余裕があることが分かります。残り30%が使われない理由としては、入力ファイル側がボトルネックになっ ているという原因が予想されます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 125-126 DB2 UDB (PC&Unix) データベース物理設計 ESSにおけるDB設計例（１） ケース１ １つのランクに１つのコンテナーを作成し、PARALLEL_IOを使う DB2_PARALLEL_IO=* (または表スペースID) ExtentSize = 32 PrefetchSize = 192 200万件テスト(900MB , 400B/rec) LOAD 48.6秒 表スキャン 43.6秒 再編成①47.8秒②49.1秒③49.3秒④1分10秒 (別の１ランク上の一時表スペースを使用) ESSのランク ESSのランク 表スペース 表スペース コンテナー Raw Device (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ESSにおけるDB設計例（１） LOAD時のiostat（1秒間隔） Disks: % hdisk7 hdisk7 hdisk7 ............... hdisk7 hdisk7 hdisk7 ............... tm_act 82.4 96.0 97.0 Kbps 21207.8 24192.0 24192.0 15.0 0.0 3.0 4880.0 3456.0 3456.0 tps 187.3 211.0 212.0 108.0 26.0 28.0 Kb_read 0 0 0 load phase Kb_wrtn 21632 24192 24192 184 0 0 4696 3456 3456 build phase テーブルスキャン時のiostat（1秒間隔） Disks: hdisk7 hdisk7 hdisk7 hdisk7 hdisk7 hdisk7 % tm_act 31.0 31.0 36.0 33.0 32.7 32.0 Kbps 20688.0 21460.0 22228.0 21460.0 20491.1 19164.0 tps Kb_read Kb_wrtn 654.0 20688 0 679.0 21460 0 700.0 22228 0 678.0 21460 0 646.5 20696 0 604.0 19164 0 一時表スペース 再編成時①のiostat（SMS一時表） Disks: hdisk2 hdisk7 % tm_act 96.0 62.4 Kbps 35736.6 37811.9 tps Kb_read Kb_wrtn 248.5 0 36094 1150.5 36088 2102 hdisk2 hdisk7 0.0 91.0 0.0 23424.0 0.0 184.0 hdisk2 hdisk7 0.0 93.0 0.0 63060.0 0.0 1970.0 hdisk2 hdisk7 0.0 89.0 0.0 4984.0 0.0 1247.0 0 0 0 63060 0 0 data表スペース 0 23424 再編成時④のiostat（DMS一時表） 0 0 0 4984 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 127-128 Disks: hdisk4 hdisk7 % tm_act 88.0 39.0 Kbps 17736.0 17964.0 tps 156.0 558.0 hdisk4 hdisk7 34.7 64.4 16221.8 16221.8 126.7 126.7 hdisk4 hdisk7 0.0 77.0 0.0 52524.0 0.0 1649.0 hdisk4 hdisk7 0.0 98.0 0.0 4980.0 0.0 1245.0 Kb_read 36 17964 Kb_wrtn 17700 0 16384 0 0 16384 0 52524 0 0 0 0 0 4980 DB2 UDB (PC&Unix) データベース物理設計 ESSにおけるDB設計例（２） ケース２ １つのランクに４つのコンテナーを作成し、PARALLEL_IOを使わない DB2_PARALLEL_IO= ExtentSize = 32 PrefetchSize = 128 200万件テスト(900MB, 400B/rec) LOAD 48.7秒 表スキャン 43.5秒 ESSのランク 表スペース コンテナー RawDevice コンテナー RawDevice コンテナー RawDevice (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： ESSにおけるDB設計例（２） LOAD時のiostat（1秒間隔） Disks: hdisk4 hdisk4 hdisk4 ........... hdisk4 hdisk4 hdisk4 ........... % tm_act 65.0 71.0 70.0 Kbps 25984.0 26240.0 26752.0 22.0 3.0 1.0 8644.0 3328.0 3456.0 load phase tps Kb_read Kb_wrtn 231.0 0 25984 228.0 0 26240 231.0 0 26752 124.0 26.0 27.0 132 0 0 build phase 8512 3328 3456 テーブルスキャン時のiostat（1秒間隔） Disks: hdisk4 hdisk4 hdisk4 hdisk4 hdisk4 % tm_act 30.0 38.0 33.0 33.0 32.0 Kbps 18904.0 21972.0 21464.0 21972.0 19416.0 tps Kb_read Kb_wrtn 597.0 18904 693.0 21972 677.0 21464 694.0 21972 613.0 19416 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 129-130 0 0 0 0 0 コンテナー RawDevice DB2 UDB (PC&Unix) データベース物理設計 ESSにおけるDB設計例（３） ケース３ ２つのランクに１つずつのコンテナーを作成し、PARALLEL_IOを使う DB2_PARALLEL_IO=* ExtentSize = 32 PrefetchSize = 384 V7 100万件IMPORT（400B/rec） 1分45秒 / 1分50秒 ESSのランク ESSのランク 400万件テスト(1.8GB, 400B/rec) LOAD 1分42秒 / 1分36秒 REORG（一時表③） 4分3秒 / 3分58秒 表スペース V8 コンテナー RawDevice コンテナー RawDevice 100万件IMPORT（400B/rec） 1分51秒 / 1分52秒 400万件テスト(1.8GB, 400B/rec) LOAD 2分2秒 / 1分42秒 REORG（一時表③） 3分40秒 / 3分38秒 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ESSにおけるDB設計例（４） ケース４ ２つのランクに６つずつのコンテナーを作成し、PARALLEL_IOを使わな い（コンテナーの並びは各ランクごとに固まるように配置） DB2_PARALLEL_IO= ExtentSize = 32 PrefetchSize = 384 V7 100万件IMPORT （400B/rec） 1分49秒 / 1分44秒 400万件テスト(1.8GB, 400B/rec) LOAD 1分40秒 / 1分37秒 REORG（一時表③） 3分59秒 / 3分57秒 表スペース ESSのランク ESSのランク コンテナー ① コンテナー ② コンテナー ⑦ コンテナー ⑧ コンテナー ③ コンテナー ④ コンテナー ⑨ コンテナー ⑩ コンテナー ⑤ コンテナー ⑥ コンテナー ⑪ コンテナー ⑫ V8 100万件IMPORT （400B/rec） V8 1回目のテストの時に、表スペース のPrefetchSizeをデフォルト(32)のまま にしてしまい、REORGの処理が50秒遅く なった 2分1秒 / 1分55秒 400万件テスト(1.8GB, 400B/rec) LOAD 1分49秒 / 1分45秒 REORG（一時表③） 4分29秒 / 3分41秒 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 131-132 DB2 UDB (PC&Unix) データベース物理設計 ESSにおけるDB設計例（５） ケース５ ２つのランクに６つずつのコンテナーを作成し、PARALLEL_IOを使わな い（コンテナーの並びはランクに対して交互に配置） DB2_PARALLEL_IO= ExtentSize = 32 PrefetchSize = 384 V7 100万件IMPORT （400B/rec） 1分50秒 / 1分46秒 表スペース ESSのランク ESSのランク コンテナー ① コンテナー ③ コンテナー ② コンテナー ④ コンテナー ⑤ コンテナー ⑦ コンテナー ⑥ コンテナー ⑧ コンテナー ⑨ コンテナー ⑪ コンテナー ⑩ コンテナー ⑫ 400万件テスト(1.8GB, 400B/rec) LOAD 1分36秒 / 1分37秒 REORG（一時表③） 3分58秒 / 3分57秒 V8 100万件IMPORT （400B/rec） 1分52秒 / 1分54秒 400万件テスト(1.8GB, 400B/rec) LOAD 1分38秒 / 1分43秒 REORG（一時表③） 3分58秒 / 3分38秒 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 133-134 DB2 UDB (PC&Unix) データベース物理設計 AIX 32bit環境の制限 データベース共有メモリーの制限 V7.2以前 最大7セグメント（1.75GB) V8.1 最大8セグメント(2.0GB) エージェントプライベートメモリーの制限 ulimitのデータ値以下である必要がある ulimitのデータ+スタック <= 255MBである必要がある 拡張ストレージ セグメント・サイズは256MB以下に設定する必要がある 256MBが最も効率が良い (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： AIX 32bit環境の制限 AIX32ビットモードでDB2を動作させる場合、データベース共有メモリとエージェント・プライベート・メモリにおいて制限があります。 AIXのメモリーマップ まず、AIX 32ビットモード時のメモリーマップです。以下の図はV.7までのものです。 0x00000000 AIX kernel -- 0x10000000 User Private Text (Code) db2sysc Executable 0x20000000 User Private Data Agent Private Memory 0x30000000 Free Instance Shared Memory 0x40000000 Free Database Shared Memory 0x50000000 Free Database Shared Memory 0x60000000 Free Database Shared Memory 0x70000000 Free Database Shared Memory 0x80000000 Free Database Shared Memory or Instance Shared Memory (FCM only*) 0x90000000 Free Database Shared Memory or UDF/Agent Shared Memory* 0xA0000000 Free Database Shared Memory or Application Shared Memory* 0xB0000000 Free Agent/Local Application Shared Memory 0xC0000000 Free db2trc (DB2's trace facility) 0xD0000000 Shared Libraries libdb2e.a (and other DB2 shared libraries) 0xE0000000 Free* Application Shared Memory (if not in 0xA0000000)* 0xF0000000 AIX kernel -- データベース共有メモリ（Database Shared Memory）には最大でセグメント0x4から0xaまでの７つが使えます。 V8.1ではこの制限が１つ緩和され、データベース共有メモリは最大で0x4から0xbまでの８つが使えるようになりました。 エージェント・プライベート・メモリ（Agent Private Memory）はセグメント0x2です。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 135-136 DB2 UDB (PC&Unix) データベース物理設計 解説： AIX 32bit環境の制限（データベース共有メモリー） データベース共有メモリ データベース共有メモリはデータベース毎にアロケーションされ、合計でV7までは最大7セグメント(256MB*7 = 1792MB)、V8では8 セグメント以内である必要があります。データベース共有メモリには以下のメモリ領域が含まれます。合計値の計算時には、オー バーヘッドとして10%を加え、制限以下であることを確認する必要があります。 バッファプール（Bufferpool）の合計サイズ ロック・リスト（Locklist） パッケージ・キャッシュ（Package Cache） データベース・ヒープ（DB Heap） データベース・ヒープには以下のメモリが含まれます。 カタログ・キャッシュ（Catalog Cache） ※カタログ・キャッシュはV8ではカタログ・キャッシュヒープとして、データベース・ヒープからは独立しています。 ログ・バッファ（LogBufSz） ユーティリティー・ヒープ（Util Heap） ユーティリティー・ヒープには以下のメモリが含まれます。 バックアップ・バッファ（BackBuf） リストアー・バッファ（RestBuf） ソートヒープしきい値（Sheap Thresh）（intra_parallel=yesの場合） この領域サイズに影響を与えるパラメータとして、「拡張セグメント（NUM_ESTORE_SEGS）」と「Intra_parallel」があります。拡張セグ メントが使用されていると、データベース・グローバル・メモリーに使用できるセグメント数が１つ減少し、Intra_parallelが有効になっ ていると、使用できるセグメント数が１つ減少します。つまり、両方とも使用している場合は、データベース・グローバル・メモリーに 使用できるセグメント数は、5セグメント(256MB*5 = 1280MB、V8では6セグメント)ということになります。 この制限を超える値を設定した場合、該当のデータベースに接続することができません。 またこのセグメント数は、DB2_MMAP_READおよびDB2_MMAP_WRITEのDB2レジストリ変数によっても影響を受けます。デフォルト でこれらの値はONになっていますが、この場合、さらに１セグメント、使用できるセグメント数が減少します。このレジストリ変数は ファイルシステムを使用したテーブルスペースの動作に影響を与えるので、DMSローデバイスのみで構成されている場合はOFF にし、データベース共有メモリに使用できるセグメントを確保することができます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： AIX 32bit環境の制限（エージェント・プライベートメモリー） エージェント・プライベート・メモリー データベース接続毎にサーバー上で起動されるエージェント・プロセスの私用メモリは、１セグメント（256MB）中のデータ・サイズ (ulimitのデータ)を超えないように設定する必要があります。私用メモリは以下の構成パラメータによって使用量が決まります。 アプリケーション・ヒープ・サイズ（applheapsz） ソート・ヒープ・サイズ（sortheap） （私用ソートの場合） ステートメント・ヒープ・サイズ（stmtheap） 照会ヒープ・サイズ（query_heap_sz） Javaヒープ・サイズ（java_heap_sz） UDF共用メモリー・セット・サイズ（udf_mem_sz） 統計ヒープ・サイズ（stat_heap_sz） ulimit –aの出力サンプル DRDAヒープ・サイズ（drda_heap_sz） time(seconds) クライアント入出力ブロック・サイズ（rqrioblk） （リモートクライアントの場合） unlimited DB2インスタンス・オーナーのulimit file(blocks) unlimited UDBインスタンス・オーナーの以下のユーザー制限パラメータが重要な意味を持ちます。 245760 data(kbytes) data stack(kbytes) 15360 stack memory(kbytes) 32768 coredump(blocks) 2097151 nofiles(descriptors) 2000 それぞれの値は、インスタンス・オーナーにlogin後に、コマンド"ulimit -a"を実行することによって確認できます。 データ・サイズとスタック・サイズを合計して、255MB以内にしなければなりません。 これが、合計255MB以上の場合、どちらかのメモリ使用量の増加によってもう片方のメモリが上書きされ、 UDBが不安定になる可能性があります。サンプルでは、データが240MB、スタックが15MBという設定になっています。 特に行ってはいけない設定は、データまたはスタック・サイズをunlimited：無制限にすることです。このような設定を行っている環 境では、DB2がダウンするケースもあります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 137-138 DB2 UDB (PC&Unix) データベース物理設計 解説： AIX 32bit環境の制限（拡張ストレージ) 拡張ストレージ 32bitアドレッシングの場合、バッファプールなどで使用できるメモリ量の限界が低くなりますが、物理的にそれ以上のメモリを搭載 していた場合、拡張ストレージという２次キャッシュに使用することが可能です。 この構成は、DB構成パラメータ：ESTORE_SEG_SZとNUM_ESTORE_SEGSとバッファプールの属性によって設定します。 AIX 32bitの場合、１セグメントのサイズは256MBであることは前述の通りです。よって、AIX 32bitで拡張ストレージを使用する場 合、構成パラメータ：NUM_ESTORE_SEGSにも256MBを設定します。（256MBより大きな値を指定すると、余計にメモリーを消費す るので、絶対にやめてください） 拡張ストレージは、バッファプールとディスクの間に位置する２次キャッシュの役割を持っています。これによって、ディスクへのIO 負荷を減らすことが可能です。ただし、複数のページサイズを使用していた場合は、拡張ストレージのアロケーションは大きなペー ジサイズに合わせてしまうので、小さなページサイズでも使用すると、アロケーションしても使われない無駄なメモリ領域ができる 可能性があります。そのため、拡張ストレージを使用するバッファプールのページサイズは１種類にすることをお勧めします。 また、拡張ストレージは一時表スペースの代わりに使われることもあります。この場合、ディスク上で行うソートよりもメモリ上で行 うソートの方がパフォーマンス的に非常に有利になります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 ブランク・ページです 139-140 DB2 UDB (PC&Unix) データベース物理設計 AIX特有の設定(DB2_MMAP_READ/WRITE） 32bit環境では、これらをOFFにすることによってバッファプールを含む DB共有メモリに使えるメモリが１セグメント（256MB）増える デフォルトのONではOSのファイルキャッシュを使わない OFFにするとSMS ,File DMSコンテナーに対して、ファイルキャッシュが 有効になる システムカタログ、一時表以外の表スペースがRaw Deviceの場合、 DB2_MMAP_READ/WRITEはOFFに設定すべきである DB2 UDB MMAP =OFF DB2 UDB MMAP =ON JFS Cache SMS/ DMS File SMS/ DMS File (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 AIX特有の設定(DB2_MMAP_READ/WRITE） 表スペースがRaw Deviceでない場合、MMAPはどちらが妥当か？ MMAP=OFFがよい場合 ファイルキャッシュが使われることがオーバーヘッドではなく、パフォーマンス向上に効果があ る 少量データの読み書きが数多く行われるような場合、ファイルキャッシュの効果が期待できる MMAP=ONがよい場合 ファイルキャッシュがあっても単なるオーバーヘッドになる場合、パフォーマンスに悪影響があ る ファイルキャッシュに入りきらないほどの大量データの読み書きが頻繁に発生する場合、 キャッシュは単なるオーバーヘッドになってしまう ON/OFFのどちらにしたらよいか分からない場合、MMAP = ONの方が 安全 ファイルキャッシュを使うことによるオーバーヘッドで非常に遅くなる場合がある ファイルキャッシュ用のメモリがDB2のメモリを圧迫して、DB2がページアウトされるとパフォー マンスは非常に悪くなる (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 141-142 DB2 UDB (PC&Unix) データベース物理設計 AIX特有の設定(DISCLAIM,AIOなど) DISCLAIM エージェント・プライベートメモリーをデータベース接続切断時に解放するための設定 DB2MEMDISCLAIM,DB2MEMMAXFREE レジストリ変数 DB2MEMDISCLAIM=ONに設定すると、切断時にメモリーをDB2MEMMAXFREE分残して解放する DB2MEMMAXFREE=8M(V7 Default) V6迄はデフォルト=OFF V7以降ではデフォルト=ON , 8MB AIO V8では必須（ページクリーナーが使用） 最大サーバー数の目安 = 物理ディスクの数 × 10 vmtune ファイルキャッシュを抑えるために使用(minperm,maxperm) (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： AIX特有の設定(DISCLAIM,AIOなど) AIX特有のDISCLAIM AIXではエージェント・プライベート・メモリの増加に対して、特に注意する点があります。AIXではメモリのフリーを行っても実際には 物理メモリはプロセスから解放されず、同じプロセスで再利用しようとします。そのプロセスからメモリを解放するためには、 disclaimというAPIを実行する必要があります。DB2レジストリ変数：DB2MEMDISCLAIMをONに指定し、DB2MEMMAXFREEを指定 することによって、接続が切断された時に、DB2MEMMAXFREEに指定した量のメモリを残して、他のメモリをdisclaimによって解放 することができます。DB2MEMDISCLAIMはDB2 UDB V.6.1ではデフォルト=OFFでしたが、V7以降ではデフォルト=ON （DB2MEMMAXFREE=8MB）になっています。エージェント・プライベート・メモリの合計量が大きくなり、物理メモリでは不足しそうな 場合は、これらのパラメータによってエージェント・プライベート・メモリの総量を減らすことができます。 非同期I/O DB2 UDB V8.1では非同期I/Oを使用します。その為、OS上では非同期I/Oが使用可能になっている必要があります。 非同期I/Oの最大サーバー数は、デフォルトでは10となっていますが、ディスク数の10倍を目安にします。 最小サーバー数はデフォルトの1のままで問題ありません。 vmtune maxperm,minpermによってJFSキャッシュのサイズを調整することができます。デフォルトの設定は80%,20%ですが、データベース サーバーでは通常20%,20%の様な設定が多く使われています。 また、LOADなどに対する入力ファイルのI/Oのために、minpgahead,maxpgaheadの数を増やすと、パフォーマンスに対する効果が 期待できます。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 143-144 DB2 UDB (PC&Unix) データベース物理設計 プラットフォーム別32bitデータベース共有メモリの制限 AIX Solarisのメモリーマップ V7では1.75GB V8では2GB Solaris 3.35GB HP 1GB Windows2000 AWEなしでは3GB Advanced Serverでは8GB DataCenterでは64GB AWEを使用した場合、ブロック化バッファプールおよび拡張ストレージは使用できません (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 解説： プラットフォーム別32bitデータベース共有メモリの制限 Windows2000のAWEについて Windows 2000を使用する場合、DB2は最大で64GBまでの共有メモリがサポートされます。（Windows Data Center Server上でDB2だけが稼動している場 合です。）大容量の仮想アドレス空間を使用して、メモリ内にデータをキャッシュすることで、高い性能を発揮することができます。このサポートはMicrosoft Address Windowing Extensions（AWE）を通じて得ることが出来ます。 標準の32ビット・アドレスは、最大4GBの物理アドレスしかマッピングできません。したがって、Microsoft Windows 2000の32ビットプロセスの標準のアドレス 空間は、4GBに制限されます。 ただし、4GBの物理メモリを搭載したコンピュータの場合、Windows 2000は4GBのメモリを2GBずつに分け、一方の2GBをカーネル用の仮想アドレス空間と して予約します。したがって、アプリケーション（ユーザーモードのプロセス）は、残りの2GBの仮想アドレス空間内で実行されることになります。 Windows 2000は、「Enterprise Memory Architecture（EMA）」により、4GBを超える大容量物理メモリをサポートしています。EMAでは、｢アプリケーションメ モリ調整｣と｢PAE（Physical Address Extensions）x86｣という2種類のメモリ拡張方式が提供されています。アプリケーションメモリ調整は、2GB以上の物理 メモリを搭載したシステムで、PAE x86は4GB以上の物理メモリを搭載したシステムで使用できます。またアプリケーションメモリ調整は「4GB調整（4GT）」と も呼ばれています。 アプリケーションメモリ調整は、カーネル用に割り当てられる仮想アドレス空間を1GBに抑制し、ユーザーメモリ空間を最大3GBまで使用できるようにしま す。アプリケーションメモリ調整を有効にするには、システムドライブのboot.iniファイルに /3GBスイッチを指定するだけです。例えば以下のような指定にな ります。 multi(0)disk(0)rdisk(0)partition(2)¥WINNT = "Windows 2000 Advanced Server"/3GB 4GB以上の大容量メモリは、PAE x86でサポートされます。大容量メモリを使用するためには、インテルのPAEを使用し、システムにおいてカーネルとAPI で4GB以上の大容量メモリをサポートします。PAE x86を有効にするには、boot.iniファイルに /PAEスイッチを付けるだけです。PAE x86を有効にすると、 Windows 2000カーネルは4GB以上の物理メモリを利用できるようになります。また、/3GBと/PAEパラメータの両方を指定するときは、ブランクで区切るよ うにしてください。 AWEは Microsoft Win32(R) APIのメモリ管理機能に対する一連の拡張機能であり、これによりアプリケーションは、標準の32ビットアドレス指定で利用可能 な4GBよりも多いメモリを指定できるようになります。AWEによってアプリケーションは、物理メモリを非ページ化メモリとして獲得し、その非ページ化メモリ のビューを32ビットアドレス空間に動的にマッピングできます。32ビットアドレス空間は4GBに制限されますが、非ページ化メモリはもっと大きくすることがで きます。これにより、大型のデータベース・システムなど、メモリを集中的に使用するアプリケーションは、32ビットアドレス空間でサポートされるメモリよりも 多くのメモリをアドレス指定できます。 ただし、コンピュータ上に 16 GB を超える利用可能な物理メモリがある場合、Windows 2000 ではシステム用に2GBの仮想メモリアドレス空間が必要にな るため、サポートできる仮想アドレス空間は2GBのみになります。boot.iniファイルに /3GBパラメータを指定しないで下さい。この場合は /PAEパラメータの みの指定になります。 アプリケーションメモリ調整とPAE x86は相互に補完し合う技術です。例えば、8GBの物理メモリを搭載したシステムの場合、アプリケーションメモリ調整に より3GBの仮想アドレス空間をアプリケーション用にロックし、PAE x86カーネルとAWE APIセットを使用することで、残りの5GBの物理メモリをアプリケー ションのキャッシュとして利用できます。結果として、アプリケーションは8GBからカーネルの必要分を差し引いた物理メモリを使用できるようになります。 (C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部 145-146