Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
	by mail.postgresql.org (Postfix) with ESMTP id 0B96F1EB83EA
	for <pgsql-performance@postgresql.org>;
	Thu, 23 Feb 2012 14:01:18 -0400 (AST)
Received: from cerberus.pinpointresearch.com ([66.7.238.130]
	helo=polaris.pinpointresearch.com)
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <scrawford@pinpointresearch.com>) id 1S0cyj-0001cc-PE
	for pgsql-performance@postgresql.org; Thu, 23 Feb 2012 18:01:18 +0000
Received: from betelgeuse.pinpointresearch.com
	(betelgeuse.pinpointresearch.com [192.168.1.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP id F1486E00EC83;
	Thu, 23 Feb 2012 10:01:04 -0800 (PST)
Message-ID: <4F467EDF.5090708@pinpointresearch.com>
Date: Thu, 23 Feb 2012 10:01:03 -0800
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.26) Gecko/20120131 Lightning/1.0b2 Thunderbird/3.1.18
MIME-Version: 1.0
To: Alessandro Gagliardi <alessandro@path.com>
CC: Andy Colson <andy@squeakycode.net>, Thom Brown <thom@linux.com>,
	pgsql-performance@postgresql.org
Subject: Re: set autovacuum=off
References: 
 <CAAB3BBLsZ94tTzaiTZrdpK+2m1waj8=VL31aPvOGFJ7oAGCHiQ@mail.gmail.com>
	<CAA-aLv4KL4atuaqJFi3BBWiRsWiJOVQZMk65JYw=H8ezZV6O8g@mail.gmail.com>
	<4F4658CB.3030505@squeakycode.net>
	<CAAB3BBK-nzheaG4Ju0snB7b3hqc2pZ+wV6vv9BPt81_k_cLcWw@mail.gmail.com>
In-Reply-To: 
 <CAAB3BBK-nzheaG4Ju0snB7b3hqc2pZ+wV6vv9BPt81_k_cLcWw@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------050702050407050005090303"
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201202/172
X-Sequence-Number: 46344

This is a multi-part message in MIME format.
--------------050702050407050005090303
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 02/23/2012 09:35 AM, Alessandro Gagliardi wrote:
> I should have been more clear. I virtually never delete or do updates, 
> but I insert /a lot/. So the table does change quite a bit, but only 
> in one direction.
>
> I was unable to disable autovacuum universally (due to the 
> cant_change_runtime_param error) but I was able to disable it on 
> individual tables. Still, I know this is heavy handed and sub-optimal. 
> I tried set autovacuum_naptime='6min' but got the same 55P02 error. 
> Should/can I set that per table?
>
> I did look at autovacuum_vacuum_threshold 
> and autovacuum_vacuum_scale_factor but couldn't make sense out of 
> them. (Besides, I'd probably get the same 55P02 error if I tried to 
> change them.)

See:
http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html

The documentation has information like "This parameter can only be set 
in the postgresql.conf file or on the server command line." that will 
tell you in advance which settings will fail when you attempt to set 
them through SQL statements.

But autovacuum is pretty smart about not vacuuming tables until 
reasonably necessary. And beware that autovacuum is also controlling 
when to analyze a table. Mass inserts are probably changing the 
characteristics of your table such that it needs to be analyzed to allow 
the planner to properly optimize your queries.

Have you identified that vacuum is actually causing a problem? If not, 
I'd leave it alone. The system tables have a lot of information on table 
vacuuming and analyzing:

select
     relname,
     last_vacuum,
     last_autovacuum,
     last_analyze,
     last_autoanalyze,
     vacuum_count,
     autovacuum_count,
     analyze_count,
     autoanalyze_count
from
    pg_stat_user_tables;

Cheers,
Steve


--------------050702050407050005090303
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#ffffff" text="#000000">
    On 02/23/2012 09:35 AM, Alessandro Gagliardi wrote:
    <blockquote
cite="mid:CAAB3BBK-nzheaG4Ju0snB7b3hqc2pZ+wV6vv9BPt81_k_cLcWw@mail.gmail.com"
      type="cite">I should have been more clear. I virtually never
      delete or do updates, but I insert <i>a lot</i>. So the table
      does change quite a bit, but only in one direction.&nbsp;
      <div><br>
      </div>
      <div>I was unable to disable autovacuum&nbsp;universally&nbsp;(due to the&nbsp;<span
          style="font-family: verdana,sans-serif; font-size: 12px;
          text-align: left; background-color: rgb(255, 255, 255);">cant_change_runtime_param</span>&nbsp;error)
        but I was able to disable it on individual tables. Still, I know
        this is heavy handed and sub-optimal. I tried&nbsp;set
        autovacuum_naptime='6min' but got the same&nbsp;55P02 error.
        Should/can I set that per table?</div>
      <div><br>
      </div>
      <div>I did look at&nbsp;autovacuum_vacuum_threshold
        and&nbsp;autovacuum_vacuum_scale_factor but couldn't make sense out
        of them. (Besides, I'd probably get the same&nbsp;55P02 error if I
        tried to change them.) <br>
      </div>
    </blockquote>
    <br>
    See:<br>
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html">http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html</a><br>
    <br>
    The documentation has information like "This parameter can only be
    set in the <tt class="FILENAME">postgresql.conf</tt> file or on the
    server command line." that will tell you in advance which settings
    will fail when you attempt to set them through SQL statements.<br>
    <br>
    But autovacuum is pretty smart about not vacuuming tables until
    reasonably necessary. And beware that autovacuum is also controlling
    when to analyze a table. Mass inserts are probably changing the
    characteristics of your table such that it needs to be analyzed to
    allow the planner to properly optimize your queries.<br>
    <br>
    Have you identified that vacuum is actually causing a problem? If
    not, I'd leave it alone. The system tables have a lot of information
    on table vacuuming and analyzing:<br>
    <br>
    select<br>
    &nbsp;&nbsp;&nbsp; relname,<br>
    &nbsp;&nbsp;&nbsp; last_vacuum,<br>
    &nbsp;&nbsp;&nbsp; last_autovacuum,<br>
    &nbsp;&nbsp;&nbsp; last_analyze,<br>
    &nbsp;&nbsp;&nbsp; last_autoanalyze,<br>
    &nbsp;&nbsp;&nbsp; vacuum_count,<br>
    &nbsp;&nbsp;&nbsp; autovacuum_count,<br>
    &nbsp;&nbsp;&nbsp; analyze_count,<br>
    &nbsp;&nbsp;&nbsp; autoanalyze_count<br>
    from<br>
    &nbsp;&nbsp; pg_stat_user_tables;<br>
    <br>
    Cheers,<br>
    Steve<br>
    <br>
  </body>
</html>

--------------050702050407050005090303--