Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id BDAE62E00BD for ; Fri, 22 Feb 2008 07:06:01 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 24016-04 for ; Fri, 22 Feb 2008 07:05:57 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by postgresql.org (Postfix) with ESMTP id 691352E005B for ; Fri, 22 Feb 2008 07:05:57 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so467547waf.5 for ; Fri, 22 Feb 2008 03:05:55 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=wPnxuJroGvNWhwvxLint661erHyYy4nYFkcT8RRrWIY=; b=vB1UiH9Si8/UYwCQONJ9Shpd6prI4xhH4IV24LT0W5qUkFlMDKTIC8zaWtgMEodsJdrzdbrVWAdytLLEkOxV18OzFJ5V8G1r75YsMpNUA2EC2oHE91FBgLB+JWKd7JGCGIsFZZlQfyWR2pqwvuPT+eePTckJ6JG/3xacJ5I+jTg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=RGKg3MyVYCeqJGQDkbAqj+++VcpqOA3CJq6Wf/wC5RhdoiPx0x4gVZ6F/JpxTEfVsL0nub3NQrjy6nbwwEiHmh+wbNlRNd5pxxOfcs2z4wY6nWIFpZJAfz42aQjdSzhPj+UfceBXH5NiT21i61dCbkWCG53oPyd5GcAaaa+k59A= Received: by 10.115.95.1 with SMTP id x1mr1131185wal.94.1203678355757; Fri, 22 Feb 2008 03:05:55 -0800 (PST) Received: by 10.114.102.14 with HTTP; Fri, 22 Feb 2008 03:05:55 -0800 (PST) Message-ID: <2ca799770802220305v38b800dbt909c84e3b3be57cc@mail.gmail.com> Date: Fri, 22 Feb 2008 13:05:55 +0200 From: "Mikko Partio" To: pgsql-admin@postgresql.org Subject: Stray autovacuum daemon MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9847_20007909.1203678355751" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/270 X-Sequence-Number: 28344 ------=_Part_9847_20007909.1203678355751 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_9847_20007909.1203678355751 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
------=_Part_9847_20007909.1203678355751--