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