From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
To: pgsql-admin@lists.postgresql.org
To: pgsql-performance@lists.postgresql.org
Subject: Re: autovacuum is running but pg_stat_all_tables empty
Date: Mon, 19 Nov 2018 19:52:43 +0100
Message-ID: <b669a66ef51af3416f422a180d0a3cb1903ea741.camel@cybertec.at> (raw)
In-Reply-To: <CA+t6e1=uC1WfSpuMYwZwYhn28aQEoiAc4D9DY-VQ9ZJcBm26Uw@mail.gmail.com>
References: <CA+t6e1=uC1WfSpuMYwZwYhn28aQEoiAc4D9DY-VQ9ZJcBm26Uw@mail.gmail.com>
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
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-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, mariel.cherkassky@gmail.com, pgsql-admin@lists.postgresql.org, pgsql-performance@lists.postgresql.org
Subject: Re: autovacuum is running but pg_stat_all_tables empty
In-Reply-To: <b669a66ef51af3416f422a180d0a3cb1903ea741.camel@cybertec.at>
* 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