From: Guillaume Cottenceau <gc@mnc.ch>
To: Franck Routier <franck.routier@axege.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Postgresql performance degrading... how to diagnose the root cause
Date: Fri, 29 Mar 2013 15:50:26 +0100
Message-ID: <m3hajudzv1.fsf@mnc.ch> (raw)
In-Reply-To: <5155A33A.4090000@axege.com>
References: <5155A33A.4090000@axege.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Franck Routier <franck.routier 'at' axege.com> writes:
> Hi,
>
> I have a postgresql database (8.4) running in production whose
> performance is degrading.
> 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.
>
> So the problem seems to be limited to this database, even on newly
> created tables...
>
> What should I check to find the culprit of this degrading performance ?
I don't know that tcp-b does but it looks like bloat, provided
your comparison with the newly created database is using the same
amount of data in database. You may want to use this loose bloat
estimate:
http://wiki.postgresql.org/wiki/Show_database_bloat
and then use any preferred unbloat mechanism (vacuum full,
cluster, possibly also reindex), and in the long term better
configure some parameters (activate autovacuum if not already the
case, lower autovacuum_vacuum_cost_delay and raise
autovacuum_vacuum_cost_limit, raise max_fsm_* on your 8.4 or
upgrade to 9.x).
--
Guillaume Cottenceau
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: gc@mnc.ch, franck.routier@axege.com
Subject: Re: Postgresql performance degrading... how to diagnose the root cause
In-Reply-To: <m3hajudzv1.fsf@mnc.ch>
* 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