ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

RDB が ROLLBACK の処理コストを支払うタイミング

データベース プログラミング

概要

Oracle Database で大量のデータを ROLLBACK するのには時間がかかります。これに対して PostgreSQL の ROLLBACK は一瞬で完了します。この差について検証と考察を行いました。そこで見えてきたのは ROLLBACK の処理コストを支払うタイミングの違いでした。

検証環境

Windows の Hyper-V による仮想マシンを検証環境としました。

ホスト

プロセッサ Intel Core i5-6600 CPU @ 3.30GHz
メモリ 20.0 GB
OS Windows 10 Pro バージョン 1909

Oracle Database

プロセッサ 4個の仮想プロセッサ
メモリ 8.0GB
OS Oracle Linux Server release 7.8
DB Oracle Database 19c EE 19.3

PostgreSQL

プロセッサ 4個の仮想プロセッサ
メモリ 8.0GB
OS CentOS 8.1.1911
DB PostgreSQL 12.1

検証方法

各DBで以下の操作を行います。

  1. テーブルを作成する。
  2. テーブルに大量データを INSERT する。
  3. INSERT を ROLLBACK する。
  4. （PostgreSQLのみ）テーブルに VACUUM を実行する。

各操作 (INSERT, ROLLBACK, VACUUM) の処理時間を測定します。

Oracle Database

CREATE TABLE tab1(c1 INTEGER);

ALTER SYSTEM FLUSH BUFFER_CACHE;

SET TIMING ON

INSERT INTO tab1 SELECT rownum
  FROM (SELECT level FROM dual CONNECT BY level <= 10000),
       (SELECT level FROM dual CONNECT BY level <= 10000);

ROLLBACK;

PostgreSQL

CREATE TABLE tab1(c1 INTEGER);

BEGIN;

\timing

INSERT INTO tab1 SELECT generate_series(1, 100000000);

ROLLBACK;

VACUUM tab1;

検証結果・考察

操作 Oracle Database PostgreSQL
INSERT 09:28.942 04:22.612
ROLLBACK 01:07.629 00:00.240
VACUUM --- 15:36.632

各DBは環境やクエリが異なっており、チューニングも行っていません。よって、処理時間を絶対的に比較するのは適切ではありません。

ここでは、各DBがどの操作に相対的な観点で時間がかかっていたかを確認します。

Oracle Database

Oracle Database では ROLLBACK に時間がかかっています。このことはドキュメントにも明記されています。

ロールバックの持続時間は、変更されたデータ量の関数になります。

トランザクション

また、Oracle Database が ROLLBACK で行う内部処理についてもドキュメントに記載があります。

データベースでは、UNDOセグメントからデータを読み取り、操作を逆順にして、適用されたとおりにUNDOエントリをマークします。したがって、トランザクションで行が挿入されると、ロールバックによって削除されます。

トランザクション

つまり、INSERT に対する ROLLBACK は DELETE を行っているのに相当するといえます。このことからも ROLLBACK に処理時間がかかる理由を説明できます。

PostgreSQL

PostgreSQL では ROLLBACK が一瞬で完了しています。PostgreSQL では ROLLBACK してもトランザクションの状態を Abort に更新するのみであり、テーブルファイルには更新処理の結果を残したままとするためです。これは PostgreSQL がストレージアーキテクチャとして採用した「追記型アーキテクチャ」に由来しています。

PostgreSQL のトランザクションと可視性については下記の記事を参照ください。
PostgreSQL のトランザクション & MVCC & スナップショットの仕組み

実際の挙動については以前の記事で調査結果をまとめています。
PostgreSQL は更新処理を ROLLBACK してもテーブルファイルに追記される - ぱと隊長日誌

追記型アーキテクチャにおいて、テーブルファイル内の不要なデータは VACUUM を実行するまで削除されません。そして、検証結果からは ROLLBACK によって大量に残った不要データを削除するための VACUUM に時間を要したことが分かります。

まとめ

ROLLBACK を各DBで比較すると次のように言えます。

  • Oracle Database は ROLLBACK の処理コストを即座に支払う。
  • PostgreSQL は ROLLBACK の処理コストを後払いにする。

各DBの特性としてこのような違いがあることを意識し、設計することが肝要といえます。

PostgreSQL は更新処理を ROLLBACK してもテーブルファイルに追記される

データベース プログラミング

概要

PostgreSQL はテーブルに対する更新処理 (INSERT / UPDATE / DELETE) を行うと、テーブルファイルに追記されます（追記型アーキテクチャ）。これは最終的に COMMIT した場合に限らず、ROLLBACK された場合でも同様となります。

本エントリではこの挙動を検証します。

なお、追記型アーキテクチャについて知りたい方は下記の記事を参照ください。
PostgreSQL Deep Dive: PostgreSQLのストレージアーキテクチャ（基本編）

検証

検証環境

  • PostgreSQL 12.1
    • contrib モジュールをインストールしました。
    • postgresql.conf で "autovacuum = off" に設定しました。

ROLLBACK とテーブルファイルサイズ

各ステップでテーブルファイルサイズがどのように変化するかを確認します。

テスト用のテーブルを作成し、テーブルファイルのパスを調べます。

testdb=# CREATE TABLE tab1(c1 integer);

testdb=# SELECT pg_relation_filepath('tab1');
 pg_relation_filepath
----------------------
 base/16384/25445
(1 row)

テーブルファイルのサイズを調べます。

$ cd /usr/local/pgsql/data/base/16384/

$ ls -l 25445*
-rw-------. 1 postgres postgres 0  6月  6 10:23 25445

作成直後は0バイトであることがわかります。

テーブルに1行追加します。

testdb=# BEGIN;

testdb=# INSERT INTO tab1(c1) VALUES (1);

testdb=# CHECKPOINT;

この時点でのテーブルファイルのサイズとハッシュ値を調べます。

$ ls -l 25445*
-rw-------. 1 postgres postgres 8192  6月  6 10:24 25445

$ md5sum 25445
6a5133d3254b46397a1cd33b184e8248  25445

COMMIT も ROLLBACK もしていませんが、テーブルファイルのサイズが8192バイトになりました。

ROLLBACK を実行します。

testdb=# ROLLBACK;

testdb=# CHECKPOINT;

testdb=# SELECT COUNT(*) FROM tab1;
 count
-------
     0
(1 row)
$ ls -l 25445*
-rw-------. 1 postgres postgres 8192  6月  6 10:24 25445

$ md5sum 25445
6a5133d3254b46397a1cd33b184e8248  25445

ROLLBACK したのでテーブルは0行ですが、テーブルファイルのサイズは変わらず8192バイトあります。また、ROLLBACK前後でファイルのハッシュ値が一致したことから、更新されていないことが分かります。

VACCUM を実行します。

testdb=# VACUUM tab1;
$ ls -l 25445*
-rw-------. 1 postgres postgres     0  6月  6 10:27 25445
-rw-------. 1 postgres postgres 16384  6月  6 10:27 25445_fsm
-rw-------. 1 postgres postgres     0  6月  6 10:27 25445_vm

テーブルファイルのサイズが0バイトに戻りました。

ROLLBACK とテーブルファイルの中身

ROLLBACK 前後でテーブルファイルの中身（ページヘッダとタプルの情報）を比較しました。比較には pageinspect モジュールを利用しました。

ページヘッダとタプルのデータ構造は以下の記事を参照ください。
The Internals of PostgreSQL
1.3. Internal Layout of a Heap Table File
5.2. Tuple Structure

testdb=# SELECT * FROM page_header(get_raw_page('tab1', 0));
ERROR:  block number 0 is out of range for relation "tab1"

testdb=# SELECT * FROM heap_page_items(get_raw_page('tab1', 0));
ERROR:  block number 0 is out of range for relation "tab1"

testdb=# BEGIN;
BEGIN
testdb=# INSERT INTO tab1(c1) VALUES (1);
INSERT 0 1

testdb=# SELECT * FROM page_header(get_raw_page('tab1', 0));
    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 2/692608B8 |        0 |     0 |    28 |  8160 |    8192 |     8192 |       4 |         0
(1 row)

testdb=# SELECT * FROM heap_page_items(get_raw_page('tab1', 0));
 lp | lp_off | lp_flags | lp_len |  t_xmin  | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data
----+--------+----------+--------+----------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 | 26881546 |      0 |        0 | (0,1)  |           1 |       2048 |     24 |        |       | \x01000000
(1 row)

testdb=# ROLLBACK;
ROLLBACK

testdb=# SELECT * FROM page_header(get_raw_page('tab1', 0));
    lsn     | checksum | flags | lower | upper | special | pagesize | version | prune_xid
------------+----------+-------+-------+-------+---------+----------+---------+-----------
 2/692608B8 |        0 |     0 |    28 |  8160 |    8192 |     8192 |       4 |         0
(1 row)

testdb=# SELECT * FROM heap_page_items(get_raw_page('tab1', 0));
 lp | lp_off | lp_flags | lp_len |  t_xmin  | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |   t_data
----+--------+----------+--------+----------+--------+----------+--------+-------------+------------+--------+--------+-------+------------
  1 |   8160 |        1 |     28 | 26881546 |      0 |        0 | (0,1)  |           1 |       2048 |     24 |        |       | \x01000000
(1 row)

ROLLBACK 前後でページヘッダとタプルの情報に差異は無いことが分かりました。

なお、この後に PostgreSQL を再起動しても、ページヘッダとタプルの情報に変化はありませんでした。このことからも進行中のトランザクションの更新結果は揮発性の情報として管理されるのでなく、テーブルファイルに書き込まれたと推測できます。

Twitterでは @tatsuo_ishii さんからも裏付けるコメントをいただきました。


なお、コメント後半の「直観に反している」はご指摘の通り「Oracle的な常識」からの話でした（※本記事ではなく、私のTwitter投稿内容に対するコメントです）。

まとめ

PostgreSQL のトランザクション & MVCC & スナップショットの仕組み
この記事の説明によれば、PostgreSQL は ROLLBACK してもトランザクションの状態を Abort に更新するのみであり、テーブルファイルには更新処理の結果が残っています。今回の検証結果はこれを裏付けるものとなりました。

テーブルファイル内の不要なデータは VACUUM を実行するまで削除されません。これは処理性能劣化の原因となり得ます。

最終的に ROLLBACK するからと安易にテーブルを更新することは、例えテストであっても慎重になった方が良いでしょう。

更新情報

2020/06/06

【ROLLBACK とテーブルファイルサイズ】

更新結果がバッファのみに存在する可能性を考慮し、INSERT と ROLLBACK の後に CHECKPOINT を実行する手順に変更しました。また、テーブルファイルのハッシュ値比較を追加しました。

【ROLLBACK とテーブルファイルの中身】

以下の内容を追記しました。

  • ページヘッダとタプルのデータ構造についての参考記事。
  • ROLLBACK 後に PostgreSQL を再起動して、ページヘッダとタプルを比較した結果と考察。
  • Twitter での @tatsuo_ishii さんからのコメント引用。

チームメンバーに求める「体調管理」

マネジメント／リーダーシップ コミュニケーション

『体調管理をしっかりするように』この言葉を社会人になってから幾度も聞いた。はい、と返事するものの内心困っていたりする。風邪をひきたくてひくわけではないのに、どう回避したらいいのよ、と悩んでしまうのだ。そして、体調不良で休んだら怒られるんじゃないだろうか、仕事の遅れをどうリカバリーするのかと責められるのではないかと気が気でなかった。

だから私がメンバーを率いるようになってから代わりにこう伝えるようにしている。「体調が悪ければ休んでください。周囲への影響を考えるように。休むべき時に休むことも体調管理です。」と。

こうやって考えるきっかけになったのは人材教育コンサルタントの田中淳子さんのブログにこんな言葉を見つけたからだ。

あのね、「腐ったミカン」って言うでしょう？ 田中さんがよくても、田中さんの風邪が周囲にうつることもあるわけで、そうなると影響が大きいんだよね。だから、あなたのことを心配しているんじゃなくて、ま、それもあるけれど、大勢への影響を気にしているの。だから、帰って。それから明日も休んで！

あなたは“腐ったミカン”です：田中淳子の“言葉のチカラ”（3） - ＠IT

この言葉に触れた時、それまで自分の事しか考えていなかったとガツンと衝撃を受けた。自分が休めば自分のタスクは遅れるかもしれない。でも、無理して出社してもパフォーマンスは出せないし、自分の風邪が他の方にうつってチーム全体のパフォーマンスを下げてしまうかもしれない。それぐらいなら自分は休んだ方がいい、そう思えるようになった。

メンバーが休むことに躊躇する理由の一つはスケジュールに遅れを作ってしまうことだ。だから、私が休めばよいと宣言する以上は休んだことによる遅れを責めないことにした。スケジュールにはバッファを設けるし、どうしようもなければ私も手を動かすし、それでも遅れるなら頭を下げて回る。社内SEかつ理解あるユーザーに恵まれたからではあるが、なんとか実現できてきた。

体調不良の休みは突然なものだから、普段から備えは怠らない。メンバーとのやり取りの内容は記録に残す。中間成果物を決まった場所に置く。夕会でその日の成果と翌営業日の予定を把握する。そうやって突然の休みの場合でもカバーできるように備えている。

メンバーの普段の様子を観察するようにしている。笑顔は出ているか。ミスを繰り返していないか。昼休みをちゃんととっているか。「普段通り」がどんな状態であるかを観察している。

また、何気ない雑談から体調や家庭の事情などで配慮すべきことがないかを聞き漏らさないように意識している。そして、できる限りの配慮を行うようにしている。

ただ、体調管理が「休むべき時に休むこと」だけではないこととも釘を刺している。規則正しく生活し、しっかり食べて、しっかり寝る。体調を崩す前兆を感じたら対処する。これらをしているという信頼の前提での「休むべき時に休むこと」だ。この前提が崩れてしまえば厳しい対応をせざるを得ない。

チームメンバーあってこそのチームであり、メンバーの体調管理もまたリーダーとマネージャーの責務だと考えている。