Re: set autovacuum=off

On Thu, Feb 23, 2012 at 10:01 AM, Steve Crawford <
scrawford(at)pinpointresearch(dot)com> wrote:

> **
> 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.
>
> Ah. I missed that. Sorry for asking stupid questions.

> 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.
>
> Okay, that makes more sense to me; because the stats would be changing
quickly and so while vacuuming may not be necessary, analyzing would be. At
the same time, I can't afford to analyze if it's causing my inserts to take
over 50 ms. Something else I should add: if my selects are slow, that's
annoying; but if my inserts are slow, that could be disastrous. Does
analyze increase the efficiency of inserts or just selects? (I assumed the
latter.) Obviously, I will need to analyze sometimes, but perhaps not
nearly as often as postgres would predict under the circumstances.

> 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:
>
> Not indubitably, but circumstantially, I did notice that significantly
fewer of my commits were taking over 50 ms after I set
autovacuum_enabled=off on many of my tables. Unfortunately, it was not an
isolated experiment, so I can't really be sure. At the same time, I'm
hesitant to turn it back on until I'm sure it either didn't make a
difference or I've got a better plan for how to deal with this.

> select
> relname,
> last_vacuum,
> last_autovacuum,
> last_analyze,
> last_autoanalyze,
> vacuum_count,
> autovacuum_count,
> analyze_count,
> autoanalyze_count
> from
> pg_stat_user_tables;
>
> Apparently the last four columns don't exist in my database. As for the
first four, that is somewhat illuminating. It looks like the
last_autovacuum that occurred on any of my tables was late Monday evening
(almost two days before I set autovacuum_enabled=off). The last_autoanalyze
on one of the tables where I set autovacuum_enabled=off was yesterday at
10:30, several hours before I disabled auto-vacuum. (I've had others since
then on tables where I didn't disable auto-vacuum.) It looks like
disabling auto-vacuum also disabled auto-analyze (did it?) but it also
looks like that might not have been the continuous problem I thought it was.

So if it's not auto-vacuuming that's making my inserts so slow, what is it?
I'm batching my inserts (that didn't seem to help at all actually, but
maybe cause I had already turned off synchronous_commit anyway). I've
gotten rid of a bunch of indices (especially those with low
cardinality–that I did around the same time as disabling auto-vacuum, so
that could account for the coincidental speed up). I'm not sure what else I
could be doing wrong. It's definitely better than it was a few days ago,
but I still see "LOG: duration: 77.315 ms statement: COMMIT" every minute
or two.

Thank you,
-Alessandro