やりたいのは、夜間バッチ処理などで特定の条件にマッチした行のみを高速に削除したいとのこと。
その時は、テーブルを分離して全削除できるようにしてTRUNCATEをすすめたけど、他に方法がないか確認してみる。
SQLを見てないけど、多分以下のような感じだと思う。
FETCH FIRST n ROWS ONLY を指定して一度に削除する行数を限定しログが溢れない様にしているんじゃないかな。
db2 "DELETE FROM (SELECT * FROM 表名 WHERE 条件 FETCH FIRST 件数 ROWS ONLY)"
※ 削除すべき行がすべて削除されるまで繰り返し実行。
「SQL0100W FETCH、UPDATE または DELETEの対象となる行がないか、または照会の結果が空の表です。 SQLSTATE=02000」
削除対象がない場合は、リターンコード（echo $?の結果）は「1」が返却される。
表全体を削除するなら、方法はあるんだけど、表の中で特定の条件となるとログを取らなくするしかないかな。
★一時的にログを取得しない案
ALTER TABLEのACTIVATE NOT LOGGED INITIALLY ステートメントを使って、同一トランザクション内に限ってログを取得させないようにする方法。db2 +c "ALTER TABLE 表名 ACTIVATE NOT LOGGED INITIALLY"
db2 +c "DELETE FROM 表名 WHERE 条件"
db2 +c "commit"
※デフォルトでは自動コミットがオンになっているため、そのまま実行すると ALTER TABLEが自体がコミットされてしまい NOT LOGGED INITIALLY が有効にならない。
なので、+c オプションを指定して自動コミットされないようにする必要がある。
参考
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.ha.doc/doc/c0006079.html
重要！！
上記状態で実行中に障害などで失敗するとテーブルdrop＆リストアしないとNGっぽい。。
処理中に、次のエラーが返されました。
SQL1476N 現行トランザクションがエラー "-952"
のため、ロールバックしました。 SQLSTATE=40506
db2inst1@linux-d9mi:~> db2 "select count(*) from stock"
1
-----------
SQL1477N 表 "DB2INST1.STOCK" で、表スペース "3"
内のオブジェクト "9" にはアクセスできません。
SQLSTATE=55019
db2inst1@linux-d9mi:~> db2 ? 55019
SQLSTATE 55019: オブジェクトが無効な状態にあるため、操作で
きません。
懸案事項
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.admin.ha.doc/doc/c0006079.html
★テーブル設計を見直す案
まぁ全削除できるようなテーブル設計にする。で、不要になったらテーブルのデータを全削除する。
・nullで上書き案
該当の表に対して空のデータを REPLACE オプションによって IMPORT することで行をすべて削除する方法。
db2 "IMPORT FROM /dev/null OF DEL REPLACE INTO 表名"
※Windows の場合は /dev/null の代わりに NUL を指定する。
・ALTER TABLEにWITH EMPTY TABLEを付与する案
ALTER TABLE 表名 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
WITH EMPTY TABLEをつけることで、現在表にあるすべてのデータを削除できる。
※一度データが除去されると、 データをリストア使用しなければ復旧できない。
ALTER TABLEを発行した作業単位をロールバックしても、データは元の状態には回復しないので、注意！
・TRUNCATEを使って該当表に含まれるすべての行を削除する案
※V9.7以降使える。
db2 "TRUNCATE TABLE 表名 IMMEDIATE"
TRUNCATEを使用するにはDB2_COMPATIBILITY_VECTORレジストリ変数で8番目のビット(TRUNCATE TABLE)を立てる必要がある。
※V10.1ではレジストリ設定なしでも実行可能。
db2set DB2_COMPATIBILITY_VECTOR=8
db2stop
db2start
なお、設定を有効にするためには、インスタンスを停止し、再起動する必要がある。
また、既存の DB2 パッケージを再バインドする必要もある。
以下検証
TPCC DBのstock表に500万レコードを入れた状態で、499950(約50万) レコード削除することで計測してみる。
db2 "select count(*) from stock"
1
-----------
5000000
STMMと自動保守をOFFに設定。
db2 update db cfg using SELF_TUNING_MEM OFF
db2 update db cfg using AUTO_MAINT OFF
db2 "BACKUP DB tpcc TO /tmp"
バックアップは成功しました。
このバックアップ・イメージのタイム・スタンプは
20120506024617 です。
-rw------- 1 db2inst1 db2iadm1 3054231552 5月 6 02:47 /tmp/TPCC.0.db2inst1.DBPART000.20120506024617.001
※検証完了のタイミングでdb2 "RESTORE DB tpcc FROM /tmp/"を実行しリストア。
★検証1
db2 "DELETE FROM (SELECT * FROM stock WHERE s_i_id < 10000 FETCH FIRST 499950 ROWS ONLY)"
※速度を検証したいので、分割しないで一気に消す。
ログサイズは大きくして実施している。
db2 get db cfg for tpcc | grep LOG
ログ・バッファー・サイズ (4KB) (LOGBUFSZ) = 2149
ログ・ファイルのサイズ (4KB) (LOGFILSIZ) = 20000
1 次ログ・ファイルの数 (LOGPRIMARY) = 20
2 次ログ・ファイル数 (LOGSECOND) = 11
db2batchを使って測定する。test.sqlの中に実行するSQLを記述。
db2batch -d tpcc -f test.sql
DELETE FROM (SELECT * FROM stock WHERE s_i_id < 10000 FETCH FIRST 499950 ROWS ONLY);
* 経過時間: 272.355927 秒
db2 "select count(*) from stock"
1
-----------
4500050
272秒かかった。
※追記 ログ格納DISKを性能の良いのに変えてみたら結構早くなった。
算術平均時間: 124.527187 秒
★検証2
ログを取得しないように設定し削除する。
db2 +c "ALTER TABLE stock ACTIVATE NOT LOGGED INITIALLY"
db2 +c "DELETE FROM (SELECT * FROM stock WHERE s_i_id < 10000 FETCH FIRST 499950 ROWS ONLY)"
db2 +c "commit"
db2batchで、db2 +cが使えなかったので、shでdateコマンドを挟んで上記コマンドを実行した。
※「db2 update command options using c off」コマンドでオートコミットをOFFにしたんだけど、何故か反映されない。。。バグかな？
※追記 以下で変更できた。
export DB2OPTIONS=+c
db2inst1@linux-d9mi:~> sh test.sh
2012年05月06日 06時49分02秒 082
DB20000I SQL コマンドが正常に完了しました。
2012年05月06日 06時49分21秒 825
DB20000I SQL コマンドが正常に完了しました。
2012年05月06日 06時58分22秒 551
db2 "select count(*) from stock"
1
-----------
4500050
1 レコードが選択されました。
499950行deleteするためにかかった時間は約8.5分。
内、deleteは約20秒、コミットが8分程度。
★検証3
WITH EMPTY TABLEにて一気に削除する。
db2 "ALTER TABLE stock ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE"
2012年05月06日 07時30分33秒 302
DB20000I SQL コマンドが正常に完了しました。
2012年05月06日 07時30分34秒 247
db2 "select count(*) from stock"
1
-----------
0
★検証4
/dev/nullで0クリアする方法
db2inst1@linux-d9mi:~> sh test.sh
2012年05月06日 07時55分06秒 429
SQL3109N ユーティリティーが、ファイル "/dev/null" から
データのロードを開始しています。
SQL3110N ユーティリティーが処理を完了しました。 "0"
行が、入力ファイルから読み取られました。
SQL3221W ...COMMIT WORK が開始されました。
入力レコード・カウント = "0"
SQL3222W ...すべてのデータベース変更の COMMIT
が成功しました。
SQL3149N "0" 行が、入力ファイルから処理されました。 "0"
行が、正常に表に挿入されました。"0"
行が、拒否されました。
読み込まれた行数 = 0
スキップされた行数 = 0
挿入された行数 = 0
更新された行数 = 0
拒否された行数 = 0
コミットされた行数 = 0
2012年05月06日 07時55分07秒 933
db2 "select count(*) from stock"
1
-----------
0
★検証5
TRUNCATEする方法
db2set DB2_COMPATIBILITY_VECTOR=8
db2set -all
[i] DB2_COMPATIBILITY_VECTOR=8
[i] DB2_PARALLEL_IO=*
[i] DB2AUTOSTART=NO
[g] DB2_COMPATIBILITY_VECTOR=MYS
[g] DB2SYSTEM=linux-d9mi
[g] DB2INSTDEF=db2inst1
[g] DB2ADMINSERVER=dasusr1
db2 connect reset
DB20000I SQL コマンドが正常に完了しました。
db2stop
SQL1064N DB2STOP の処理が正常に終了しました。
db2start
SQL1063N DB2START の処理が正常に終了しました。
db2inst1@linux-d9mi:~> sh test.sh
データベース接続情報
データベース・サーバー = DB2/LINUXX8664 10.1.0
SQL 許可 ID = DB2INST1
ローカル・データベース別名 = TPCC
2012年05月06日 08時30分43秒 504
DB20000I SQL コマンドが正常に完了しました。
2012年05月06日 08時30分44秒 345
db2 "select count(*) from stock"
1
-----------
0
(´・ω・｀)まとめ
検証1と2
うーむ。通常は、ログを書いてI/Oサーバがバッファプールに書き込んで終わりだけど、ログがないからコミット時に100%DISK書き込みに行く。
なので、DISKのwrite性能が悪いと返って性能が悪くなるな。。。
エンタープライズ系のDISK装置とかだとこっちのほうが速いんだろうけど、市販の5000回転ぐらいのHDDだとちょっと考えものだ。
検証3,4,5
速度的にあまり変わらない。
V10.1ではレジストリを編集する必要がないTRUNCATEが使い勝手が良いかな。
0 件のコメント:
コメントを投稿