pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum run but last_autovacuum is empty
4+ messages / 2 participants
[nested] [flat]
* autovacuum run but last_autovacuum is empty
@ 2018-11-26 15:12 Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Mariel Cherkassky @ 2018-11-26 15:12 UTC (permalink / raw)
To: pgsql-performance
Hi,
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 ?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: autovacuum run but last_autovacuum is empty
@ 2018-11-26 15:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2018-11-26 15:24 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance
Mariel Cherkassky <mariel.cherkassky@gmail.com> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: autovacuum run but last_autovacuum is empty
@ 2018-11-26 15:51 Mariel Cherkassky <mariel.cherkassky@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Mariel Cherkassky @ 2018-11-26 15:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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 <tgl@sss.pgh.pa.us
>:
> Mariel Cherkassky <mariel.cherkassky@gmail.com> 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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: autovacuum run but last_autovacuum is empty
@ 2018-11-26 16:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mariel Cherkassky <mariel.cherkassky@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2018-11-26 16:10 UTC (permalink / raw)
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>; +Cc: pgsql-performance
Mariel Cherkassky <mariel.cherkassky@gmail.com> writes:
> I think that something is wrong because some tables has a value in
> last_autoanalyze and some dont while most of them were analyzed.
It's possible that autovacuum abandoned some vacuum attempts without
getting to the end because of lock conflicts.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-11-26 16:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-11-26 15:12 autovacuum run but last_autovacuum is empty Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-11-26 15:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-11-26 15:51 ` Mariel Cherkassky <mariel.cherkassky@gmail.com>
2018-11-26 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
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