Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id D0C28B5DBEE for ; Thu, 11 Aug 2011 22:17:09 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 23024-06 for ; Fri, 12 Aug 2011 01:17:03 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.17.9]) by mail.postgresql.org (Postfix) with ESMTP id 9286DB5DBDC for ; Thu, 11 Aug 2011 22:17:03 -0300 (ADT) Received: from [192.168.1.6] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mrbap1) with ESMTP (Nemesis) id 0LaJZ8-1RbPzF06py-00lLIx; Fri, 12 Aug 2011 03:17:03 +0200 Message-ID: <4E447F0E.60208@2ndQuadrant.com> Date: Thu, 11 Aug 2011 21:17:02 -0400 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20110702 Icedove/3.0.11 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Recommended optimisations slows down PostgreSQL 8.4 References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Provags-ID: V02:K0:hgOxuWDWOJOUCMqlMIlQ82zTrBzBnyB18FQu5m3EYdb kV96A3iI2GHgdf1Cbb+yrMZUsFCTXUqsIV+9EPEEYE9z8cyqXp +dEkl2Txn/7DSliQf8NNtJKXJpaMXkiGyIvMNGaPk5R9AqKOMc C2LYsgX1MVNSIUUnpjFHCBULbU3bJ1lGTfbnqRbpCMrzrrO3mp zKeYpTzbHoliT95J65H1PlyQ4/lZmBz/78IvAjVyvg= 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, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201108/139 X-Sequence-Number: 44586 On 08/11/2011 07:35 PM, Waldo Nell wrote: > Please explain why the system is slower with the recommended values for these two settings? If the other parameters are at their defaults, the server is probably executing a checkpoint every few seconds running your test. I'd wager your log is filled with checkpoint warnings, about them executing too frequently. Each time a checkpoint happens, all of shared_buffers is dumped out. So if you increase shared_buffers, but don't space the checkpoints out more, it just ends up writing the same data over and over again. Using a smaller shared_buffers lets the OS deal with that problem instead, so it actually ends up being more efficient. Basically, you can't increase shared_buffers usefully without also increasing checkpoint_segments. All three of shared_buffers, wal_buffers, and checkpoint_segments have to go up before you'll see the expected benefit from raising any of them; you can't change a parameter at a time and expect an improvement. Try this: shared_buffers=512MB wal_buffers=16MB checkpoint_segments=64 And see how that does. If that finally beats your 50 minute record, you can see if further increase to shared_buffers and checkpoint_segments continue to help from there. Effective upper limits on your server are probably around 2GB for shared_buffers and around 256 for checkpoint_segments; they could be lower if your application uses a lot of transitory data (gets read/written once, so the database cache is no better than the OS one). -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us