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
