Post on 11-Jun-2020
1
3. データベース設計
設計 開発 テスト要件定義
アプリケーショ
アプリケーション設計者/開発者
システム構成の選択
パフォーマンス
本番運用
・接続形態・ユーザー認証・セキュリティ・耐障害性設計
システム構築の流れとDB2
© 2009 IBM Corporation 2
データベース設計
運用設計
アプリケーション設計
プログラミング・インターフェー
スの選択
設計者/開発者
データベース設計者/管理者
システム要件の整理
データ項目の標準化
を考慮したプログラミング
統合テスト
キャパシティ、パフォーマンスに関する検証
テスト
・Diskへの配置・表・索引の設計・パラメーター設計
チューニング
データベース管理者の役割
要件定義 論理設計 物理設計コーディング単体テスト
統合・システムテスト
本番稼働
データベース管理者 モデリング データベース物理設計
キャパシティープランニング
パフォーマンス・チューニング
© 2009 IBM Corporation 3
キャパシティープランニングデータベース定義(テーブル、インデックス etc.)配置パラメーター設定
データベース運用設計
SQL標準作成
監視
問題判別
データベース管理者の論理設計での役割
データベースの論理設計
– 業務開発担当が中心になる作業だが、RDBを意識した設計も必要になる
– しかしながら、ER図やテーブル定義書を作成するにあたり、正規化をどこまで行うかなどの判断や、テーブルごとのアクセス分析をする過程で、データベース管理者の参画が求められる場合がある
© 2009 IBM Corporation 4
• 論理設計後半からデータベース管理者が参画できれば、物理設計局面へ円滑につなげることができ、プロジェクトの品質向上を期待できる
データベース管理者の物理設計での役割
データベースの物理設計
– 環境設定(パラメーター定義)、資源(テーブルやインデックスなど)の配置が主な作業
– 論理設計をどのように物理的に配置するか• 有限な資源の分配を考えること
– チューニングは、物理設計を 適化すること
データベース管理者が中心になる作業であり、パフォーマンスや運用への影響が大きい
© 2009 IBM Corporation 5
(良い物理設計は、パフォーマンス障害も少ない)– 使用できるハードウェア資源と業務要件をバランスさせて 善の設計(定義)をする
• データベースのテーブルやインデックスなどの、ユーザーが直接使用するオブジェクトを、どのようにディスクに定義するか
• データベース自身の管理に使用するログなどのオブジェクトを、どのようにディスクに定義するか
• データベース自身の構成パラメーターの 適化を考える
RDBMSの種類(DB2やORACLE)により実装方法は異なる
物理設計の重要性
不適切なデータベース物理設計が引き起こす問題
– チューニングが難しい
• キャパシティープランニングによる資源が十分でない– 十分に資源があってもパラメーター設定が悪く効率良く利用できない
• ディスクの装置のi/oが 終的にボトルネックになる
• 過度の正規化による多大なジョインの発生
– 要件を満たすことができない
• パフォーマンス要件をクリアするためには、業務要件把握した物理設計が必要
• 運用要件をクリアするためには、設計やパラメーター設定の検討が必要
© 2009 IBM Corporation 6
• 運用要件をクリアするためには、設計やパラメーター設定の検討が必要
– 拡張が難しい
• 拡張時の停止時間を短く、作業時間を少なくしておきたい
データベース物理設計
データベース論理設計書
業務要件の確認運用要件の確認
キャパシティープランニング
オブジェクトの定義
各種パラメーター設定
ディスクへの配置
OSパラメーター設定
オブジェクト作成DB2パラメーター設定
物理設計の影響
パフォーマンス・チューニングへの影響
運用設計への影響
業務開発への影響
モデリング データベース物理設計 パフォーマンス・チューニング
© 2009 IBM Corporation 7
キャパシティープランニングデータベース定義(テーブル、インデックス etc.)配置パラメーター設定
データベース運用設計
SQL標準作成
監視
問題判別
データベース物理設計のパフォーマンスへの影響
オブジェクト作成ディスクへの配置
オプティマイザー(アクセス・プラン)
CPUの有効利用
パフォーマンスの要素
キャパシティープランニング
物理設計
論理設計
© 2009 IBM Corporation 8
DB2パラメーターOSパラメーター
CPUの有効利用
メモリーの有効利用
i/oの有効利用
キャパシティープランニング
論理設計業務要件運用要件
ロック(Lock)
パフォーマンスへ影響が大きい処理
オプティマイザー(アクセス・プラン)
パフォーマンスの要素
CPUの有効利用
インデックスの利用
ソート処理方法
ジョイン処理方法
影響の大きい処理
© 2009 IBM Corporation 9
CPU並列処理
メモリーの有効利用
i/oの有効利用
CPUの有効利用 ジョイン処理方法
キャッシュ(バッファー)
先読み
i/o並列
LOCK処理ロック(Lock)
本当は、もっと複雑に影響
データベース・ストレージ
1 CPU coreあたり、6-20 (15-20)の物理スピンドル数を確保する
– 多いほどベター
DB2 データベース・サーバー / DPF データベース・パーティションごとにLUNを割り当てる
2レベルでストライプする
DB2のトランザクション・ログは、他のデータベース・オブジェクトとは分離し、別ディスク(LUN)上に配置する
RAWデバイスではなくファイル・システム・コンテナーを検討する—LUNごとに1ファイル・シス
© 2009 IBM Corporation 10
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設定を使用する <デフォルト>
クイズ 表スペースを3RAID上に構成するなら?
RAIDRAIDRAID
コンテナ表スペース
ファイル・システム
答え
悪
© 2009 IBM Corporation 11
RAIDRAIDRAID
RAIDRAIDRAID
コンテナ コンテナ コンテナ
コンテナ コンテナ コンテナ
表スペース
表スペース
コンテナ
ファイル・システム
ファイル・システム ファイル・システム ファイル・システム 推薦
悪
自動ストレージ・データベース V8.2.2
– 単一区分の自動ストレージ・データベース登場
V9.1より
– DPF データベース・パーティションもサポート新規作成データベースは自動ストレージDB(省略時)
• CREATE DATABASE DB3 ON /dbpath1, /dbpath2, /dbpath3, /dbpath4• CREATE TABLESPACE TS1
INITIALSIZE 500 K
© 2009 IBM Corporation 12
INITIALSIZE 500 K INCREASESIZE 100 K MAXSIZE NONE
– ベストプラクティス通り
表スペース TS1
構成パラメーター自動化 V8.2
– DFT_PREFETCH_SZ=AUTOMATIC (省略時)• create tablespaceでPREFETCHSIZEを省略して指定を自動化
• = コンテナ数*(物理スピンドル数)*extentsize– 表スキャン、リストプリフェッチ、ユーティリティの性能向上
• DB2_PARALLEL_IO=* レジストリー変数で物理スピンドル数を決定– 省略時 6スピンドル
– *:スピンドル数 または 表スペースID:スピンドル数, .. と指定
© 2009 IBM Corporation 13
– NUM_IO_SERVERS=3 (省略時)• 拡大を強く推薦
– NUM_IO_CLEANERS=1 (省略時)• 拡大を強く推薦
IO_SERVERの用途
AP/ユーティリティ SELECT * FROM 表
表スキャン、リスト・プリフェッチ、バックアップ、リストア、create index、MDCの並列IOによる性能向上
– エクステント単位IO
要求並列度PREFETCHSIZE / EXTENTSIZE
(DB2_PARALLEL_IO=*の時)
© 2009 IBM Corporation 14
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数から算出
© 2009 IBM Corporation 15
• CPU数から算出
– 自動的にベスト・プラクティスに準拠
• 不用意なprefetchsize=extentsizeの自動排除
• 不用意なnum_ioservers不足の自動排除
• 表スキャン、リスト・プリフェッチ、バックアップ他を 適化
パフォーマンス チューニングの心得
まず、これだけはチェック
– データを取る量が少ない方が速い
– 必要なデータは、できるだけメモリー上で操作できる方が速い
© 2009 IBM Corporation 16
– 必要なデータは、できるだけメモリー上で操作できる方が速い
• メモリー上のデータアクセスと、ディスク上のデータアクセスでは、1,000倍以上の差がある
– 必要以上に排他制御することは、待ちを増加させる
データを取得する量が少ない方が速い
物理アクセスはページ単位
– これから先の講義でも、時々思い出す必要がある
インデックスを利用できれば、絞り込んだデータアクセスが可能
– 単にインデックスを利用しているだけでなく、どこまで絞り込みが可能なインデックスを使用しているかがポイントになる
– DB2のテーブル・タイプによる実装も検討可能
© 2009 IBM Corporation 17
– DB2のテーブル・タイプによる実装も検討可能
• 多次元クラスター表(MDC)、パーティション表(テーブル・パーティション)
必要なデータは同じページにまとまっている方が良い
– Key word:クラスター・インデックス、再編成、圧縮
必要なデータはできるだけメモリー上で操作
データをメモリー上で操作するには
– バッファープール
– ソート処理用のヒープ
– パッケージのキャッシュ
– その他のヒープ
© 2009 IBM Corporation 18
サーバーで使用できるメモリーの大容量化、DB2 UDBの64ビット対応、DB2のSTMMによる自動設定により、細かなメモリー使用デザインの重要性は少なくなってきている
– インデックスの設計も含め、適切な基本設計を行うためには自動設計、構成機能の結果を、取捨選択するための知識が必要となる場面はある
例
© 2009 IBM Corporation 19
通常の表に保管されたデータ
© 2009 IBM Corporation 20
必要な行へのアクセスのために大
量の不要な行も読み込む
ひとつのクエリーを処理するのはひ
とつのCPUのみ。
複数パーティションにハッシュ分割(DBパーティション)
P 1 P 2 P 3
© 2009 IBM Corporation 21
ひとつのクエリーを複数のCPUを
使って並列に処理することができる
複数パーティションにハッシュ分割して並列処理
P 1 P 2 P 3
© 2009 IBM Corporation 22
依然として不要なI/Oは存在。
可用性を損なうことなく大量
データの入れ替えを行いたい。
データをレンジ分割して保存(パーティション表)
P 1
Jan
P 2 P 3
© 2009 IBM Corporation 23
Feb
Mar条件に合致したパーティショ
ンのみを参照すればよい
パーティションの高速削除と
追加が可能
データをレンジ分割して保存(パーティション表)
P 1
Jan
P 2 P 3
© 2009 IBM Corporation 24
Feb
Mar
未だ不要なI/Oが残っている
各行をブロックに整理して保管(多次元クラスター表)
P 1
Jan
P 2 P 3
© 2009 IBM Corporation 25
Feb
Mar
同じ値を持った行同士を同じ
ブロックに集めて保管。
必要な行を取り出すための
I/Oが 小限で済む
各行をブロックに整理して保管(多次元クラスター表)
P 1
Jan
P 2 P 3
© 2009 IBM Corporation 26
Feb
Marこれ以上I/Oの効率を上げるこ
とはできないか???
各ブロックに格納される行数を増やす(行圧縮)
P 1
Jan
P 2 P 3
© 2009 IBM Corporation 27
Feb
Mar
各ブロックにより多くの行を保管
し、ディスク容量の削減が可能。
必要な行を取り出すためのI/O
がさらに少なくなる。
パーティションDB(DPF、データベース・パーティション)
ハッシングによるデータ分散
– シェアード・ナッシング・アーキテクチャー
大規模DBの実現
– SQL、ユーティリティの高速並列処理
– 巨大な表、表スペース、バッファープール
– 複数サーバーによる
Interconnect
ハッシングによるデータ分散
動的なパーティション追加
– DB2を停止せずに追加可能
V9.7
© 2009 IBM Corporation 2828
– 複数サーバーによるインスタンス構成
Database PartitioningFeature(V8/V9)
InfoSphere Warehouse (V9.7)
– InfoSphere Balanced Warehouse に採用されている(初期導入/DB構成済み)
DBパーティション0
DBパーティション1
DBパーティション2
DBパーティション3
表 T1 表 T1 表 T1 表 T1
ハッシングによるデータ分散
CREATE TABLE T1 ( COL1 INT, COL2 CHAR(3) )
DISTRIBUTE BY (COL1)
ハイ・パフォーマンスを支える並列処理
シェアード・ナッシング・アーキテクチャーによる並列処理
アプリケーションは意識することなく、複数サーバーによるスケールアウトにより、並列処理を実現します。
データベースの区分を持つサーバー群
– 全体で1つのデータベース
– パラレル・オプティマイザーと高速通信経路で接続
ハッシングにより均等にデータ分散アプリケーションDB2クライアント
どの区画に接続しても同じ結果が返ってきます
1000区分までのスケーラビリティー1000区分までのスケーラビリティー
透過的データアクセス透過的データアクセス
アプリケーションDB2クライアント
100TBのDBも100区分で1TBのDB設計と同じ
© 2009 IBM Corporation 29
単一データベース
データベース区画1
CPUCPU
メモリー
データ
・・・
データベース区画2
CPUCPU
メモリー
データ
データベース区画3
CPUCPU
メモリー
データ
データベース区画N
CPUCPU
メモリー
データ
データベース区画N-1
CPUCPU
メモリー
データ
DB2クライアント同じ結果が返ってきます
DB2クライアント
IBM Balanced Warehouse は、事前テスト済みのスケーラブルかつ完全に統合された InfoSphere Warehouse、Server、および Storage のシステム・コンポーネントで構成される、完全なデータウェアハウジング・ソリューションです。
IBM Balanced Warehouses は、お客様のビジ
ネス・インテリジェンス・ソリューションの基盤として
すぐに使用できるリアルタイム・データウェアハウジング・ソリューション
IBM InfoSphere™ Balanced Warehouse 構築・設計の容易性スピード構築を実現
© 2009 IBM Corporation 30
Balanced Warehouse
ネス・インテリジェンス・ソリューションの基盤として提供する必要のあるあらゆるものを含んでいます。その中でも、特筆すべきは以下の通りです。
• 堅固な InfoSphere Warehouse ソフトウェア• 最新の IBM サーバー・テクノロジー• 高性能の IBM ストレージ• 包括的なトータル・ソリューション・サポート
IBM InfoSphere Balanced Warehouse ソリューションは、パフォーマンスと信頼性に関して厳正なテスト、検証、および調整が実施されます。InfoSphere Balanced Warehouse は、複数のお客様の成功事例を通じてIBM が得たベスト・プラクティスに基づき、開発されています。IBM は、信頼性とセキュリティーの向上のため、 製品をリリースする前に、システムの構成とテストを行い、さらに、 高クラスの照会パフォーマンスが達成されるように、システムのきめ細かい調整を行っています。
正確なバランスによる 適パフォーマンス
IBM InfoSphere™ Balanced Warehouse
© 2009 IBM Corporation 31
ステップ 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
© 2009 IBM Corporation 32
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)
© 2009 IBM Corporation 33
– 索引
の利用を検討する
テーブル・パーティショニグ(パーティション表)
ひとつの表を複数の区分に分割
古い区分を高速にロールアウト (区分のデタッチ)
既存データはオンライン状態で、新しい区分をロールイン (区分のアタッチ)
区分単位でのアクセス性能向上
各区分は異なる表スペースに配置可能
パーティション索引のサポート
V9.7
© 2009 IBM Corporation 3434
Jan Feb Mar Apr
過去のデータはまとめて瞬時に
切り離し
新規データを個別にLOADして
から区分を取り付けDETACH ATTACH
日付などのレンジで区分に分割し整理するパーティション表
Jan
売上履歴表
読みたい区分のみにアクセス
多次元クラスタリング(MDC) 多次元クラスタリング(MDC)は、複数属性の値でデータを分類して、自動的に格納する機能
– 「2008年10月」の「DB2」の「東京」というような複数の属性をもつクラスター
クエリー要求に対する高いパフォーマンスを提供
– 次元別検索のパフォーマンス向上• 同じ値のレコードは同じセルに存在するため、範囲を指定した検索などのように比較的多量の連続したアクセスに有効(ブロック・ベース(BID)の索引を使用
した照会処理)
– 削除のパフォーマンスアップ(ブロック削除が可能)
データ並べ替えを目的とした再編成不要
© 2009 IBM Corporation 3535
データ並べ替えを目的とした再編成不要
– 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
© 2009 IBM Corporation 36
= Row
1 index entry per block
MDC利用の検討例
MDCは特に情報系の大規模検索時に有効な場合が多い
時系列の履歴表などには年月(または年月日)を次元したMDCを検討してみる
次元列がイコール条件や範囲条件となる照会や削除が多用される場合
– 同じ値のレコードは同じセルに存在するため、範囲を指定した検索などのように比較的多量の連続したアクセスに有効
比較的ユニーク性の低い列を使用した条件しか指定できない検索
– 従来のレコード・インデックスでは、ユニーク性が低く、不向きとされていた列に使用
© 2009 IBM Corporation 37
– 従来のレコード・インデックスでは、ユニーク性が低く、不向きとされていた列に使用
– ユニーク性の高い列では逆効果になるケースもある
• 生成列を使用して、ユニーク性を落とす方法を検討する適切なレコード・インデックスとの併用を検討する
• 次元列をレコード・インデックスの第一列に指定しない(カーディナリティーの低い列が第一列だとアクセスプランが不安定になりやすいため)
OLTPにおける、MDC利用による再編成不要運用
DB2 V9.7の圧縮機能
表の行圧縮
– 辞書を使った行レベルのデータ圧縮• 列単位ではなく行単位
– 圧縮の対象のデータ(※)圧縮可能なデータの種類はバージョンによって異なります。
• 表データ
• XML(INLINE)データ(V9.5~)
• XML(XDA)データ(V9.7)
V9.7で圧縮機能が大幅に強化
© 2009 IBM Corporation 38
• 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データベースと比較して、
© 2009 IBM Corporation 39
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. 「表圧縮+索引圧縮」と「表圧縮+索引圧縮なし」の比較
結果
© 2009 IBM Corporation 40
(参考)索引圧縮のパフォーマンスへの影響検証
1. 「表圧縮+索引圧縮」と「表圧縮なし+索引圧縮なし」の比較
結果まとめ
圧縮により、表・索引ともにバッファープールヒット率が向上した影響で、レスポンスタイムの向上に繋がったと思われる。
© 2009 IBM Corporation 41
(参考)索引圧縮のパフォーマンスへの影響検証
2. 「表圧縮+索引圧縮」と「表圧縮+索引圧縮なし」の比較
シナリオ
LINEITEM1表にcol1(varchar)索引を作成。
以下のパターンでのOLTPツールでのSELECT,UPDATE,INSERT,DELETEのパフォーマンスを検証した。
表用バッファープールのヒット率が100%になるように、バッファープールサイズを調整。
①LINEITEM1表を圧縮+col1索引を圧縮
②LINEITEM1表を圧縮+col1索引を圧縮なし
© 2009 IBM Corporation 42
・環境
・SQL
(参考)索引圧縮のパフォーマンスへの影響検証
2. 「表圧縮+索引圧縮」と「表圧縮+索引圧縮なし」の比較
結果
© 2009 IBM Corporation 43
(参考)索引圧縮のパフォーマンスへの影響検証
2. 「表圧縮+索引圧縮」と「表圧縮+索引圧縮なし」の比較
結果まとめ
圧縮による、索引のバッファープールヒット率向上だけでも大幅なレスポンスタイム向上になる。
バッファープールヒット率が共に100%の場合、レスポンスに変化はほとんど見られない。
参考(バッファープールヒット率100%の場合)
© 2009 IBM Corporation 44
索引圧縮使用上の注意
圧縮表に対して新しく作成する索引は、明示的にCOMPRESS NOを指定しない限り、圧縮索引となる。
– 一度圧縮索引として作成されたものを非圧縮に戻したい場合には、COMPRESS NOを指定してALTER INDEXを実行し、さらに索引再編成を行う。
非圧縮表に対して新しく作成する索引は、明示的にCOMPRESS YESを指定しない限り、非圧縮索引となる。
© 2009 IBM Corporation 45
定しない限り、非圧縮索引となる。
– 一度非圧縮索引として作成されたものを圧縮したい場合には、COMPRESS YESを指定してALTER INDEXを実行し、さらに索引の再編成を行う。
圧縮表の定義されたV9.1、V9.5のデータベースをV9.7に移行しても、索引圧縮は行われない。
– 索引圧縮を行うには、ALTER INDEX・・・COMPRESS YES、およびREORG INDEXを実行する
インデックス(索引)のメリットと考慮点 メリット
– インデックスを使った照会、更新、削除処理の性能向上
– ロックの範囲が小さくなり、処理の並行性が上がる
– データ値が一意であることを保証する(ユニーク/非ユニーク)インデックススキャン
•全データをスキャン •条件
表スキャン
表スキャン
インデックス設計
© 2009 IBM Corporation 46
表 インデックス表
•全データをスキャンして、条件にあうかどうかを判定•途中で条件にあうデータを見つけても
後まで検査
•条件にあうデータのみをスキャ
ン
考慮点
– ディスク容量が必要
– 更新処理、LOAD処理への影響
表スキャン
DB2の索引(レコード索引)
レコード索引(通常の索引:B+ツリー構造の索引)
– ユニーク索引
• 対象レコードがユニークになることを保証
– 複合列索引
• 索引列に複数の列を定義できる
• ユニーク索引、クラスター索引も複合列で構成することが可能
– クラスター索引 (次頁参照)
© 2009 IBM Corporation 47
– クラスター索引 (次頁参照)
– 索引オプション
• 双方向索引– 昇順、降順ともに効率よく索引を使用できる
• INCLUDE文節を使ったユニーク索引– 一意性の検査の対象にはしないが、索引には追加したい列を含めることができる
– 動的ビットマップ索引
• DB2UDBオプティマイザーが必要と判断した時に自動的に作成され、ユーザーが特別に指定する必要はない
クラスター索引とは
非クラスター索引(省略時) INSERT時に使われるデータページは 初に見つけた空エリア ランダムアクセスに対して有効
20
529
28
33
2928205 3533 6550
35データ・ページ
© 2009 IBM Corporation 48
クラスター索引
– INSERT時に使うデータぺージはデータの並び順が索引順とできるだけ同じになるよう試みる
• 表にあらかじめ空スペースを用意しておくと効果的(デフォルトでは空スペースがないため、alter tableで変更する必要がある)
– 順次スキャン時の I/O が軽減され、処理効率が上がる
20
5
29
28 33
50
65
35
2928205 3533 6550
その他の索引設計時の考慮点
索引設計の考慮点
–複数列索引の第一列•ユニーク性の高い列にする、または検索条件で も頻繁に指定される列
•ユニーク性が高い=値の種類が多い=カーディナリティが高い
•不適切な索引の例–第一列の値が種類が少ない、特に1通りしかない索引
複数列索引
© 2009 IBM Corporation 49
–列上の他の索引キーの部分キーとなるような索引は作成しないようにする•indexA( a , b , c )のインデックスと、indexB ( a , b )が存在する場合、
indexBはindexAでまかなえるので、不要。
第一列 第二列 第三列
複数列索引
インデックス定義のまとめ 表にはインデックスを付ける
– 表が非常に小さいなど、特殊な場合を除き1つはインデックスを作成する
インデックス数の一般的な目安
– OLTP であれば 2~3個/表
– DSS であれば 5~6個/表
複合列インデックス、双方向インデックスを利用して索引の数を減らすのも1つの手法
インデックス候補となる列
© 2009 IBM Corporation 50
• WHERE句で指定された検索条件の列
• ソート列(DISTINCT、ORDER BY、GROUP BY)として指定されることが多い列
• JOIN列 など
– そのインデックスでどれくらい結果行を絞り込めるか?を考える
– 設計アドバイザーの利用
単純に表にインデックスを作成しただけでは、インデックスが使われるかは分からない
– Explainによるアクセス・プランの確認
– Db2pdコマンドによる確認• db2pd –db データベース名 -tcbstats index
– 設計アドバイザーにより、特定Workloadで使用されていないインデックスの確認も可能
パラメーター設定
DB作成時に構成アドバイザーはデフォルトでオン
– 構成アドバイザー
• DB構成パラメーターの推奨値を提示
• 入力した情報(メモリー量、トランザクションの負荷、トランザクション数、トランザクションの特徴など)に基づいて推奨構成パラメーターを提示
• 更新コマンド、スケジュール実行も可
© 2009 IBM Corporation 51
• 更新コマンド、スケジュール実行も可能
– パラメータ結果の反映方法としては、即実行、スケジューリング実行、値を編集して実行など選択可能
バッファープールも含めメモリー関係のチューニングはSTMMでストレステスト時に決定
(参考)アーキテクチャー(スレッドモデルへの変更)
スレッドモデル
– DB2 9.5 以降、従来のプロセスモデルからスレッドモデルに変更
– “db2sysc”というプロセスの配下で様々なスレッドが稼動
– スレッドモデルのメリット
•リソースの節約/パフォーマンス向上/使いやすさの向上
データベースのアプリケーション要求を処理するために起動するエージェント(db2agent)や各データベースで使用されるプリフェッチャー(db2pfchr)、ページクリーナー(db2pclnr)、ログ書き込みを担当するdb2loggwなどのスレッドが起動
© 2009 IBM Corporation 52
ログ書き込みを担当する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 データベース・エージェントによって割り振られる
© 2009 IBM Corporation 53
• アプリケーション要求に応えるために DB2 データベース・エージェントによって割り振られる
メモリー管理
進化した自己チューニング・メモリー管理 (STMM)
– DB2が自動的にメモリー・チューニングを実施
• メモリー構成の簡素化
• メモリー不足によるエラーの削減
オペレーティング・システムのメモリ管理
DATABASE MEMORY
INSTANCE MEMORY
APPLICATION
MEMORY
© 2009 IBM Corporation 54
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に設定
© 2009 IBM Corporation 5555
• 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つのバッファー・プールを利用
– チューニング等の管理が簡素化される
– メモリー全体が も効率的に使用される
以下のケースでは複数バッファー・プールも検討する
バッファー・プール設計における考慮点
© 2009 IBM Corporation 56
以下のケースでは複数バッファー・プールも検討する
– デフォルトの 4KB 以外のページ・サイズを使用する場合• デフォルトのサイズはDB作成時に指定が可能なので、サイズを統合することも可能
– 多数の短い更新トランザクション・アプリケーションによって、頻繁に高速でアクセスされる表に専用バッファー・プールを作成する場合
– 常に表全体が走査される、メイン・メモリーより大きな表に専用バッファー・プールを作成する場合
– インデックス専用のバッファー・プールを作成する場合
– マスター・テーブル専用のバッファー・プールを作成する場合
複数バッファー・プール割り当て例
頻繁にアクセスする大切なデータには、専用のバッファー・プールを割り当てる
– 小さなテーブルには、テーブルが全部入りきるサイズのバッファー・プール
– 大きいテーブルには、重要なインデックスに専用のバッファー・プールを割り当てる
その他のテーブルは、まとめて大きなバッファー・プールを割り当てる
マスター用一般テーブル用 大規模テーブル大規模テーブルデータ用
バッファープール バッファープール バッファープール バッファープール
© 2009 IBM Corporation 57
マスター用一般テーブル用 大規模テーブル
インデックス用データ用
頻繁にアクセスするのでメモリーに常駐させたい データは大容量なのでメモリー消費を制
限したい。しかしインデックスは頻繁に使うのでメモリーに常駐させたい
特殊要件はないので、共用のバッファープールを使用
マスターテーブル
大規模テーブル
大規模テーブルのインデックス
一般テーブル
DB2 UDB における定義の流れ
物理特性、運用管理基準の決定
– インスタンスの定義 : db2icrt– データベースの定義 : CREATE DATABASE– バッファープールの定義 : CREATE BUFFERPOOL– スキーマの定義 : CREATE SCHEMA– 表スペースの定義 : CREATE TABLESPACE
論理設計の反映
– テーブルの定義 : CREATE TABLE• MQT、MDCの利用
インスタンス
システム
© 2009 IBM Corporation 58
• 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により自動で柔軟なメモリ割り当てが可能
© 2009 IBM Corporation 59
能