Re: BUG #13837: Files in pg_committs not deleted

From: Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
To: Alain Laporte <alain(dot)laporte12345(at)gmail(dot)com>
Cc: PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #13837: Files in pg_committs not deleted
Date: 2017-06-14 04:37:40
Message-ID: CAEepm=2D26JmKX4jf6pAkKom_wq9SvNsj4E38-5Pviktjm7zDQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

On Tue, Dec 29, 2015 at 4:24 PM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> On Tue, Dec 29, 2015 at 6:32 AM, <alain(dot)laporte12345(at)gmail(dot)com> wrote:
>> I use PostgreSQL 9.4.5 and I have activated the parameter
>> track_commit_timestamp to use BDR (0.9.3) and to be able to replicate two
>> databases. A directory pg_committs was created (this directory is named
>> pg_commit_ts in PostgreSQL 9.5 =>
>> http://www.postgresql.org/docs/9.5/static/storage-file-layout.html) but,
>> his content is not periodically deleted (all files stays after 2 weeks of
>> running).
>
> In stock PostgreSQL, pg_commit_ts is truncated when the cluster-wide
> oldest xid is advanced by autovacuum (or manual vacuum freeze of all
> databases including templates). With the default
> autovacuum_freeze_max_age setting, that'll take more than a couple of
> weeks unless you're doing somewhere around 15 million transactions per
> day. (I guess that BDR-patched 9.4 is the same, but I don't know.)
>
> The documentation describes how autovacuum_freeze_max_age affects the
> space occupied by pg_clog[1], but the same thing applies to
> pg_commit_ts. pg_clog uses 2 bits per xid, and it looks like
> pg_commit_ts uses 10 bytes per xid, so the default
> autovacuum_freeze_max_age gives you ~50MB of pg_clog and ~2GB of
> pg_commit_ts.
>
> I wonder if doc/src/sgml/maintenance.sgml should be updated to reflect
> this, maybe with something along the lines of the attached patch.
>
> (Isn't it a bit strange that we say that the *sole* disadvantage of
> setting autovacuum_freeze_max_age to a higher number is disk space
> usage? Freezing later also has consequences for whether you'll
> actually be able to complete the freeze before wraparound, especially
> if you set it to 2 billion as recommended.)

Rebased, will add to commitfest.

--
Thomas Munro
http://www.enterprisedb.com

Attachment Content-Type Size
pg-commit-ts-size-note-v2.patch application/octet-stream 2.3 KB

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Sandeep Thakkar 2017-06-14 05:21:19 Re: Error-Report Postgresql
Previous Message Michael Paquier 2017-06-14 01:03:11 Re: Re: BUG #14680: startup process on standby encounter a deadlock of TwoPhaseStateLock when redo 2PC xlog