Re: Berserk Autovacuum (let's save next Mandrill)
От | Tomas Vondra |
---|---|
Тема | Re: Berserk Autovacuum (let's save next Mandrill) |
Дата | |
Msg-id | 20190413195048.nfuysawke5xytcdg@development обсуждение исходный текст |
Ответ на | Re: Berserk Autovacuum (let's save next Mandrill) (Chris Travers <chris.travers@adjust.com>) |
Ответы |
Re: Berserk Autovacuum (let's save next Mandrill)
|
Список | pgsql-hackers |
On Thu, Apr 11, 2019 at 11:25:29AM +0200, Chris Travers wrote: > On Wed, Apr 10, 2019 at 5:21 PM Andres Freund <andres@anarazel.de> wrote: > > Hi, > > On April 10, 2019 8:13:06 AM PDT, Alvaro Herrera > <alvherre@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
В списке pgsql-hackers по дате отправления: