Performance Problems

Are you sure that is the exact command line you are using for
autovacuum? I'm not sure it will work like that, I believe the the -s
and -S options require a value to be given.

Anyway, a few things you can do. It sounds like the table isn't getting
vacuumed frequently enough for you, you need to play with the autovacuum
thresholds and make them more aggressive, or you need to frequently
issue vacuum commands from cron in addition to autovacuum. You can also
upgrade to 8.1.x since that has integrated autovacuum and allows you to
set a more aggressive vacuuming policy on a per table basis.

Matt

Warren wrote:

On Mon, 2006-02-20 at 14:12, Warren wrote:

I have one table that gets slower and slower over time. It has a lot of
UPDATES INSERTS and DELETES run on it. It may have as many as 20,000 rows at
any given time. I am running autovacuum using the following command line:

/usr/local/pgsql/bin/pg_autovacuum -D -s -S -L
/usr/local/pgsql/log/autovacuum -U postgres -P password -H 127.0.0.1 -p 5432

It does speed back up after I do a full vacuum on it. What can I do to keep
the performance consistent.

You likely don't have high enough FSM settings.

Run your database for a while (24 hours or so) without running a vacuum
full. Regular vacuums are fine.

Then, by hand, run vacuum verbose and it will tell you how many extra
pages / slots you need.

On Mon, Feb 20, 2006 at 02:38:08PM -0600, Scott Marlowe wrote:

On Mon, 2006-02-20 at 14:12, Warren wrote:

I have one table that gets slower and slower over time. It has a lot of
UPDATES INSERTS and DELETES run on it. It may have as many as 20,000 rows at
any given time. I am running autovacuum using the following command line:

/usr/local/pgsql/bin/pg_autovacuum -D -s -S -L
/usr/local/pgsql/log/autovacuum -U postgres -P password -H 127.0.0.1 -p 5432

It does speed back up after I do a full vacuum on it. What can I do to keep
the performance consistent.

You likely don't have high enough FSM settings.

Run your database for a while (24 hours or so) without running a vacuum
full. Regular vacuums are fine.

Then, by hand, run vacuum verbose and it will tell you how many extra
pages / slots you need.

http://www.pervasivepostgres.com/lp/newsletters/2005/Insights_opensource_Nov.asp#3
has some more info on that. Also, pg_autovacuum's default scaling
factors are *way* too high. I'd recommend setting them to 0.2 for
vacuums and 0.1 for analyze. You might also want to drop the thresholds;
something closer to 200-300 for vacuum.

Another option given the size of that table is to vacuum just it every
minute or so from crontab.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461