Обсуждение: check_postgres reports most of my tables are unanalyzed for 8 weeks; why isn't autovacuum analyzing them?

Hi.  I've just discovered the check_postgres utility and am running
all the tests against my database.

The "last_analyze" test comes out critical - many tables unanalyzed
for 8 weeks.

I am running PostgreSQL 8.4.4 with default autovacuum settings.  I
thought autovacuum was
supposed to take care of running vacuum and analyze.

Per "last_analyze",  9 out of my 100 tables have been analyzed within
the last 24 hours.

Why aren't all my tables being analyzed?  How do I troubleshoot this, please?

Best,
-at

P.S. I checked the PostgreSQL logs, and in the last week, I just have
three messages with "vacuum" in them:
(I am running at default log levels)

2010-07-20 02:05:05 PDT ERROR:  canceling autovacuum task
2010-07-20 02:05:05 PDT CONTEXT:  automatic vacuum of table
"mydb.pg_catalog.pg_listener"

2010-07-20 07:27:14 PDT ERROR:  canceling autovacuum task
2010-07-20 07:27:14 PDT CONTEXT:  automatic vacuum of table
"mydb._slony_cluster.sl_event"

2010-07-20 07:27:53 PDT ERROR:  canceling autovacuum task
2010-07-20 07:27:53 PDT CONTEXT:  automatic vacuum of table
"mydb._slony_cluster.sl_log_2"

I just ran the "last_autovacuum" test of check_postgres, and it reported

7 (of my 100) tables have been autovacuumed more than 1 day ago;
the oldest autovacuum time was 7 weeks ago.

8 more tables in pg_catalog were autovacuumed more than 1 day ago.

Thanks,
-at

I am sorry for the multiple posts; just noticed there are two tests
for analyze:  last_analyze and last_autoanalyze

last_autoanalyze matches last_autovacuum -  7 weeks ago

Aleksey

On Tue, Jul 20, 2010 at 4:31 PM, Aleksey Tsalolikhin
<atsaloli.tech@gmail.com> wrote:
> Hi.  I've just discovered the check_postgres utility and am running
> all the tests against my database.
>
> The "last_analyze" test comes out critical - many tables unanalyzed
> for 8 weeks.

have those tables been modified at all?  this is only an issue if the
database's understanding of the table is different from reality AFIAK.

> P.S. I checked the PostgreSQL logs, and in the last week, I just have
> three messages with "vacuum" in them:
> (I am running at default log levels)
>
> 2010-07-20 02:05:05 PDT ERROR:  canceling autovacuum task
> 2010-07-20 02:05:05 PDT CONTEXT:  automatic vacuum of table
> "mydb.pg_catalog.pg_listener"
>
> 2010-07-20 07:27:14 PDT ERROR:  canceling autovacuum task
> 2010-07-20 07:27:14 PDT CONTEXT:  automatic vacuum of table
> "mydb._slony_cluster.sl_event"
>
> 2010-07-20 07:27:53 PDT ERROR:  canceling autovacuum task
> 2010-07-20 07:27:53 PDT CONTEXT:  automatic vacuum of table
> "mydb._slony_cluster.sl_log_2"

this is normal.  note that slony abuses the listener notify system,
and autovac punts to avoid messing with user sessions.  9.0 has a much
cleaner async notify mechanism so won't have this issue.

merlin