I'd like to have a toolbox prepared for when performance goes south. I'm clueless. Would someone mind providing some detail about how to measure these four items Craig listed: 1. The first thing is to find out which query is taking a lot of time. 2. A long-running transaction keeps vacuum from working. 3. A table grows just enough to pass a threshold in the planner and a drastically different plan is generated. 4. An index has become bloated and/or corrupted, and you need to run the REINDEX command. Thx. On Wed, Aug 30, 2006 at 11:45:06AM -0700, Jeff Frost wrote: > On Wed, 30 Aug 2006, Joe McClintock wrote: > > >I ran a vacuum, analyze and reindex on the database with no change in > >performance, query time was still 37+ sec, a little worse. On our test > >system I found that a db_dump from production and then restore brought the > >database back to full performance. So in desperation I shut down the > >production application, backed up the production database, rename the > >production db, create a new empty production db and restored the > >production backup to the empty db. After a successful db restore and > >restart of the web application, everything was then up and running like a > >top. > > Joe, > > I would guess that since the dump/restore yielded good performance once > again, a VACUUM FULL would have also fixed the problem. How are your FSM > settings in the conf file? Can you run VACUUM VERBOSE and send us the last > 10 or so lines of output? > > A good article on FSM settings can be found here: > > http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10087&cNode=5K1C3W > > You probably should consider setting up autovacuum and definitely should > upgrade to at least 8.0.8 if not 8.1.4 when you get the chance. > > When you loaded the new data did you delete or update old data or was it > just a straight insert? > > -- > Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx> > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 650-780-7908 FAX: 650-649-1954 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- On Mon, Feb 19, 2007 at 10:02:46AM -0800, Craig A. James wrote: > Andreas Tille wrote: > >My web application was running fine for years without any problem > >and the performance was satisfying. Some months ago I added a > >table containing 4500000 data rows ... > > > >Since about two weeks the application became *drastically* slower > >and I urgently have to bring back the old performance. As I said > >I'm talking about functions accessing tables that did not increased > >over several years and should behave more or less the same. > > Don't assume that the big table you added is the source of the problem. It > might be, but more likely it's something else entirely. You indicated that > the problem didn't coincide with creating the large table. > > There are a number of recurring themes on this discussion group: > > * A long-running transaction keeps vacuum from working. > > * A table grows just enough to pass a threshold in the > planner and a drastically different plan is generated. > > * An index has become bloated and/or corrupted, and you > need to run the REINDEX command. > > And several other common problems. > > The first thing is to find out which query is taking a lot of time. I'm no > expert, but there have been several explanations on this forum recently how > to find your top time-consuming queries. Once you find them, then EXPLAIN > ANALYZE should get you started > Craig > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings -- You have no chance to survive make your time.
