Re: Postgresql performance degrading... how to diagnose the root cause

On Fri, Mar 29, 2013 at 7:20 AM, Franck Routier <franck(dot)routier(at)axege(dot)com>wrote:

> Hi,
>
> I have a postgresql database (8.4) running in production whose performance
> is degrading.
>

There have been substantial improvements in performance monitoring in newer
versions, so using 8.4 limits your options.

> There is no single query that underperforms, all queries do.
> Another interesting point is that a generic performance test (
> https://launchpad.net/tpc-b) gives mediocre peformance when run on the
> database, BUT the same test on a newly created database, on the same pg
> cluster, on the same tablespace, does perform good.
>

Is the server still running its production workload while you do these
test, or are you running it on a clone or during off-peak hours? If the
former, then if you do your test in a clone which has no load other than
the benchmark, do you still see the same thing.

Also, have you tried running pgbench, which also has a tpc-b-ish workload?
People on this list will probably be more familiar with that than with the
one you offer. What was the size of the test set (and your RAM) and the
number of concurrent connections it tests?

Cheers,

Jeff