From: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: Rob Nagler <nagler@bivio.biz>
Cc: pgsql-performance@postgresql.org
Subject: Re: vacuum locking
Date: Fri, 17 Oct 2003 20:41:59 +0530
Message-ID: <3F9006BF.40906@persistent.co.in> (raw)
In-Reply-To: <16272.318.870000.93584@gargle.gargle.HOWL>
References: <16272.318.870000.93584@gargle.gargle.HOWL>
Rob Nagler wrote:
> It seems a simple "vacuum" (not full or analyze) slows down the
> database dramatically. I am running vacuum every 15 minutes, but it
> takes about 5 minutes to run even after a fresh import. Even with
> vacuuming every 15 minutes, I'm not sure vacuuming is working
> properly.
>
> There are a lot of updates. The slowest relation is the primary key
> index, which is composed of a sequence. I've appended a csv with the
> parsed output from vacuum. The page counts are growing way too fast
> imo. I believe this is caused by the updates, and index pages not
> getting re-used. The index values aren't changing, but other values
> in the table are.
You should try 7.4 beta and pg_autovacuum which is a contrib module in CVS tip.
It works with 7.3 as well.
Major reason for 7.4 is, it fixes index growth in vacuum. So if your database is
fit, it will stay that way with proper vacuuming.
>
> Any suggestions how to make vacuuming more effective and reducing the
> time it takes to vacuum? I'd settle for less frequent vacuuming or
> perhaps index rebuilding. The database can be re-imported in about an
> hour.
Make sure that you have FSM properly tuned. Bump it from defaults to suit your
needs. I hope you have gone thr. this page for general purpose setting.
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html>
> Rob
> ----------------------------------------------------------------
> Spacing every 15 minutes
> Pages,Tuples,Deleted
> 7974,1029258,1536
> 7979,1025951,4336
> 7979,1026129,52
> 7979,1025618,686
Assuming those were incremental figures, largest you have is ~8000 tuples per 15
minutes and 26 pages. I think with proper FSM/shared buffers/effective cache and
a pg_autovacuum with 1 min. polling interval, you could end up in lot better shape.
Let us know if it works.
Shridhar
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: shridhar_daithankar@persistent.co.in, nagler@bivio.biz
Subject: Re: vacuum locking
In-Reply-To: <3F9006BF.40906@persistent.co.in>
* 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