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秒。
トータルでみるとそれほど差はない気がする。
レプリケーション環境の場合はどうなるのよ？
という疑問も当然出てくるけど、これは明日以降試してみる(仕事後に気力が残っていたら)。
