Re: [HACKERS] Block level parallel vacuum

On Sat, 30 Nov 2019 at 22:11, Mahendra Singh <mahi6run(at)gmail(dot)com> wrote:
>
> 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.
>

Thank you for testing!

> 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.

I think it's normal behavior when the shared buffer is not enough.
Since the total 10 processes were processing different pages at the
same time and you set a small value to shared_buffers the shared
buffer gets full easily. And you got the proper error. So I think in
this case we should consider either to increase the shared buffer size
or to decrease the parallel degree. I guess you can get this error
even when you vacuum 10 different tables concurrently instead.

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services