Обсуждение: Performance Problems
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. Thanks, Warren Bell
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: > 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. > > Thanks, > > Warren Bell > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
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