Hatena::ブログ(Diary)

日々の記録 別館

2015-08-11

PostgreSQL 9.5 ALTER TABLE .. SET LOGGED / UNLOGGED

今日は9.5の小ネタを。
9.5のリリースノートを見ると、こんな項目がある。

Allow changing of the WAL logging status of a table ater creation with ALTER TABLE .. SET LOGGED / UNLOGGED (Fabrizio de Royes Mello)


要するにALTER TABLEによるテーブルの属性の変更可能な属性として
ALTER TABLE .. SET LOGGED / UNLOGGED
という項目が加わったと。
今日はそれを試してみた。

UNLOGGED TABLEって(おさらい)

  • 更新時に更新ログ(WAL)を書かないテーブル。
  • WALを書かないから、更新が高速になる。
  • そんかしクラッシュ時は表の内容が爆発四散・・・というか消えてしまう。南無三!

たとえば、こんな感じ。

test=# CREATE UNLOGGED TABLE test (id int, data text);
CREATE TABLE
test=# INSERT INTO test VALUES (generate_series(1,3), 'AAA');
INSERT 0 3
test=# TABLE test;
 id | data 
----+------
  1 | AAA
  2 | AAA
  3 | AAA
(3 rows)

で、この状態でPostgreSQLサーバがクラッシュ(例えばpg_ctl -m immediate)すると

$ pg_ctl stop -m i -D ~/pgdata/9.5-alpha2/
waiting for server to shut down....LOG:  received immediate shutdown request
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
 done
server stopped

サーバ再起動するとクラッシュリカバリを行うのだが

$ pg_ctl start -D ~/pgdata/9.5-alpha2/
server starting
LOG:  database system was interrupted; last known up at 2015-08-11 21:49:17 JST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/20581D00
LOG:  invalid record length at 0/2059D9F0
LOG:  redo done at 0/2059D9C8
LOG:  last completed transaction was at log time 2015-08-11 21:50:28.366269+09
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

testテーブルを参照すると、

$ psql test
psql (9.5alpha2)
Type "help" for help.

test=# \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | test | table | nuko
(1 row)

test=# TABLE test;
 id | data 
----+------
(0 rows)

テーブル定義自体は残っているが、さっきINSERTしたデータは消えている。
UNLOGGEDな表ってのはそういうもの。

LOGGEDに変更されたら?

UNLOGGEDからLOGGEDに変更されたときに気になるのは、UNLOGGEDのときに更新されたレコードはどうなるのか、ということ。
LOGGEDに変更され、その直後にクラッシュしたらどうなるのか。

ということで試してみた。
まず、UNLOGGED TABLEを作成してレコードを挿入。

test=# CREATE UNLOGGED TABLE test (id int, data text);
CREATE TABLE
test=# \d test
 Unlogged table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 data   | text    | 

test=# INSERT INTO test VALUES (generate_series(1,3), 'AAA');
INSERT 0 3

その状態でALTER TABLEコマンドを使って、テーブルをLOGGEDに変更して、更にレコードを挿入する。

test=# ALTER TABLE test SET LOGGED ;
ALTER TABLE
test=# \d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 data   | text    | 

test=# INSERT INTO test VALUES (generate_series(4,6), 'BBB');
INSERT 0 3
test=# TABLE test;
 id | data 
----+------
  1 | AAA
  2 | AAA
  3 | AAA
  4 | BBB
  5 | BBB
  6 | BBB
(6 rows)

ここでPostgreSQLサーバをimmediateモードで停止(クラッシュと同じ)させ、その後にPostgreSQLサーバ再起動する。

$ pg_ctl stop -m i -D ~/pgdata/9.5-alpha2/
waiting for server to shut down....LOG:  received immediate shutdown request
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
 done
server stopped
$ pg_ctl start -D ~/pgdata/9.5-alpha2/
server starting
LOG:  database system was interrupted; last known up at 2015-08-11 22:14:38 JST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  invalid record length at 0/205E5280
LOG:  redo is not required
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

さて、このときにtestテーブルの内容はどうなるのか。

  • 答え1 UNLOGGEDのときに更新されたデータも、LOGGEDに変更された後のデータも保持される。
  • 答え2 LOGGEDにに変更された後のデータのみ保持される。
  • 答え3 かわせない。現実は非情である。

もちろん、答え1が正しい。

test=# TABLE test;
 id | data 
----+------
  1 | AAA
  2 | AAA
  3 | AAA
  4 | BBB
  5 | BBB
  6 | BBB
(6 rows)

UNLOGGEDに変更されたら?

では逆にLOGGEDからUNLOGGEDに変更された場合はどうなのか?

test=# \d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 data   | text    | 

test=# INSERT INTO test VALUES (generate_series(1,3), 'AAA');
INSERT 0 3
test=# ALTER TABLE test SET UNLOGGED ;
ALTER TABLE
test=# \d test
 Unlogged table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 data   | text    | 

test=# INSERT INTO test VALUES (generate_series(4,6), 'BBB');
INSERT 0 3
test=# TABLE test;
 id | data 
----+------
  1 | AAA
  2 | AAA
  3 | AAA
  4 | BBB
  5 | BBB
  6 | BBB
(6 rows)

この状態で、クラッシュしたらどうなるのか。

  • 答え1 UNLOGGEDのときに更新されたデータも、LOGGEDに変更された後のデータも消えてしまう。
  • 答え2 LOGGEDの状態で更新されたデータのみ保持される。
  • 答え3 かわせない。現実は非情である。

ということで、この状態でimmediateでPostgreSQLサーバを停止させ、再起動すると・・・

test=# \d test
 Unlogged table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 data   | text    | 

test=# TABLE test;
 id | data 
----+------
(0 rows)

もちろん、答え1のように全てのデータが消えてしまう。

ここまでの挙動から推測されること(単なる推測)

この実験から推測したこと。

  • UNLOGGEDからLOGGEDになった時点で、現状のUNLOGGEDテーブルに存在しているレコードが書き込まれた状態を復元するためのWALが書き出される。
  • LOGGEDからUNLOGGEDになった時点で、現状のテーブルに存在しているレコードが全て削除された(TRUNCATEと同様?)というWALが書き出され、以降はWALが書き出されなくなる。

本当にそうなのかどうかは、まだきちんと調べていない。

この機能を使って、

  • 大量のロードを行うときにはUNLOGGEDに変更してガン速ロード
  • ロード後にLOGGEDに変更

みたいないいとこ取りできるかなーと思ったけど、多分、ALTER TABLEでLOGGEDに変更するときに、大量のWAL書き出しが発生するから、トータルではそんなに変わらないような気もしてきた。

一応、試してみた。
UNLOGGED TABLEに1000万件データを挿入後に、LOGGEDに変更。

test=# INSERT INTO test VALUES (generate_series(1, 10000000), 'AAA');
INSERT 0 10000000
Time: 5750.821 ms
test=# ALTER TABLE test SET LOGGED ;
ALTER TABLE
Time: 5743.382 ms

INSERTとALTERの合計時間は約11.5秒。

UNLOGGEDでないフツーのテーブルに1000万件データを挿入した場合。

test=# INSERT INTO test VALUES (generate_series(1, 10000000), 'AAA');
INSERT 0 10000000
Time: 13302.407 ms

INSERTの時間は約13秒。

トータルでみるとそれほど差はない気がする。

レプリケーション環境の場合はどうなるのよ?

という疑問も当然出てくるけど、これは明日以降試してみる(仕事後に気力が残っていたら)。

Connection: close