データベース - 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
