Temporary disable autovacuum in pgsql 8.1.0
We are in the process of upgrading our 400GB database from PostgreSQL
7.4.7 to 8.1.0. During the pg_restore I noticed that the autovacuum
process was slowing things down significantly. I should have disabled it
in postgresql.conf before starting the restore (duh).
Not wanting to restart the restore, I searched for a method of disabling
autovacuum on the fly. I found the solution based on the hints I found
at http://archives.postgresql.org/pgsql-patches/2004-06/msg00414.php
This works because pg_restore will create all tables before it starts
filling them. Resetting the pg_autovacuum table is a simple truncate.
Perhaps it will help someone with the same problem.
insert into pg_autovacuum (
vacrelid,
enabled,
vac_base_thresh,
vac_scale_factor,
anl_base_thresh,
anl_scale_factor,
vac_cost_delay,
vac_cost_limit)
select a.oid,
false,
current_setting('autovacuum_vacuum_threshold')::int,
current_setting('autovacuum_vacuum_scale_factor')::real,
current_setting('autovacuum_analyze_threshold')::int,
current_setting('autovacuum_analyze_scale_factor')::real,
current_setting('autovacuum_vacuum_cost_delay')::int,
current_setting('autovacuum_vacuum_cost_limit')::int
from pg_class a
inner join pg_stat_all_tables b
on a.oid=b.relid
left outer join pg_autovacuum c
on a.oid = c.vacrelid
where a.relkind = 'r'
and schemaname not like 'pg_temp_%%'
and a.oid not in (select distinct vacrelid from
pg_autovacuum);
update pg_autovacuum
set enabled=false
where enabled;
--
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------
Richard van den Berg <richard.vandenberg@trust-factory.com> writes:
We are in the process of upgrading our 400GB database from PostgreSQL
7.4.7 to 8.1.0. During the pg_restore I noticed that the autovacuum
process was slowing things down significantly. I should have disabled it
in postgresql.conf before starting the restore (duh).
Not wanting to restart the restore, I searched for a method of disabling
autovacuum on the fly. I found the solution based on the hints I found
at http://archives.postgresql.org/pgsql-patches/2004-06/msg00414.php
Couldn't you have just set autovacuum = off in postgresql.conf? (Unlike
some other things, this setting can be changed after postmaster start.)
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote on 06-12-2005 15:49:28:
Couldn't you have just set autovacuum = off in postgresql.conf? (Unlike
some other things, this setting can be changed after postmaster start.)
If that is true, the documentation at
http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html
should be updated:
autovacuum (boolean)
Controls whether the server should start the autovacuum subprocess. This
is off by default. stats_start_collector and stats_row_level must also be
on for this to start. This option can only be set at server start or in
the postgresql.conf file.
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------
Richard van den Berg <richard.vandenberg@trust-factory.com> writes:
Tom Lane <tgl@sss.pgh.pa.us> wrote on 06-12-2005 15:49:28:
Couldn't you have just set autovacuum = off in postgresql.conf? (Unlike
some other things, this setting can be changed after postmaster start.)
If that is true, the documentation at
http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html
should be updated:
autovacuum (boolean)
Controls whether the server should start the autovacuum subprocess. This
is off by default. stats_start_collector and stats_row_level must also be
on for this to start. This option can only be set at server start or in
the postgresql.conf file.
That documentation is exactly correct. Note that it doesn't say "can
only be set at server start" full stop.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote on 06-12-2005 16:07:41:
That documentation is exactly correct. Note that it doesn't say "can
only be set at server start" full stop.
That's a very suttle difference, but you are right of couse. It's the word
'only' that threw me off. In my mind, it should read:
This option can be set at server start or in the postgresql.conf file
during runtime.
Sincerely,
Richard van den Berg, CISSP
-------------------------------------------
Trust Factory B.V. | www.dna-portal.net
Bazarstraat 44a | www.trust-factory.com
2518AK The Hague | Phone: +31 70 3620684
The Netherlands | Fax : +31 70 3603009
-------------------------------------------