Обсуждение: [Fwd: Re: Autovacuum keeps vacuuming a table disabled in pg_autovacuum]

I stopped using autovacuum months ago because of similar problems
(version 8.1.4). Because we do some major inserts and updates about four
times a day, there were a few tables that I didn't want autovacuumed.
Even after I turned autovacuum off for these tables it still tried to
vacuum them while the updates were running. Instead, I just created cron
jobs to vacuum the two tables with the most updates daily, and the one
with the most inserts weekly. Performance has been pretty good.

Most other tables don't have nearly as many updates, however when I
checked the transaction IDs, I see that a problem is creeping closer as
we slowly approach the 2 billion mark.
  imp=# select age(datfrozenxid) from pg_database where datname = 'imp';
      age
  ------------
   1571381411
  (1 row)

Time to start VACUUM FULL ANALYZE over the weekend.

Ron



-------- Original Message --------

> select age(datfrozenxid) from pg_database where datname = 'your database'
>
> 2 billions and you are screwed.  Autovacuum starts panicking way before
> that, to have enough slack.

dbname=# select age(datfrozenxid) from pg_database where datname = 'dbname';
    age
------------
 1648762992
(1 row)

Sooo... looks like time to quickly upgrade to 8.1 head.

Thanks for the help,
Csaba.



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Ron St-Pierre wrote:
> I stopped using autovacuum months ago because of similar problems
> (version 8.1.4). Because we do some major inserts and updates about four
> times a day, there were a few tables that I didn't want autovacuumed.
> Even after I turned autovacuum off for these tables it still tried to
> vacuum them while the updates were running. Instead, I just created cron
> jobs to vacuum the two tables with the most updates daily, and the one
> with the most inserts weekly. Performance has been pretty good.

FYI, in 8.2 and up the Xid wraparound problem is considered on a table
by table basis, which means that only the tables that have not been
vacuumed recently need to be vacuumed.  The need for database wide
vacuuming is gone.

> Time to start VACUUM FULL ANALYZE over the weekend.

For Xid wraparound you don't need FULL anyway, in any release.

--
Alvaro Herrera                 http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)

Alvaro Herrera wrote:
> FYI, in 8.2 and up the Xid wraparound problem is considered on a table
> by table basis, which means that only the tables that have not been
> vacuumed recently need to be vacuumed.  The need for database wide
> vacuuming is gone.
>
>
That's good.
>> Time to start VACUUM FULL ANALYZE over the weekend.
>>
>
> For Xid wraparound you don't need FULL anyway, in any release
I realize that I don't need a FULL VACUUM for the xid wraparound, but
since it hasn't been run for a while I'll do a FULL.


Thanks!

Ron

On Fri, Jun 01, 2007 at 01:27:14PM -0700, Ron St-Pierre wrote:
>  imp=# select age(datfrozenxid) from pg_database where datname = 'imp';
>      age
>  ------------
>   1571381411
>  (1 row)
>
> Time to start VACUUM FULL ANALYZE over the weekend.

I guess this comes too late, but you don't need VACUUM FULL for that.
VACUUM FULL _does not_ mean "vacuum everything"!

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
        --Scott Morris

Andrew Sullivan wrote:
> On Fri, Jun 01, 2007 at 01:27:14PM -0700, Ron St-Pierre wrote:
>
>>  imp=# select age(datfrozenxid) from pg_database where datname = 'imp';
>>      age
>>  ------------
>>   1571381411
>>  (1 row)
>>
>> Time to start VACUUM FULL ANALYZE over the weekend.
>>
>
> I guess this comes too late, but you don't need VACUUM FULL for that.
Yes, I know that VACUUM FULL isn't required here, but because the
weekend is our slow time on the server I thought that I would perform a
full vacuum.
> VACUUM FULL _does not_ mean "vacuum everything"!
>
>
What do you mean by this?  I wanted to do both a VACUUM ANALYZE and a
VACUUM FULL, so ran VACUUM FULL ANALYZE. Is there something odd about
VACUUM FULL, other than locking the table it's working on?
> A
>
>

On Mon, Jun 04, 2007 at 07:34:13AM -0700, Ron St-Pierre wrote:
> What do you mean by this?  I wanted to do both a VACUUM ANALYZE and a
> VACUUM FULL, so ran VACUUM FULL ANALYZE. Is there something odd about
> VACUUM FULL, other than locking the table it's working on?

It tends to bloat indexes. Also, people tend to find that CLUSTER is
faster anyway.

If all you want is to avoid XID wraparound, an ordinary VACUUM will do
fine.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Mon, Jun 04, 2007 at 07:34:13AM -0700, Ron St-Pierre wrote:
> >VACUUM FULL _does not_ mean "vacuum everything"!

> What do you mean by this?

Sorry, I was trying to prevent you doing a VACUUM FULL you didn't
want (but another message said you actually intended a vacuum full).
Several people have been bitten by the misunderstanding that "VACUUM
FULL" means "VACUUM ALL TABLES" (e.g. vaccum full database).

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to.  That actually seems sort of quaint now.
        --J.D. Baldwin