X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 223D99FB3B2 for ; Mon, 28 Aug 2006 05:15:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 29512-06 for ; Mon, 28 Aug 2006 05:15:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 6FD929FCDC6 for ; Mon, 28 Aug 2006 05:15:01 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mail.mnc.ch (62-2-77-205.static.cablecom.ch [62.2.77.205]) by svr4.postgresql.org (Postfix) with ESMTP id 4EFA95AF8AA for ; Mon, 28 Aug 2006 08:14:59 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by mail.mnc.ch (Postfix) with ESMTP id 468F8A82F0 for ; Mon, 28 Aug 2006 10:20:17 +0200 (CEST) Received: from mail.mnc.ch ([127.0.0.1]) by localhost (mail.test.lan [127.0.0.1]) (amavisd-new, port 10025) with ESMTP id 06473-06 for ; Mon, 28 Aug 2006 10:20:15 +0200 (CEST) Received: from meuh.mnc.lan (62-2-77-204.static.cablecom.ch [62.2.77.204]) by mail.mnc.ch (Postfix) with ESMTP id E3681A82CB for ; Mon, 28 Aug 2006 10:20:15 +0200 (CEST) Received: by meuh.mnc.lan (Postfix, from userid 1000) id E2A651835C3; Mon, 28 Aug 2006 10:14:52 +0200 (CEST) To: pgsql-performance@postgresql.org Subject: perf pb solved only after pg_dump and restore X-Hashcash: 1:20:060828:pgsql-performance@postgresql.org::2DQz8Uo3rXJOpqVo:000000000000000000000000000002WxP From: Guillaume Cottenceau Date: 28 Aug 2006 10:14:52 +0200 Message-ID: <8764gd1dkz.fsf@meuh.mnc.lan> Lines: 27 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: amavisd-new at mnc.ch X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.135 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200608/420 X-Sequence-Number: 20605 Hi, We noticed a slowdown on our application while traffic was kinda heavy. The logics after reading the docs commanded us to trim the enlarged tables, run VACUUM ANALYZE and then expect fast performance again; but it wasn't the case[1]. Out of the blue, we dumped the database, removed it, recreated from the restore, and now the performance is lightning fast again. Does it look familiar to anyone? I thought running VACUUM ANALYZE after a trim should be enough so that pg has assembled the data and has good statistical knowledge of the tables contents.. Thanks for any tips. Ref: [1] Processes were always showing one/some postmaster on SELECT, a constant load of 1, and vmstat always showing activity in IO blocks out (application generate all sort of typical statements, some SELECT, UPDATE, INSERT either "directly" or through stored procedures) -- Guillaume Cottenceau Create your personal SMS or WAP Service - visit http://mobilefriends.ch/