Обсуждение: Triggering autovacuum

Hi, everyone.  Some people with whom I'm working, and who have an 8.3
system running under Windows, asked me to look into their performance
issues.  They have a 1.5 GB database with a few dozen tables, and
500,000 records at most.  They said that their system has been running
for a few days, doing lots of INSERTs and SELECTs, and that the
performance has gotten worse and worse over time.  (I don't have numbers
to share.)  It's true that the computer is being used by other processes
as part of a black-box manufacturing system, but those are pretty
constant in CPU, disk, and memory needs, so I don't think that we would
expect to see degradation over time as a result of that work.

I looked at the system, and found that we need to change
effective_cache_size, such that it'll match the "system cache" number in
the Windows performance monitor.   So yes, we'll take care of that, and
I expect to see some improvement.

But the really surprising thing to me was that autovacuum hadn't run at
all in the last three days.  I checked, and the "autovacuum" parameter
was set in postgresql.conf, and using "show" in psql shows me that it
was set.  But when I looked at pg_stat_user_tables, there was no
indication of autovacuum *ever* having run.    We also fail to see any
autovacuum processes in the Windows process listing.

Could this be because we're only doing INSERTs and SELECTs?  In such a
case, then we would never reach the threshold of modified tuples that
autovacuum looks for, and thus it would never run.  That would, by my
reasoning, mean that we'll never tag dead tuples (which isn't a big deal
if we're never deleting or updating rows), but also that we'll never run
ANALYZE as part of autovacuum.  Which would mean that we'd be running
with out-of-date statistics.

I ran a manual "vacuum analyze", by the way, and it's taking a really
long time (1.5 hours, as of this writing) to run, but it's clearly doing
something.  Moreover, when we went to check on our vacuum process after
about an hour, we saw that autovacuum had kicked in, and was now
running.  Could it be that our manual invocation of vacuum led to
autovacuum running?

I have a feeling that our solution is going to have to involve a cron
type of job, running vacuum at regular intervals (like in the bad old
days), because autovacuum won't get triggered.  But hey, if anyone has
any pointers to offer on this topic, I'd certainly appreciate it.

Reuven

--
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner

Reuven M. Lerner wrote:
> Could this be because we're only doing INSERTs and SELECTs?  In such a
> case, then we would never reach the threshold of modified tuples that
> autovacuum looks for, and thus it would never run.  That would, by my
> reasoning, mean that we'll never tag dead tuples (which isn't a big
> deal if we're never deleting or updating rows), but also that we'll
> never run ANALYZE as part of autovacuum.  Which would mean that we'd
> be running with out-of-date statistics.

The computation for whether the auto-analyze portion of autovacuum runs
takes into account INSERT traffic, so the stats don't go too far out of
data on this style of workload.  The one for the vacuum work only
considers dead rows.  So your case should be seeing regular entries for
the last auto-analyze, but possibly not for last auto-vacuum.

Eventually autovacuum will kick in anyway for transaction id wraparound,
and that might be traumatic when it does happen.  You might want to
schedule periodic manual vacuum on these tables to at least have that
happen at a good time.  Wraparound autovacuum has this bad habit of
finally kicking in only during periods of peak busy on the server.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

Hi, Greg.  Thanks for the quick and useful answer, even if it means that
my hopes for a quick fix have been dashed.  I guess I'll need to do some
actual monitoring, then...

Reuven

On Sat, Jun 11, 2011 at 4:37 PM, Reuven M. Lerner <reuven@lerner.co.il> wrote:
> Hi, Greg.  Thanks for the quick and useful answer, even if it means that my
> hopes for a quick fix have been dashed.  I guess I'll need to do some actual
> monitoring, then...

You mention pg_stat_user_tables, what did the last_analyze column for
those tables say?