Postgres Pro
Downloads
Home   >   mailing lists

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

autovacuum vacuums entire database...is this right?

From
"richyen3@gmail.com"
Date:
Hi,

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?

--Richard

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

From
"richyen3@gmail.com"
Date:
It's been about a month and a half, and I'm getting this VACUUM
again.  This time, I'm wondering if there's any way to tell if
autovacuum is doing a database-wide vacuum for the sake of xid
wraparound or for some other reason.  Is there some sort of entry that
gets put into the log, and if so, what log level would it be at?

If this doesn't get logged, could I make this a feature request?

Thanks!
--Richard



On Apr 15, 6:35 am, "richy...@gmail.com" <richy...@gmail.com> wrote:
> Hi,
>
> 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?
>
> --Richard

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

From
Alvaro Herrera
Date:
richyen3@gmail.com escribió:
> It's been about a month and a half, and I'm getting this VACUUM
> again.  This time, I'm wondering if there's any way to tell if
> autovacuum is doing a database-wide vacuum for the sake of xid
> wraparound or for some other reason.  Is there some sort of entry that
> gets put into the log, and if so, what log level would it be at?

No, I don't think we had yet set more extensive logging in autovacuum.

> If this doesn't get logged, could I make this a feature request?

Not really, because starting from 8.2 autovacuum no longer does that.
There is no longer a need for database-wide vacuums.  So if you're
having problems because of that, your best bet is to upgrade.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.