From: Gerhard Wohlgenannt <wohlg@ai.wu.ac.at>
To: Andy Colson <andy@squeakycode.net>
Cc: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org, Heinz-Peter Lang <heinz@langatium.net>
Cc: Gerhard Wohlgenannt <wohlg@ai.wu-wien.ac.at>
Cc: Weichselbraun, Albert <albert.weichselbraun@wu.ac.at>
Subject: Re: Sudden drop in DBb performance
Date: Tue, 06 Sep 2011 10:40:40 +0200
Message-ID: <4E65DC88.3020206@ai.wu.ac.at> (raw)
In-Reply-To: <4E64E816.80905@squeakycode.net>
References: <4E61D6B4.9090804@ai.wu.ac.at>
<33fadeb6ac1bade5ef01bd08d1fd7807.squirrel@sq.gransy.com>
<4E64D3D3.8000205@squeakycode.net>
<4E64D7EA.3010704@ai.wu.ac.at>
<cc2305e664bc05f72ce55eaf7c18db63.squirrel@sq.gransy.com>
<4E64E816.80905@squeakycode.net>
> That's a good thought, maybe the stats are old and you have bad
> plans? It could also be major updates to the data too (as opposed to
> growth).
we have made checks for number of dead tuples etc recently, but looks
ok. and as "everything" in the database seems to be very slow atm, I
guess the problem is not caused by bad plans for specific tables/queries.
>
> Gerhard, have you done an 'explain analyze' on any of your slow
> queries? Have you done an analyze lately?
>
yes we added the 'auto_explain' module to log/analyze queries >= 5000ms.
a sample result from the logs (there is lots of stuff in the logs, I
selected this query because it is very simple):
2011-09-06 04:00:35 CEST ANWEISUNG: INSERT into
keywords.table_x_site_impact (content_id, site_impact_id, site_impact)
VALUES (199083087, 1, 1.000000)
2011-09-06 04:00:35 CEST LOG: Dauer: 15159.723 ms Anweisung: INSERT
into keywords.table_x_site_impact (content_id, site_impact_id,
site_impact) VALUES (199083087, 1 , 1.000000)
2011-09-06 04:00:35 CEST LOG: duration: 15159.161 ms plan:
Result (cost=0.00..0.01 rows=1 width=0) (actual
time=0.017..0.019 rows=1 loops=1)
Output:
nextval('keywords.table_x_site_impact_internal_id_seq'::regclass),
199083087::bigint, 1::smallint, 1::double precision
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: wohlg@ai.wu.ac.at, andy@squeakycode.net, tv@fuzzy.cz, heinz@langatium.net, wohlg@ai.wu-wien.ac.at, albert.weichselbraun@wu.ac.at
Subject: Re: Sudden drop in DBb performance
In-Reply-To: <4E65DC88.3020206@ai.wu.ac.at>
* 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