Search Postgresql Archives

Re: autovacuum vacuums entire database...is this right?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"richyen3@xxxxxxxxx" <richyen3@xxxxxxxxx> writes:
> I'm just wondering if autovacuum is ever supposed to vacuum the entire
> database during one of its runs.  As far as I remember, it's supposed
> to vacuum one table at a time, based on the
> autovacuum_vacuum_threshold, autovacuum_analyze_threshold, etc.
> settings.
>
> For some reason, autovacuum decided to run a vacuum on my entire
> database (29GB large), and it's taking forever:
>
> select now(), query_start, current_query, backend_start, procpid,
> usename from pg_stat_activity where current_query <> '<IDLE>';
>               now              |          query_start          |
> current_query |         backend_start         | procpid | usename
> -------------------------------+-------------------------------
> +---------------+-------------------------------+---------+----------
>  2007-04-15 06:34:27.925042-07 | 2007-04-14 22:23:31.283894-07 |
> VACUUM        | 2007-04-14 22:23:31.274121-07 |    9406 | postgres
>
> Is this expected behavior?

It is, for the case where, pre-8.2, a database is getting towards the
point where it will soon see XID roll-over, which has the potential
for data to disappear (the data's there; it's just invisible).  In
that case, a vacuum of the whole database is necessary.

In version 8.2 and later, XID rollover is tracked on a per-table
basis, not on a per-database basis, which eliminates the need to ever
vacuum "the whole thang."

Compare the 8.1 and 8.2 documentation, as needed:

http://www.postgresql.org/docs/8.1/static/maintenance.html#VACUUM-FOR-WRAPAROUND

http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://linuxfinances.info/info/x.html
"I'd crawl over an acre of 'Visual This++' and 'Integrated Development
That' to  get to gcc, Emacs,  and gdb.  Thank you."
-- Vance Petree, Virginia Power


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux