pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
strange autovacuum behaviour 3+ messages / 2 participants [nested] [flat]
* strange autovacuum behaviour @ 2009-02-09 17:43 Stefano Nichele <stefano.nichele@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Stefano Nichele @ 2009-02-09 17:43 UTC (permalink / raw) To: pgsql-admin Hi All, I have a couple of questions about autovacuum/vacuum behavior. On my production system, I set autovacuum ON since it's pretty hard to me find a good timeframe for vacuum (the system is pretty busy over the day) Last week the system was really slow and running vacuum manually the performance was really improved. So now I would like to understand why autovacuum did not work as expected (at least for me). Any ideas ? Other question. Yesterday I noticed another strange thing. autovacuum was off since the aforementioned issue and a vacuum scheduled at 06:00 UTC. Checking pg_stat_all_tables (last_autovacuum field) I saw that at 05:30 UTC an autovacuum was performed for almost all the tables. After 30 min (at 06:00) vacuum started and for about 2 hours, vacuum and autovacuum were running simultaneously. Why autovacuum (that was OFF) started ? For preventing transaction ID wraparound ? Is it right that in such case all the tables are (auto)vacuumed ? My database is about 35 GB and the most updated table (in terms of insert/delete/update) is indeed the biggest one (3.6 GB of data, 3.2 GB primary key index, 1.5 GB another index) and I'm using postgres 8.2.9. Thanks in advance for any suggestion. Cheers, ste ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: strange autovacuum behaviour @ 2009-02-10 14:07 Alvaro Herrera <alvherre@commandprompt.com> parent: Stefano Nichele <stefano.nichele@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Alvaro Herrera @ 2009-02-10 14:07 UTC (permalink / raw) To: Stefano Nichele <stefano.nichele@gmail.com>; +Cc: pgsql-admin Stefano Nichele escribió: > Hi All, > I have a couple of questions about autovacuum/vacuum behavior. > > On my production system, I set autovacuum ON since it's pretty hard to > me find a good timeframe for vacuum (the system is pretty busy over the day) > Last week the system was really slow and running vacuum manually the > performance was really improved. > So now I would like to understand why autovacuum did not work as > expected (at least for me). Any ideas ? Maybe the autovacuum naptime was set too high. Or perhaps the threshold and scale settings were too high. Maybe the vacuum_cost_delay for autovacuum was too high. > Why autovacuum (that was OFF) started ? For preventing transaction ID > wraparound ? Is it right that in such case all the tables are > (auto)vacuumed ? Not necessarily all tables; only those that require a vacuum to prevent xid wraparound. (It was all tables in 8.1, but this changed in 8.2). ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: strange autovacuum behaviour @ 2009-02-24 19:05 Stefano Nichele <stefano.nichele@gmail.com> parent: Alvaro Herrera <alvherre@commandprompt.com> 0 siblings, 0 replies; 3+ messages in thread From: Stefano Nichele @ 2009-02-24 19:05 UTC (permalink / raw) To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: pgsql-admin Hi Alvaro, thanks for your answer and sorry for the delay but I was in vacation. Alvaro Herrera wrote: > Stefano Nichele escribió: > >> Hi All, >> I have a couple of questions about autovacuum/vacuum behavior. >> >> On my production system, I set autovacuum ON since it's pretty hard to >> me find a good timeframe for vacuum (the system is pretty busy over the day) >> Last week the system was really slow and running vacuum manually the >> performance was really improved. >> So now I would like to understand why autovacuum did not work as >> expected (at least for me). Any ideas ? >> > > Maybe the autovacuum naptime was set too high. Or perhaps the threshold > and scale settings were too high. Maybe the vacuum_cost_delay for > autovacuum was too high. > Currently I'm still run vacuum but as soon as I can, I'll do other tests. > >> Why autovacuum (that was OFF) started ? For preventing transaction ID >> wraparound ? Is it right that in such case all the tables are >> (auto)vacuumed ? >> > > Not necessarily all tables; only those that require a vacuum to prevent > xid wraparound. (It was all tables in 8.1, but this changed in 8.2). > > Actually I saw that it autovacuumed all tables (except 2 of 100 tables). And why during vacuum ? (this is not just a coincidence since occurred more than one time). It seems autovacuum was triggered by vacuum. Cheers, ste ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-02-24 19:05 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2009-02-09 17:43 strange autovacuum behaviour Stefano Nichele <stefano.nichele@gmail.com> 2009-02-10 14:07 ` Alvaro Herrera <alvherre@commandprompt.com> 2009-02-24 19:05 ` Stefano Nichele <stefano.nichele@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox