Re: [HACKERS] Block level parallel vacuum

Hi all,

I would like to share my observation on this PG feature "Block-level
parallel vacuum".
I have tested the earlier patch (i.e v48) with below high-level test
scenarios, and those are working as expected.

- I have played around with these GUC parameters while testing

max_worker_processes

autovacuum = off

shared_buffers

max_parallel_workers

max_parallel_maintenance_workers

min_parallel_index_scan_size

vacuum_cost_limit

vacuum_cost_delay

- Tested the parallel vacuum with tables and Partition tables having
possible datatypes and Columns having various indexes(like btree, gist,
etc.) on part / full table.
- Tested the pgbench tables data with multiple indexes created manually
and ran script(vacuum_test.sql) with DMLs and VACUUM for multiple Clients,
Jobs, and Time as below.

./pgbench -c 8 -j 16 -T 900 postgres -f vacuum_test.sql

We observe the usage of parallel workers during VACUUM.

- Ran few isolation schedule test cases(in regression) with huge data
and indexes, perform DMLs -> VACUUM
- Tested with PARTITION TABLEs -> global/local indexes -> DMLs -> VACUUM
- Tested with PARTITION TABLE having different TABLESPACE in different
location -> global/local indexes -> DMLs -> VACUUM
- With Changing STORAGE options for columns(as PLAIN / EXTERNAL /
EXTENDED) -> DMLs -> VACUUM
- Create index with CONCURRENTLY option / Changing storage_parameter for
index as below -> DMLs -> VACUUM

with(buffering=auto) / with(buffering=on) / with(buffering=off) /
with(fillfactor=30);

- Tested with creating Simple and Partitioned tables -> DMLs ->
pg_dump/pg_restore/pg_upgrade -> VACUUM

Verified the data after restore / upgrade / VACUUM.

- Indexes on UUID-OSSP data -> DMLs -> pg_upgrade -> VACUUM
- Verified with various test scenarios for better performance of
parallel VACUUM as compared to Non-parallel VACUUM.

Time taken by VACUUM on PG HEAD+PATCH(with PARALLEL) < Time taken
by VACUUM on PG HEAD (without PARALLEL)

Machine configuration: (16 VCPUs / RAM: 16GB / Disk size: 640GB)

*PG HEAD:*
VACUUM tab1;

Time: 38915.384 ms (00:38.915)

Time: 48389.006 ms (00:48.389)

Time: 41324.223 ms (00:41.324)

*Time: 37640.874 ms (00:37.641) --median*

Time: 36897.325 ms (00:36.897)

Time: 36351.022 ms (00:36.351)

Time: 36198.890 ms (00:36.199)

*PG HEAD + v48 Patch:*
VACUUM tab1;

Time: 37051.589 ms (00:37.052)

*Time: 33647.459 ms (00:33.647) --median*

Time: 31580.894 ms (00:31.581)

Time: 34442.046 ms (00:34.442)

Time: 31335.960 ms (00:31.336)

Time: 34441.245 ms (00:34.441)

Time: 31159.639 ms (00:31.160)

--

With Regards,
Prabhat Kumar Sahu
EnterpriseDB: http://www.enterprisedb.com