Re: [HACKERS] Block level parallel vacuum

On Sat, 30 Nov 2019 at 19:18, Sergei Kornilov <sk(at)zsrv(dot)org> wrote:

> Hello
>
> Its possible to change order of index processing by parallel leader? In
> v35 patchset I see following order:
> - start parallel processes
> - leader and parallel workers processed index lixt and possible skip some
> entries
> - after that parallel leader recheck index list and process the skipped
> indexes
> - WaitForParallelWorkersToFinish
>
> I think it would be better to:
> - start parallel processes
> - parallel leader goes through index list and process only indexes which
> are skip_parallel_index_vacuum = true
> - parallel workers processes indexes with skip_parallel_index_vacuum =
> false
> - parallel leader start participate with remainings parallel-safe index
> processing
> - WaitForParallelWorkersToFinish
>
> This would be less running time and better load balance across leader and
> workers in case of few non-parallel and few parallel indexes.
> (if this is expected and required by some reason, we need a comment in
> code)
>
> Also few notes to vacuumdb:
> Seems we need version check at least in vacuum_one_database and
> prepare_vacuum_command. Similar to SKIP_LOCKED or DISABLE_PAGE_SKIPPING
> features.
> discussion question: difference between --parallel and --jobs parameters
> will be confusing? We need more description for this options
>

While doing testing with different server configuration settings, I am
getting error (ERROR: no unpinned buffers available) in parallel vacuum
but normal vacuum is working fine.

*Test Setup*:
max_worker_processes = 40

autovacuum = off

shared_buffers = 128kB

max_parallel_workers = 40

max_parallel_maintenance_workers = 40

vacuum_cost_limit = 2000

vacuum_cost_delay = 10

*Table description: *table have 16 indexes(14 btree, 1 hash, 1 BRIN ) and
total 10,00,000 tuples and I am deleting all the tuples, then firing vacuum
command.
Run attached .sql file (test_16_indexes.sql)
$ ./psql postgres
postgres=# \i test_16_indexes.sql

Re-start the server and do vacuum.
Case 1) normal vacuum:
postgres=# vacuum test ;
VACUUM
Time: 115174.470 ms (01:55.174)

Case 2) parallel vacuum using 10 parallel workers:
postgres=# vacuum (parallel 10)test ;
ERROR: no unpinned buffers available
CONTEXT: parallel worker
postgres=#

This error is coming due to 128kB shared buffer. I think, I launched 10
parallel workers and all are working paralleling so due to less shared
buffer, I am getting this error.

Is this expected behavior with small shared buffer size or we should try to
come with a solution for this. Please let me know your thoughts.

Thanks and Regards
Mahendra Thalor
EnterpriseDB: http://www.enterprisedb.com