Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 40FCBB5DBC2 for ; Sun, 19 Jun 2011 20:14:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 18553-08 for ; Sun, 19 Jun 2011 23:14:21 +0000 (UTC) X-Greylist: delayed 00:08:43.959593 by SQLgrey-1.7.6 Received: from mo2.mail-out.ovh.net (mo2.mail-out.ovh.net [178.32.228.2]) by mail.postgresql.org (Postfix) with ESMTP id E186AB5D801 for ; Sun, 19 Jun 2011 20:14:20 -0300 (ADT) Received: from mail172.ha.ovh.net (b6.ovh.net [213.186.33.56]) by mo2.mail-out.ovh.net (Postfix) with SMTP id 6E213DC0717 for ; Mon, 20 Jun 2011 01:05:44 +0200 (CEST) Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 20 Jun 2011 01:05:35 +0200 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13) (lists%peufeu.com@88.161.102.87) by ns0.ovh.net with SMTP; 20 Jun 2011 01:05:33 +0200 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: pgsql-performance@postgresql.org, "Kabu Taah" Subject: Re: Degrading PostgreSQL 8.4 write performance References: <20110617124857.1A82B432167@Bounce1.estpak.ee> Date: Mon, 20 Jun 2011 01:05:32 +0200 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit From: "Pierre C" Message-ID: In-Reply-To: <20110617124857.1A82B432167@Bounce1.estpak.ee> User-Agent: Opera Mail/11.10 (Linux) X-Ovh-Tracer-Id: 9276570808153015857 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N X-VR-SPAMSTATE: OK X-VR-SPAMSCORE: 0 X-VR-SPAMCAUSE: Vade Retro 01.322.02 AV+AS Profile: OVH; Bailout: 300; X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201106/165 X-Sequence-Number: 44098 > Load testing of postgresql 8.4 for OLTP application > suitability showed that throughput of the > database significantly degraded over time from thousands of write > transactions per second to almost zero. A typical postgres benchmarking gotcha is : - you start with empty tables - the benchmark fills them - query plans which were prepared based on stats of empty (or very small) tables become totally obsolete when the table sizes grow - therefore everything becomes very slow as the tables grow So you should disconnect/reconnect or issue a DISCARD ALL periodically on each connection, and of course periodically do some VACUUM ANALYZE (or have autovacuum do that for you).