pg_stat: last vacuum and analyze times are not being updated - v8.3.5

This is a multi-part message in MIME format.

------_=_NextPart_001_01CACA78.7F51811F
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

PostgreSQL Version: 8.3.5

OS Version: FreeBSD 7.0 i386

Issue: the last_vacuum column is not
being updated following a manual vacuum against a table or database.
This was highlighted recently when I configured the check_postgresql.pl
script to be used from NAGIOS.

Background:

Drilling down through the pg_stat_all_tables view, the underlying
functions

* pg_stat_get_last_vacuum_time(),

* pg_stat_get_last_autovacuum_time(),

* pg_stat_get_last_analyze_time() and

* pg_stat_get_last_autoanalyze_time()

are not returning anything even though I have run manual and auto
vacuums against these tables recently, along with ANALYZE.

Whether it makes a difference, I can confirm that track_counts and
track_activities are both on in postgresql.conf. The stats collection
service is also running on the server - I've verified this by looking at
the processes on the server.

This is causing issues with both our monitoring and also our confidence
that the autovacuum service is running as it should. We have a standby
server with identical configuration (as far as I can see) and this is
correctly keeping the stats up to date. The only difference is postgres
has been running well over a year on the server with the issue.

Many thanks in advance,

Steve Jones

------_=_NextPart_001_01CACA78.7F51811F
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40"><head><meta =
http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii"><meta name=3DGenerator content=3D"Microsoft Word 14 =
(filtered medium)"><style></style></head><body lang=3DEN-GB link=3Dblue =
vlink=3Dpurple><div class=3DWordSection1>PostgreSQL =
Version: 8.3.5<o:p></o:p>OS Version: =
 &=
nbsp; FreeBSD 7.0 i386<o:p></o:p><o:p> </o:p>Issue: =
 &=
nbsp; &n=
bsp; the =
last_vacuum column is not being updated following a manual vacuum =
against a table or database. This was highlighted recently when I =
configured the check_postgresql.pl script to be used from NAGIOS. =
<o:p></o:p><o:p> </o:p>Background:<o:p></o:p>Drilling down through the pg_stat_all_tables view, the =
underlying functions <o:p></o:p><o:p> </o:p><![if =
!supportLists]>· =
<![endif]>pg_stat_get_last_vacuum_time(), =
<o:p></o:p><![if =
!supportLists]>· =
<![endif]>pg_stat_get_last_autovacuum_time(), =
<o:p></o:p><![if =
!supportLists]>· =
<![endif]>pg_stat_get_last_analyze_time() =
and<o:p></o:p><![if =
!supportLists]>· =
<![endif]>pg_stat_get_last_autoanalyze_time()<o:p></=
o:p><o:p> </o:p>are not returning anything even though I have run =
manual and auto vacuums against these tables recently, along with =
ANALYZE.<o:p></o:p><o:p> </o:p>Whether it makes a difference, I can confirm that =
track_counts and track_activities are both on in postgresql.conf. =
 The stats collection service is also running on the server =
– I’ve verified this by looking at the processes on the =
server.<o:p></o:p><o:p> </o:p>This is causing issues with both our monitoring and =
also our confidence that the autovacuum service is running as it =
should. We have a standby server with identical configuration (as =
far as I can see) and this is correctly keeping the stats up to =
date. The only difference is postgres has been running well over a =
year on the server with the issue.<o:p></o:p><o:p> </o:p>Many thanks =
in advance,<o:p></o:p><o:p> </o:p><div>Steve Jones<o:p></o:p></div><o:p> </o:p></div></body></html>
------_=_NextPart_001_01CACA78.7F51811F--

Steve Jones [ Di, 23 März 2010 12:03 ] [ ID #2036067 ]

Re: pg_stat: last vacuum and analyze times are not being updated - v8.3.5

"Steve Jones" <Steve.Jones [at] codeweavers.net> writes:
> Issue: the last_vacuum column is not
> being updated following a manual vacuum against a table or database.

It works for me in 8.3 ... as long as there already is a stats entry for
the table in question. You may be running into the pre-9.0 behavior
that the vacuum time update gets thrown away if there is no pre-existing
stats entry. If you vacuum twice, does the time of the second vacuum
show up?

http://archives.postgresql.org/pgsql-committers/2009-09/msg0 0062.php

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Tom Lane [ Di, 23 März 2010 16:43 ] [ ID #2036074 ]

Re: pg_stat: last vacuum and analyze times are not being updated - v8.3.5

[ re-including the mailing list ]

"Steve Jones" <Steve.Jones [at] codeweavers.net> writes:
> For your information, this is what I did last night.

> Restarted the stats collector process, by issuing a kill -9 against it.
> As you said, postgres started up a new collector process immediately. I
> tried a few vacuums and the stats still weren't being updated.

> I then restarted postgres in the hope this would help. It didn't.

> I finally had to reboot the whole server which fixed the problem. As
> you said, I have to run two manual vacuums before the stats are showing
> up. This possible points a finger towards FreeBSD now? Would
> appreciate your thoughts.

If restarting Postgres didn't fix it then the problem has to have been
at the operating system level. I can hardly think what the problem
would have been exactly. It's not hard to imagine something going
wrong in the kernel state for the UDP socket we use to send messages to
the stats collector process; but restarting Postgres should have torn
down that socket and created a new one. Perhaps something wrong in
global state for all local UDP transfers? I dunno. But it seems like
time to call in some FreeBSD hackers.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Tom Lane [ Do, 25 März 2010 17:01 ] [ ID #2036532 ]