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

> > I don't know that tcp-b does
>
> tpcb.jar is a java implementation of the http://www.tpc.org/tpcb/
> benchmark. It is not particularly representative of my workload, but
> gives a synthetic, db-agnostic, view of the system performance.
> We use it to have quick view to compare differents servers (different
> OS, different RDBMS, etc...).

For information, pgbench is a sort of limited TPC-B benchmark.

> That said, the test wil create tables, load them with data, and perform
> some transactions on them.
> The point that makes me wonder what happens, is that the test run on my
> main database is slow, while the same test run on a database on its own
> is quick.

Do you mean when you run it against already existing data vs its own TPC-B DB?

> This is the same postgresql cluster (same postgresql.conf), same
> tablespace (so same disks), same hardware obviously.
>
> Regarding the server activity, it seems quite flat : iostat shows that
> disks are not working much (less than 5%), top shows only one active
> core, and load average is well under 1...
>
> > http://wiki.postgresql.org/wiki/Show_database_bloat
>
> How do I interpret the output of this query ? Is 1.1 bloat level on a
> table alarming, or quite ok ?

quite ok. The threshold for maintenance task is around 20%.
I wonder about your system catalogs (pg_type, pg_attribute, ...)

You can use low level tool provided by PostgreSQL to help figure what's going
wrong.
pg_buffercache, pg_stattuple come first to explore your cached data and the
block content.

Or some weird database configuration ? (parameters in PostgreSQL can be set
per DB, per role, etc...)
--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation