Skip site navigation (1) Skip section navigation (2)

Re: How to debug performance problems

From: "Craig A(dot) James" <cjames(at)modgraph-usa(dot)com>
To: Andreas Tille <tillea(at)rki(dot)de>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: How to debug performance problems
Date: 2007-02-19 18:02:46
Message-ID: 45D9E646.6060909@modgraph-usa.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Jeff DavisDate: 2007-02-19 18:18:27
Subject: Re: How to debug performance problems
Previous:From: Reinhard VicinusDate: 2007-02-19 17:03:22
Subject: Query Optimization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group