Обсуждение: strange autovacuum behaviour
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
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).
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