On Tue, Mar 9, 2021 at 2:22 PM Peter Geoghegan <p...@bowt.ie> wrote: > > On Tue, Mar 2, 2021 at 8:49 PM Masahiko Sawada <sawada.m...@gmail.com> wrote: > > On Tue, Mar 2, 2021 at 2:34 PM Peter Geoghegan <p...@bowt.ie> wrote: > > > lazy_vacuum_table_and_indexes() should probably not skip index > > > vacuuming when we're close to exceeding the space allocated for the > > > LVDeadTuples array. Maybe we should not skip when > > > vacrelstats->dead_tuples->num_tuples is greater than 50% of > > > dead_tuples->max_tuples? Of course, this would only need to be > > > considered when lazy_vacuum_table_and_indexes() is only called once > > > for the entire VACUUM operation (otherwise we have far too little > > > maintenance_work_mem/dead_tuples->max_tuples anyway). > > > > Doesn't it actually mean we consider how many dead *tuples* we > > collected during a vacuum? I’m not sure how important the fact we’re > > close to exceeding the maintenance_work_mem space. Suppose > > maintenance_work_mem is 64MB, we will not skip both index vacuum and > > heap vacuum if the number of dead tuples exceeds 5592404 (we can > > collect 11184809 tuples with 64MB memory). But those tuples could be > > concentrated in a small number of blocks, for example in a very large > > table case. It seems to contradict the current strategy that we want > > to skip vacuum if relatively few blocks are modified. No? > > There are competing considerations. I think that we need to be > sensitive to accumulating "debt" here. The cost of index vacuuming > grows in a non-linear fashion as the index grows (or as > maintenance_work_mem is lowered). This is the kind of thing that we > should try to avoid, I think. I suspect that cases where we can skip > index vacuuming and heap vacuuming are likely to involve very few dead > tuples in most cases anyway. > > We should not be sensitive to the absolute number of dead tuples when > it doesn't matter (say because they're concentrated in relatively few > heap pages). But when we overrun the maintenance_work_mem space, then > the situation changes; the number of dead tuples clearly matters just > because we run out of space for the TID array. The heap page level > skew is not really important once that happens. > > That said, maybe there is a better algorithm. 50% was a pretty arbitrary > number.
I agreed that when we're close to overrunning the maintnenance_work_mem space, the situation changes. If we skip it in even that case, the next vacuum will be likely to use up maintenance_work_mem, leading to a second index scan. Which is bad. If this threshold is aimed to avoid a second index scan due to overrunning the maintenance_work_mem, using a ratio of maintenance_work_mem would be a good idea. On the other hand, if it's to avoid accumulating debt affecting the cost of index vacuuming, using a ratio of the total heap tuples seems better. The situation where we need to deal with here is a very large table that has a lot of dead tuples but those fit in fewer heap pages (less than 1% of all heap blocks). In this case, it's likely that the number of dead tuples also is relatively small compared to the total heap tuples, as you mentioned. If dead tuples fitted in fewer pages but accounted for most of all heap tuples in the heap, it would be a more serious situation, there would definitely already be other problems. So considering those conditions, I agreed to use a ratio of maintenance_work_mem as a threshold. Maybe we can increase the constant to 70, 80, or so. > > Have you thought more about how the index vacuuming skipping can be > configured by users? Maybe a new storage param, that works like the > current SKIP_VACUUM_PAGES_RATIO constant? Since it’s unclear to me yet that adding a new storage parameter or GUC parameter for this feature would be useful even for future improvements in this area, I haven't thought yet about having users control skipping index vacuuming. I’m okay with a constant value for the threshold for now. Regards, -- Masahiko Sawada EDB: https://www.enterprisedb.com/
