Thread: Database performance post-VACUUM FULL
Hi all, We're using Postgresql 8.3.7 on Debian. We are seeing a very strange performance situation with our application which Iam hoping that someone can shed light on. Our tests find that our application runs quite well on 8.3.7 initially. The test consists of database creation followedby 30 cycles of creation and removal of approximately 1,000,000 rows (across all tables) per cycle. However, when database maintenance takes place (which consists of a VACUUM FULL operation, and some table REINDEX operations), subsequent cycle performance is more than 2x worse. What's more, after one VACUUM FULL operation has been done on the database, no subsequent VACUUM FULL operations *ever* seem to restore it to proper performance levels. We used the same general maintenance procedure with 8.2 and found that it worked as expected, so we were quite surprisedto discover this problem with 8.3.7. Anybody know what's going on? Thanks, Karl -- Karl Wright Software Engineer MetaCarta, Inc. 350 Massachusetts Avenue, 4th Floor, Cambridge, MA 02139 USA (617)-301-5511 www.metacarta.com <http://www.metacarta.com> Where to find it. This message may contain privileged, proprietary, and otherwise private information. If you are not the intended recipient, please notify the sender immediately.
On Fri, Sep 18, 2009 at 8:44 AM, Karl Wright <kwright@metacarta.com> wrote: > Hi all, > > We're using Postgresql 8.3.7 on Debian. We are seeing a very strange > performance situation with our application which I am hoping that someone > can shed light on. > > Our tests find that our application runs quite well on 8.3.7 initially. The > test consists of database creation followed by 30 cycles of creation and > removal of approximately 1,000,000 rows (across all tables) per cycle. > However, when database maintenance takes place (which consists of a VACUUM > FULL operation, and some table REINDEX operations), subsequent cycle > performance is more than 2x worse. What's more, after one VACUUM FULL > operation has been done on the database, no subsequent VACUUM FULL > operations *ever* seem to restore it to proper performance levels. > > We used the same general maintenance procedure with 8.2 and found that it > worked as expected, so we were quite surprised to discover this problem with > 8.3.7. Anybody know what's going on? Can you post to the list all the uncommented settings from your postgresql.conf, the output of VACUUM VERBOSE, and the output of EXPLAIN ANALYZE for some representative queries? ...Robert
Karl Wright <kwright@metacarta.com> wrote: > when database maintenance takes place (which consists of a VACUUM > FULL operation, and some table REINDEX operations) Besides providing the information requested by Robert, can you explain why you chose to use VACUUM FULL? The FULL option is only really useful in a small set of unusual use cases for recovery from serious problems. In most cases it will do more harm than good. If autovacuum isn't covering your need by itself, a VACUUM of the database, usually with the ANALYZE option and *possibly* with the FREEZE option, is almost always adequate, without resorting to the pain of VACUUM FULL. If you've run VACUUM FULL without a REINDEX of *all* indexes *after* the VACUUM FULL, you've probably seriously bloated your indexes. You may also have shuffled around the rows to positions where you're doing more random access than before. CLUSTER would be one way to fix both problems, although if you've bloated your system tables you might be best off recreating your database with the output from pg_dump.. But you might want to provide the information Robert requested to confirm the nature of the problem before attempting to fix it.... -Kevin