はじめに
数日前に流れていた、PostgreSQL開発者の一人、Oleg Bartunov氏のツイートが気になっていたので、手元の環境で調べてみることにした。
なお、Oleg Bartunov氏は、PostgreSQLの配列、拡張、JSON型、GINインデックス等の開発に携わっている方だ。1
ツイート内容引用
Generate less WAL during GiST, GIN and SP-GiST index build. Just checked on imdb database with GIN index - wal traffic decreased from 3.3GB to 0.4GB and building time from 200s to 133 s, great patch for PG12 !
雑に訳すとこんな感じ。
GiST、GIN、およびSP-GiSTインデックス作成時に生成されるWALが少なくなる。 GINインデックスでimdbデータベースをチェックしたところ - walトラフィックは3.3GBから0.4GBに減少し、構築時間は200秒から133秒に短縮された。PG12に最適なパッチである。
imdbは先日のpg_plan_advsr調査でも使った映画関連の情報が入ったデータベースだ。
git log内容
commit 9155580fd5fc2a0cbb23376dfca7cd21f59c2c7b の内容
Generate less WAL during GiST, GIN and SP-GiST index build.
Instead of WAL-logging every modification during the build separately,
first build the index without any WAL-logging, and make a separate pass
through the index at the end, to write all pages to the WAL. This
significantly reduces the amount of WAL generated, and is usually also
faster, despite the extra I/O needed for the extra scan through the index.
WAL generated this way is also faster to replay.For GiST, the LSN-NSN interlock makes this a little tricky. All pages must
be marked with a valid (i.e. non-zero) LSN, so that the parent-child
LSN-NSN interlock works correctly. We now use magic value 1 for that during
index build. Change the fake LSN counter to begin from 1000, so that 1 is
safely smaller than any real or fake LSN. 2 would've been enough for our
purposes, but let's reserve a bigger range, in case we need more special
values in the future.
確認方法
PostgreSQL 11.2で確認
PostgreSQL 11.2上で、以下の手順でGINインデックスの生成時間と、GINインデックス生成によるWALサイズの変分を確認する。
- initdbでデータベースクラスタを生成する。postgresql.confはデフォルトのままにしておく。その状態で、PostgreSQLサーバを起動する。
- integer型配列
integer[]列を含むテーブルを定義する。
testdb=# CREATE TABLE test (id int, data int[]);
CREATE TABLE
testdb=#
- 5個の配列要素を含むデータ1000万件をロードする。
- 最初のデータ生成時には、こんな感じのINSERT文を実行する。
INSERT INTO test SELECT generate_series(1,10000000),
ARRAY[
(random() * 1000)::int,
(random() * 1000)::int,
(random() * 1000)::int,
(random() * 1000)::int,
(random() * 1000)::int
]
;
- ロードすると、こんな感じのデータが格納される。
testdb=# SELECT * FROM test ORDER BY id LIMIT 3;
id | data
----+-----------------------
1 | {318,417,222,31,884}
2 | {264,445,735,529,620}
3 | {801,395,547,960,250}
(3 rows)
- INSEETでロードした後にCOPY TOでデータをエクスポートしておく。(PostgreSQL 12-devel検証で使う）
testdb=# COPY (SELECT * FROM test ORDER BY id) TO '/tmp/test.dat';
COPY 10000000
testdb=#
- 一旦、PostgreSQLサーバを停止させる。
- pg_resetwalユーティリティでWAL関連の情報/WALセグメントファイルを初期化する。
$ pg_resetwal /tmp/11
Write-ahead log reset
$
- PostgreSQLサーバを再起動する。
- その時点のwalの位置を
pg_current_wal_lsn()で取得する。
testdb=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/7E000108
(1 row)
- この状態のpg_walフォルダ内をlsで確認する。
$ ls -1 /tmp/11/pg_wal/
00000001000000000000007E
archive_status
$
- psqlの
\timingメタコマンドでコマンドの実行時間を出力するように設定する。（雑） - 配列要素をもつ列(data)をGINインデックスとして生成する。
testdb=# \timing
Timing is on.
testdb=# CREATE INDEX gin_idx ON test USING gin (data);
CREATE INDEX
Time: 15438.906 ms (00:15.439)
testdb=#
- CREATE INDEXが終わったあとの状態のLSNをpg_wal_lsn()で取得する。
testdb=# SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/8801DE08
(1 row)
- この状態のpg_walフォルダ内をlsで再確認する。
$ ls -1 /tmp/11/pg_wal/
00000001000000000000007E
00000001000000000000007F
000000010000000000000080
000000010000000000000081
000000010000000000000082
000000010000000000000083
000000010000000000000084
000000010000000000000085
000000010000000000000086
000000010000000000000087
000000010000000000000088
archive_status
$
PostgreSQL 12-develで確認
同様の手順でPostgreSQL 12-devel2でも確認を行う。（雑）
インデックス作成前のデータは、エクスポート済みのデータをCOPY FROMでロードすればOK。
確認結果
WALセグメントファイルの差分
pg_resetwalユーティリティで初期化した後は、空のWALセグメントファイルが1つだけpg_walフォルダに生成された状態になっている。
この状態から、CREATE INDEXを行ったあとのWALセグメント個数を比較してみる。
| バージョン | CREATE INDEX前の個数 | CREATE INDEX後の個数 |
|---|---|---|
| PostgreSQL 11.2 | 1 | 11 |
| PostgreSQL 12-devel | 1 | 6 |
CREATE INDEXコマンドによって生成されたセグメントファイルの数が、PostgreSQL 12-develだと明らかに少なくなっていることがわかる。
pg_lsnの差分
同様に、CREATE INDEX実行前後のpg_current_wal_lsn()の結果の差分を比較してみる。
| バージョン | CREATE INDEX前のLSN | CREATE INDEX後のLSN | 差分 |
|---|---|---|---|
| PostgreSQL 11.2 | 0/7E000108 | 0/8801DE08 | 167894272 |
| PostgreSQL 12-devel | 0/4F000110 | 0/5487B2C8 | 92778936 |
LSNの差分についても、PostgreSQL 12-develが小さくなっていることがわかる。
処理性能
処理性能については、（この検証パターンでは）これという有意な差はとれなかった。
(PostgreSQL 11のほうが微妙に処理時間が短い・・・)
| バージョン | CREATE INDEX処理時間(ms) |
|---|---|
| PostgreSQL 11.2 | 15438.906 |
| PostgreSQL 12-devel | 16268.265 |
このケースだと、インデックス生成のための読み込み処理時間のほうが処理時間の多くを占めており、WAL書き出し量の差が性能に影響を与えるほどではなかったのだろう。もっと多くのWALが出るようなGINインデックス生成パターン、例えばpg_bigmのような全文インデックスを生成するものだと、より効果が見えやすいのかもしれない。別途実験してみるのが良さげ。
おわりに
今回はシンプルな配列要素に対するGINインデックスを対象に検証を実施して、WAL作成量の変化やWALインデックス作成時間の差を見てみた。
今回は非常にシンプルな配列を使ったパターンかつデータ件数も1000万件程度なので、それほど大きな効果は見えないかもしれないが、JSONB型や全文検索(pg_trgm, pg_bigm, textsearch)で大きなインデックスを生成するときには、この改善の効果がもっと見えてきそうな気がする。