From: Bossart, Nathan <bossartn@amazon.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: New vacuum option to do only freezing
Date: Mon, 5 Nov 2018 16:00:19 +0000
Message-ID: <42826130-91C1-4586-A086-4C33EE9DCF07@amazon.com> (raw)
In-Reply-To: <CAD21AoCn03NJWWZ3hwxkLZk+u8xRrDk3FXHpwOwHFr=pUpipBQ@mail.gmail.com>
References: <CAD21AoAt5R3DNUZSjOoXDUY=naYPUOuffVsRzuTYMz29yLzQCA@mail.gmail.com>
<CA+TgmoZQ_sLvsmTG251+i_Y-v3AARJ0FAy2dUH4DgL3p8xsgrw@mail.gmail.com>
<CAD21AoCn03NJWWZ3hwxkLZk+u8xRrDk3FXHpwOwHFr=pUpipBQ@mail.gmail.com>
On 11/5/18, 2:07 AM, "Masahiko Sawada" <sawada.mshk@gmail.com> wrote:
> On Fri, Nov 2, 2018 at 1:32 AM Bossart, Nathan <bossartn@amazon.com> wrote:
>> 1. Create a separate FREEZE command instead of adding a new VACUUM
>> option
>>
>> The first line of the VACUUM documentation reads, "VACUUM reclaims
>> storage occupied by dead tuples," which is something that we would
>> explicitly not be doing with FREEZE_ONLY.
>
> No. Actually FREEZE_ONLY option (maybe will be changed its name) could
> reclaim dead tuples by HOT-purning. If a page have HOT-updated chains
> the FREEZE_ONLY prunes them and reclaim disk space occupied.
I see.
>> I think it makes sense to
>> reuse many of the VACUUM code paths to implement this feature, but
>> from a user perspective, it should be separate.
>
> I'm concernced that since the existing users already have recognized
> that vacuuming and freezing are closely related they would get
> confused more if we have a similar purpose feature with different
> name.
That seems reasonable to me. Perhaps decoupling this option from
FREEZE would make it clearer to users and easier to name. This would
allow users to do both VACUUM (WITHOUT_INDEX_CLEANUP) and VACUUM
(FREEZE, WITHOUT_INDEX_CLEANUP).
>> 2. We should reclaim transaction IDs from dead tuples as well
>>
>> Unless we also have a way to freeze XMAX like we do XMIN, I doubt this
>> feature will be useful for the imminent-XID-wraparound use-case. In
>> short, we won't be able to advance relfrozenxid and relminmxid beyond
>> the oldest XMAX value for the relation.
>> IIUC the idea of freezing> XMAX doesn't really exist yet. Either the XMAX is aborted/invalid and
>> can be reset to InvalidTransactionId, or it is committed and the tuple
>> can be removed if it beyond the freezing threshold. So, we probably
>> also want to look into adding a way to freeze XMAX, either by setting
>> it to FrozenTransactionId or by setting the hint bits to
>> (HEAP_XMAX_COMMITTED | HEAP_XMIN_INVALID) as is done for XMIN.
>
> That's a good point. If the oldest xmax is close to the old
> relfrozenxid we will not be able to advance relfrozenxid enough.
> However, since dead tuples are vacuumed by autovacuum periodically I
> think that we can advance relfrozenxid enough in common case. There is
> possible that we eventually need to do vacuum with removing dead
> tuples after done FREEZE_ONLY but it would be a rare case. Thought?
Given that a stated goal of this patch is to help recover from near
wraparound, I think this is very important optimization. It's true
that you might be able to advance relfrozenxid/relminmxid a bit in
some cases, but there are many others where you won't. For example,
if I create a row, delete it, and insert many more rows, my table's
XID age would be stuck at the row deletion. If I was in a situation
where this table was near wraparound and autovacuum wasn't keeping up,
I would run VACUUM (WITHOUT_INDEX_CLEANUP, FREEZE) with the intent of
reclaiming transaction IDs as fast as possible.
>> I'd be curious to see the improvements you get when there are several
>> indexes on the relation. The ability to skip the index scans is
>> likely how this feature will really help speed things up.
>>
>
> I've tested performance of FREEZE option and FREEZE_ONLY option using
> a 3GB table having 3 indexes. Before do vacuum I modified 1 % of data
> on the table.
>
> * FREEZE
> Time: 78677.211 ms (01:18.677)
> Time: 86958.452 ms (01:26.958)
> Time: 78351.190 ms (01:18.351)
>
> * FREEZE_ONLY
> Time: 19913.863 ms (00:19.914)
> Time: 18917.379 ms (00:18.917)
> Time: 20048.541 ms (00:20.049)
Nice.
Nathan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: bossartn@amazon.com, sawada.mshk@gmail.com, robertmhaas@gmail.com
Subject: Re: New vacuum option to do only freezing
In-Reply-To: <42826130-91C1-4586-A086-4C33EE9DCF07@amazon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox