VACUUMの改善二題

はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の21日目のエントリです。
今日も小ネタですいませんです。

今回のお題

PostgreSQLといえばVACUUM! だった時代は遠くに去りにけり。特殊なワークロードでもない限り、最近は自動VACUUMがよろしくやってくれるので、VACUUMを以前ほど意識して運用することは少ないかもしれない。
とはいえ、現状の追記方式をPostgreSQLが使っている限り、裏ではVACUUMが動いているわけで、未だに重要な機構の一つではある。
なので、VACUUMについてもやっぱり改善がされている。

PostgreSQL 10ではざっくり、以下の2つの改善項目がある。

• VACUUM VERBOSEの表示内容改善
• 末端のタプルが削除されたときの性能改善

これらの改善について、ちょこっと試してみた。

おためし方法

以下の操作を、PostgereSQL 9.6とPostgreSQL 10で実施する。

• pgbench_accounts(scale = 30)のテーブルをpgbenchを使ってロードする。
• pgbench_accountsに対して、一旦VACUUM FREEZEをかける。
• pgbench_accountsの末端30%のタプルをDELETEする。
• ALTER TABLEでpgbench_accountsのprimary key制約を除去する。
• この状態で、VACUUM VERBOSEを実施する。

スクリプト自体はこんな感じ。

$ cat vacuum.sql 
\timing
SET log_min_duration_statement = 0;
VACUUM FREEZE VERBOSE pgbench_accounts;
DELETE FROM pgbench_accounts WHERE aid > 270000;
ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey ;
CHECKPOINT;
SELECT pg_relation_size('pgbench_accounts');
SET maintenance_work_mem = '256MB';
VACUUM VERBOSE pgbench_accounts;
SELECT pg_relation_size('pgbench_accounts');
TRUNCATE pgbench_accounts;

これで表示内容の改善と性能改善結果を確認してみる。

VACUUM VERBOSEの表示内容改善

• PostgreSQL 9.6

うー、無駄に長い。

VACUUM VERBOSE pgbench_accounts;
psql:vacuum.sql:9: INFO:  vacuuming "public.pgbench_accounts"
psql:vacuum.sql:9: INFO:  "pgbench_accounts": removed 2730000 row versions in 44755 pages
psql:vacuum.sql:9: INFO:  "pgbench_accounts": found 2730000 removable, 14 nonremovable row versions in 44755 out of 49181 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.23s/0.41u sec elapsed 0.66 sec.
LOG:  duration: 1727.164 ms  statement: VACUUM VERBOSE pgbench_accounts;
psql:vacuum.sql:9: INFO:  "pgbench_accounts": truncated 49181 to 4427 pages
DETAIL:  CPU 0.20s/0.00u sec elapsed 1.04 sec
VACUUM
Time: 1727.355 ms

• PostgreSQL 10

VACUUM VERBOSE pgbench_accounts;
psql:vacuum.sql:9: INFO:  vacuuming "public.pgbench_accounts"
psql:vacuum.sql:9: INFO:  "pgbench_accounts": removed 2730000 row versions in 44755 pages
psql:vacuum.sql:9: INFO:  "pgbench_accounts": found 2730000 removable, 14 nonremovable row versions in 44755 out of 49181 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1288948
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 4426 frozen pages.
0 pages are entirely empty.
CPU: user: 0.24 s, system: 0.18 s, elapsed: 0.44 s.
psql:vacuum.sql:9: INFO:  "pgbench_accounts": truncated 49181 to 4427 pages
DETAIL:  CPU: user: 0.03 s, system: 0.17 s, elapsed: 0.86 s
VACUUM
Time: 1323.373 ms (00:01.323)

PostgreSQL 10では

• 4426 frozen pages.
• oldest xmin: 1288948

という情報が追加されている。

末端のタプルが削除されたときの性能改善

今回測定したケースだと、若干、PostgreSQL 10のほうが良いケースも見られたが、思ったよりも測定結果のブレが大きく、この検証結果だけでは、PostgreSQL 10のほうが性能的に有意な結果は得られなかった。残念。

おわりに

• 今でもVACUUMの改善は続いているのだなあと。

参考：該当するリリースノート

本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.4. Utility Commands

• Make VACUUM VERBOSE report the number of skipped frozen pages and oldest xmin (Masahiko Sawada, Simon Riggs)
• Improve speed of VACUUM's removal of trailing empty heap pages (Claudio Freire, Álvaro Herrera)