From: Craig A. James <cjames@modgraph-usa.com>
To: Andreas Tille <tillea@rki.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to debug performance problems
Date: Mon, 19 Feb 2007 10:02:46 -0800
Message-ID: <45D9E646.6060909@modgraph-usa.com> (raw)
In-Reply-To: <Pine.LNX.4.62.0702191150020.15820@wr-linux02>
References: <Pine.LNX.4.62.0702191150020.15820@wr-linux02>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cjames@modgraph-usa.com, tillea@rki.de
Subject: Re: How to debug performance problems
In-Reply-To: <45D9E646.6060909@modgraph-usa.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox