簡単シリーズ パラメーター・チューニング編 2004/03

簡単シリーズ
パラメーター・チューニング編
2004/03
はじめに
この資料は、パフォーマンス・チューニングを４つに分けて解説しています。
チューニングについて
ここではチューニングについての基本的な考え方を解説しています
チューニングの第1歩
ここでは必ず行っていただきたいチューニングについて解説しています
チューニング 基本編
全てのデータベース・サーバーで設定していただきたいパラメーターについて解説しています
チューニング 個別編
特定のハードウェアやアプリケーションによって設定したほうがよいパラメーターを解説しています
なお、この資料はデータベースのパラメーター・チューニングを中心に解説しています。
チューニングについて
ここではチューニングについての基本的な考え方を解説しています
チューニングとは？
パフォーマンス
が悪い
チューニング
トラブルの原因
アプリケーション・デザインのミス
データベース・デザインのミス
リソース不足
パラメーター設定ミス
チューニング
9アプリケーション・デザインの変更
9データベース・デザインの変更
9リソース追加
9パラメーター・チューニング
チューニングとは？
データベースのチューニングの多くは、パフォーマンスの問題が顕在化した場合に、データベースやオペレーティング・システムのパラメーター調整という形で実施されます。
では、パフォーマンスのトラブル発生時にデータベースのパラメーター調整を行うことで効果が見られるのでしょうか。
答えは、「イエス」でもあり「ノー」でもあります。
一般的に、システムを開発する際にはいくつかのフェーズが存在します。非常に大雑把にいうと
設計 / 開発 / 運用
と分けることができます。（パフォーマンス・トラブルの多くは開発の終盤の本番前テスト時や、運用時に表面化します。）
そのうち、データベースのチューニングは、いつ行うのがよいのでしょうか。
答えは「設計」段階です。
なぜならシステムやデータベースのパラメーターを調整しても、物理的にメモリーが増えたりＣＰＵが速くなるわけではありませんし、ネットワークの転送速度が速くなるわけではありませ
ん。つまり、パラメーター・チューニングは、システム設計や、物理リソース、アプリケーション設計に大きく制限されるからです。
一般的にパフォーマンス・トラブル時に行われる（パラメーター・）チューニングは、設計や開発段階からパフォーマンスを考慮したシステムの場合は、非常に大きな効果を発揮することが
できますが、そうでない場合にはチューニングにかけた作業時間ほど、また期待していたほど効果が見られない場合が多く見受けられます。
パフォーマンス・トラブルの原因の多くは次のものです。
zアプリケーション・デザインのミス（大量のアンサー・セットを返す）
zデータベース・デザイン（索引が作成・使用されていない、参照の整合性などが保証されていない）
zシステム・リソースの不足（メモリー不足、CPUパワー不足）
zパラメーターの設定ミス（バッファー・プール、デッドロックなど）
従って、データベースをチューニングするには以下のことが必要となります。
zアプリケーション・デザインの変更（SQL文の変更、アンサー・セットの考慮、インターフェイスの変更など）
zデータベース・デザインの変更（データベース/テーブル設計の変更など）
zシステム・リソースの変更（メモリー増設、CPU変更など）
zパラメーター・チューニング（パラメーター変更など）
チューニングの目的と効果
チューニングの理想
アプリA
アプリB
バックアップ
ロード
アプリC
チューニングの現実
アプリA
アプリC
バックアップ
ロード
アプリB
チューニングの目的と効果
データベースのチューニングは、当然今よりも良い状態にシステムを維持することを目的として実施されます。
つまり、チューニングの実施対象となるシステムの全体が、処理が速くなるなど良い状態になることが理想です。
しかし、現実のシステムのほとんどでは、あるレベルまでは全体のパフォーマンスの向上が見られますが、ある一定レベル以上は向上しません。それ以上のパフォーマンスを追及すると、
向上するものがある一方で低下するものが発生するようになります。
実際のプロジェクトなどでチューニングを行う場合には、チューニングにかけられる期間や費用を考慮して以下の2点を明確にしましょう。
zパラメーター・チューニングでやめるのか、コーディングや設計までを対象とするのか、チューニングの方法を明確にする。
zシステム全体のチューニングを行うのか、それとも特定のアプリケーションのみに注力するのかなど、チューニングの対象を明確にする。
なぜなら、チューニングの目標値を決めて、それを目標にチューニングを行うことが理想ですが、現実的には既に説明したように、パラメーター・チューニングには限界があるために、場
合によっては設計やコーディングの変更が発生します。しかし、設計やコーディングの変更はプロジェクト上は手戻りとなり多くの期間や費用が発生するために、システム利用者（お客
様）、システム開発者双方に不利益をこうむる場合が少なくないからです。
チューニングの前に
問題点の原因は本当は何ですか？
●アプリケーション
●ネットワーク
●データベース
・・・・etc
パフォーマンスが悪い原因は､どこでしょう。
データベースが悪い場合には､DB2のスナップショット･モニターを
活用し､原因の概略を確認してみましょう
チューニングの前に
データベースのチューニングの前に、問題点をきちんと切り分け原因の予測をしてみましょう。
「パフォーマンスが悪い」だけでは雲をつかむような話で､具体的なチューニング作業に入るまでに時間もかかります。
当然のことですが、パラメーター･チューニングやSQL文の変更でパフォーマンスが向上するのは、純粋にデータベース内部の部分だけです。
しかしほとんどの場合、パフォーマンスが悪いといわれるのは､ユーザーに対するレスポンス時間です。
一般的なアプリケーションでは、レスポンス時間は大まかに以下の3つ部分に分けられます。
z
ビジネス・ロジック部分
z
ネットワーク部分
z
データベース部分
それぞれ時間を出力するなどして､まず本当に時間がかかっていて､ボトルネックになっているのはどこかを確認しましょう。
大量のデータを処理している場合には､ビジネス・ロジック部分やネットワークに意外と時間がかかっている場合があり､データベース部分をチューニングしてもレスポンス時間への影響が
少ない場合もあります。
データベース部分に関しては、特定の業務/SQL文が遅いのか、全体が遅いのかを確認しましょう。
また､スナップショット･モニターを取得して、データベース内部の動きを大まかにつかんでみましょう。
大まかにでもデータベースの動きがわかれば、どこをチューニングする必要があるか（しなければいけないか）を判断することができるようになります。
チューニングの第一歩
ここでは必ず行っていただきたいチューニングについて解説しています
構成アドバイザー
構成アドバイザーを実行しましょう
必ず一度は構成アドバイザーの実行を！！
構成アドバイザー
構成アドバイザーを実行しましょう
DB2 UDBは導入時やデータベース・オブジェクト(インスタンスやデータベース、テーブルなど)作成時に初期パラメーターで構成を実施します。しかし初期の構成パラメーターは小さ
いので、ほとんどの場合、最適なパフォーマンスは得られません。
構成アドバイザーは管理者が使用するツールで、質問に答えるだけで自動的にチューニングが行われます。
詳細なチューニングを行う前に、構成アドバイザーを一度は実行してみましょう。
実行コマンド例： 構成アドバイザーはコントロール・センターから起動するGUIツールです。
コントロール・センターで、対象のデータベースを選択し、選択メニューから「構成アドバイザー…」を実行します。
質問項目：
・データベースに割り振るメモリー量を指定します。
デフォルトは80%。サーバーでほかのアプリケーションを実行している場合は100%より小さくします。
・データベースのワークロードのタイプを指定します。
「照会」、「混合」、「トランザクション」から選択します。ほとんどのデータベースは混合で対応できます。
・データベースで実行しているトランザクション数の見積もりを設定します。
コミット間に実行されるステートメント数を「10以上」、「10未満」から選択します。
１分間に実行されるトランザクション数の見積もり数を入力します。
・データベース管理の優先順位を選択します。
「トランザクションのパフォーマンスを速くする（リカバリーはゆっくり）」、「両方」、「データベースリカバリーを速くする（トランザクションはゆっくり）」の3つから
選択します。
・データベースのデータ量を考慮して推奨値を計算するかどうかを指定します。
「はい」、「いいえ」のどちらかを選択します。
・データベースに接続するアプリケーションを見積もります。
ローカルとリモートのアプリケーションの平均数をそれぞれ入力します。
・データベースの分離レベルを選択します。
適切な分離レベルを「反復可能読み取り」「読み取り固定」「カーソル固定」「非コミット読み取り」の4つから選択します。
・推奨値のパラメーター設定の反映のタイミングを指定します。
「タスク履歴を保管しないで今すぐ実行」、「タスク・センターでタスクとして作成」から選択します。タスクとして作成した場合、スケジューリングすることが可
能です。
構成アドバイザーの内容は、AUTOCONFIGUREコマンドや、CREATE DATABASEのAUTOCONFIGUREオプションと同じ内容です。
AUTOCONFIGUREコマンドや、CREATE DATABASEのAUTOCONFIGUREオプションも、入力値をもとにバッファー・プール・サイズ、データベース・マネージャー構成パラメーター、
データベース構成パラメーターの推奨値を計算し、適用します。
実行コマンド例１：
実行コマンド例２：
AUTOCONFIGURE USING input-keyword param-value APPLY DB ONLY
CREATE DATABASE database-name … AUTOCONFIGURE USING input-keyword param-value APPLY DB ONLY
RUNSTATS (統計情報取得)
RUNSTATSを実行しましょう
サービス・イン直前など、データに大きな増減があった場合には必ずRUNSTATSを実行し、DB2 UDBが最適なアクセス・パスを選択
するようにしてください。
RUNSTATS (統計情報取得)
RUNSTATSを実行しましょう
DB2は、統計情報をもとにしたコスト・ベースの計算を行い、実際にデータを読むアクセス・パスを決定します。従って、統計情報が正しくない場合間違ったアクセス・パスを使用して
しまい、パフォーマンスが悪くなることがあります。
表に初めてデータを入れたり、大量データのLOADや、INSERT、DELETEなどでデータの件数が前回の統計情報取得（RUNSTATS）から極端に増減が発生した場合に、RUNSTATS
を実行し統計情報を取得し直す必要があります。
サービス・イン直前の本番データロード時など、データ件数に増減があった場合には必ずRUNSTATSを実行してください。またアプリケーションによっては日常の運用でデータの増
減が発生しますので、定期的なRUNSTATSを運用に組み込むことが必要です。
また、アプリケーションが静的SQLプログラムの場合には、統計情報がBIND時にアプリケーションに適用されるため、RUNSTATS実行後必ずBINDを実行し、アプリケーションが最新
の統計情報を使用するようにしてください。
RUNSTATSはコントロール・センターやコマンド行プロセッサーから実行することができます。
実行コマンド例：
RUNSTATS ON TABLE schema.table WITH DISTRIBUTION AND INDEXES ALL
確認コマンド例１：
SELECT * FROM SYSSTAT.TABLES WHERE TABSCHEMA='schema'
→“-1”が表示されるテーブルは統計情報が取得されていない。
SELECT INDNAME,TABNAME,CLUSTERRATIO,CLUSTERFACTOR FROM SYSSTAT.INDEXES WEHRE INDSCHEMA='schema'
→CLUSTERRATIOとCLUSTERFACTORがともに“-1”と“-1.00000000000000E+000”の索引については統計情報が取得されていない。
確認コマンド例２：
出力ファイル例 ：
db2look -d database-name -u userid -m -o output.sql
RUNSTATS ON TABLE “DB2ADMIN"."ORG" ;
UPDATE SYSSTAT.INDEXES
SET NLEAF=-1,
NLEVELS=-1,
FIRSTKEYCARD=-1,
（中略）
NUMRIDS_DELETED=-1,
NUM_EMPTY_LEAFS=-1
WHERE TABNAME = 'ORG' AND TABSCHEMA = ‘DB2ADMIN';
テスト・システムで実稼働環境をモデル化するにはdb2lookを実行します。
データベース名(-d)、スキーマの所有者名(-u)を指定し、模擬モード(-m)でdb2lookを使用すると、テスト・データベースのカタログ統計を実働のものと一致さ
せるのに必要なupdateステートメントを生成することができます。
出力ファイルのupdateステートメントで、“-1” は統計情報が取得されていないことを意味しています。
チューニング 基本編
全てのデータベース・サーバーで設定していただきたいパラメーターについて解説しています
基本設定 - 一覧表
設定、もしくは確認しておいたほうがよいパラメーターの一覧です。
パラメーター
省略時値
size
(CREATE/ALTER
BUFFERPOOL文)
IBMDEFAULTBP：
AIX環境：1000 (4K)
Windows環境：250 (4K)
prefetchsize
(CREATE/ALTER
TABLESPACE文)
dft_prefetch_sz：
AIX環境：32
Windows環境：16
データベース構成パラメーター
推奨値
備考
使用可能なメモリー量に応じて省略時値より増やす
extentsize × コンテナー数
DB2 UPDATE DB CFG FOR DB名 USING パラメーター 設定値
パラメーター
省略時値
推奨値
備考
num_ioservers
3
物理的なディスク数
最大でも物理的なディスク数＋3程度まで
num_iocleaners
1
物理的なディスク数以内
照会専用では0を検討する
logfilsz
AIX環境：1000(4KB)
Windows環境：250(4KB)
5000(4KB)
更新処理量やコミットの間隔に応じて省略時値より増やす
maxappls
Automatic
Automatic
最大同時接続ユーザー数よりは大きくする
locklist
AIX環境：100(4KB)
Windows環境：25(4KB)
5000以上
maxlocks
AIX環境：10
Windows環境：22
20以下
基本設定 - 一覧表
設定、もしくは確認しておいたほうがよいパラメーターの一覧です。
データベース・マネージャー構成パラメーター
DB2 UPDATE DBM CFG USING パラメーター 設定値
パラメーター
省略時値
推奨値
備考
diaglevel
3
3
障害等、不具合を調査する場合のみ4にする。
DB2レジストリー変数
db2set 環境変数 = 設定値
環境変数
省略時値
推奨値
備考
DB2COMM
導入環境による
TCPIP
使用しているプロトコルのみ指定
DB2_RR_TO_RS
指定なし(NO)
YES
DB2 V7の推奨値であり、分離レベルRRが必要なアプリケーションの場合NO
DB2 V8でType2索引の場合設定不要
解説 – size (CREATE BUFFERPOOL文)
バッファー・プールのサイズは大きくしましたか？
DB2はデータへのアクセスを、必ず物理メモリー上に確保されたバッファー・プール経由で行います。
具体的にはデータがアクセスされた際に、DB2はバッファー・プールを確認し、バッファー・プール内に対象データが存在した場合にはディスクへはアクセスを行わず、バッファー・
プール内のデータを返します。バッファー・プール内に対象データがない場合には、物理的にディスクへアクセスしデータを返します。（キャッシュを想像してください。）
従って、バッファー・プールが小さいとディスクへのアクセスが多くなり、メモリー I/O（バッファー・プールへの I/O）よりもはるかに遅いディスクI/Oが増えるために、レスポンスタイム
が遅くなることになります。
いくらたくさんの物理メモリーを積んでも、バッファー・プール・サイズに適当な値を設定しなければ、それらのメモリーをDB2は有効に使用しません。（大きくとりすぎてもＣＰＵ負荷
が高くなり効率が悪くなることがあります。）デフォルトでは、IBMDEFAULTBPバッファー・プールが使用されます。IBMDEFAULTBPの初期値はAIX環境では1000ページ、Windows
環境では250ページと小さすぎるので使用可能な物理メモリーのサイズに応じてバッファー・プール・サイズを拡張してください。
実行コマンド例１：
db2 alter bufferpool IBMDEFAULTBP size 10000
確認コマンド例１：
db2 select * from syscat.bufferpools
(syscat.bufferpoolsのnpagesの値を確認)
注１）使用可能な物理メモリーはデータベース専用マシンでも、メモリーサイズの75%程度を最大としてください。それ以上にするとOSなどが使用するメモリーが不足し、システムに
悪影響が出ることがあります。
注２）バッファー・プールはキャッシュと同様の働きを行います。従って、照会の分散度合いが大きい（キャッシュへのヒット率が悪い）ようなアプリケーションの場合は、バッファー・
プールを大きくしてもあまり効果が期待できません。その場合にはデータを対象とするのではなく、インデックスのみをバッファー・プールにいれるような工夫が必要です。
基本的に1つのデータベースに1つのバッファー・プールの利用が最もメモリー全体を効率的に使用し、かつ管理が容易です。しかし、以下の場合には複数のバッファー・プールの
作成を考慮すると良いでしょう。
z
ページサイズ4KB以外のバッファー・プールを作成する場合
（表スペースを作成する前に、表スペースのページサイズと同じページサイズのバッファー・プールの作成が必要である）
z
多数の短い更新トランザクション・アプリケーションによって頻繁にアクセスされる表があり、専用のバッファー・プールを作成する場合
z
インデックス専用のバッファー・プールを作成する場合
z
マスター・テーブル専用のバッファー・プールを作成する場合
実行コマンド例２：
db2 create bufferpool bufferpool-name size 10000
確認コマンド例２：
db2 select * from syscat.bufferpools
(syscat.bufferpoolsのnpagesの値を確認)
解説 – prefetchsize (CREATE TABLESPACE文)
prefetchsize は extentsize × 表スペース・コンテナー数にしてください
DB2 UDBは、索引やデータを実際読み込む際にバッファー・プールを経由します。しかし、通常のディスクI/Oを行うだけでは､無駄な待ち時間が生じることがあるため、必要なデー
タが物理的に連続したページに並んでいる場合や、そうでない場合にも、「順次事前取り出し」や、「リスト事前取り出し」といった事前取り出し（プリフェッチ）を行い､いくつかのデータ
ページを使用されるという想定でバッファー・プールに読み込み、ディスクI/Oのオーバーヘッドを軽減し、パフォーマンスの向上を図っています。
prefetchsizeは“CREATE TABLESPACE”または“ALTER TABLESPACE”を使用して設定します。省略した場合は、dft_prefetch_szデータベース構成パラメーターの値（AIX環境では
32ページ、Windows環境では16ページ）になります。
ユーザーの表スペースのextentsizeの値に表スペース・コンテナーの数を掛け合わせた値を明示的に指定することをお勧めします。
実行コマンド例１：
db2 update db cfg for database-alias using dft_prefetch_sz size
確認コマンド例１：
db2 get db cfg for database-alias
実行コマンド例２：
CREATE TABLESPACE ACCOUNTING
MANAGED BY SYSTEM
USING ('d:¥acc_tbsp', 'e:¥acc_tbsp', 'f:¥acc_tbsp')
EXTENTSIZE 32
PREFETCHSIZE 96
3つの別個のドライブの3つのディレクトリーを使用し、エクステント・サイズを32ページ、事前取り出しサイズを96ページに指定して、Windowsで通常のSMS
表スペースを作成。
注１）プリフェッチのサイズを大きくすると、大きな表の入出力時に効果が現れる場合があります。
注２）表スペースが複数のコンテナーから構成されている時は、複数の入出力を並列に実行することができます。従って､最大の効果を得るためには､コンテナーが別個の物理装置
（ディスク、ディスク・コントローラー）に作成されていることが重要です。
解説 - num_ioservers､num_iocleaners
num_ioservers､num_iocleaners はデータベースのディスク数分指定してありますか？
num_ioserversには、ディスクからバッファー・プールへのデータの読み込み（プリフェッチや、ユーティリティーなどの非同期I/O）を実行する入出力サーバー数を指定します。この数
が少なすぎても多すぎても、ディスクからメモリーであるバッファー・プールへのデータの読み込みが効率よく行われないために、ディスクI/Oに待ちが発生しレスポンスを悪くする
可能性があります。
num_iocleanersには､バッファー・プールへの書き込みを行うための空間（ページ）をあけるために､変更があったページをバッファー・プールからディスクへの書出しを実行するペー
ジ・クリーナーの数を指定します。（ページに変更がなかった場合は、ページは単純に上書きされます）
照会専用データベースではデータに変更が発生せず、バッファー・プールからディスクへ書き出す必要がないため、0の指定を検討してください。
ただし、このパラメーターを0に設定した場合は、開始されるページ・クリーナーがないので、その結果として、バッファー・プールからディスクへのページ書き込みのすべてを、デー
タベース・エージェントが実行することになります。データベースが多くの物理ストレージ・デバイスにまたがって保管されている場合は、物理ストレージ・デバイスの1つがアイドル
状態になる可能性が高いので、このパラメーターがデータベースに重大なパフォーマンス上の影響を及ぼす可能性があります。また、ページ・クリーナーが構成されていない場合
は、周期的にログがいっぱいになる状態がアプリケーションで検出される可能性があります。
num_ioservers､num_iocleanersには実際に稼動しているディスク数を指定してください。例えばRAID5構成で5＋P（パリティ）＋S（スペア）という場合は、6（5＋P）を指定します。
RAID1で片系6ディスク数のミラーリングの場合は6を指定します。
テーブルを作成する場合には1RAIDに１コンテナとし 1RAID中に複数のコンテナを持つテーブルを作成しないようにしてください。
実行コマンド例：
db2 update db cfg for database-alias using num_ioservers ディスク数
db2 update db cfg for database-alias using num_iocleaners ディスク数
確認コマンド例：
db2 get db cfg for database-alias
注）num_ioserversに設定するディスク数に関しては、物理ディスク数にする場合と、物理ディスク数 +1もしくは+2、+3とする場合もあり、環境や状況によって多少異なります。
解説 - logfilsiz
logfilsiz は大きくしましたか？
データベースは、データの整合性を保つために、更新や追加、IMPORT作業などのデータに対して行われた変更作業の全てを記録し保持しています。全ての変更は、一旦メモリー
上のログ・バッファーに書き込まれ、COMMIT時にディスク上のログ・ファイルに書き込まれます。logfilsizは、この書き込まれるログ・ファイルのサイズを指定します。
ログ・ファイルは障害時にデータベースを正常な状態に戻すために使用される重要なファイルです。従って、ある程度のサイズを確保しておく必要があります。
ログ・ファイルは1つでなく、複数のログ・ファイルを作成することが可能で、アクティブ・ログのファイル容量を増やすには以下の2つの方法があります。
z
ログ・ファイル数を増やす
z
1つのログ・ファイルのサイズを多くする
一般に、適度に大きなログ・ファイルを少数持つほうが、パフォーマンスに良い傾向が見られます。
従って、1作業単位（COMMITもしくはROLLBACKから、次のCOMMIT、ROLLBACKまで）の更新量、使用可能な物理ディスクのサイズを考慮し、logfilsizを拡張してください。
実行コマンド例：
db2 update db cfg for database-alias using logfilsiz 5000
確認コマンド例：
db2 get db cfg for database-alias
解説 - maxappls
maxappls は最大同時接続アプリケーション数以上を指定してください。
maxapplsはデータベースに接続できるアプリケーションの最大数を指定します。従って、データベース・システム側から見た最大同時接続アプリケーション数より、多い数字を設定
してください。
Automatic（デフォルト）に設定すると、接続されたアプリケーションを幾つでも任意の数だけ使用できます。DB2では、新しいアプリケーションをサポートするために必要なリソース
を動的に割り振ります。
任意の数を指定する場合で2フェーズ・コミットを使用する場合は、最大同時接続アプリケーション数より、さらに大きな数を設定してください。
実行コマンド例：
確認コマンド例：
db2 update db cfg for database-alias using maxappls 40
db2 get db cfg for database-alias
注１）同時接続アプリケーションが非常に多い場合には、maxapplsと同時にmaxagentsデータベース・マネージャー構成パラメーターや、locklistデータベース構成パラメーターを大
きくすることも考慮してください。
注２）その他に、同時接続数に関するパラメーターとして、max_connectionsおよびmax_coordagentsデータベース・マネージャー構成パラメーターの値を確認してください。
max_connectionsはインスタンスに接続できるアプリケーションの最大数を、max_coordagentsはコーディネーター・エージェントの最大数を指定するパラメーターです。
max_connectionsをmax_coordagentsより大きく設定すると、コネクション・コンセントレーター機能が作動し、コーディネーター・エージェントを複数の接続で使いまわすようになり、メ
モリー・リソースの消費を抑えることができます。
このパラメーターの値は、接続されたアプリケーションの数に、これらと同じアプリケーションで 2 フェーズ・コミットおよびロールバックを完了する処理で同時に実行される数を加え
た合計に等しいか、それよりも大でなければなりません。次に、どのようなときでも発生する可能性がある未確定トランザクション数をこの合計に追加します。
解説 – locklist、maxlocks
locklist や maxlocks は maxappls にあわせて増減してください。
データベースは､データの整合性のためにロックを必要とします。しかし､不必要なロックはロック待ちを起こす要因となり､アプリケーション並行性のパフォーマンス低下を招きます。
基本的な考え方として､不必要なロックやロック待ちが起こらないように､アプリケーションやデータベースを設計することが重要です。
しかし､ロックは必ず発生するものです。DB2 UDBは行ロックにおいて､そのロック情報を保持するために非常に小さい量のメモリーを消費します。(32ビットのプラットフォームでは
36もしくは72バイト、64ビットのプラットフォームでは56もしくは112バイト）しかし､大量の行ロックが発生すると､その量は無視できないものとなります。その場合DB2 UDBでは､ある
一定以上の行ロックが発生すると、有限のシステムであるメモリーの浪費を防ぎ､システムダウンなど重大なエラーを引き起こさないように､ロック・エスカレーションを行い､大量の
行ロックから表ロックへとロックのレベルを変更します。こうすることによって､メモリーを開放しシステムダウンを防ぎます。
ただし､表ロックは行ロックに比べてアプリケーションの並行性をさらに低減するので､ロックをきちんと管理して不用意なロック・エスカーレーションが発生しないように、保持できる
ロック情報の数を増やしておく必要があります。
保持できるロック情報の数を増やすには、locklistとmaxlocksのパラメーターを調整します。
locklistはロック情報のための割り振られる記憶域の量を示しています。データベースごとに1つのロック・リストが確保され､全てのロック情報が保持されます。
maxlocksはパーセントで指定され､ロック・エスカレーションを自動実行する比率を指定します。locklistに指定された記憶域とアプリケーション稼動中のロック数を比較し、1つのアプ
リケーションのロック数がmaxlocksの値（%）を超えると､ロック･エスカレーションが自動実行されます。
従って､locklistが小さくても、maxlocksが小さくても、頻繁にロック･エスカレーションが発生することとなり､並行性のパフォーマンスが低減します。
1つのアプリケーションに保持できる最大ロック数は以下の通りです。
(locklist * 4096 / 36 ) * (maxlocks / 100) ･･･32ビット・システムの場合
(locklist * 4096 / 56 ) * (maxlocks / 100) ･･･64ビット・システムの場合
実行コマンド例：
db2 update db cfg for database-alias using maxlocks 20
db2 update db cfg for database-alias using locklist 5000
確認コマンド例：
db2 get db cfg for database-alias
注）locklistを5000、maxlocksを20に設定すると、ロック情報の保持する記憶域として20ＭＢが確保され、32ビット・システムで約57000∼114000、64ビット・システムで約37000∼
73000のロック情報を保持できます。
解説 - diaglevel
診断データのキャプチャー・レベルは３に設定されていますか？
diaglevelはdb2diag.logファイルに記録されるエラーや警告の診断データのキャプチャー・レベルを指定しています。
キャプチャー・レベルは0∼4まであり、それぞれ以下のものをキャプチャーします。
z
0 − キャプチャーしない
z
1 − 重大エラーのみ
z
2 − 全てのエラー
z
3 − 全てのエラーおよび警告
z
4 − 全てのエラーおよび警告と通知メッセージ
何かのトラブル対策のために、DB2の診断データ・キャプチャー・レベルを4にした後に、その設定を戻すのを忘れて本番業務を行ってしまうことがあります。diaglevelが4だと、エ
ラーばかりでなく、警告やインフォメーションもdb2diag.logファイルに書き込まれるため、頻繁にディスクI/Oを発生しレスポンスの悪化を招きます。
通常の業務では、キャプチャー・レベルを3もしくは、それ以下に設定しておいてください。DB2のデフォルトのキャプチャー・レベルは3です。
実行コマンド例：
db2 update dbm cfg using diaglevel 3
確認コマンド例：
db2 get dbm cfg
解説 - DB2COMM
DB2COMM レジストリー変数は必要なプロトコルのみ設定してありますか？
DB2COMMレジストリー変数は、クライアントとサーバー間で使用される通信プロトコルを指定します。
DB2 UDBはデータベース起動時(DB2START時、正確にはインスタンス起動時)にこの変数を参照し、クライアントからの接続を監視するためのリスナーを起動します。従って使用
しないプロトコルが指定されていると、リソースを消費することとなります。
クライアントからTCP/IPプロトコルのみでサーバーへ接続するのであれば、“TCPIP”のみを、NETBIOS プロトコルのみを使用するのであれば、“NETBIOS”のみを指定するようにし
てください。
実行コマンド例：
db2set DB2COMM=TCPIP
db2set DB2COMM=NETBIOS
db2set DB2COMM=TCPIP,NETBIOS
確認コマンド例：
db2set -all
(TCP/IPのみ使用)
(NETBIOSのみ使用)
(TCP/IP,NETBIOSの両方使用)
注）WindowsなどPCにDB2 UDBを導入している場合には、NPIPEがプロトコルで指定されています。これはNamed Pipeのことで､ローカル・データベースへのアクセスに使用される
ことがあります。
解説 - DB2_RR_TO_RS
DB2_RR_TO_RS = YES レジストリー変数は設定しましたか？
OLTPのアプリケーションでは、大量のSQL処理を行うために1つのＳＱＬ処理を軽くする必要があり、ロック待ちを軽減するために分離レベルRR (Repeatable Read、ISO標準分離レ
ベル、Oracle分離レベルでいうところのSerializable)を使用しない設計をすることがあります。しかし､設計上分離レベルRRを使用しない設計を行っていても、DB2 UDBにその設定
をしなければ意味がありません。分離レベルRRを使用しない設計を行った場合には、必ずDB2_RR_TO_RS=YESを設定してください。
DB2 V7までのType1索引では分離レベルRRを保証するために、索引が作成されている表の行を削除する際に次のキーにロックを取得（NEXT KEY LOCK）したのち、キーの値を
索引のツリー構造から削除していました。DB2_RR_TO_RS=YESを設定することでDB2 UDBはNEXT KEY LOCK取得を省略する事ができ、メモリー確保などRDBMS内部の処理が軽
減されるため、SQL文の処理パフォーマンスの向上を期待することができます。
OLTP アプリケーションに限らず､分離レベルRRを必要としないアプリケーションの場合には、DB2_RR_TO_RS=YESを設定してください。
実行コマンド例：
db2set DB2_RR_TO_RS=YES
確認コマンド例：
db2set –all
DB2 V8からは、NEXT KEY LOCKの使用を最小限に抑え、同時稼動性を向上させることを目的としてType2索引が採用されることになりました。
Type2索引では、行を削除する際にすぐにキー値を削除せず、キー値を疑似削除（pseudo deleted）とマークして関連する行にXロックを取得します。キー値が物理的に索引ページ
から削除されるのは、DELETEまたはUPDATEがCOMMITされた後になります。そのため、Type2索引使用時は、基本的にDB2_RR_TO_RSレジストリー変数の設定は不要です。
DB2 V8で新規の索引はすべてType2索引として作成されますが、1つの表でType1索引とType2索引を混合させることはできないことから、すでにType1索引が含まれている表に
索引を追加する場合はType1索引が作成されるため注意が必要です。
チューニング 個別編
特定のハードウェアやアプリケーションによって設定していただきたい
パラメーターを解説しています
個別設定 - 一覧 設定、もしくは確認しておいたほうがよいパラメーターの一覧です。
ＲＡＩＤ５マシン対象
DB2レジストリー変数
db2set 環境変数 = 設定値
環境変数
省略時値
推奨値
備考
DB2_PARALLEL_IO
指定なし
* もしくはテーブルスペースID
DB2_STRIPED_CONTAINERS
指定なし（OFF）
ON
DB2 V7までの推奨値
DB2 V8では設定不要
備考
データベース構成パラメーター、その他
パラメーター
省略時値
推奨値
extentsize（CREATE/ALTER
TABLESPACE文）
dft_extent_sz：
32
RAIDストライプの倍数
ＳＭＰマシン対象
データベース構成パラメーター
DB2 UPDATE DB CFG FOR DB名 USING パラメーター 設定値
パラメーター
省略時値
推奨値
備考
dft_degree
1
-1
OLTP業務の場合 1
データベース・マネージャー構成パラメーター
DB2 UPDATE DBM CFG USING パラメーター 設定値
パラメーター
省略時値
推奨値
備考
intra_paralell
YES
NO
OLTP業務の場合 NO
個別設定 - 一覧 照会業務専用
データベース構成パラメーター
DB2 UPDATE DB CFG FOR DB名 USING パラメーター 設定値
パラメーター
省略時値
推奨値
備考
num_io_cleaners
1
0
照会専用以外の場合は物理的なディスク数
OLTP業務対象
データベース構成パラメーター、その他
パラメーター
省略時値
推奨値
備考
・DB2OPTIMIZATION（db2cli.ini）
・CURRENT QUERY OPTIMIZATION特殊レジストリー
・QUERYOPTオプション（BINDコマンド）
dft_queryopt ：
5
2
OLTP業務の場合 2
解説 - DB2_PARALLEL_IO、DB2_STRIPED_CONTAINERS
ディスク・システムが RAID-5 の場合、DB2_PARALLEL_IO = * 、DB2_STRIPED_CONTAINERS = ON を設定しましたか？
RAID-5のディスク・システムを使用している場合、物理的には複数のディスクがあっても、ディスク・アレイとしては1つであるため、I/O性能がフルに活用できないことがあります。
これを明示的にパラレルなI/O処理を行わせるものがDB2_PARALLEL_IO=* です。
DB2 V7までは、DMS表スペースの場合、コンテナーの最初の1ページにコンテナー・タグを作成するため、DB2のエクステントとRAIDストライプの境界がうまくそろいません。RAID装
置に表スペースを作成する場合には、エクステント・サイズをRAIDストライプ・サイズかその倍数を推奨していますが、1ページのコンテナー・タグがあるために、エクステントはRAID
ストライプとは同列にならないため､より多くの物理ディスクにアクセスする必要があるかもしれません。DB2_STRIPED_CONTAINERS=ONに設定すると、コンテナー・タグの大きさが1
エクステントに合わせられるため、パフォーマンスの向上がはかれます。DB2_STRIPED_CONTAINERS=ONの設定後に作成した表スペースのみ有効となる点にご注意ください。
DB2 V7までで、RAID-5ディスク・システムを使用している場合には、この2つのDB2環境変数を設定してください。
実行コマンド例１：
db2set DB2_STRIPED_CONTAINERS=ON
db2set DB2_PARALLEL_IO = *
各表スペースに設定。
実行コマンド例２：
db2set DB2_STRIPED_CONTAINERS=ON
db2set DB2_PARALLEL_IO = 1,2,4,8
表スペースIDを指定して、特定の表スペースに設定。
確認コマンド例：
db2set –all
DB2 V8からは、デフォルトで1エクステント・サイズのコンテナー・タグが作成されるようになったため、DB2_STRIPED_CONTAINERSレジストリー変数の設定は不要になりました。
DB2_STRIPED_CONTAINERS=OFF
DB2_STRIPED_CONTAINERS=ON
RAID
stripes
RAID
stripes
data storage
解説 – extentsize (CREATE TABLESPACE文)
ディスク・システムが RAID-5 の場合、extentsize は RAID ストライプサイズの倍数であることを確認してください。
DB2 UDBは表スペースにデータを保持します。表スペースは複数のコンテナーと呼ばれるファイルやドライブから構成されます。DB2 UDBはコンテナー間でワークロードバランス
をとろうとするので､データを格納するのに全てのコンテナーを使用します。コンテナーを使用する際に、一定のページサイズを最初に確保しますが、その確保するサイズがエクス
テント・サイズです。
従って､RAID-5の場合、extentsizeはRAIDストライプ・サイズの倍数である場合、RAIDのストライピングが効率よく行われます。RAID以外の場合はデフォルトで問題ありません。
エクステント・サイズはデータベース構成パラメーターで指定しておくか、“CREATE TABLESPACE” または“ALTER TABLESPACE” で明示的に指定します。
実行コマンド例１：
db2 update db cfg for database-alias using dft_extent_sz size
確認コマンド例１：
db2 get db cfg for database-alias
実行コマンド例２：
CREATE TABLESPACE ACCOUNTING
MANAGED BY SYSTEM
USING ('d:¥acc_tbsp', 'e:¥acc_tbsp', 'f:¥acc_tbsp')
EXTENTSIZE 32
PREFETCHSIZE 96
3つの別個のドライブの3つのディレクトリーを使用し、エクステント・サイズを32ページ、事前取り出しサイズを96ページに指定して、Windowsで通常のSMS
表スペースを作成。
解説 - dft_degree､intra_parallel
dft_degree､intra_parallel を確認してください。
dft_degree､intra_parallelは区画内並列（SMPパラレル）処理の実行を制御するパラメーターです。
intra_parallelは区画内並列機能を有効か無効かを指定し､dft_degreeは省略時の並列度合いを指定します。
区画内並列処理を機能させるには､以下のように設定しておく必要があります。
z
intra_parallel を YES
z
dft_degree を -1（並列度は自動設定）
SMP構成のシステムに導入した場合、intra_parallelの省略時値はYESですが､dft_degreeは1となっているので確認が必要です。
しかし、SMP構成であっても、OLTPアプリケーションが主体の場合にはSQLが簡単で非常に短い時間で処理できるようにテーブルなども設計されている場合があり､区画内並列
処理を行うための準備コストが、区画内並列処理によって得られる効果よりも大きくなってしまうこともあります。このような場合には、区画内並列を無効化しておく必要があります。
従って､SMP構成の場合は稼動させるアプリケーションによって､各パラメーターを確認してください。
„区画内並列処理を有効化する
実行コマンド例１：
db2 update dbm cfg using intra_parallel YES
db2 update db cfg for database-alias using dft_degree -1
確認コマンド例１：
db2 get dbm cfg
db2 get db cfg for database-alias
„区画内並列処理を無効化する
実行コマンド例２：
db2 update dbm cfg using intra_parallel NO
db2 update db cfg for database-alias using dft_degree 1
確認コマンド例２：
db2 get dbm cfg
db2 get db cfg for database-alias
注）並列度は､アプリケーション内でパラメーターを発行したり､BIND時に指定した場合には、dft_degreeパラメーターの影響を受けません。
解説 - num_iocleaners
照会専用データベースの場合には、num_iocleaners に 0 を設定してください。
num_iocleanersには､バッファー・プールへの書き込みを行うための空間（ページ）をあけるために､変更があったページをバッファー・プールからディスクへの書出しを実行するペー
ジ・クリーナーの数を指定します。（ページに変更がなかった場合は、ページは単純に上書きされます）
照会専用データベースではデータに変更が発生せず、バッファー・プールからディスクへ書き出す必要がないため、0の指定を検討してください。
ただし、このパラメーターを0に設定した場合は、開始されるページ・クリーナーがないので、その結果として、バッファー・プールからディスクへのページ書き込みのすべてを、デー
タベース・エージェントが実行することになります。データベースが多くの物理ストレージ・デバイスにまたがって保管されている場合は、物理ストレージ・デバイスの1つがアイドル
状態になる可能性が高いので、このパラメーターがデータベースに重大なパフォーマンス上の影響を及ぼす可能性があります。また、ページ・クリーナーが構成されていない場合
は、周期的にログがいっぱいになる状態がアプリケーションで検出される可能性があります。
実行コマンド例：
確認コマンド例：
db2 update db cfg for database-alias using num_iocleaners 0
db2 get db cfg for database-alias
解説 – dft_queryopt
OLTPアプリケーションの場合､SQLの照会最適化クラスを２に変更しましたか？
OLTPアプリケーションでは､大量のトランザクションを処理しなければいけません。そのため､テーブルの設計にも注意をはらい､SQL文を簡単にして、トランザクションにかかる時間
を短くし､できるだけデータベース・システムに負荷をかけない設計をすることがあります。このような場合、SQLの照会の最適化にかかるコストを削減することで、アプリケーションの
実行パフォーマンスがよくなる場合があります。JAVAなどCLIのOLTPアプリケーションの場合には効果が見られます。
DB2 UDBは投げられたSQL文を最適化を行ってから実際の処理を行います。従って､SQL文を処理する前には必ず最適化の時間が必要となっています。一般的なアプリケーション
の場合には、最適化にかかるコストよりも、最適化によって得られる効果が大きいため問題にはなりません。しかしOLTP アプリケーションの場合は、処理する数が多いことと､最適
化するほど複雑なSQL文ではないために、最適化にかかるコストの方が得られる効果よりも大きくなることが多いです。
SQL照会最適化クラスを変更するには4つの方法があります。
z
db2cli.iniで指定
z
SET CURRENT QUERY OPTIMIZATIONステートメントで指定
z
BINDコマンドのQUERYOPTオプションで指定
z
dft_queryoptデータベース構成パラメーターで指定
db2cli.ini、“SET CURRENT QUERY OPTIMIZATION” 、BINDコマンドのQUERYOPTオプションのいずれでも指定されなかった場合、dft_queryoptデータベース構成パラメーターで指
定された照会最適化クラスが有効になります。SET CURRENT QUERY OPTIMIZATIONステートメント、BINDコマンドのQUERYOPTオプションを使用すると、アプリケーションごとの
照会最適化クラスを設定することができます。db2cli.iniは内部的に“SET CURRENT QUERY OPTIMIZATION”を発行しています。
実行コマンド例１：
確認コマンド例１：
・構成アシスタントで設定するデータベースを選択し、「選択」メニューから「CLI設定…」を選択する。
「CLI設定」画面の「設定」タブで「追加」ボタンにより「DB2Optimization」を追加し、最適化レベル2を指定する。
・db2cli.iniに直接、対象データーベースセクションに
DB2OPTIMIZATION=2を追加する。
・構成アシスタントで確認する。
・db2cli.iniファイルの中を確認する。
実行コマンド例２：
確認コマンド例２：
db2 SET CURRENT QUERY OPTIMIZATION 2
db2 values(CURRENT QUERY OPTIMIZATION)
実行コマンド例３：
確認コマンド例３：
db2 BIND file-name QUERYOPT 2
db2 SELECT PKGNAME, PKGSCHEMA FROM SYSCAT.PACKAGES WHERE QUERYOPT = CURRENT QUERY OPTIMIZATION
SYSCAT.PACKAGESカタログ・ビューを使用して、CURRENT QUERY OPTIMIZATION特殊レジスターの現行値と同じ設定でバインドされたすべてのプラン
を検索しています。
実行コマンド例４：
確認コマンド例４：
db2 update db cfg for database-alias using dft_quryopt 2
db2 get db cfg for database-alias
参考
アプリケーション開発の基礎の基礎 - まとめ アンサー・セットを意識してください
アンサー・セットの数を予想しましょう
– １回の照会で、ユーザーやシステムが処理できるアンサー・セット数を考えてください
–
無駄なアクセスを減らしてください
必要のない列を取得しないようにしましょう
– 同じデータを何度も取得しないような設計を考えてみましょう
–
コミット、ロールバックを実行してください
–
作業単位（Unit of Work）を意識しましょう
アプリケーション開発 基礎の基礎
アンサーセットを意識していますか？
アプリケーション開発の際には、必ずアンサー・セットを意識してください。
いくら速いCPUでシステムを稼動させようと、限りなくRDBMSをチューニングしても、アンサー・セットが物理的に多ければそれだけRDBMSの検索時間もかかりますし、さらにRDBMS
からユーザーに返す時間も大きくなり、レスポンスタイムにはより多くの時間が必要となります。
特にWebアプリケーションの場合は、ほとんどの場合LANよりもはるかに転送速度の遅いWAN経由になるため、アンサー・セットが大きいとレスポンスタイムが悪くなります。一般的
にアンサー・セットをユーザーへ返す場合にはいろいろな付帯データを一緒に返すため、アンサー・セットよりも大きなデータを転送することになるからです。
まず､1SQLで処理対象となる、アンサー･セットを意識してください。アンサー･セット数が大きい場合は､CPUもより速いものが必要となります。
アンサー･セットを分割できないかどうか、アプリケーション設計を考えてみましょう。
アンサー・セットが分割できない場合､fetch first x rows onlyなどを使用し､ユーザーに最初に返す件数だけを高速化することを検討してみましょう。
次に、アンサー･セットを使用して、実際にユーザーへ返す部分のデータ量を意識しましょう。ほとんどの場合、複数のテーブルのデータを組み合わせているので､意外とデータは大
きいはずです。ネットワーク上（LAN & WAN）の流れるデータ量を考慮した上で、もう一度1回の処理で対象となるアンサー・セットを検討してください。
アプリケーション開発 基礎の基礎
無駄なアクセスを減らしましょう。
昔からよく言われていることですが、「人間やプリンタが一番遅く､次にディスクが遅い」ということは､ディスクがかなり速くなった今でも同じです。
従って､パフォーマンスをあげる簡単な方法の1つは、ディスク・アクセスを減らし､メモリー・アクセスを多くすることです。そのためには､バッファー・プールを大きく取ることは､非常に
有効なチューニングの1つです。しかし､メモリーは有限の資源であるため､無限にデータをバッファー・プールに取り込むことは不可能です。
やはり､ディスク・アクセスをできるだけ減らすことが､パフォーマンスを向上させるには有効なのです。
アプリケーション開発においては､アンサー・セットと同じ考え方で、ロジック中の無用な列に対するSELECTは、DBサーバーだけでなく、列データを受け取るアプリケーション、ネット
ワーク、Webサーバーなどのオーバーヘッドとなり、システムに余分な負荷を必要とします。従ってプログラム中の SQLを確認し、不要な列はSELECT対象からはずしてください。
また場合によっては､処理に必要なデータ列を索引に組み込むことが可能な複合索引を作成できる場合があります。このような場合には、実際のデータを読むことなく、索引を読ん
だ時点で必要なデータが取得できるような場合もあります。
また､コード系のデータなどはマスター情報であり､頻繁に必要とされるデータですが､データ自身の変更が少なく､件数自身も変更が少ない場合が一般的です。このような場合には、
このマスター・テーブルやデータをバッファー・プールに常時入っているように物理設計を行うことで、かなりのパフォーマンス向上を期待できます。
アプリケーション開発 基礎の基礎
コミット/ロールバックを実行するようにしましょう。
DB2 UDBではINSERTやUPDATEがSQLCODE=-803（UNIQUE INDEXやPRIMARY KEY制約による重複キー・エラー）の時は、行LOCKが残ります。このような行LOCKは予定外の
ロックであり、ロック待ち状態やデッドロックを引き起こす原因となります。
このような状況にならないために、SQL Exceptionの時にもCOMMIT/ROLLBACKを発行するようにしてください。
WebSphereのConnection Poolingを使用している場合に、これを行わずにConnection PoolへConnectionを戻すと、後続の同一キーへのINSERTやUPDATEの処理結果が
SQLCODE=-803とならずに、LOCK待ち状態となります。
また､オンライン中のバッチ処理にも長時間LOCKを保持しないようにCOMMITを発行するようにしましょう。
一方でINSERTを実行しCOMMITを発行しないでいるところへ、他方から全件検索等の表スキャンを実行すると分離レベルCS以上の時にLOCK待ちとなります。長時間COMMITが発
行されないような状態を避けることで、このような長時間のLOCK待ちは回避することができます。
このように意識しないところでロックが発生することもあるので､アプリケーション開発時には作業単位を意識し1トランザクション（Unit Of Work）終了時には､必ずCOMMITや
ROLLBACK を実施するようにしましょう。
また､バッチ処理中にCOMMIT処理を行う場合にも適当な間隔(たとえば1000件単位など)でCOMMITを発行するようにして下さい。ただし､COMMIT発行後カーソルはクローズされて
しまいますので、カーソルに“WITH HOLD”オプションを付加して、COMMIT発行後もFETCHを継続して行えるようにしてください。カーソルがクローズされてしまうと、再度全てのレ
コードをFETCHしなおす必要があります。