pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql performance degrading... how to diagnose the root cause
9+ messages / 5 participants
[nested] [flat]
* Postgresql performance degrading... how to diagnose the root cause
@ 2013-03-29 14:20 Franck Routier <franck.routier@axege.com>
2013-03-29 14:42 ` Re: Postgresql performance degrading... how to diagnose the root cause Julien Cigar <jcigar@ulb.ac.be>
2013-03-29 14:50 ` Re: Postgresql performance degrading... how to diagnose the root cause Guillaume Cottenceau <gc@mnc.ch>
2013-03-29 23:23 ` Re: Postgresql performance degrading... how to diagnose the root cause Jeff Janes <jeff.janes@gmail.com>
2013-04-16 09:16 ` Re: Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
0 siblings, 4 replies; 9+ messages in thread
From: Franck Routier @ 2013-03-29 14:20 UTC (permalink / raw)
To: pgsql-performance
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
Attachments:
smime.p7s (application/pkcs7-signature, 4.4K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql performance degrading... how to diagnose the root cause
2013-03-29 14:20 Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
@ 2013-03-29 14:42 ` Julien Cigar <jcigar@ulb.ac.be>
3 siblings, 0 replies; 9+ messages in thread
From: Julien Cigar @ 2013-03-29 14:42 UTC (permalink / raw)
To: pgsql-performance
On 03/29/2013 15:20, Franck Routier wrote:
> Hi,
>
Hello,
> 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, ...)
> Franck
>
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql performance degrading... how to diagnose the root cause
2013-03-29 14:20 Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
@ 2013-03-29 14:50 ` Guillaume Cottenceau <gc@mnc.ch>
2013-03-29 15:31 ` Re: Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
3 siblings, 1 reply; 9+ messages in thread
From: Guillaume Cottenceau @ 2013-03-29 14:50 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql performance degrading... how to diagnose the root cause
2013-03-29 14:20 Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
2013-03-29 14:50 ` Re: Postgresql performance degrading... how to diagnose the root cause Guillaume Cottenceau <gc@mnc.ch>
@ 2013-03-29 15:31 ` Franck Routier <franck.routier@axege.com>
2013-03-30 00:02 ` Re: Postgresql performance degrading... how to diagnose the root cause Guillaume Cottenceau <gc@mnc.ch>
2013-03-30 12:57 ` Re: Postgresql performance degrading... how to diagnose the root cause Cédric Villemain <cedric@2ndquadrant.com>
2013-03-30 19:00 ` Re: Postgresql performance degrading... how to diagnose the root cause Jeff Janes <jeff.janes@gmail.com>
0 siblings, 3 replies; 9+ messages in thread
From: Franck Routier @ 2013-03-29 15:31 UTC (permalink / raw)
To: Guillaume Cottenceau <gc@mnc.ch>; +Cc: pgsql-performance
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...).
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...
>
> 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
Attachments:
smime.p7s (application/pkcs7-signature, 4.4K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql performance degrading... how to diagnose the root cause
2013-03-29 14:20 Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
2013-03-29 14:50 ` Re: Postgresql performance degrading... how to diagnose the root cause Guillaume Cottenceau <gc@mnc.ch>
2013-03-29 15:31 ` Re: Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
@ 2013-03-30 00:02 ` Guillaume Cottenceau <gc@mnc.ch>
2 siblings, 0 replies; 9+ messages in thread
From: Guillaume Cottenceau @ 2013-03-30 00:02 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
Franck Routier <franck.routier 'at' axege.com> writes:
>> 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.
--
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql performance degrading... how to diagnose the root cause
2013-03-29 14:20 Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
2013-03-29 14:50 ` Re: Postgresql performance degrading... how to diagnose the root cause Guillaume Cottenceau <gc@mnc.ch>
2013-03-29 15:31 ` Re: Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
@ 2013-03-30 12:57 ` Cédric Villemain <cedric@2ndquadrant.com>
2 siblings, 0 replies; 9+ messages in thread
From: Cédric Villemain @ 2013-03-30 12:57 UTC (permalink / raw)
To: pgsql-performance; +Cc: Franck Routier <franck.routier@axege.com>; Guillaume Cottenceau <gc@mnc.ch>
> > 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
Attachments:
signature.asc (application/pgp-signature, 489B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql performance degrading... how to diagnose the root cause
2013-03-29 14:20 Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
2013-03-29 14:50 ` Re: Postgresql performance degrading... how to diagnose the root cause Guillaume Cottenceau <gc@mnc.ch>
2013-03-29 15:31 ` Re: Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
@ 2013-03-30 19:00 ` Jeff Janes <jeff.janes@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Jeff Janes @ 2013-03-30 19:00 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: Guillaume Cottenceau <gc@mnc.ch>; pgsql-performance
On Fri, Mar 29, 2013 at 8:31 AM, Franck Routier <franck.routier@axege.com>wrote:
> 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?
> 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?
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql performance degrading... how to diagnose the root cause
2013-03-29 14:20 Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
@ 2013-03-29 23:23 ` Jeff Janes <jeff.janes@gmail.com>
3 siblings, 0 replies; 9+ messages in thread
From: Jeff Janes @ 2013-03-29 23:23 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
On Fri, Mar 29, 2013 at 7:20 AM, Franck Routier <franck.routier@axege.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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Postgresql performance degrading... how to diagnose the root cause
2013-03-29 14:20 Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
@ 2013-04-16 09:16 ` Franck Routier <franck.routier@axege.com>
3 siblings, 0 replies; 9+ messages in thread
From: Franck Routier @ 2013-04-16 09:16 UTC (permalink / raw)
To: pgsql-performance
Le 29/03/2013 15:20, Franck Routier a écrit :
> 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
>
Just for the record, the problem turned out to be a too high
default_statistics_target (4000) that was causing the planner to take up
to seconds just to evaluate the better plan (probably in eqjoinsel() ).
See thread titled "What happens between end of explain analyze and end
of query execution ?" on this list for details.
Thanks again to those who responded.
Franck
Attachments:
smime.p7s (application/pkcs7-signature, 4.4K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2013-04-16 09:16 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-03-29 14:20 Postgresql performance degrading... how to diagnose the root cause Franck Routier <franck.routier@axege.com>
2013-03-29 14:42 ` Julien Cigar <jcigar@ulb.ac.be>
2013-03-29 14:50 ` Guillaume Cottenceau <gc@mnc.ch>
2013-03-29 15:31 ` Franck Routier <franck.routier@axege.com>
2013-03-30 00:02 ` Guillaume Cottenceau <gc@mnc.ch>
2013-03-30 12:57 ` Cédric Villemain <cedric@2ndquadrant.com>
2013-03-30 19:00 ` Jeff Janes <jeff.janes@gmail.com>
2013-03-29 23:23 ` Jeff Janes <jeff.janes@gmail.com>
2013-04-16 09:16 ` Franck Routier <franck.routier@axege.com>
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