Re: TODO : Allow parallel cores to be used by vacuumdb [ WIP ]

On Thu, Oct 16, 2014 at 1:56 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 16 October 2014 06:05, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> > On Thu, Oct 16, 2014 at 8:08 AM, Simon Riggs <simon(at)2ndquadrant(dot)com>
wrote:
> >>
> >> This patch seems to allow me to run multiple VACUUMs at once. But I
> >> can already do this, with autovacuum.
> >>
> >> Is there anything this patch can do that cannot be already done with
> >> autovacuum?
> >
> > The difference lies in the fact that vacuumdb (or VACUUM) gives
> > the option to user to control the vacuum activity for cases when
> > autovacuum doesn't suffice the need, one of the example is to perform
> > vacuum via vacuumdb after pg_upgrade or some other maintenance
> > activity (as mentioned by Jeff upthread). So I think in all such cases
> > having parallel option can give benefit in terms of performance which
> > is already shown by Dilip upthread by running some tests (with and
> > without patch).
>
> Why do we need 2 ways to do the same thing?

Isn't the multiple ways to do the same already exists like via
vacuumdb | Vaccum and autovaccum?

> Why not ask autovacuum to do this for you?
>
> Just send a message to autovacuum to request an immediate action. Let
> it manage the children and the tasks.
>
> SELECT pg_autovacuum_immediate(nworkers = N, list_of_tables);
>
> Request would allocate an additional N workers and immediately begin
> vacuuming the stated tables.

I think doing anything on the server side can have higher complexity like:
a. Does this function return immediately after sending request to
autovacuum, if yes then the behaviour of this new functionality
will be different as compare to vacuumdb which user might not
expect?
b. We need to have some way to handle errors that can occur in
autovacuum (may be need to find a way to pass back to user),
vacuumdb or Vacuum can report error to user.
c. How does nworkers input relates to autovacuum_max_workers
which is needed at start for shared memory initialization and in calc
of maxbackends.
d. How to handle database wide vacuum which is possible via vacuumdb
e. What is the best UI (a command like above or via config parameters)?

I think we can find a way for above and may be if any other similar things
needs to be taken care, but still I think it is better that we have this
feature
via vacuumdb rather than adding complexity in server code. Also the
current algorithm used in patch is discussed and agreed upon in this
thread and if now we want to go via some other method (auto vacuum),
it might take much more time to build consensus on all the points.

> vacuumdb can still issue the request, but the guts of this are done by
> the server, not a heavily modified client.
>
> If we are going to heavily modify a client then it needs to be able to
> run more than just one thing. Parallel psql would be nice. pg_batch?

Could you be more specific in this point, I am not able to see how
vacuumdb utility has anything to do with parallel psql?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com