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

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