I think that something is wrong because some tables has a value in
last_autoanalyze and some dont while most of them were analyzed. For
example :
relname | n_live_tup | n_dead_tup |
last_autovacuum | last_autoanalyze
-----------------------------+------------+------------+-------------------------------+-------------------------------
tbl1 | 975 | 37 |
| 2018-11-26 16:15:51.557115+02
tbl2 | 4283798 | 739345 | 2018-11-26
17:43:46.663709+02 | 2018-11-26 17:44:38.234908+02
tbl3 | 3726015 | 596362 | 2018-11-26
17:37:33.726438+02 | 2018-11-26 17:48:30.126623+02
pg_language | 0 | 0 |
|
pg_toast_19018 | 0 | 0 |
|
pg_toast_13115 | 0 | 0 |
|
tbl4 | 0 | 0 |
|
log data :
[root@pg_log]# cat postgresql-Sun.log | grep automatic | awk {'print $6
$7,$10'} | grep db1 | sort -n | uniq -c
1 automaticanalyze "db1.public.attributes"
4 automaticanalyze "db1.public.tbl3"
3 automaticanalyze "db1.public.tbl3"
11 automaticanalyze "db1.public.tbl2"
1 automaticanalyze "db1.public.tbl4"
5 automaticanalyze "db1.public.tbl1"
1 automaticvacuum "db1.pg_catalog.pg_statistic":
5 automaticvacuum "db1.pg_toast.pg_toast_19239":
1 automaticvacuum "db1.pg_toast.pg_toast_19420":
2 automaticvacuum "db1.pg_toast.pg_toast_2619":
3 automaticvacuum "db1.public.tbl3":
9 automaticvacuum "db1.public.tbl2":
1 automaticvacuum "db1.public.tbl4":
I just changed the name of the tables but all other values are accurate.
Firewall rules should block all stats not just some of them right ?
Something is weird...
בתאריך יום ב׳, 26 בנוב׳ 2018 ב-17:25 מאת Tom Lane <[email protected]
>:
> Mariel Cherkassky <[email protected]> writes:
> > I checked pg_stat_all_tables and I see that the last_autovacuum is empty
> > for all the tables but in the database`s log I see that autovacuum was
> > running and deleted records(not all of them but still deleted...).
> > Can someone explain why the pg_stat_all_tables doesnt show the real data
> ?
>
> Hm, are you sure the stats collector is working at all? Are other fields
> in the pg_stat data updating?
>
> (If not, you may have a problem with firewall rules blocking traffic
> on the stats loopback port.)
>
> regards, tom lane
>
