Re: Vacuum problems

Thanks for the advice Scott. I've taken out the vacuum fulls entirely.
I've now got a nightly vacuum analyze as well as reindex. I'll probably
drop both to every other night.

BTW, the database shrunk by 2 gigs just from reindexing last night. I
expect I'll see a performance gain from actually doing reindexing since
this database has never been reindexed since it was put in production 6
months ago.

I've got about 12 tables that get caught by the autoanalyze and about 6
that get caught by autovacuum on a daily basis. I'm not sure how often
the autovacuum and autoanalyze runs on those tables. I probably need to
up the logging to find out. I'm not worried about making it more
aggressive yet.

One other problem though... my database has a "-" in the name... when I
try to run:

psql -U postgres -d rms-prod -c "REINDEX SYSTEM rms-prod"

I get this:

ERROR: syntax error at or near "-"
LINE 1: REINDEX SYSTEM rms-prod

The user tables run fine. Should I reindex the system tables also? If
so, how do I get around the dash in the db name?

Thanks,

Scot Kreienkamp
La-Z-Boy Inc.
skreien(at)la-z-boy(dot)com
734-242-1444 ext 6379

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Monday, January 05, 2009 1:37 PM
To: Scot Kreienkamp
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Vacuum problems

On Mon, Jan 5, 2009 at 10:15 AM, Scot Kreienkamp <SKreien(at)la-z-boy(dot)com>
wrote:
> Scott,
>
> Would the "alter user postgres set statement_timeout=0;" be a
permanent
> change? I ask because our application is using that for its login to
> the database. (No lectures please, I inherited the system that way.
I
> already read the riot act to our developers about that.) If so I'll
> have to set it back after the vacuum is done.

Then you can just issue a "set statement_timeout=0" before you run
vacuum / vacuum full.

The update versus insert ratio isn't as important as how many rows are
updated out of the total between each run of vacuum analyze. Vacuum
full is definitely NOT a regular, recommended practice. I don't think
the docs really say it is. But a few other people have seemed to get
the same idea from the docs, so there must be some gray area I'm not
seeing when I read them. Given the usage pattern you described
earlier, I'd say vacuum full is definitely NOT called for, but regular
vacuum should be plenty.

The best thing to do is to examine how many dead tuples you've got to
keep track of, and if that number keeps rising then figure out if fsm
pages needs to be bumped up, and / or autovacuum needs more aggresive
settings. Note that autovacuum is kind of hand cuffed on pg versions
before 8.3 because it was single threaded, and one really big table
could throw it behind on other more frequently updated tables getting
bloated while the vacuum thread runs against that one large table.

Use vacuum verbose to get an idea of how many dead tuples there are in
the database, and see if they rise to a plateu, or just keep rising.
For most usage patterns with autovacuum enabled, you'll see a steady
rise to about 10-20% dead tuples then it should level off.

> FYI, when I inherited the system it was doing nightly vacuum fulls.
It
> was that way for several months. If that causes bloated indexes, then
> that's fairly likely a problem I have. Sounds like I should quit
> running vacuum fulls altogether except maybe once or twice per year.

A lot of times a pgsql doing nightly fulls is a sign of someone who
started out with an old version that only supported full vacuum and
applying the faulty knowledge they gained from there to the newer
version which likely doesn't need it.

If you do find one table that really needs full vacuums because of its
usage pattern, it's best to cron up a single vacuum (regular) to run
more often on it, or make autovacuum more aggresive, or, failing those
two, to make a regular nightly vacuum full / cluster / reindex for
that one relation.

Usually cluster is a better choice, as it doesn't bloat indexes and
puts the table into index order (on the index you clustered on).