Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 60A82B5DDA5 for ; Tue, 6 Sep 2011 05:40:59 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 43164-09 for ; Tue, 6 Sep 2011 08:40:53 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from aic.ai.wu-wien.ac.at (aic.ai.wu-wien.ac.at [137.208.51.5]) by mail.postgresql.org (Postfix) with ESMTP id 16E6AB5DCDF for ; Tue, 6 Sep 2011 05:40:53 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by aic.ai.wu-wien.ac.at (Postfix) with ESMTP id 0B2C02E5D2; Tue, 6 Sep 2011 10:40:52 +0200 (CEST) Received: from aic.ai.wu-wien.ac.at ([127.0.0.1]) by localhost (aic [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 20866-01; Tue, 6 Sep 2011 10:40:46 +0200 (CEST) Received: from [10.112.43.144] (081-003-214-196.yesss.at [81.3.214.196]) by aic.ai.wu-wien.ac.at (Postfix) with ESMTP id 37DF52E5D1; Tue, 6 Sep 2011 10:40:44 +0200 (CEST) Message-ID: <4E65DC88.3020206@ai.wu.ac.at> Date: Tue, 06 Sep 2011 10:40:40 +0200 From: Gerhard Wohlgenannt User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.18) Gecko/20110617 Thunderbird/3.1.11 MIME-Version: 1.0 To: Andy Colson Cc: Tomas Vondra , pgsql-performance@postgresql.org, Heinz-Peter Lang , Gerhard Wohlgenannt , "Weichselbraun, Albert" Subject: Re: Sudden drop in DBb performance References: <4E61D6B4.9090804@ai.wu.ac.at> <33fadeb6ac1bade5ef01bd08d1fd7807.squirrel@sq.gransy.com> <4E64D3D3.8000205@squeakycode.net> <4E64D7EA.3010704@ai.wu.ac.at> <4E64E816.80905@squeakycode.net> In-Reply-To: <4E64E816.80905@squeakycode.net> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new-20030616-p10 at ai.wu-wien.ac.at X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.13 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_SORBS_WEB=0.77 X-Spam-Level: X-Archive-Number: 201109/62 X-Sequence-Number: 44838 > 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