Message-ID: <b669a66ef51af3416f422a180d0a3cb1903ea741.camel@cybertec.at>
Subject: Re: autovacuum is running but pg_stat_all_tables empty
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>,
	pgsql-admin@lists.postgresql.org, pgsql-performance@lists.postgresql.org
Date: Mon, 19 Nov 2018 19:52:43 +0100
In-Reply-To: 
 <CA+t6e1=uC1WfSpuMYwZwYhn28aQEoiAc4D9DY-VQ9ZJcBm26Uw@mail.gmail.com>
References: 
	<CA+t6e1=uC1WfSpuMYwZwYhn28aQEoiAc4D9DY-VQ9ZJcBm26Uw@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

Mariel Cherkassky wrote:
> I'm trying to understand something that is weird on one of my environments.
> When I query pg_stat_all_tables I see that most of the tables dont have any
> value in the last_autovacuum/analyze column. In addition the columns
> autovacuum_count/analyze_count is set to 0. However, when checking the logs,
> I see that on some of those tables autovacuum run. I think that there is
> something wrong with the database statistics collector. In addition, the
> column n_dead_tup and n_live_tup are set and in some of the cases n_dead_tup
> is more then 20% of the table tuples. In addition, all tables have default
> vacuum threshold.
> 
> Any idea what else I can check ?
> The problem isnt only that dead tuples arent deleted (I dont have long running
> transaction that might cause it) but the fact that the statistics arent accurate/wrong.

You can use the "pgstattuple" extension to check that table for the actual
dead tuple percentage to see if the statistics are accurate or not.

To see the statistic collector's UDP socket, run

    netstat -a|grep udp|grep ESTABLISHED

Check if it is there.  If it is on IPv6, make sure that IPv6 is up, otherwise
that would explain why you have no accurate statistics.

Are there any log messages about statistics collection?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com