Postgres Pro
Downloads
Home   >   mailing lists

Thread: autovacuum starting for no apparent reason

autovacuum starting for no apparent reason

From
hubert depesz lubaczewski
Date:
1. postgresql 8.2.4
2. system is 8-way xeon, 64bit with 32gram.
3. autovacuum is (and was) turned off in configuration.
4. today in peak hours autovacuum started. no mention of it in logs. it
just showed. started to vacuum the largest table in main database, and
brought the website down due to enormouos i/o.

autovacuum is not properly configured because we dont use it - we do
nightly vacuumdb -azv, and we are positive that it did work (nagios
check for vacuumdb completion and possible errors).

now - xid wraparound is not really a problem:
# select oid, age(datfrozenxid) from pg_database;
   oid    |    age
----------+-----------
    10819 | 158797276
  7800493 | 158792051
 19785040 | 158785318
        1 | 158777444
    10818 | 193463316
  7805444 | 158776554
 19812104 | 158767651
   582103 | 212103362
 19528166 | 212103362
  7795500 | 212103362
  7815547 | 212103362
 19818144 | 212103362
 19818149 | 212103362
  6158349 | 212103362
 19828634 | 212103362
  4540444 | 212103362
 19834405 | 212103362
  7810585 | 212103362
 19834438 | 212103362
(19 rows)

at the moment we are working at about 1500 transactions per second (when autovac started it was around 1300).

when i killed autovacuum process, a couple of minutes later it restarted! still nothing in logs.

any ideas?
unfortunatelly i can't do much as this is production server, but perhaps it
will "ring the bell" and somebody will know what to fix.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Re: autovacuum starting for no apparent reason

From
hubert depesz lubaczewski
Date:
sorry to bother you. problem has been found, it's the (default, too low)
value of autovacuum_freeze_max_age.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

Re: autovacuum starting for no apparent reason

From
Stefan Kaltenbrunner
Date:
hubert depesz lubaczewski wrote:
> 1. postgresql 8.2.4
> 2. system is 8-way xeon, 64bit with 32gram.
> 3. autovacuum is (and was) turned off in configuration.
> 4. today in peak hours autovacuum started. no mention of it in logs. it
> just showed. started to vacuum the largest table in main database, and
> brought the website down due to enormouos i/o.

ouch

>
> autovacuum is not properly configured because we dont use it - we do
> nightly vacuumdb -azv, and we are positive that it did work (nagios
> check for vacuumdb completion and possible errors).
>
> now - xid wraparound is not really a problem:
> # select oid, age(datfrozenxid) from pg_database;
>    oid    |    age
> ----------+-----------
>     10819 | 158797276
>   7800493 | 158792051
>  19785040 | 158785318
>         1 | 158777444
>     10818 | 193463316
>   7805444 | 158776554
>  19812104 | 158767651
>    582103 | 212103362
>  19528166 | 212103362
>   7795500 | 212103362
>   7815547 | 212103362
>  19818144 | 212103362
>  19818149 | 212103362
>   6158349 | 212103362
>  19828634 | 212103362
>   4540444 | 212103362
>  19834405 | 212103362
>   7810585 | 212103362
>  19834438 | 212103362
> (19 rows)
>
> at the moment we are working at about 1500 transactions per second (when autovac started it was around 1300).

1500 tps makes up for about 130M transactions per day which is fairly
close in the ballpark of the default setting for
autovacuum_freeze_max_age (200M). So if you say had a 30% increase in
transactions on that day it could easily explain why autovacuum started
automatically (or something prevented the former vacuum from doing its
work).


Stefan