From: | Vik Fearing <vik(dot)fearing(at)2ndquadrant(dot)com> |
---|---|
To: | Stephen Frost <sfrost(at)snowman(dot)net> |
Cc: | Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: new autovacuum criterion for visible pages |
Date: | 2017-02-01 15:18:03 |
Message-ID: | CAJguA1ScYtTUjB6gX7YwEjErg1B8fO4zen3s5EpFi=Htq5C+Nw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Sun, Jan 22, 2017 at 4:45 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Amit,
>
> * Amit Kapila (amit(dot)kapila16(at)gmail(dot)com) wrote:
> > On Sun, Jan 22, 2017 at 3:27 AM, Stephen Frost <sfrost(at)snowman(dot)net>
> wrote:
> > > * Simon Riggs (simon(at)2ndquadrant(dot)com) wrote:
> > >> On 12 August 2016 at 01:01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > >> > Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> > >> >> In short, autovacuum will need to scan by itself the VM of each
> > >> >> relation and decide based on that.
> > >> >
> > >> > That seems like a worthwhile approach to pursue. The VM is
> supposed to be
> > >> > small, and if you're worried it isn't, you could sample a few pages
> of it.
> > >> > I do not think any of the ideas proposed so far for tracking the
> > >> > visibility percentage on-the-fly are very tenable.
> > >>
> > >> Sounds good, but we can't scan the VM for every table, every minute.
> > >> We need to record something that will tell us how many VM bits have
> > >> been cleared, which will then allow autovac to do a simple SELECT to
> > >> decide what needs vacuuming.
> > >>
> > >> Vik's proposal to keep track of the rows inserted seems like the best
> > >> approach to this issue.
> > >
> > > I tend to agree with Simon on this. I'm also worried that an approach
> > > which was based off of a metric like "% of table not all-visible" might
> > > result in VACUUM running over and over on a table because it isn't able
> > > to actually make any progress towards improving that percentage. We'd
> > > have to have some kind of "cool-off" period or something.
> > >
> > > Tracking INSERTs and then kicking off a VACUUM based on them seems to
> > > address that in a natural way and also seems like something that users
> > > would generally understand as it's very similar to what we do for
> > > UPDATEs and DELETEs.
> > >
> > > Tracking the INSERTs as a reason to VACUUM is also very natural when
> you
> > > consider the need to update BRIN indexes.
> >
> > Another possible advantage of tracking INSERTs is for hash indexes
> > where after split we need to remove tuples from buckets that underwent
> > split recently.
>
> That's a good point also, and for clearing GIN pending lists too, now
> that I think about it.
>
As much as I want my patch to go in, this is not an argument for it. The
GIN pending lists will be handled by autoanalyze.
> We really need to get this fixed for PG10.
>
I'm not sure what more I'm supposed to be doing, if anything.
--
Vik Fearing +33 6 46 75 15
36http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et
Support
From | Date | Subject | |
---|---|---|---|
Next Message | Fabien COELHO | 2017-02-01 15:33:28 | Re: \if, \elseif, \else, \endif (was Re: PSQL commands: \quit_if, \quit_unless) |
Previous Message | Anastasia Lubennikova | 2017-02-01 14:41:34 | Cast jsonb to numeric, int, float, bool |