[pgsql-jp: 28734] Re: current transaction is aborted について

[Satoshi Nagayasu]

永安です。

Hiroki Kataoka <kataoka ＠ interwiz.jp> wrote:
> 片岡です。どうも。
> 
>   この辺は興味深いのでおじゃまします。

こんばんわです。

>   同じ行（uid = 1）を10000回更新していますが、PostgreSQLは追記型アーキテ
> クチャです。BEGIN〜COMMITで囲んだパターンの最後の方は、更新対象の1行を探
> すのが大変だったろうと察します。テーブルにデータが1行しか無かったとして
> も、追記型アーキテクチャのために10000回目のUPDATEの直前では10000行のレコー
> ドが物理的に存在していたはずですし、またこの中から本物の1行を探すのにイ
> ンデックスは全く役に立たなかったと思います。だって10000行全部がuid=1です
> （笑）。

なるほど。追記型のMVCCだから、すべてのタプルに対して、同じトランザクショ
ンIDが付くわけですね。そうすると、その中から探さなければならない、と。

一方、autocommitの場合にはそれぞれのレコードに異なるXIDが付くので、
一つだけ見つければよい、と。

ううむ、ようやくなんとなく分かったような。なるほど。

>   ここまでは7.2、7.3とも同様です。
> 
>   7.3ではCOMMIT時にインデックスが多少整理されるようになったため、今回の
> 場合はCOMMITが多く発生した方が「本物の1行」を見つけやすくなり、結果とし
> てAUTO COMMITの方が速くなったのだと思います。

みたいですね。

>   でもこれって、かなり特殊なベンチマークパターンですよね。同じ1行
> を1万回更新かつ1つのトランザクションブロックで囲む………（笑）。なので、
> テーブルにインデックスが無かったり単純なINSERTだったり、あるいは更新対象
> がばらけているUPDATEなどでは、当初の予想通りBEGIN〜COMMITの方が速いんじゃ
> ないかと思います。試してませんが。

試しました(爆)。某所に送ったメールからのコピペですが、添付します。

まず、uidを1,2,1,2と交互にした場合です。
-------------------------------------------------------------
[snaga ＠ ia32-02s pgsql]$ head update2.sql
drop table update_test;
create table update_test ( uid integer primary key, counter integer not null );
insert into update_test values ( 1, 0 );
insert into update_test values ( 2, 0 );
begin;
update update_test set counter = 0 where uid = 1;
update update_test set counter = 1 where uid = 2;
update update_test set counter = 2 where uid = 1;
update update_test set counter = 3 where uid = 2;
update update_test set counter = 4 where uid = 1;
[snaga ＠ ia32-02s pgsql]$ tail update2.sql 
update update_test set counter = 9991 where uid = 2;
update update_test set counter = 9992 where uid = 1;
update update_test set counter = 9993 where uid = 2;
update update_test set counter = 9994 where uid = 1;
update update_test set counter = 9995 where uid = 2;
update update_test set counter = 9996 where uid = 1;
update update_test set counter = 9997 where uid = 2;
update update_test set counter = 9998 where uid = 1;
update update_test set counter = 9999 where uid = 2;
commit;
-------------------------------------------------------------
[snaga ＠ ia32-02s pgsql]$ head update2_autocommit.sql
drop table update_test;
create table update_test ( uid integer primary key, counter integer not null );
insert into update_test values ( 1, 0 );
insert into update_test values ( 2, 0 );
-- begin;
update update_test set counter = 0 where uid = 1;
update update_test set counter = 1 where uid = 2;
update update_test set counter = 2 where uid = 1;
update update_test set counter = 3 where uid = 2;
update update_test set counter = 4 where uid = 1;
[snaga ＠ ia32-02s pgsql]$ tail update2_autocommit.sql
update update_test set counter = 9991 where uid = 2;
update update_test set counter = 9992 where uid = 1;
update update_test set counter = 9993 where uid = 2;
update update_test set counter = 9994 where uid = 1;
update update_test set counter = 9995 where uid = 2;
update update_test set counter = 9996 where uid = 1;
update update_test set counter = 9997 where uid = 2;
update update_test set counter = 9998 where uid = 1;
update update_test set counter = 9999 where uid = 2;
-- commit;
-------------------------------------------------------------
[snaga ＠ ia32-02s pgsql]$ time psql -f update2_autocommit.sql >/dev/null 
real    0m17.410s
user    0m0.379s
sys     0m0.307s
[snaga ＠ ia32-02s pgsql]$ 
[snaga ＠ ia32-02s pgsql]$ time psql -f update2.sql >/dev/null 
real    1m7.154s
user    0m0.441s
sys     0m0.277s
[snaga ＠ ia32-02s pgsql]$ 
-------------------------------------------------------------

次に全部違うUIDでやってみたら、autocommitの方が遅くなりました。
-------------------------------------------------------------
[snaga ＠ ia32-02s pgsql]$ head update3_tblock.sql 
begin;
update update_test set counter = 0 where uid = 0;
update update_test set counter = 1 where uid = 1;
update update_test set counter = 2 where uid = 2;
update update_test set counter = 3 where uid = 3;
update update_test set counter = 4 where uid = 4;
update update_test set counter = 5 where uid = 5;
update update_test set counter = 6 where uid = 6;
update update_test set counter = 7 where uid = 7;
update update_test set counter = 8 where uid = 8;
[snaga ＠ ia32-02s pgsql]$ head update3_autocommit.sql
-- begin;
update update_test set counter = 0 where uid = 0;
update update_test set counter = 1 where uid = 1;
update update_test set counter = 2 where uid = 2;
update update_test set counter = 3 where uid = 3;
update update_test set counter = 4 where uid = 4;
update update_test set counter = 5 where uid = 5;
update update_test set counter = 6 where uid = 6;
update update_test set counter = 7 where uid = 7;
update update_test set counter = 8 where uid = 8;
[snaga ＠ ia32-02s pgsql]$ 
-------------------------------------------------------------
[snaga ＠ ia32-02s pgsql]$ time psql -f  update3_tblock.sql > /dev/null 

real    0m4.550s
user    0m0.426s
sys     0m0.320s
[snaga ＠ ia32-02s pgsql]$ time psql -f  update3_autocommit.sql > /dev/null 

real    0m12.383s
user    0m0.428s
sys     0m0.336s
[snaga ＠ ia32-02s pgsql]$ 
-------------------------------------------------------------

というわけで、楽をしようとしてソース読まずにバックエンドを
ブラックボックス扱いでいろいろ調べようとすると無理が出てくるわけですが、:-)
かと言ってソース読み始めると「また現実逃避して、、」とか言われるので、
このネタは一旦ここで打ち止めにします。X-)

-- 
NAGAYASU Satoshi <snaga ＠ snaga.org>