Subject: Re: Make autovacuum sort tables in descending order of xid_age
To: David Fetter <david@fetter.org>,
 PostgreSQL Development <pgsql-hackers@postgresql.org>
Cc: Christophe Pettus <xof@thebuild.com>
References: <20191129180138.GD8731@fetter.org>
 <20191129222152.GE8731@fetter.org>
From: Mark Dilger <hornschnorter@gmail.com>
Message-ID: <2488acea-328b-1328-d437-8c156bc8f7a4@gmail.com>
Date: Sat, 30 Nov 2019 10:04:07 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.2.2
MIME-Version: 1.0
In-Reply-To: <20191129222152.GE8731@fetter.org>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


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*, 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.

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 have not tested this change, but I may do so later today or perhaps
on Monday.

-- 
Mark Dilger