On Thu, Apr 11, 2019 at 11:25:29AM +0200, Chris Travers wrote:
On Wed, Apr 10, 2019 at 5:21 PM Andres Freund <and...@anarazel.de> wrote:
Hi,
On April 10, 2019 8:13:06 AM PDT, Alvaro Herrera
<alvhe...@2ndquadrant.com> wrote:
>On 2019-Mar-31, Darafei "Komяpa" Praliaskouski wrote:
>
>> Alternative point of "if your database is super large and actively
>written,
>> you may want to set autovacuum_freeze_max_age to even smaller values
>so
>> that autovacuum load is more evenly spread over time" may be needed.
>
>I don't think it's helpful to force emergency vacuuming more
>frequently;
>quite the contrary, it's likely to cause even more issues. We should
>tweak autovacuum to perform freezing more preemtively instead.
I still think the fundamental issue with making vacuum less painful is
that the all indexes have to be read entirely. Even if there's not much
work (say millions of rows frozen, hundreds removed). Without that issue
we could vacuum much more frequently. And do it properly in insert only
workloads.
So I see a couple of issues here and wondering what the best approach is.
The first is to just skip lazy_cleanup_index if no rows were removed. Is
this the approach you have in mind? Or is that insufficient?
I don't think that's what Andres had in mind, as he explicitly mentioned
removed rows. So just skipping lazy_cleanup_index when there were no
deleted would not help in that case.
What I think we could do is simply leave the tuple pointers in the table
(and indexes) when there are only very few of them, and only do the
expensive table/index cleanup once there's anough of them.
The second approach would be to replace the whole idea of this patch with
a lazy freeze worker which would basically periodically do a vacuum freeze
on relations matching certain criteria. This could have a lower max
workers than autovacuum and therefore less of a threat in terms of total
IO usage.
Thoughts?
Not sure. I find it rather difficult to manage more and more different
types of cleanup workers.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
