Re: Auto-vacuum is not running in 9.1.12

Hi,

Currently the issue is easily reproducible. Steps to reproduce:
* Set some aggressive values for auto-vacuuming.
* Run a heavy database update/delete/insert queries. This leads to invoking
auto-vacuuming in quick successions.
* Change the system time to older for eg. 1995-01-01

Suddenly auto-vacuuming stops working. Even after changing system time back
to current time, the auto-vacuuming did not resume.

So the question is, "does postrges supports system time changes?".

On Tue, Jun 16, 2015 at 10:12 AM, Prakash Itnal <prakash074(at)gmail(dot)com>
wrote:

> 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
>

--
Cheers,
Prakash