Search Postgresql Archives

Temporary disable autovacuum in pgsql 8.1.0

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
-------------------------------------------


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux