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

Search Discussions

  • Greg Smith at Jun 9, 2011 at 4:52 pm

    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
  • Reuven M. Lerner at Jun 11, 2011 at 10:38 pm
    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
  • Scott Marlowe at Jun 12, 2011 at 3:55 am

    On Sat, Jun 11, 2011 at 4:37 PM, Reuven M. Lerner 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?

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJun 9, '11 at 3:24p
activeJun 12, '11 at 3:55a
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase