Re: How to debug performance problems

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(at)frostconsultingllc(dot)com>
> 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.