From: | Mahendra Singh <mahi6run(at)gmail(dot)com> |
---|---|
To: | Sergei Kornilov <sk(at)zsrv(dot)org> |
Cc: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Masahiko Sawada <masahiko(dot)sawada(at)2ndquadrant(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, Robert Haas <robertmhaas(at)gmail(dot)com>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Amit Langote <langote_amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, David Steele <david(at)pgmasters(dot)net>, Claudio Freire <klaussfreire(at)gmail(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] Block level parallel vacuum |
Date: | 2019-11-30 21:10:47 |
Message-ID: | CAKYtNAoyt7DE8LhWjfb1H0E_pNmrisxoV-wy1QrP=N8kUV2kQw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
test_16_indexes.sql | application/octet-stream | 1.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2019-11-30 22:23:15 | Re: Make autovacuum sort tables in descending order of xid_age |
Previous Message | Fabien COELHO | 2019-11-30 19:33:25 | Re: pgbench -i progress output on terminal |