Date: Wed, 30 Mar 2022 21:04:07 -0700
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
	Robert Haas <robertmhaas@gmail.com>,
	Thomas Munro <thomas.munro@gmail.com>,
	Masahiko Sawada <sawada.mshk@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Removing more vacuumlazy.c special cases, relfrozenxid
 optimizations
Message-ID: <20220331040407.ole7orep6sj4iore@alap3.anarazel.de>
References: 
 <CA+TgmoYvqfhtfobZHJb-LhNy6QCrD9sqE9gtRgntFJFkmo2_XQ@mail.gmail.com>
 <CAH2-Wz=HTfWpmeXeCP-Q9CuWGbHeZW-j75tgtdL7B7e2O2TnBA@mail.gmail.com>
 <CAH2-Wz=Q2NQprVdf8wojerm-WYwy_eKmLB64mwJ25Prjf=TdvQ@mail.gmail.com>
 <20220330061041.GE28503@telsasoft.com>
 <CAH2-WzmwM81qMxjYdm4-Z51PKwsgXXAr21bfBRncXCR7VB8W1g@mail.gmail.com>
 <CAH2-Wzn_LLtTk-jGranhLcciSt_70pFKJNngZOf8Gt_MNgTmnA@mail.gmail.com>
 <20220331020018.qht46hbua4gztmdf@alap3.anarazel.de>
 <CAH2-WzmRZEzeGvLv8yDW0AbFmSvJjTziORqjVUrf74mL4GL0Ww@mail.gmail.com>
 <20220331032844.j7wu5ijjpx3qay5c@alap3.anarazel.de>
 <CAH2-WzkvYTo4rbUi_izHOvcHNw78Ur9xLPGvj1PmDgt-csc8QQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAH2-WzkvYTo4rbUi_izHOvcHNw78Ur9xLPGvj1PmDgt-csc8QQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/20220331040407.ole7orep6sj4iore%40alap3.anarazel.de>
Precedence: bulk

Hi,

On 2022-03-30 20:35:25 -0700, Peter Geoghegan wrote:
> On Wed, Mar 30, 2022 at 8:28 PM Andres Freund <andres@anarazel.de> wrote:
> > I triggered twice now, but it took a while longer the second time.
>
> Great.
>
> I wonder if you can get an RR recording...

Started it, but looks like it's too slow.

(gdb) p MyProcPid
$1 = 2172500

(gdb) p vacrel->NewRelfrozenXid
$3 = 717
(gdb) p vacrel->relfrozenxid
$4 = 717
(gdb) p OldestXmin
$5 = 5112
(gdb) p aggressive
$6 = false

There was another autovacuum of pg_database 10s before:

2022-03-30 20:35:17.622 PDT [2165344][autovacuum worker][5/3:0][] LOG:  automatic vacuum of table "postgres.pg_catalog.pg_database": index scans: 1
        pages: 0 removed, 3 remain, 3 scanned (100.00% of total)
        tuples: 61 removed, 4 remain, 1 are dead but not yet removable
        removable cutoff: 1921, older by 3 xids when operation ended
        new relfrozenxid: 717, which is 3 xids ahead of previous value
        index scan needed: 3 pages from table (100.00% of total) had 599 dead item identifiers removed
        index "pg_database_datname_index": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        index "pg_database_oid_index": pages: 4 in total, 0 newly deleted, 0 currently deleted, 0 reusable
        I/O timings: read: 0.029 ms, write: 0.034 ms
        avg read rate: 134.120 MB/s, avg write rate: 89.413 MB/s
        buffer usage: 35 hits, 12 misses, 8 dirtied
        WAL usage: 12 records, 5 full page images, 27218 bytes
        system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s

The dying backend:
2022-03-30 20:35:27.668 PDT [2172500][autovacuum worker][7/0:0][] DEBUG:  autovacuum: processing database "contrib_regression_hstore"
...
2022-03-30 20:35:27.690 PDT [2172500][autovacuum worker][7/674:0][] CONTEXT:  while cleaning up index "pg_database_oid_index" of relation "pg_catalog.pg_database"


Greetings,

Andres Freund