Re: Auto-vacuum is not running in 9.1.12

From: Prakash Itnal <prakash074(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org, rasna(dot)t(at)nokia(dot)com, sandhya(dot)k_s(at)nokia(dot)com
Subject: Re: Auto-vacuum is not running in 9.1.12
Date: 2015-06-16 04:42:29
Message-ID: CAHC5u7_VLEXW+Tw55K4Gp9gnumO8gWAyKXaV6LdhY3tddB6NTg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)2ndquadrant(dot)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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2015-06-16 06:04:57 Re: pg_rewind failure by file deletion in source server
Previous Message Naoya Anzai 2015-06-16 03:11:12 Re: [Proposal] More Vacuum Statistics