X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C58525E46C4 for ; Sat, 7 Aug 2004 07:11:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68395-03 for ; Sat, 7 Aug 2004 10:11:50 +0000 (GMT) Received: from pns.mm.eutelsat.org (pns.mm.eutelsat.org [194.214.173.227]) by svr1.postgresql.org (Postfix) with ESMTP id 7E3825E46C1 for ; Sat, 7 Aug 2004 07:11:48 -0300 (ADT) Received: from nts-03.mm.eutelsat.org (localhost [127.0.0.1]) by pns.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id i77ABPd22251; Sat, 7 Aug 2004 12:11:25 +0200 Received: from [127.0.0.1] (accesspoint.mm.eutelsat.org [194.214.173.4]) by nts-03.mm.eutelsat.org (8.11.6/linuxconf) with ESMTP id i77ABoZ09457; Sat, 7 Aug 2004 12:11:50 +0200 Message-ID: <4114AABD.7050505@bigfoot.com> Date: Sat, 07 Aug 2004 12:11:09 +0200 From: Gaetano Mendola User-Agent: Mozilla Thunderbird 0.7.1 (Windows/20040626) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane , "pgsql-performance@postgresql.org" Subject: Re: Performance Bottleneck References: <410FDC53.5080509@bigfoot.com> <4110FFF6.1030001@bigfoot.com> <41140D17.7070101@ethereal-realms.org> <24609.1091836821@sss.pgh.pa.us> In-Reply-To: <24609.1091836821@sss.pgh.pa.us> X-Enigmail-Version: 0.84.2.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/95 X-Sequence-Number: 7731 Tom Lane wrote: > Martin Foster writes: > >>Gaetano Mendola wrote: >> >>>change this values in: >>>shared_buffers = 50000 >>>sort_mem = 16084 >>> >>>wal_buffers = 1500 > > > This value of wal_buffers is simply ridiculous. Instead I think is ridiculous a wal_buffers = 8 ( 64KB ) by default. > There isn't any reason to set wal_buffers higher than the amount of > WAL log data that will be generated by a single transaction, because > whatever is in the buffers will be flushed at transaction commit. > If you are mainly dealing with heavy concurrency then it's the mean time > between transaction commits that matters, and that's even less than the > average transaction length. I partially agree with you, tell me how decide that value without even now the typical queries, the tipical load ... nothing. I suggested to OP to keep the wal_buffers so high in order to eliminate one freedom of degree in his performance problems. You can see from following reply, ======================================================================== Gaetano Mendola wrote: Unfortunately there is no a "wizard tuning" for postgres so each one of us have a own "school". The data I gave you are oversized to be sure to achieve improvements. Now you can start to decrease these values ( starting from the wal_buffers ) in order to find the good compromise with your HW. ======================================================================== However wal_buffers = 1500 means ~12 MB that are not so expensive considering a server with 2GB of ram and I think that is a good compromise if you are not starving for RAM. I had a discussion about how fine tuning a postgres server with a client, my question was: are you planning to have someone that periodically take a look at your server activities in order to use your hardware at the best? Easy answer: No, because when the server is overloaded I will buy a bigger one that is less expensive that pay someone, considering also that shareolders prefer increase the capex that pay salaries ( if the company close the hardware can be selled :-( ). This is the real world out there. Regards Gaetano Mendola