Postgres Pro
Downloads
Home   >   mailing lists

Re: Auto-vacuum is not running in 9.1.12 - Mailing list pgsql-hackers

From Prakash Itnal
Subject Re: Auto-vacuum is not running in 9.1.12
Date
Msg-id CAHC5u7_VLEXW+Tw55K4Gp9gnumO8gWAyKXaV6LdhY3tddB6NTg@mail.gmail.com
Whole thread Raw
In response to Re: Auto-vacuum is not running in 9.1.12  (Alvaro Herrera <alvherre@2ndquadrant.com>)
List pgsql-hackers
Tree view
Hi,

@Avaro Herrera, Thanks for quick reply. I was on leave and hence not able to reply soon.

This issue was observed on customer site. However after long discussion and digging into what happened around the date 2nd May 2015, we got to know that NTP server suddenly went back in time to 1995. It remained there for some time until it is noticed and corrected. So after correcting NTP server time the whole cluster is synced to current date. After this change in time the auto-vacuum stopped. Since auto-vacuuming is triggered periodically, I doubt if this time change has affected any timer! 

So I suspect the time change is the root cause! It would be great if someone can clarify if this is the root cause for auto-vacuum stopped.


On Wed, Jun 10, 2015 at 8:19 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
Prakash Itnal wrote:
> Hello,
>
> Recently we encountered a issue where the disc space is continuously
> increasing towards 100%. Then a manual vacuum freed the disc space. But
> again it is increasing. When digged more it is found that auto-vacuuming
> was not running or it is either stucked/hanged.

Hm, we have seen this on Windows, I think.

Is the "stats collector" process running?  Is it stuck?

If you attach to process 6504 (autovac launcher), what's the backtrace?

> 4) Last run auto-vacuum:
> SELECT now(), schemaname, relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM pg_stat_user_tables;
>
>               now              | schemaname |    relname    | last_vacuum |        last_autovacuum        | vacuum_count | autovacuum_count
> -------------------------------+------------+---------------+-------------+-------------------------------+--------------+------------------
>  2015-06-10 01:03:03.574212+02 | public     | abcd          |             | 2015-04-18 00:52:35.008874+02 |            0 |                2
>  2015-06-10 01:03:03.574212+02 | public     | xyz           |             | 2015-05-02 06:01:35.220651+02 |            0 |               20
>
> NOTE: I changed the relname for above two tables due to confidentiality.

Are there dead tuples in tables?  Maybe vacuums are getting executed and
these values are not updated, for instance?

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



--
Cheers,
Prakash

pgsql-hackers by date:

Previous
From: Naoya Anzai
Date:
Subject: Re: [Proposal] More Vacuum Statistics
Next
From: Michael Paquier
Date:
Subject: Re: pg_rewind failure by file deletion in source server