Обсуждение: autovacuum is running but pg_stat_all_tables empty
Hi,
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.
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
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