Обсуждение: autovacuum running even when not set in config?
Hello, Recently we noticed this autovacuum process running on one of our db servers, which is running pg 8.2.3. 2273 ? Ds 0:00 postgres: autovacuum process fy2006outcomesdb4091 Autovacuum is off in this machine's config. I verified that by connecting via psql and running 'show autovacuum'. Also, pg_autovacuum on this machine has no rows. Has anyone seen this sort of behavior? Is it normal for autovacuuming to start up once in a while even when the configuration does not call for autovacuuming? Thanks, Douglas Birdzell Senior Software Developer Advanced Outcomes Design http://www.aod.cx voice: 202-872-1033 fax: 202-833-9168
Douglas Birdzell wrote: > Hello, > > Recently we noticed this autovacuum process running on one of our db > servers, which is running pg 8.2.3. > > 2273 ? Ds 0:00 postgres: autovacuum process fy2006outcomesdb4091 > > Autovacuum is off in this machine's config. I verified that by > connecting via psql and running 'show autovacuum'. Also, > pg_autovacuum on this machine has no rows. It is normal if the database is in danger of Xid wraparound. (So now you know have been neglecting to vacuum some tables). -- Alvaro Herrera http://www.advogato.org/person/alvherre "The important things in the world are problems with society that we don't understand at all. The machines will become more complicated but they won't be more complicated than the societies that run them." (Freeman Dyson)
Well, the database is also showing a hint when less then 9 million transactions are available - and this hint is telling us to vacuum the whole database. If, vacuum the whole database, would take days (quite unsure how many days sometimes) is it possible to find out manually which tables originally need a vacuum or is the xid wraparound problem database wide so we MUST vacuum the whole database? The automatic startup of autovacuum in this cases is really good, but we would like to manage vacuum by ourself, especially WHEN it is running, because we have different load times on different database servers depending on time zones and the users using it. > It is normal if the database is in danger of Xid wraparound. (So now > you know have been neglecting to vacuum some tables). >
Aldor wrote: > Well, the database is also showing a hint when less then 9 million > transactions are available - and this hint is telling us to vacuum > the whole database. > > If, vacuum the whole database, would take days (quite unsure how > many days sometimes) is it possible to find out manually which > tables originally need a vacuum or is the xid wraparound problem > database wide so we MUST vacuum the whole database? On 8.2 this is per table. You can check which tables need vacuum with select age(relfreezexid) from pg_class (I'm not sure if the column is relfreezexid or relfrozenxid). > The automatic startup of autovacuum in this cases is really good, > but we would like to manage vacuum by ourself, especially WHEN it is > running, because we have different load times on different database > servers depending on time zones and the users using it. Yeah, this is something we are planning for 8.4. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.