From: Reuven M. Lerner <reuven@lerner.co.il>
To: pgsql-performance@postgresql.org
Subject: Triggering autovacuum
Date: Thu, 09 Jun 2011 18:24:16 +0300
Message-ID: <4DF0E5A0.7080101@lerner.co.il> (raw)
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
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: reuven@lerner.co.il
Subject: Re: Triggering autovacuum
In-Reply-To: <4DF0E5A0.7080101@lerner.co.il>
* 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