3 . データベース設計...データベース管理者の論理設計での役割...

1

3. データベース設計

設計開発テスト要件定義

アプリケーショ

アプリケーション設計者／開発者

システム構成の選択

パフォーマンス

本番運用

・接続形態・ユーザー認証・セキュリティ・耐障害性設計

システム構築の流れとDB2

© 2009 IBM Corporation 2

データベース設計

運用設計

アプリケーション設計

プログラミング・インターフェー

スの選択

設計者／開発者

データベース設計者／管理者

システム要件の整理

データ項目の標準化

を考慮したプログラミング

統合テスト

キャパシティ、パフォーマンスに関する検証

テスト

・Diskへの配置・表・索引の設計・パラメーター設計

チューニング

データベース管理者の役割

要件定義論理設計物理設計ｺｰﾃﾞｨﾝｸﾞ単体ﾃｽﾄ

統合・ｼｽﾃﾑﾃｽﾄ

本番稼働

データベース管理者ﾓﾃﾞﾘﾝｸﾞﾃﾞｰﾀﾍﾞｰｽ物理設計

キャパシティープランニング

ﾊﾟﾌｫｰﾏﾝｽ･ﾁｭｰﾆﾝｸﾞ


キャパシティープランニングデータベース定義(テーブル､インデックス etc.)配置パラメーター設定

ﾃﾞｰﾀﾍﾞｰｽ運用設計

SQL標準作成

監視

問題判別

データベース管理者の論理設計での役割

データベースの論理設計

– 業務開発担当が中心になる作業だが、RDBを意識した設計も必要になる

– しかしながら、ER図やテーブル定義書を作成するにあたり、正規化をどこまで行うかなどの判断や、テーブルごとのアクセス分析をする過程で、データベース管理者の参画が求められる場合がある


• 論理設計後半からデータベース管理者が参画できれば、物理設計局面へ円滑につなげることができ、プロジェクトの品質向上を期待できる

データベース管理者の物理設計での役割

データベースの物理設計

– 環境設定(パラメーター定義)、資源(テーブルやインデックスなど)の配置が主な作業

– 論理設計をどのように物理的に配置するか• 有限な資源の分配を考えること

– チューニングは、物理設計を適化すること

データベース管理者が中心になる作業であり、パフォーマンスや運用への影響が大きい


(良い物理設計は、パフォーマンス障害も少ない)– 使用できるハードウェア資源と業務要件をバランスさせて善の設計(定義)をする

• データベースのテーブルやインデックスなどの、ユーザーが直接使用するオブジェクトを、どのようにディスクに定義するか

• データベース自身の管理に使用するログなどのオブジェクトを、どのようにディスクに定義するか

• データベース自身の構成パラメーターの適化を考える

RDBMSの種類(DB2やORACLE)により実装方法は異なる

物理設計の重要性

不適切なデータベース物理設計が引き起こす問題

– チューニングが難しい

• キャパシティープランニングによる資源が十分でない– 十分に資源があってもパラメーター設定が悪く効率良く利用できない

• ディスクの装置のi/oが終的にボトルネックになる

• 過度の正規化による多大なジョインの発生

– 要件を満たすことができない

• パフォーマンス要件をクリアするためには、業務要件把握した物理設計が必要

• 運用要件をクリアするためには、設計やパラメーター設定の検討が必要


• 運用要件をクリアするためには、設計やパラメーター設定の検討が必要

– 拡張が難しい

• 拡張時の停止時間を短く、作業時間を少なくしておきたい

データベース物理設計

データベース論理設計書

業務要件の確認運用要件の確認


オブジェクトの定義

各種パラメーター設定

ディスクへの配置

OSパラメーター設定

オブジェクト作成DB2パラメーター設定

物理設計の影響

パフォーマンス・チューニングへの影響

運用設計への影響

業務開発への影響

ﾓﾃﾞﾘﾝｸﾞﾃﾞｰﾀﾍﾞｰｽ物理設計ﾊﾟﾌｫｰﾏﾝｽ・ﾁｭｰﾆﾝｸﾞ


キャパシティープランニングデータベース定義(テーブル､インデックス etc.)配置パラメーター設定

ﾃﾞｰﾀﾍﾞｰｽ運用設計

SQL標準作成

監視

問題判別

データベース物理設計のパフォーマンスへの影響

ｵﾌﾞｼﾞｪｸﾄ作成ﾃﾞｨｽｸへの配置

ｵﾌﾟﾃｨﾏｲｻﾞｰ(ｱｸｾｽ･ﾌﾟﾗﾝ)

CPUの有効利用

パフォーマンスの要素


物理設計

論理設計


DB2パラメーターOSパラメーター

CPUの有効利用

ﾒﾓﾘｰの有効利用

i/oの有効利用


論理設計業務要件運用要件

ロック(Lock)

パフォーマンスへ影響が大きい処理

ｵﾌﾟﾃｨﾏｲｻﾞｰ(ｱｸｾｽ･ﾌﾟﾗﾝ)

パフォーマンスの要素

CPUの有効利用

ｲﾝﾃﾞｯｸｽの利用

ｿｰﾄ処理方法

ジョイン処理方法

影響の大きい処理


CPU並列処理

ﾒﾓﾘｰの有効利用

i/oの有効利用

CPUの有効利用ジョイン処理方法

ｷｬｯｼｭ(ﾊﾞｯﾌｧｰ)

先読み

i/o並列

LOCK処理ロック(Lock)

本当は、もっと複雑に影響

データベース・ストレージ

1 CPU coreあたり、6-20 (15-20)の物理スピンドル数を確保する

– 多いほどベター

DB2 データベース・サーバー / DPF データベース・パーティションごとにLUNを割り当てる

2レベルでストライプする

DB2のトランザクション・ログは、他のデータベース・オブジェクトとは分離し、別ディスク(LUN)上に配置する

RAWデバイスではなくファイル・システム・コンテナーを検討する—LUNごとに1ファイル・シス


RAWデバイスではなくファイル・システム・コンテナーを検討する—LUNごとに1ファイル・システムを作成

トランザクション・ログにRAID-10を、データ用LUNにRAID-10 または RAID-5 を検討する

NO FILE SYSTEM CACHINGの指定<デフォルト>によりCIO/DIO(並行 I/O または直接 I/O)を使用する

自動ストレージを使用して可能な限り全てをストライピング配置する

NUM_IOCLEANERS, NUM_IOSERVERS, PREFETCHSIZE– AUTOMATIC設定を使用する <デフォルト>

クイズ表スペースを３RAID上に構成するなら？

RAIDRAIDRAID

コンテナ表スペース

ファイル・システム

答え

悪


RAIDRAIDRAID

RAIDRAIDRAID

コンテナコンテナコンテナ

コンテナコンテナコンテナ

表スペース

表スペース

コンテナ

ファイル・システム

ファイル・システムファイル・システムファイル・システム推薦

悪

自動ストレージ・データベース V8.2.2

– 単一区分の自動ストレージ・データベース登場

V9.1より

– DPF データベース・パーティションもサポート新規作成データベースは自動ストレージDB(省略時)

• CREATE DATABASE DB3 ON /dbpath1, /dbpath2, /dbpath3, /dbpath4• CREATE TABLESPACE TS1

INITIALSIZE 500 K


INITIALSIZE 500 K INCREASESIZE 100 K MAXSIZE NONE

– ベストプラクティス通り

表スペース TS1

構成パラメーター自動化 V8.2

– DFT_PREFETCH_SZ=AUTOMATIC (省略時)• create tablespaceでPREFETCHSIZEを省略して指定を自動化

• = コンテナ数*(物理スピンドル数)*extentsize– 表スキャン、リストプリフェッチ、ユーティリティの性能向上

• DB2_PARALLEL_IO=* レジストリー変数で物理スピンドル数を決定– 省略時６スピンドル

– *：スピンドル数または表ｽﾍﾟｰｽID:スピンドル数, .. と指定


– NUM_IO_SERVERS=3 (省略時)• 拡大を強く推薦

– NUM_IO_CLEANERS=1 (省略時)• 拡大を強く推薦

IO_SERVERの用途

AP/ﾕｰﾃｨﾘﾃｨ SELECT * FROM 表

表スキャン、リスト・プリフェッチ、バックアップ、リストア、create index、MDCの並列ＩOによる性能向上

– エクステント単位ＩＯ

要求並列度PREFETCHSIZE / EXTENTSIZE

(DB2_PARALLEL_IO=*の時)


AP/ﾕｰﾃｨﾘﾃｨ

バッファープール

エクステント

IOｻｰﾊﾞｰ

IOｻｰﾊﾞｰ

エクステント

エクステント

表スペース

非同期プール・データ・ページ読み取り = 1063

プリフェッチ待機時間 (ミリ秒) = 0

IOｻｰﾊﾞｰ

IOｻｰﾊﾞｰ

IOｻｰﾊﾞｰ

IOｻｰﾊﾞｰ

(DB2_PARALLEL_IO=*の時)コンテナ数(それ以外)

実装並列度NUM_IOSERVERS

足りないとプリフェッチ待ち発生

構成パラメーター自動化 V9.1より

– DFT_PREFETCH_SZ=AUTOMATIC (省略時)• V8.2と同等

– NUM_IOSERVERS=AUTOMATIC• 物理スピンドル数とコンテナ数から算出

– NUM_IOCLEANERS=AUTOMATIC• CPU数から算出


• CPU数から算出

– 自動的にベスト・プラクティスに準拠

• 不用意なprefetchsize=extentsizeの自動排除

• 不用意なnum_ioservers不足の自動排除

• 表スキャン、リスト・プリフェッチ、バックアップ他を適化

パフォーマンスチューニングの心得

まず、これだけはチェック

– データを取る量が少ない方が速い

– 必要なデータは、できるだけメモリー上で操作できる方が速い


– 必要なデータは、できるだけメモリー上で操作できる方が速い

• メモリー上のデータアクセスと、ディスク上のデータアクセスでは、1,000倍以上の差がある

– 必要以上に排他制御することは、待ちを増加させる

データを取得する量が少ない方が速い

物理アクセスはページ単位

– これから先の講義でも、時々思い出す必要がある

インデックスを利用できれば、絞り込んだデータアクセスが可能

– 単にインデックスを利用しているだけでなく、どこまで絞り込みが可能なインデックスを使用しているかがポイントになる

– DB2のテーブル・タイプによる実装も検討可能


– DB2のテーブル・タイプによる実装も検討可能

• 多次元クラスター表（MDC）、パーティション表（テーブル・パーティション）

必要なデータは同じページにまとまっている方が良い

– Key word：クラスター・インデックス、再編成、圧縮

必要なデータはできるだけメモリー上で操作

データをメモリー上で操作するには

– バッファープール

– ソート処理用のヒープ

– パッケージのキャッシュ

– その他のヒープ


サーバーで使用できるメモリーの大容量化、DB2 UDBの64ビット対応、DB2のSTMMによる自動設定により、細かなメモリー使用デザインの重要性は少なくなってきている

– インデックスの設計も含め、適切な基本設計を行うためには自動設計、構成機能の結果を、取捨選択するための知識が必要となる場面はある

例


通常の表に保管されたデータ


必要な行へのアクセスのために大

量の不要な行も読み込む

ひとつのクエリーを処理するのはひ

とつのCPUのみ。

複数パーティションにハッシュ分割（DBパーティション）

P 1 P 2 P 3


ひとつのクエリーを複数のCPUを

使って並列に処理することができる

複数パーティションにハッシュ分割して並列処理

P 1 P 2 P 3


依然として不要なI/Oは存在。

可用性を損なうことなく大量

データの入れ替えを行いたい。

データをレンジ分割して保存（パーティション表）

P 1

Jan

P 2 P 3


Feb

Mar条件に合致したパーティショ

ンのみを参照すればよい

パーティションの高速削除と

追加が可能

データをレンジ分割して保存（パーティション表）

P 1

Jan

P 2 P 3


Feb

Mar

未だ不要なI/Oが残っている

各行をブロックに整理して保管（多次元クラスター表）

P 1

Jan

P 2 P 3


Feb

Mar

同じ値を持った行同士を同じ

ブロックに集めて保管。

必要な行を取り出すための

I/Oが小限で済む

各行をブロックに整理して保管（多次元クラスター表）

P 1

Jan

P 2 P 3


Feb

Marこれ以上I/Oの効率を上げるこ

とはできないか？？？

各ブロックに格納される行数を増やす（行圧縮）

P 1

Jan

P 2 P 3


Feb

Mar

各ブロックにより多くの行を保管

し、ディスク容量の削減が可能。

必要な行を取り出すためのI/O

がさらに少なくなる。

パーティションDB（DPF、データベース・パーティション）

ハッシングによるデータ分散

– シェアード・ナッシング・アーキテクチャー

大規模DBの実現

– SQL、ユーティリティの高速並列処理

– 巨大な表、表スペース、バッファープール

– 複数サーバーによる

Interconnect


動的なパーティション追加

– DB2を停止せずに追加可能

V9.7


– 複数サーバーによるインスタンス構成

Database PartitioningFeature（V8/V9）

InfoSphere Warehouse （V9.7）

– InfoSphere Balanced Warehouse に採用されている（初期導入／DB構成済み）

DBパーティショﾝ0




表 T1 表 T1 表 T1 表 T1


CREATE TABLE T1 ( COL1 INT, COL2 CHAR(3) )

DISTRIBUTE BY (COL1)

ハイ・パフォーマンスを支える並列処理

シェアード・ナッシング・アーキテクチャーによる並列処理

アプリケーションは意識することなく、複数サーバーによるスケールアウトにより、並列処理を実現します。

データベースの区分を持つサーバー群

– 全体で１つのデータベース

– パラレル・オプティマイザーと高速通信経路で接続

ハッシングにより均等にデータ分散アプリケーションＤＢ２クライアント

どの区画に接続しても同じ結果が返ってきます

１０００区分までのスケーラビリティー１０００区分までのスケーラビリティー

透過的データアクセス透過的データアクセス

アプリケーションＤＢ２クライアント

１００TBのDBも１００区分で１TBのDB設計と同じ


単一データベース

データベース区画1

CPUCPU

メモリー

データ

・・・


CPUCPU

メモリー

データ


CPUCPU

メモリー

データ

データベース区画N

CPUCPU

メモリー

データ

データベース区画N-1

CPUCPU

メモリー

データ

ＤＢ２クライアント同じ結果が返ってきます

ＤＢ２クライアント

IBM Balanced Warehouse は、事前テスト済みのスケーラブルかつ完全に統合された InfoSphere Warehouse、Server、および Storage のシステム・コンポーネントで構成される、完全なデータウェアハウジング・ソリューションです。

IBM Balanced Warehouses は、お客様のビジ

ネス・インテリジェンス・ソリューションの基盤として

すぐに使用できるリアルタイム・データウェアハウジング・ソリューション

IBM InfoSphere™ Balanced Warehouse 構築・設計の容易性スピード構築を実現


Balanced Warehouse

ネス・インテリジェンス・ソリューションの基盤として提供する必要のあるあらゆるものを含んでいます。その中でも、特筆すべきは以下の通りです。

• 堅固な InfoSphere Warehouse ソフトウェア• 最新の IBM サーバー・テクノロジー• 高性能の IBM ストレージ• 包括的なトータル・ソリューション・サポート

IBM InfoSphere Balanced Warehouse ソリューションは、パフォーマンスと信頼性に関して厳正なテスト、検証、および調整が実施されます。InfoSphere Balanced Warehouse は、複数のお客様の成功事例を通じてIBM が得たベスト・プラクティスに基づき、開発されています。IBM は、信頼性とセキュリティーの向上のため、製品をリリースする前に、システムの構成とテストを行い、さらに、高クラスの照会パフォーマンスが達成されるように、システムのきめ細かい調整を行っています。

正確なバランスによる適パフォーマンス

IBM InfoSphere™ Balanced Warehouse


ステップ 1お客様の成功要件の検討

ステップ 2主要ドライバーの定量化

ステップ 3ソリューション・アーキテクチャーの設計

ステップ 4トータル・ソリューションの構築およびテスト

ステップ 5最適パフォーマンスのための調整およびバランス

IBM のベスト・プラクティス IBM の開発

IBM Balanced Warehouse の開発予定表

IBM InfoSphere Warehouse V9.7は、データウェアハウスを構築、配置、操作、および保守し、管理される情報資産を企業全体に送達するために必要な、データ・サーバーおよびツールを提供する統合データウェアハウス・プラットフォームです。これには、ユーザーによるウェアハウス・ベースのビジネス分析の設計および配置を可能にする統合機能が組み込まれています。

IBM InfoSphere Warehouse V9.7 Enterprise Editionには以下の機能が含まれています。

統合された製品群機能概要

platform Unix、Linux、Windows

Integrated Installer 統合インストーラー

DB2 ESE データベースエンジン

組み込み解析

データ・マイニング

インライン分析

キュービング・サービス

非構造化分析

設計

制御

統合されたInfoSphere Warehouse


SQL Warehousing Tool 簡易なETLツール

WebSphere Application Server Webアプリケーションサーバー

Admin Console 管理コンソール

Design Studio 開発用PC：分析PGM、ETL処理の開発・管理

Cubing Services OLAP分析機能

Database Partitioning Feature スケールアウトを実現するDBパーティション機能

Performance Optimization Feature パフォーマンス分析ツール、ワークロード管理

DB2 Storage Optimization Feature データ圧縮機能

Alphablox 分析／レポーティング／アプリケーション

Intelligent Miner データマイニング

Unstructured Text Analysis テキスト分析

データベース管理

パフォーマンスの最適化

IBM InfoSphere Warehouse

モデリングと設計

管理

と制

御

データ移動とトランスフォーメーション

ワークロード管理

データ・パーティショニング

高いデータ圧縮

表設計時の選択肢

アプリケーションによっては、

– テーブル・パーティショニング

– 多次元クラスタリング（MDC)– データ圧縮

– マテリアライズ照会表（MQT)


– 索引

の利用を検討する

テーブル・パーティショニグ（パーティション表）

ひとつの表を複数の区分に分割

古い区分を高速にロールアウト（区分のデタッチ）

既存データはオンライン状態で、新しい区分をロールイン（区分のアタッチ）

区分単位でのアクセス性能向上

各区分は異なる表スペースに配置可能

パーティション索引のサポート

V9.7


Jan Feb Mar Apr

過去のデータはまとめて瞬時に

切り離し

新規データを個別にLOADして

から区分を取り付けDETACH ATTACH

日付などのレンジで区分に分割し整理するパーティション表

Jan

売上履歴表

読みたい区分のみにアクセス

多次元クラスタリング(MDC) 多次元クラスタリング（MDC）は、複数属性の値でデータを分類して、自動的に格納する機能

– 「2008年10月」の「DB2」の「東京」というような複数の属性をもつクラスター

クエリー要求に対する高いパフォーマンスを提供

– 次元別検索のパフォーマンス向上• 同じ値のレコードは同じセルに存在するため、範囲を指定した検索などのように比較的多量の連続したアクセスに有効（ブロック・ベース（BID)の索引を使用

した照会処理）

– 削除のパフォーマンスアップ（ブロック削除が可能）

データ並べ替えを目的とした再編成不要


データ並べ替えを目的とした再編成不要

– REORGにより空ブロック（エクステント）を他の表で再利用も可能

MDC表の作成例：CREATE TABLE TB_MDC (

年月 CHAR(7), 地域 CHAR(10), 製品 VARCHAR(10))

ORGANIZE BY DIMENSIONS (年月, 地域, 製品)製品

dimension

20082008年年1010月月東京東京DB2DB2

20082008年年1010月月大阪大阪DB2DB2

20082008年年1111月月大阪大阪

WebSphereWebSphere20082008年年1010月月大阪大阪DB2DB2

20082008年年1111月月東京東京

WebSphereWebSphere20082008年年1010月月東京東京DB2DB2

年月dimension

地域dimension

ブロック(エクステント）

レコード

セルV9.7

多次元クラスタリングRow Indexes (RIDs) とブロック・インデックス (BIDs)

イメージを表示できません。メモリ不足のためにイメージを開くことができないか、イメージが破損している可能性があります。コンピュータを再起動して再度ファイルを開いてください。それでも赤い x が表示される場合は、イメージを削除して挿入してください。

非 MDC Index MDC IndexRow Indexes (RIDs)

1 index entry per row

Block Indexes (BIDs)1 index entry per block


= Row

1 index entry per block

MDC利用の検討例

MDCは特に情報系の大規模検索時に有効な場合が多い

時系列の履歴表などには年月（または年月日）を次元したMDCを検討してみる

次元列がイコール条件や範囲条件となる照会や削除が多用される場合

– 同じ値のレコードは同じセルに存在するため、範囲を指定した検索などのように比較的多量の連続したアクセスに有効

比較的ユニーク性の低い列を使用した条件しか指定できない検索

– 従来のレコード・インデックスでは、ユニーク性が低く、不向きとされていた列に使用


– 従来のレコード・インデックスでは、ユニーク性が低く、不向きとされていた列に使用

– ユニーク性の高い列では逆効果になるケースもある

• 生成列を使用して、ユニーク性を落とす方法を検討する適切なレコード・インデックスとの併用を検討する

• 次元列をレコード・インデックスの第一列に指定しない（カーディナリティーの低い列が第一列だとアクセスプランが不安定になりやすいため）

OLTPにおける、MDC利用による再編成不要運用

DB2 V9.7の圧縮機能

表の行圧縮

– 辞書を使った行レベルのデータ圧縮• 列単位ではなく行単位

– 圧縮の対象のデータ(※)圧縮可能なデータの種類はバージョンによって異なります。

• 表データ

• XML（INLINE)データ（V9.5～）

• XML(XDA)データ（V9.7）

V9.7で圧縮機能が大幅に強化


• XML(XDA)データ（V9.7）

• LOBデータ（V9.7）

– 一時表の圧縮も可能（V9.7)• システム一時表、ユーザー一時表

– レプリケーションのソース表のサポート（V9.7）

索引の圧縮(V9.7)– 複数のアルゴリズムを用いた索引自動圧縮

– 圧縮対象外の索引

– MDCブロック索引, カタログ表の索引, index specifications, XMLメタ索引, XMLパス索引

SunTrust 社データ圧縮で$2Mを削減

“わが社のデータウェアハウスでは83%以上のデータ圧縮率を実現しています。これにより、初期投資として200万ドル以上、また運用時には年間50万ドルのコストが削減できると予想しています。 “

—Michael Henson, SunTrust Bank, Inc.

• 低ストレージコスト

• Oracleデータベースと比較して、


1.5 倍の

向上

3.3 倍の

向上2.0 倍の

向上

8.7 倍の

向上

DB2 9DB2 9 OtherOther

30%のストレージコストが削減

• パフォーマンスの向上

• バッファープールヒット率が向上し、I/Oが現象

• バックアップ、リカバリーのスピード化

• ストレージサイズが小さくなることでバックアップとリカバリーの時間が短縮

http://www.tpc.org/

http://www.sap.com/solutions/benchmark/

(参考)索引圧縮のパフォーマンスへの影響検証

1. 「表圧縮+索引圧縮」と「表圧縮+索引圧縮なし」の比較

結果



1. 「表圧縮+索引圧縮」と「表圧縮なし+索引圧縮なし」の比較

結果まとめ

圧縮により、表・索引ともにバッファープールヒット率が向上した影響で、レスポンスタイムの向上に繋がったと思われる。




シナリオ

LINEITEM1表にcol1(varchar)索引を作成。

以下のパターンでのOLTPツールでのSELECT,UPDATE,INSERT,DELETEのパフォーマンスを検証した。

表用バッファープールのヒット率が100%になるように、バッファープールサイズを調整。

①LINEITEM1表を圧縮+col1索引を圧縮

②LINEITEM1表を圧縮+col1索引を圧縮なし


・環境

・SQL



結果




結果まとめ

圧縮による、索引のバッファープールヒット率向上だけでも大幅なレスポンスタイム向上になる。

バッファープールヒット率が共に100%の場合、レスポンスに変化はほとんど見られない。

参考（バッファープールヒット率100%の場合）


索引圧縮使用上の注意

圧縮表に対して新しく作成する索引は、明示的にCOMPRESS NOを指定しない限り、圧縮索引となる。

– 一度圧縮索引として作成されたものを非圧縮に戻したい場合には、COMPRESS NOを指定してALTER INDEXを実行し、さらに索引再編成を行う。

非圧縮表に対して新しく作成する索引は、明示的にCOMPRESS YESを指定しない限り、非圧縮索引となる。


定しない限り、非圧縮索引となる。

– 一度非圧縮索引として作成されたものを圧縮したい場合には、COMPRESS YESを指定してALTER INDEXを実行し、さらに索引の再編成を行う。

圧縮表の定義されたV9.1、V9.5のデータベースをV9.7に移行しても、索引圧縮は行われない。

– 索引圧縮を行うには、ALTER INDEX・・・COMPRESS YES、およびREORG INDEXを実行する

インデックス（索引）のメリットと考慮点メリット

– インデックスを使った照会、更新、削除処理の性能向上

– ロックの範囲が小さくなり、処理の並行性が上がる

– データ値が一意であることを保証する（ユニーク／非ユニーク）インデックススキャン

•全データをスキャン •条件

表スキャン

表スキャン

インデックス設計


表インデックス表

•全データをスキャンして、条件にあうかどうかを判定•途中で条件にあうデータを見つけても

後まで検査

•条件にあうデータのみをスキャ

ン

考慮点

– ディスク容量が必要

– 更新処理、LOAD処理への影響

表スキャン

DB2の索引（レコード索引）

レコード索引（通常の索引：Ｂ＋ツリー構造の索引）

– ユニーク索引

• 対象レコードがユニークになることを保証

– 複合列索引

• 索引列に複数の列を定義できる

• ユニーク索引、クラスター索引も複合列で構成することが可能

– クラスター索引 (次頁参照）


– クラスター索引 (次頁参照）

– 索引オプション

• 双方向索引– 昇順、降順ともに効率よく索引を使用できる

• INCLUDE文節を使ったユニーク索引– 一意性の検査の対象にはしないが、索引には追加したい列を含めることができる

– 動的ビットマップ索引

• DB2UDBｵﾌﾟﾃｨﾏｲｻﾞｰが必要と判断した時に自動的に作成され、ﾕｰｻﾞｰが特別に指定する必要はない

クラスター索引とは

非クラスター索引（省略時） INSERT時に使われるデータページは初に見つけた空エリアランダムアクセスに対して有効

20

529

28

33

2928205 3533 6550

35データ・ページ


クラスター索引

– INSERT時に使うデータぺージはデータの並び順が索引順とできるだけ同じになるよう試みる

• 表にあらかじめ空スペースを用意しておくと効果的（デフォルトでは空スペースがないため、alter tableで変更する必要がある）

– 順次スキャン時の I/O が軽減され、処理効率が上がる

20

5

29

28 33

50

65

35

2928205 3533 6550

その他の索引設計時の考慮点

索引設計の考慮点

–複数列索引の第一列•ユニーク性の高い列にする、または検索条件でも頻繁に指定される列

•ユニーク性が高い＝値の種類が多い＝カーディナリティが高い

•不適切な索引の例–第一列の値が種類が少ない、特に１通りしかない索引

複数列索引


–列上の他の索引キーの部分キーとなるような索引は作成しないようにする•indexA( a , b , c )のインデックスと、indexB ( a , b )が存在する場合、

indexBはindexAでまかなえるので、不要。

第一列第二列第三列

複数列索引

インデックス定義のまとめ表にはインデックスを付ける

– 表が非常に小さいなど、特殊な場合を除き１つはインデックスを作成する

インデックス数の一般的な目安

– OLTP であれば 2～3個／表

– DSS であれば 5～6個／表

複合列インデックス、双方向インデックスを利用して索引の数を減らすのも1つの手法

インデックス候補となる列


• WHERE句で指定された検索条件の列

• ソート列（DISTINCT、ORDER BY、GROUP BY）として指定されることが多い列

• JOIN列など

– そのインデックスでどれくらい結果行を絞り込めるか？を考える

– 設計アドバイザーの利用

単純に表にインデックスを作成しただけでは、インデックスが使われるかは分からない

– Explainによるアクセス・プランの確認

– Db2pdコマンドによる確認• db2pd –db データベース名 -tcbstats index

– 設計アドバイザーにより、特定Workloadで使用されていないインデックスの確認も可能

パラメーター設定

DB作成時に構成アドバイザーはデフォルトでオン

– 構成アドバイザー

• ＤＢ構成パラメーターの推奨値を提示

• 入力した情報（メモリー量、トランザクションの負荷、トランザクション数、トランザクションの特徴など）に基づいて推奨構成パラメーターを提示

• 更新コマンド、スケジュール実行も可


• 更新コマンド、スケジュール実行も可能

– パラメータ結果の反映方法としては、即実行、スケジューリング実行、値を編集して実行など選択可能

バッファープールも含めメモリー関係のチューニングはSTMMでストレステスト時に決定

(参考）アーキテクチャー(スレッドモデルへの変更)

スレッドモデル

– DB2 9.5 以降、従来のプロセスモデルからスレッドモデルに変更

– “db2sysc”というプロセスの配下で様々なスレッドが稼動

– スレッドモデルのメリット

•リソースの節約/パフォーマンス向上/使いやすさの向上

データベースのアプリケーション要求を処理するために起動するエージェント（db2agent）や各データベースで使用されるプリフェッチャー（db2pfchr）、ページクリーナー（db2pclnr）、ログ書き込みを担当するdb2loggwなどのスレッドが起動


ログ書き込みを担当するdb2loggwなどのスレッドが起動

UDB Client Library

Active

db2agntp

Write Log Requests

Victim

Noti

ficati

ons

Parallel, Page

Write Requests

Shared Mem & Semaphores, TCPIP, Named Pipes,…

Process/Thread Organization

Instance Level

Per-instance

Idle, pooled agent or subagent

db2tcpcm db2ipccmdb2agent (idle)

Per-application

db2agent

db2pclnr

db2pfchr

db2loggwdb2dlock

db2agntp

db2loggr

Per-database

Data Disks

CommonClient

Subagents

UDB Server

Listeners

CoordinatorAgents

PrefetchersPage

Cleaners

Data Disks

CommonClient

Subagents

UDB Server

Listeners

CoordinatorAgents

PrefetchersPage

Cleaners

Buffer Pool(s)

DeadlockDetector

Log Disks

Idle Agent Pool

LoggingSubsystem

Log Disks

Idle Agent Pool

LoggingSubsystem Log Buffer

Database Level

Idle

Async IO Prefetch Requests

Parallel, Big-block,

Read Requests

V9.5からは、点線枠で囲まれた部分は、1つ

のプロセスとなる。各EDUは、スレッドとして存在して、 db2syscプ

ロセスに紐づく。

db2fmp

db2acd

FMP

Health Monitor

db2wdog

メモリー管理

DB2が利用する主なメモリ領域

– データベース・マネージャー・グローバル共用メモリー (インスタンス共用メモリー）：

• データベース・マネージャ（インスタンス）が開始（db2start) されたときに割り振られる。

– データベース・グローバル・メモリー：

• データベースが初に活動化または接続されるときに割り当てられる

– アプリケーション・グローバル・メモリー：

• アプリケーション・プログラムが使用

• アプリケーション要求に応えるために DB2 データベース・エージェントによって割り振られる


• アプリケーション要求に応えるために DB2 データベース・エージェントによって割り振られる

メモリー管理

進化した自己チューニング・メモリー管理 (STMM)

– DB2が自動的にメモリー・チューニングを実施

• メモリー構成の簡素化

• メモリー不足によるエラーの削減

オペレーティング・システムのメモリ管理

DATABASE MEMORY

INSTANCE MEMORY

APPLICATION

MEMORY


MEMORY

Sorts &

Hash Joins

Package Cache

Lock List

Buffer

Pool 1

Buffer

Pool 2APPL 1

APPL 2

メモリー管理 – STMM(Self Tuning Memory Management)

STMMが管理するパラメーター

• buffer pools• lock list• max locks• package cache• sort heap• application heap

STMMの使用方法

SELF_TUNING_MEM をONに

– update db cfg for database <db_name> using self_tuning_mem on

自動調整したいパラメーターの値をAUTOMATICに設定


• application heap• dbheap• monitor heap• statement heap• statistics heap

– update db cfg for database <db_name> using <param> automatic

同様にバッファーを自動調整したい場合もAUTOMATICに設定

– alter bufferpool <bp_name> size automatic

– create bufferpool <bp_name> size automatic

デフォルトはSTMMで自動調整

通常は1つのバッファー･プールを利用

– チューニング等の管理が簡素化される

– メモリー全体がも効率的に使用される

以下のケースでは複数バッファー･プールも検討する

バッファー・プール設計における考慮点


以下のケースでは複数バッファー･プールも検討する

– デフォルトの 4KB 以外のページ・サイズを使用する場合• デフォルトのサイズはDB作成時に指定が可能なので、サイズを統合することも可能

– 多数の短い更新トランザクション・アプリケーションによって、頻繁に高速でアクセスされる表に専用バッファー・プールを作成する場合

– 常に表全体が走査される、メイン･メモリーより大きな表に専用バッファー・プールを作成する場合

– インデックス専用のバッファー･プールを作成する場合

– マスター・テーブル専用のバッファー･プールを作成する場合

複数バッファー･プール割り当て例

頻繁にアクセスする大切なデータには、専用のバッファー･プールを割り当てる

– 小さなテーブルには、テーブルが全部入りきるサイズのバッファー･プール

– 大きいテーブルには、重要なインデックスに専用のバッファー･プールを割り当てる

その他のテーブルは、まとめて大きなバッファー･プールを割り当てる

マスター用一般テーブル用大規模テーブル大規模テーブルデータ用

バッファープールバッファープールバッファープールバッファープール


マスター用一般テーブル用大規模テーブル

インデックス用データ用

頻繁にアクセスするのでメモリーに常駐させたいデータは大容量なのでメモリー消費を制

限したい。しかしインデックスは頻繁に使うのでメモリーに常駐させたい

特殊要件はないので、共用のバッファープールを使用

マスターテーブル

大規模テーブル

大規模テーブルのインデックス

一般テーブル

DB2 UDB における定義の流れ

物理特性、運用管理基準の決定

– インスタンスの定義： db2icrt– データベースの定義： CREATE DATABASE– バッファープールの定義： CREATE BUFFERPOOL– スキーマの定義： CREATE SCHEMA– 表スペースの定義： CREATE TABLESPACE

論理設計の反映

– テーブルの定義： CREATE TABLE• MQT、MDCの利用

インスタンス

システム


• MQT、MDCの利用

– 視点の定義： CREATE VIEW– ALIASの定義： CREATE ALIAS– テーブル検査制約の設定： ALTER TABLE ... CHECK– トリガーの定義： CREATE TRIGGER

論理設計の反映とパフォーマンスの向上

– インデックスの定義： CREATE INDEX 安全保護のための定義

– GRANT ... to user,group パラメーターの設定

データのロード

– LOAD / IMPORT

データベース

表表

まとめ

表の種類は、通常表のほか、パーティション表やMDC表、サマリー表がある

表は行圧縮機能によりデータを圧縮できる。DB2 9.7より索引圧縮も可能。

パフォーマンスを向上させるため、様々な索引を作成できる

DB2 9.5よりスレッドモデルとなる。STMMにより自動で柔軟なメモリ割り当てが可能


能

3 . データベース設計...データベース管理者の論理設計での役割...

Documents

Transcript of 3 . データベース設計...データベース管理者の論理設計での役割...