Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E45639FBB36 for ; Mon, 8 Jan 2007 00:26:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 27872-01-4 for ; Mon, 8 Jan 2007 00:26:46 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from vms048pub.verizon.net (vms048pub.verizon.net [206.46.252.48]) by postgresql.org (Postfix) with ESMTP id E405D9FBB14 for ; Mon, 8 Jan 2007 00:26:08 -0400 (AST) Received: from [192.168.1.60] ([72.66.94.209]) by vms048.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JBJ00JMF8BEDTSI@vms048.mailsrvcs.net> for pgsql-performance@postgresql.org; Sun, 07 Jan 2007 22:26:03 -0600 (CST) Date: Sun, 07 Jan 2007 23:26:01 -0500 From: Guy Rouillier Subject: Re: High update activity, PostgreSQL vs BigDBMS In-reply-to: <86E1D954-72CB-4744-BF53-A7CB57623D83@fastcrypt.com> To: PostgreSQL Performance Message-id: <45A1C7D9.90502@burntmail.com> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7bit References: <45935A49.6080602@burntmail.com> <459F0EA6.30403@burntmail.com> <45A077FB.3010802@burntmail.com> <86E1D954-72CB-4744-BF53-A7CB57623D83@fastcrypt.com> User-Agent: Thunderbird 1.5.0.9 (X11/20061230) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.77 tagged_above=0 required=5 tests=AWL, BAYES_40, SPF_SOFTFAIL X-Spam-Level: X-Archive-Number: 200701/92 X-Sequence-Number: 22693 Dave Cramer wrote: > > On 6-Jan-07, at 11:32 PM, Guy Rouillier wrote: > >> Dave Cramer wrote: >> >>>> >>>> The box has 3 GB of memory. I would think that BigDBMS would be >>>> hurt by this more than PG. Here are the settings I've modified in >>>> postgresql.conf: >>> As I said you need to set shared_buffers to at least 750MB this is >>> the starting point, it can actually go higher. Additionally effective >>> cache should be set to 2.25 G turning fsync is not a real world >>> situation. Additional tuning of file systems can provide some gain, >>> however as Craig pointed out some queries may need to be tweaked. >> >> Dave, thanks for the hard numbers, I'll try them. I agree turning >> fsync off is not a production option. In another reply to my original >> posting, Alex mentioned that BigDBMS gets an advantage from its async >> IO. So simply as a test, I turned fsync off in an attempt to open >> wide all the pipes. >> >> Regarding shared_buffers=750MB, the last discussions I remember on >> this subject said that anything over 10,000 (8K buffers = 80 MB) had >> unproven benefits. So I'm surprised to see such a large value >> suggested. I'll certainly give it a try and see what happens. > > That is 25% of your available memory. This is just a starting point. > There are reports that going as high as 50% can be advantageous, however > you need to measure it yourself. Ok, I ran with the settings below, but with shared_buffers=768MB effective_cache_size=2048MB fsync=on This run took 29000 seconds. I'm beginning to think configuration changes are not going to buy significant additional improvement. Time to look at the app implementation. > >> >>>> >>>> autovacuum=on >>>> stats_row_level = on >>>> max_connections = 10 >>>> listen_addresses = 'db01,localhost' >>>> shared_buffers = 128MB >>>> work_mem = 16MB >>>> maintenance_work_mem = 64MB >>>> temp_buffers = 32MB >>>> max_fsm_pages = 204800 >>>> checkpoint_segments = 30 >>>> redirect_stderr = on >>>> log_line_prefix = '%t %d' >> --Guy Rouillier >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org >> > -- Guy Rouillier