Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ULadb-0003Vz-Hc for pgsql-performance@arkaria.postgresql.org; Fri, 29 Mar 2013 14:50:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1ULada-0002Bk-Tq for pgsql-performance@arkaria.postgresql.org; Fri, 29 Mar 2013 14:50:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ULadZ-0002Be-SY for pgsql-performance@postgresql.org; Fri, 29 Mar 2013 14:50:37 +0000 Received: from 62-2-77-205.static.cablecom.ch ([62.2.77.205] helo=mail.mnc.ch) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1ULadR-0006hY-I5 for pgsql-performance@postgresql.org; Fri, 29 Mar 2013 14:50:36 +0000 Received: from localhost (kolab.op.lan [127.0.0.1]) by mail.mnc.ch (Postfix) with ESMTP id 5393666162; Fri, 29 Mar 2013 15:50:28 +0100 (CET) X-Virus-Scanned: by amavisd-new at mnc.ch Received: from mail.mnc.ch ([127.0.0.1]) by localhost (mail.mnc.ch [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id s34Va0fVN-gl; Fri, 29 Mar 2013 15:50:27 +0100 (CET) Received: from localhost (kolab.op.lan [127.0.0.1]) by mail.mnc.ch (Postfix) with ESMTP id B2914666BE; Fri, 29 Mar 2013 15:50:27 +0100 (CET) Received: from lmeuh2.localdomain (16-183.199-178.cust.bluewin.ch [178.199.183.16]) (Authenticated sender: gc@mnc.ch) by mail.mnc.ch (Postfix) with ESMTP id 8E67166162; Fri, 29 Mar 2013 15:50:27 +0100 (CET) Received: by lmeuh2.localdomain (Postfix, from userid 500) id C261228018B; Fri, 29 Mar 2013 15:50:26 +0100 (CET) From: Guillaume Cottenceau To: Franck Routier Cc: postgres performance list Subject: Re: Postgresql performance degrading... how to diagnose the root cause References: <5155A33A.4090000@axege.com> Date: Fri, 29 Mar 2013 15:50:26 +0100 In-Reply-To: <5155A33A.4090000@axege.com> (Franck Routier's message of "Fri, 29 Mar 2013 15:20:42 +0100") Message-ID: User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Franck Routier 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