Re: Make autovacuum sort tables in descending order of xid_age

On Sat, Nov 30, 2019 at 10:04:07AM -0800, Mark Dilger wrote:
> On 11/29/19 2:21 PM, David Fetter wrote:
> > On Fri, Nov 29, 2019 at 07:01:39PM +0100, David Fetter wrote:
> > > Folks,
> > >
> > > Per a suggestion Christophe made, please find attached a patch to
> > > $Subject:
> > >
> > > Apart from carefully fudging with pg_resetwal, and short of running in
> > > production for a few weeks, what would be some good ways to test this?
> >
> > Per discussion on IRC with Sehrope Sarkuni, please find attached a
> > patch with one fewer bug, this one in the repalloc() calls.
>
> Hello David,
>
> Here are my initial thoughts.
>
> Although you appear to be tackling the problem of vacuuming tables
> with older Xids first *per database*,

Yes, that's what's come up for me in production, but lately,
production has consisted of a single active DB maxing out hardware. I
can see how in other situations--multi-tenant, especially--it would
make more sense to sort the DBs first.

> have you considered changing the logic in building and sorting the
> database list in get_database_list and rebuild_database_list? I'm
> just curious what your thoughts might be on this subject.

I hadn't, but now that you mention it, it seems like a reasonable
thing to try.

> As far as sorting the list of tables in an array and then copying
> that array into a linked list, I think there is no need. The
> copying of table_ages into table_oids is followed immediately by
>
> foreach(cell, table_oids)
>
> and then table_oids seems not to serve any further purpose. Perhaps
> you can just iterate over table_ages directly and avoid the extra
> copying.

I hadn't looked toward any optimizations in this section, given that
the vacuums in question can take hours or days, but I can see how that
would make the code cleaner, so please find that change attached.

> I have not tested this change, but I may do so later today or perhaps
> on Monday.

Thanks for looking at this!

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate