Обсуждение: Stray autovacuum daemon
Hello list
I have a largish postgresql (8.3) instance with autovacuum turned on. I am doing some heavy importing and I want to vacuum the relevant tables manually since autovacuum seems to trigger the vacuum in the middle of the import process which slows things down.
Now, the problem is that I cannot turn autovacuum off! I have tried to set autovacuum = off at postgresql.conf with no avail. I have also tried to disable the vacuum by inserting rows to pg_autovacuum but still the vacuum processes just pop up. What's even more weird is that autovacuum is vacuuming the same tables over and over. No sign of xid wraparound issues show in the logs. How can I stop autovacuum without restarting the database instance?
Relevant non-default configuration values:
autovacuum = off
log_autovacuum_min_duration = 0
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 500
vacuum_cost_delay = 5
Logs:
2008-02-22 12:59:34 EET [23258]: [2-1] LOG: automatic vacuum of table "xxx": index scans: 0
pages: 0 removed, 15150 remain
tuples: 0 removed, 2123107 remain
system usage: CPU 0.01s/0.00u sec elapsed 151.48 sec
Regards
Mikko
I have a largish postgresql (8.3) instance with autovacuum turned on. I am doing some heavy importing and I want to vacuum the relevant tables manually since autovacuum seems to trigger the vacuum in the middle of the import process which slows things down.
Now, the problem is that I cannot turn autovacuum off! I have tried to set autovacuum = off at postgresql.conf with no avail. I have also tried to disable the vacuum by inserting rows to pg_autovacuum but still the vacuum processes just pop up. What's even more weird is that autovacuum is vacuuming the same tables over and over. No sign of xid wraparound issues show in the logs. How can I stop autovacuum without restarting the database instance?
Relevant non-default configuration values:
autovacuum = off
log_autovacuum_min_duration = 0
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 500
autovacuum_analyze_threshold = 500
vacuum_cost_delay = 5
Logs:
2008-02-22 12:59:34 EET [23258]: [2-1] LOG: automatic vacuum of table "xxx": index scans: 0
pages: 0 removed, 15150 remain
tuples: 0 removed, 2123107 remain
system usage: CPU 0.01s/0.00u sec elapsed 151.48 sec
Regards
Mikko
Mikko Partio escribió: > Now, the problem is that I cannot turn autovacuum off! I have tried to set > autovacuum = off at postgresql.conf with no avail. I have also tried to > disable the vacuum by inserting rows to pg_autovacuum but still the vacuum > processes just pop up. What's even more weird is that autovacuum is > vacuuming the same tables over and over. No sign of xid wraparound issues > show in the logs. How can I stop autovacuum without restarting the database > instance? If your database is in danger of Xid wraparound, autovacuum will run on it no matter what you do. Check pg_class.relfrozenxid and pg_database.datfrozenxid, and the current Xid counter (from pg_controldata I think). Also take a look at the description of the autovacuum_min_freeze_age param (or was it max_freeze_age? I forget). -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, Feb 22, 2008 at 5:44 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
But I thought vacuum would send some messages to log if xid wraparound is looming?
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' order by 2 desc limit 4;
relname | age
---------------+-----------
table1 | 2141707556
table2 | 2141707556
table3 | 2141707556
table4 | 2141707556
SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
-----------+------------
template1 | 1833384067
template0 | 1833384065
postgres | 1933384063
mydb | 2040873036
Seems ok to me ... ? autovacuum_freeze_max_age and vacuum_freeze_min_age have the default value.Mikko Partio escribió:If your database is in danger of Xid wraparound, autovacuum will run on
> Now, the problem is that I cannot turn autovacuum off! I have tried to set
> autovacuum = off at postgresql.conf with no avail. I have also tried to
> disable the vacuum by inserting rows to pg_autovacuum but still the vacuum
> processes just pop up. What's even more weird is that autovacuum is
> vacuuming the same tables over and over. No sign of xid wraparound issues
> show in the logs. How can I stop autovacuum without restarting the database
> instance?
it no matter what you do. Check pg_class.relfrozenxid and
pg_database.datfrozenxid, and the current Xid counter (from
pg_controldata I think). Also take a look at the description of the
autovacuum_min_freeze_age param (or was it max_freeze_age? I forget).
But I thought vacuum would send some messages to log if xid wraparound is looming?
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' order by 2 desc limit 4;
relname | age
---------------+-----------
table1 | 2141707556
table2 | 2141707556
table3 | 2141707556
table4 | 2141707556
SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
-----------+------------
template1 | 1833384067
template0 | 1833384065
postgres | 1933384063
mydb | 2040873036
Regards
Mikko
On Fri, Feb 22, 2008 at 7:01 PM, Mikko Partio <mpartio@gmail.com> wrote:
I think I may have found the problem, the import uses a temporary table which I guess autovacuum can not handle? Manually vacuuming it changed the relfrozenxids of other tables dramatically.
Regards
Mikko
On Fri, Feb 22, 2008 at 5:44 PM, Alvaro Herrera <alvherre@commandprompt.com> wrote:Mikko Partio escribió:If your database is in danger of Xid wraparound, autovacuum will run on
> Now, the problem is that I cannot turn autovacuum off! I have tried to set
> autovacuum = off at postgresql.conf with no avail. I have also tried to
> disable the vacuum by inserting rows to pg_autovacuum but still the vacuum
> processes just pop up. What's even more weird is that autovacuum is
> vacuuming the same tables over and over. No sign of xid wraparound issues
> show in the logs. How can I stop autovacuum without restarting the database
> instance?
it no matter what you do. Check pg_class.relfrozenxid and
pg_database.datfrozenxid, and the current Xid counter (from
pg_controldata I think). Also take a look at the description of the
autovacuum_min_freeze_age param (or was it max_freeze_age? I forget).
I think I may have found the problem, the import uses a temporary table which I guess autovacuum can not handle? Manually vacuuming it changed the relfrozenxids of other tables dramatically.
Regards
Mikko
Mikko Partio escribió: > I think I may have found the problem, the import uses a temporary table > which I guess autovacuum can not handle? Manually vacuuming it changed the > relfrozenxids of other tables dramatically. Ah, yes -- that would be it. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.