データベース - IBM DB2 - AUTOCONFIGURE
1. 概要 データベースのチューニングというのは難しいもので・・・。 個人で扱っているデータベースはちっちゃいのでほとんどチューニングもくそもないのですが。 仕事で扱うようなデータベースは巨大になるので、チューニングしないとアクセス速度が極端に遅かったりするのです。 IBM DB2 にはそれを自動でやってくれるコマンドがあるようなのです。 2. 推奨値を表示する 実際に設定を行う前に・・・。 推奨値を表示してみます。 これは、Windows マシンで実際にやってみました。 データベース管理者のコマンドプロンプトで > db2 connect to データベース名 データベース接続情報 データベース・サーバー = DB2/NT64 10.5.6 SQL 許可 ID = 伏字 ローカル・データベース別名 = 伏字 > db2 AUTOCONFIGURE APPLY NONE データベース・マネージャー構成の現行値と推奨値 説明 パラメーター 現行値 推奨値 ------------------------------------------------------------------------------------------------- エージェント・スタック・サイズ (AGENT_STACK_SZ) = 16 16 アプリケーション・サポート層ヒープ・サイズ (4KB) (ASLHEAPSZ) = 15 15 内部通信バッファーの数 (4KB) (FCM_NUM_BUFFERS) = AUTOMATIC(4096) AUTOMATIC(4096) パーティション内並列処理を有効にする (INTRA_PARALLEL) = NO NO 並列処理の最大照会度 (MAX_QUERYDEGREE) = 2 1 エージェント・プール・サイズ (NUM_POOLAGENTS) = AUTOMATIC(100) AUTOMATIC(100) プールにあるエージェントの初期化数 NUM_INITAGENTS) = 0 0 リクエスター入出力ブロック・サイズの最大数 (バイト) (RQRIOBLK) = 65535 65535 ソート・ヒープしきい値 (4KB) (SHEAPTHRES) = 0 0 データベース構成の現行値と推奨値 説明 パラメーター 現行値 推奨値 ------------------------------------------------------------------------------------------------- デフォルト・アプリケーション・ヒープ (4KB) (APPLHEAPSZ) = 256 256 カタログ・キャッシュ・サイズ (4KB) (CATALOGCACHE_SZ) = 300 1101 変更ページしきい値 (CHNGPGS_THRESH) = 80 80 データベース・ヒープ (4KB) (DBHEAP) = AUTOMATIC(14589) AUTOMATIC(10613) 並列処理の度合い (DFT_DEGREE) = 1 1 デフォルトの表スペース・エクステント・サイズ (ページ) (DFT_EXTENT_SZ) = 32 32 デフォルトのプリフェッチ・サイズ (ページ) (DFT_PREFETCH_SZ) = AUTOMATIC(32) AUTOMATIC(32) デフォルトの照会最適化クラス (DFT_QUERYOPT) = 5 5 ロック・リスト用最大ストレージ (4KB) (LOCKLIST) = AUTOMATIC(11712) AUTOMATIC(6200) ログ・ファイルのサイズ (4KB) (LOGFILSIZ) = 16384 1024 1 次ログ・ファイルの数 (LOGPRIMARY) = 24 10 2 次ログ・ファイル数 (LOGSECOND) = 14 12 アクティブ・アプリケーションの最大数 (MAXAPPLS) = AUTOMATIC(1281) AUTOMATIC(40) アプリケーションあたりのロック・リストのパーセント (MAXLOCKS) = AUTOMATIC(98) AUTOMATIC(60) 非同期ページ・クリーナー数 (NUM_IOCLEANERS) = AUTOMATIC(2) AUTOMATIC(2) 入出力サーバー数 (NUM_IOSERVERS) = AUTOMATIC(16) AUTOMATIC(20) パッケージ・キャッシュ・サイズ (4KB) (PCKCACHESZ) = AUTOMATIC(10251) AUTOMATIC(1533) ソート・リスト・ヒープ (4KB) (SORTHEAP) = AUTOMATIC(1000) AUTOMATIC(968) SQL ステートメント・ヒープ (4KB) (STMTHEAP) = AUTOMATIC(8192) AUTOMATIC(8192) 統計ヒープ・サイズ (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384) AUTOMATIC(4384) ユーティリティー・ヒープ・サイズ (4KB) (UTIL_HEAP_SZ) = AUTOMATIC(412000) AUTOMATIC(13054) セルフチューニング・メモリー (SELF_TUNING_MEM) = ON ON 自動 RUNSTATS (AUTO_RUNSTATS) = ON ON 共有ヒープのソート・ヒープしきい値 (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(5004) AUTOMATIC(19374) ログ・バッファー・サイズ (4KB) (LOGBUFSZ) = 9591 2149 デフォルトの表編成 (DFT_TABLE_ORG) = ROW ROW データベース・メモリーしきい値 (DB_MEM_THRESH) = 100 100 バッファー・プールの現行値と推奨値 説明 パラメーター 現行値 推奨値 ------------------------------------------------------------------------------------------------- IBMDEFAULTBP バッファー・プール・サイズ = -2 64981 Current and Recommended Values for System WLM Objects Description Current Value Recommended Value ------------------------------------------------------------------------------------------------- Work Action SYSMAPMANAGEDQUERIES Enabled = Y Y Work Action Set SYSDEFAULTUSERWAS Enabled = Y Y Work Class SYSMANAGEDQUERIES Timeroncost = 1.50000E+005 1.50000E+005 Threshold SYSDEFAULTCONCURRENT Enabled = N N Threshold SYSDEFAULTCONCURRENT Maxvalue = 12 10 DB210203I AUTOCONFIGURE が正常に完了しました。 変更の適用を選択した場合は、データベース・マネージャーまたはデータベース構成値が 変更されている可能性があります。 この種の適用した変更を有効にするには、インスタンスを再始動する必要があります。 また、新しい構成パラメーターを有効にした後は、新しい値が使用されるように、パッケ ージを再バインドすることができます。 1行目はデータベースへの接続です。 データベースへ接続しないと解析できません。 > db2 AUTOCONFIGURE APPLY NONE ってのが推奨値を表示するコマンドです。 NONE っていうオプションが「表示のみという」意味らしい。 3. 推奨値を設定する 前項のコマンドの NONE の部分を変更して > db2 AUTOCONFIGURE APPLY DB ONLY とすれば 『現行のデータベース・マネージャーの構成に基づいて、 データベース構成およびバッファー・プール設定の推奨値を表示します。 データベース構成およびバッファー・プール設定に対して推奨される変更を適用します。』とのことです。 これはまだやってみていません。 > db2 AUTOCONFIGURE APPLY DB AND DBM とすれば 『データベース・マネージャー構成、データベース構成、 およびバッファー・プール設定に対して推奨される変更を、表示および適用します。』とのことです。 やってみます。 データベースへの接続から書きますが > db2 CONNECT TO データベース名 > db2 AUTOCONFIGURE APPLY DB ONLY データベース・マネージャー構成の以前の値と適用値 説明 パラメーター 以前の値 適用値 ------------------------------------------------------------------------------------------------- エージェント・スタック・サイズ (AGENT_STACK_SZ) = 16 16 アプリケーション・サポート層ヒープ・サイズ (4KB) (ASLHEAPSZ) = 15 15 内部通信バッファーの数 (4KB) (FCM_NUM_BUFFERS) = AUTOMATIC(4096) AUTOMATIC(4096) パーティション内並列処理を有効にする (INTRA_PARALLEL) = NO NO 並列処理の最大照会度 (MAX_QUERYDEGREE) = 2 1 エージェント・プール・サイズ (NUM_POOLAGENTS) = AUTOMATIC(100) AUTOMATIC(100) プールにあるエージェントの初期化数 NUM_INITAGENTS) = 0 0 リクエスター入出力ブロック・サイズの最大数 (バイト) (RQRIOBLK) = 65535 65535 ソート・ヒープしきい値 (4KB) (SHEAPTHRES) = 0 0 データベース構成の以前の値と適用値 説明 パラメーター 以前の値 適用値 ------------------------------------------------------------------------------------------------- デフォルト・アプリケーション・ヒープ (4KB) (APPLHEAPSZ) = 256 256 カタログ・キャッシュ・サイズ (4KB) (CATALOGCACHE_SZ) = 300 1101 変更ページしきい値 (CHNGPGS_THRESH) = 80 80 データベース・ヒープ (4KB) (DBHEAP) = AUTOMATIC(14589) AUTOMATIC(10613) 並列処理の度合い (DFT_DEGREE) = 1 1 デフォルトの表スペース・エクステント・サイズ (ページ) (DFT_EXTENT_SZ) = 32 32 デフォルトのプリフェッチ・サイズ (ページ) (DFT_PREFETCH_SZ) = AUTOMATIC(32) AUTOMATIC(32) デフォルトの照会最適化クラス (DFT_QUERYOPT) = 5 5 ロック・リスト用最大ストレージ (4KB) (LOCKLIST) = AUTOMATIC(11712) AUTOMATIC(6200) ログ・ファイルのサイズ (4KB) (LOGFILSIZ) = 16384 1024 1 次ログ・ファイルの数 (LOGPRIMARY) = 24 10 2 次ログ・ファイル数 (LOGSECOND) = 14 12 アクティブ・アプリケーションの最大数 (MAXAPPLS) = AUTOMATIC(1281) AUTOMATIC(40) アプリケーションあたりのロック・リストのパーセント (MAXLOCKS) = AUTOMATIC(98) AUTOMATIC(60) 非同期ページ・クリーナー数 (NUM_IOCLEANERS) = AUTOMATIC(2) AUTOMATIC(2) 入出力サーバー数 (NUM_IOSERVERS) = AUTOMATIC(16) AUTOMATIC(20) パッケージ・キャッシュ・サイズ (4KB) (PCKCACHESZ) = AUTOMATIC(10251) AUTOMATIC(1533) ソート・リスト・ヒープ (4KB) (SORTHEAP) = AUTOMATIC(1000) AUTOMATIC(968) SQL ステートメント・ヒープ (4KB) (STMTHEAP) = AUTOMATIC(8192) AUTOMATIC(8192) 統計ヒープ・サイズ (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384) AUTOMATIC(4384) ユーティリティー・ヒープ・サイズ (4KB) (UTIL_HEAP_SZ) = AUTOMATIC(412000) AUTOMATIC(13054) セルフチューニング・メモリー (SELF_TUNING_MEM) = ON ON 自動 RUNSTATS (AUTO_RUNSTATS) = ON ON 共有ヒープのソート・ヒープしきい値 (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(5004) AUTOMATIC(19374) ログ・バッファー・サイズ (4KB) (LOGBUFSZ) = 9591 2149 デフォルトの表編成 (DFT_TABLE_ORG) = ROW ROW データベース・メモリーしきい値 (DB_MEM_THRESH) = 100 100 バッファー・プールの以前の値と適用値 説明 パラメーター 以前の値 適用値 ------------------------------------------------------------------------------------------------- IBMDEFAULTBP バッファー・プール・サイズ = -2 64981 Former and Applied Values for System WLM Objects Description Former Value Applied Value ------------------------------------------------------------------------------------------------- Work Action SYSMAPMANAGEDQUERIES Enabled = Y Y Work Action Set SYSDEFAULTUSERWAS Enabled = Y Y Work Class SYSMANAGEDQUERIES Timeroncost = 1.50000E+005 1.50000E+005 Threshold SYSDEFAULTCONCURRENT Enabled = N N Threshold SYSDEFAULTCONCURRENT Maxvalue = 12 10 DB210203I AUTOCONFIGURE が正常に完了しました。 変更の適用を選択した場合は、データベース・マネージャーまたはデータベース構成値が 変更されている可能性があります。 この種の適用した変更を有効にするには、インスタンスを再始動する必要があります。 また、新しい構成パラメーターを有効にした後は、新しい値が使用されるように、パッケ ージを再バインドすることができます。 ほぼ前項と同様の内容が「推奨値」の箇所が「適用値」となって表示されます。 実行後は、適用を反映するために、再起動します。 > db2 DISCONNECT データベース名 > db2stop > db2start 4. 実行した結果 これ、結構満足しています。 適用する前に１回に 30秒ほどかかっていた処理が 15秒以下で終了するようになりました。 5. 万能ではない あくまで推奨値ですので、万能ではありません。 わたしの環境では、前項までの設定を行ってもどうしても、テーブルをまとめてアップデートするときにトランザクションフルになることがあります。 これはトランザクションログのサイズを変えることで、対応しました。 わたしの場合、結構でかく 16384 の値を与えることで落ち着きました。 > db2 update db cfg for データベース名 using LOGFILSIZ 16384 > db2stop > db2start
> db2 connect to データベース名 データベース接続情報 データベース・サーバー = DB2/NT64 10.5.6 SQL 許可 ID = 伏字 ローカル・データベース別名 = 伏字 > db2 AUTOCONFIGURE APPLY NONE データベース・マネージャー構成の現行値と推奨値 説明 パラメーター 現行値 推奨値 ------------------------------------------------------------------------------------------------- エージェント・スタック・サイズ (AGENT_STACK_SZ) = 16 16 アプリケーション・サポート層ヒープ・サイズ (4KB) (ASLHEAPSZ) = 15 15 内部通信バッファーの数 (4KB) (FCM_NUM_BUFFERS) = AUTOMATIC(4096) AUTOMATIC(4096) パーティション内並列処理を有効にする (INTRA_PARALLEL) = NO NO 並列処理の最大照会度 (MAX_QUERYDEGREE) = 2 1 エージェント・プール・サイズ (NUM_POOLAGENTS) = AUTOMATIC(100) AUTOMATIC(100) プールにあるエージェントの初期化数 NUM_INITAGENTS) = 0 0 リクエスター入出力ブロック・サイズの最大数 (バイト) (RQRIOBLK) = 65535 65535 ソート・ヒープしきい値 (4KB) (SHEAPTHRES) = 0 0 データベース構成の現行値と推奨値 説明 パラメーター 現行値 推奨値 ------------------------------------------------------------------------------------------------- デフォルト・アプリケーション・ヒープ (4KB) (APPLHEAPSZ) = 256 256 カタログ・キャッシュ・サイズ (4KB) (CATALOGCACHE_SZ) = 300 1101 変更ページしきい値 (CHNGPGS_THRESH) = 80 80 データベース・ヒープ (4KB) (DBHEAP) = AUTOMATIC(14589) AUTOMATIC(10613) 並列処理の度合い (DFT_DEGREE) = 1 1 デフォルトの表スペース・エクステント・サイズ (ページ) (DFT_EXTENT_SZ) = 32 32 デフォルトのプリフェッチ・サイズ (ページ) (DFT_PREFETCH_SZ) = AUTOMATIC(32) AUTOMATIC(32) デフォルトの照会最適化クラス (DFT_QUERYOPT) = 5 5 ロック・リスト用最大ストレージ (4KB) (LOCKLIST) = AUTOMATIC(11712) AUTOMATIC(6200) ログ・ファイルのサイズ (4KB) (LOGFILSIZ) = 16384 1024 1 次ログ・ファイルの数 (LOGPRIMARY) = 24 10 2 次ログ・ファイル数 (LOGSECOND) = 14 12 アクティブ・アプリケーションの最大数 (MAXAPPLS) = AUTOMATIC(1281) AUTOMATIC(40) アプリケーションあたりのロック・リストのパーセント (MAXLOCKS) = AUTOMATIC(98) AUTOMATIC(60) 非同期ページ・クリーナー数 (NUM_IOCLEANERS) = AUTOMATIC(2) AUTOMATIC(2) 入出力サーバー数 (NUM_IOSERVERS) = AUTOMATIC(16) AUTOMATIC(20) パッケージ・キャッシュ・サイズ (4KB) (PCKCACHESZ) = AUTOMATIC(10251) AUTOMATIC(1533) ソート・リスト・ヒープ (4KB) (SORTHEAP) = AUTOMATIC(1000) AUTOMATIC(968) SQL ステートメント・ヒープ (4KB) (STMTHEAP) = AUTOMATIC(8192) AUTOMATIC(8192) 統計ヒープ・サイズ (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384) AUTOMATIC(4384) ユーティリティー・ヒープ・サイズ (4KB) (UTIL_HEAP_SZ) = AUTOMATIC(412000) AUTOMATIC(13054) セルフチューニング・メモリー (SELF_TUNING_MEM) = ON ON 自動 RUNSTATS (AUTO_RUNSTATS) = ON ON 共有ヒープのソート・ヒープしきい値 (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(5004) AUTOMATIC(19374) ログ・バッファー・サイズ (4KB) (LOGBUFSZ) = 9591 2149 デフォルトの表編成 (DFT_TABLE_ORG) = ROW ROW データベース・メモリーしきい値 (DB_MEM_THRESH) = 100 100 バッファー・プールの現行値と推奨値 説明 パラメーター 現行値 推奨値 ------------------------------------------------------------------------------------------------- IBMDEFAULTBP バッファー・プール・サイズ = -2 64981 Current and Recommended Values for System WLM Objects Description Current Value Recommended Value ------------------------------------------------------------------------------------------------- Work Action SYSMAPMANAGEDQUERIES Enabled = Y Y Work Action Set SYSDEFAULTUSERWAS Enabled = Y Y Work Class SYSMANAGEDQUERIES Timeroncost = 1.50000E+005 1.50000E+005 Threshold SYSDEFAULTCONCURRENT Enabled = N N Threshold SYSDEFAULTCONCURRENT Maxvalue = 12 10 DB210203I AUTOCONFIGURE が正常に完了しました。 変更の適用を選択した場合は、データベース・マネージャーまたはデータベース構成値が 変更されている可能性があります。 この種の適用した変更を有効にするには、インスタンスを再始動する必要があります。 また、新しい構成パラメーターを有効にした後は、新しい値が使用されるように、パッケ ージを再バインドすることができます。
> db2 AUTOCONFIGURE APPLY NONE
> db2 AUTOCONFIGURE APPLY DB ONLY
> db2 AUTOCONFIGURE APPLY DB AND DBM
> db2 CONNECT TO データベース名 > db2 AUTOCONFIGURE APPLY DB ONLY データベース・マネージャー構成の以前の値と適用値 説明 パラメーター 以前の値 適用値 ------------------------------------------------------------------------------------------------- エージェント・スタック・サイズ (AGENT_STACK_SZ) = 16 16 アプリケーション・サポート層ヒープ・サイズ (4KB) (ASLHEAPSZ) = 15 15 内部通信バッファーの数 (4KB) (FCM_NUM_BUFFERS) = AUTOMATIC(4096) AUTOMATIC(4096) パーティション内並列処理を有効にする (INTRA_PARALLEL) = NO NO 並列処理の最大照会度 (MAX_QUERYDEGREE) = 2 1 エージェント・プール・サイズ (NUM_POOLAGENTS) = AUTOMATIC(100) AUTOMATIC(100) プールにあるエージェントの初期化数 NUM_INITAGENTS) = 0 0 リクエスター入出力ブロック・サイズの最大数 (バイト) (RQRIOBLK) = 65535 65535 ソート・ヒープしきい値 (4KB) (SHEAPTHRES) = 0 0 データベース構成の以前の値と適用値 説明 パラメーター 以前の値 適用値 ------------------------------------------------------------------------------------------------- デフォルト・アプリケーション・ヒープ (4KB) (APPLHEAPSZ) = 256 256 カタログ・キャッシュ・サイズ (4KB) (CATALOGCACHE_SZ) = 300 1101 変更ページしきい値 (CHNGPGS_THRESH) = 80 80 データベース・ヒープ (4KB) (DBHEAP) = AUTOMATIC(14589) AUTOMATIC(10613) 並列処理の度合い (DFT_DEGREE) = 1 1 デフォルトの表スペース・エクステント・サイズ (ページ) (DFT_EXTENT_SZ) = 32 32 デフォルトのプリフェッチ・サイズ (ページ) (DFT_PREFETCH_SZ) = AUTOMATIC(32) AUTOMATIC(32) デフォルトの照会最適化クラス (DFT_QUERYOPT) = 5 5 ロック・リスト用最大ストレージ (4KB) (LOCKLIST) = AUTOMATIC(11712) AUTOMATIC(6200) ログ・ファイルのサイズ (4KB) (LOGFILSIZ) = 16384 1024 1 次ログ・ファイルの数 (LOGPRIMARY) = 24 10 2 次ログ・ファイル数 (LOGSECOND) = 14 12 アクティブ・アプリケーションの最大数 (MAXAPPLS) = AUTOMATIC(1281) AUTOMATIC(40) アプリケーションあたりのロック・リストのパーセント (MAXLOCKS) = AUTOMATIC(98) AUTOMATIC(60) 非同期ページ・クリーナー数 (NUM_IOCLEANERS) = AUTOMATIC(2) AUTOMATIC(2) 入出力サーバー数 (NUM_IOSERVERS) = AUTOMATIC(16) AUTOMATIC(20) パッケージ・キャッシュ・サイズ (4KB) (PCKCACHESZ) = AUTOMATIC(10251) AUTOMATIC(1533) ソート・リスト・ヒープ (4KB) (SORTHEAP) = AUTOMATIC(1000) AUTOMATIC(968) SQL ステートメント・ヒープ (4KB) (STMTHEAP) = AUTOMATIC(8192) AUTOMATIC(8192) 統計ヒープ・サイズ (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384) AUTOMATIC(4384) ユーティリティー・ヒープ・サイズ (4KB) (UTIL_HEAP_SZ) = AUTOMATIC(412000) AUTOMATIC(13054) セルフチューニング・メモリー (SELF_TUNING_MEM) = ON ON 自動 RUNSTATS (AUTO_RUNSTATS) = ON ON 共有ヒープのソート・ヒープしきい値 (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(5004) AUTOMATIC(19374) ログ・バッファー・サイズ (4KB) (LOGBUFSZ) = 9591 2149 デフォルトの表編成 (DFT_TABLE_ORG) = ROW ROW データベース・メモリーしきい値 (DB_MEM_THRESH) = 100 100 バッファー・プールの以前の値と適用値 説明 パラメーター 以前の値 適用値 ------------------------------------------------------------------------------------------------- IBMDEFAULTBP バッファー・プール・サイズ = -2 64981 Former and Applied Values for System WLM Objects Description Former Value Applied Value ------------------------------------------------------------------------------------------------- Work Action SYSMAPMANAGEDQUERIES Enabled = Y Y Work Action Set SYSDEFAULTUSERWAS Enabled = Y Y Work Class SYSMANAGEDQUERIES Timeroncost = 1.50000E+005 1.50000E+005 Threshold SYSDEFAULTCONCURRENT Enabled = N N Threshold SYSDEFAULTCONCURRENT Maxvalue = 12 10 DB210203I AUTOCONFIGURE が正常に完了しました。 変更の適用を選択した場合は、データベース・マネージャーまたはデータベース構成値が 変更されている可能性があります。 この種の適用した変更を有効にするには、インスタンスを再始動する必要があります。 また、新しい構成パラメーターを有効にした後は、新しい値が使用されるように、パッケ ージを再バインドすることができます。
> db2 DISCONNECT データベース名 > db2stop > db2start
> db2 update db cfg for データベース名 using LOGFILSIZ 16384 > db2stop > db2start
