From: Steve Crawford <scrawford@pinpointresearch.com>
To: Alessandro Gagliardi <alessandro@path.com>
Cc: Andy Colson <andy@squeakycode.net>
Cc: Thom Brown <thom@linux.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: set autovacuum=off
Date: Thu, 23 Feb 2012 10:01:03 -0800
Message-ID: <4F467EDF.5090708@pinpointresearch.com> (raw)
In-Reply-To: <CAAB3BBK-nzheaG4Ju0snB7b3hqc2pZ+wV6vv9BPt81_k_cLcWw@mail.gmail.com>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scrawford@pinpointresearch.com, alessandro@path.com, andy@squeakycode.net, thom@linux.com
Subject: Re: set autovacuum=off
In-Reply-To: <4F467EDF.5090708@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox