Re: [HACKERS] Block level parallel vacuum

On Thu, Dec 5, 2019 at 1:41 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Dec 2, 2019 at 2:26 PM Masahiko Sawada
> <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
> > It's just an example, I'm not saying your idea is bad. ISTM the idea
> > is good on an assumption that all indexes take the same time or take a
> > long time so I'd also like to consider if this is true even in
> > production and which approaches is better if we don't have such
> > assumption.
>
> I think his idea is good. You're not wrong when you say that there are
> cases where it could work out badly, but I think on the whole it's a
> clear improvement. Generally, the indexes should be of relatively
> similar size because index size is driven by table size; it's true
> that different AMs could result in different-size indexes, but it
> seems like a stretch to suppose that the indexes that don't support
> parallelism are also going to be the little tiny ones that go fast
> anyway, unless we have some evidence that this is really true. I also
> wonder whether we really need the option to disable parallel vacuum in
> the first place. Maybe I'm looking in the right place, but I'm not
> finding anything in the way of comments or documentation explaining
> why some AMs don't support it. It's an important design point, and
> should be documented.
>
> I also think PARALLEL_VACUUM_DISABLE_LEADER_PARTICIPATION seems like a
> waste of space. For parallel queries, there is a trade-off between
> having the leader do work (which can speed up the query) and having it
> remain idle so that it can immediately absorb tuples from workers and
> keep them from having their tuple queues fill up (which can speed up
> the query). But here, at least as I understand it, there's no such
> trade-off. Having the leader fail to participate is just a loser.
> Maybe it's useful to test while debugging the patch, but why should
> the committed code support it?
>
> To respond to another point from a different part of the email chain,
> the reason why LaunchParallelWorkers() does not take an argument for
> the number of workers is because I believed that the caller should
> always know how many workers they're going to want at the time they
> CreateParallelContext(). Increasing it later is not possible, because
> the DSM has already sized based on the count provided. I grant that it
> would be possible to allow the number to be reduced later, but why
> would you want to do that? Why not get the number right when first
> creating the DSM?
>
> Is there any legitimate use case for parallel vacuum in combination
> with vacuum cost delay? As I understand it, any serious vacuuming is
> going to be I/O-bound, so can you really need multiple workers at the
> same time that you are limiting the I/O rate? Perhaps it's possible if
> the I/O limit is so high that a single worker can't hit the limit by
> itself, but multiple workers can, but it seems like a bad idea to
> spawn more workers and then throttle them rather than just starting
> fewer workers.

I agree that there is no point is first to spawn more workers to get
the work done faster and later throttle them. Basically, that will
lose the whole purpose of running it in parallel. OTOH, we should
also consider the cases where there could be some vacuum that may not
hit the I/O limit right? because it may find all the pages in the
shared buffers and they might not need to dirty a lot of pages. So I
think for such cases it is advantageous to run in parallel. The
problem is that there is no way to know in advance whether the total
I/O for the vacuum will hit the I/O limit or not so we can not decide
in advance whether to run it in parallel or not.

In any case, the algorithm suggested in vacuumlazy.c
> around the definition of VacuumSharedCostBalance seems almost the
> opposite of what you probably want. The idea there seems to be that
> you shouldn't make a worker sleep if it hasn't actually got to do
> anything. Apparently the idea is that if you have 3 workers and you
> only have enough I/O rate for 1 worker, you want all 3 workers to run
> at once, so that the I/O is random, rather than having them run 1 at a
> time, so that the I/O is sequential. That seems counterintuitive. It
> could be right if the indexes are in different tablespaces, but if
> they are in the same tablespace it's probably wrong. I guess it could
> still be right if there's just so much I/O that you aren't going to
> run out ever, and the more important consideration is that you don't
> know which index will take longer to vacuum and so want to start them
> all at the same time so that you don't accidentally start the slow one
> last, but that sounds like a stretch. I think this whole area needs
> more thought. I feel like we're trying to jam a go-slower feature and
> a go-faster feature into the same box.
>
> + * vacuum and for performing index cleanup. Note that all parallel workers
> + * live during either index vacuuming or index cleanup but the leader process
> + * neither exits from the parallel mode nor destroys the parallel context.
> + * For updating the index statistics, since any updates are not allowed during
> + * parallel mode we update the index statistics after exited from the parallel
>
> The first of these sentences ("Note that all...") is not very clear to
> me, and seems like it may amount to a statement that the leader
> doesn't try to destroy the parallel context too early, but since I
> don't understand it, maybe that's not what it is saying. The second
> sentence needs exited -> exiting, and maybe some more work on the
> grammar, too.
>

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com