Date: Mon, 23 Mar 2020 14:59:47 -0700
From: Andres Freund <andres@anarazel.de>
To: Justin King <kingpin867@gmail.com>
Cc: Julien Rouhaud <rjuju123@gmail.com>, pgsql-general@lists.postgresql.org,
	michael@paquier.xyz, kgrittn@gmail.com
Subject: Re: PG12 autovac issues
Message-ID: <20200323215947.axm5oalgkkrnd3r2@alap3.anarazel.de>
References: <20200319223520.2mnjt6l3yybgnd77@alap3.anarazel.de>
 <CAE39h22XLA7A=05RezvcTmEp=A369vRScToP5iLdTwArKiAuoA@mail.gmail.com>
 <20200319235626.wz4b57hbhuuy4w7j@alap3.anarazel.de>
 <CAE39h23xR9F8qbo5vj9+KL1nFnnTaYJrFde0BtWczQy_+De39Q@mail.gmail.com>
 <20200320190317.gdxwzexdfutymqs6@alap3.anarazel.de>
 <20200323152247.GB52612@nol>
 <20200323162303.s7ay5hjdvimtkz6u@alap3.anarazel.de>
 <20200323194725.GC52612@nol>
 <20200323200051.qzyqvnips7o3ai4g@alap3.anarazel.de>
 <CAE39h22dt1ya5os3MAsAr6JjDFdUtbjL0q13UvJARsi40d7WnQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAE39h22dt1ya5os3MAsAr6JjDFdUtbjL0q13UvJARsi40d7WnQ@mail.gmail.com>
Precedence: bulk

Hi,

On 2020-03-23 16:31:21 -0500, Justin King wrote:
> This is occurring in our environment right now (started about 30 min
> ago).  Here 's the latest logs (grepped by vacuum):
> 
> Mar 23 20:54:16 cowtn postgres[15569]: [12-1] 2020-03-23 20:54:16.542
> GMT [15569] LOG:  automatic vacuum of table "feedi.production.tita":
> index scans: 1
> Mar 23 20:54:27 cowtn postgres[15654]: [8-1] 2020-03-23 20:54:27.964
> GMT [15654] LOG:  automatic vacuum of table
> "feedi.production.distributed_virtual_schedule": index scans: 1

Hm, unfortunately you've cut off the details in the subsequent
lines. There's a few newlines in the output. Any chance you could
re-post with those included?


> > > > I wonder if what might be happening is that we're somehow missed/failed
> > > > to update relfrozenxid and/or datfrozenxid. If you manually vacuum some
> > > > table in the oldest database, but that is *NOT* the oldest table itself,
> > > > does the problem "resolve" itself?
> 
> postgres=# SELECT datname
>     , age(datfrozenxid)
>     , current_setting('autovacuum_freeze_max_age')
> FROM pg_database;
>   datname  |    age    | current_setting
> -----------+-----------+-----------------
>  postgres  | 202375735 | 200000000
>  template1 | 202345459 | 200000000
>  template0 | 132459914 | 200000000
>  feedi     | 132459914 | 200000000
> (4 rows)

Can you show the oldest tables in 'feedi'? Or, hm, actually, could you
just post the result of all the queries from the "What is:" section in
https://postgr.es/m/20200323162303.s7ay5hjdvimtkz6u%40alap3.anarazel.de


> Since this is occurring right now, what else would be useful to
> capture?  You'd asked about a GDB -- do you want that of the main
> process or the autovac worker?

Unless you can give me gdb access directly, I don't yet have enough data
to suggest what exactly we would want to analyze with gdb in your case.


It'd be helpful if you could change log_min_messages to DEBUG1 and
reload the configuration (not restart!).

Greetings,

Andres Freund