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

[Franck Routier]

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 ?

Franck

[Julien Cigar]

Post by Franck Routier
Hi,

Hello,

Post by Franck Routier
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 ?

Difficult to answer with so few details, but I would start by logging
slow queries, and run an explain analyze on them (or use auto_explain).
Check if you're CPU bound or I/O bound (top, iostats, vmstat, systat,
gstat..), check your configuration (shared_buffers,
effective_cache_size, work_mem, checkpoint_segments, cpu_tuple_cost, ...)

Post by Franck Routier
Franck

--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.

[Guillaume Cottenceau]

Post by Franck Routier
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).

[Franck Routier]

Hi,

Post by Guillaume Cottenceau
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...).

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.
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...

Post by Guillaume Cottenceau
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 ?

Franck

[Guillaume Cottenceau]

Post by Franck Routier

Post by Guillaume Cottenceau
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 ?

I am not very used to this, but I'd start by comparing the top
result in your established DB against the top result in your
fresh DB. What does it say? The wiki page says it is a loose
estimate, however, unusually larger tbloat and/or wastedbytes
might be an indication.

Of course, if you can afford it, a good old VACUUM FULL ANALYZE
VERBOSE would tell you how many pages were reclaimed while
rewriting the table. Otherwise, VACUUM VERBOSE on both the
established DB and a backup/restore on a fresh DB also provide a
helpful comparison of how many pages are used for suspected
tables.

[Cédric Villemain]

Post by Franck Routier

Post by Guillaume Cottenceau
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.

Post by Franck Routier
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?

Post by Franck Routier
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...

Post by Guillaume Cottenceau
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...)

[Jeff Janes]

Post by Guillaume Cottenceau
Hi,
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...).

I took a quick look at that implementation, and I can't make heads nor
tails of it. It is just a lit of .java files. There is no documentation,
README, instructions, or example usage. Am I missing something? How do I
run it, and tell it what scale to use and what database to connect to?

Post by Guillaume Cottenceau
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.
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%),

Which column of the iostat output is that coming from?

Post by Guillaume Cottenceau
top shows only one active core, and load average is well under 1...

Can you show the first few rows of the top output?

Cheers,

Jeff

[Jeff Janes]

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