Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 198529FA339 for ; Sun, 7 Jan 2007 00:33:05 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 49008-03 for ; Sun, 7 Jan 2007 00:33:01 -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 B0D009F9FB2 for ; Sun, 7 Jan 2007 00:32:59 -0400 (AST) Received: from [127.0.0.1] ([72.66.94.209]) by vms048.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JBH006KGDYY53T7@vms048.mailsrvcs.net> for pgsql-performance@postgresql.org; Sat, 06 Jan 2007 22:32:59 -0600 (CST) Date: Sat, 06 Jan 2007 23:32:59 -0500 From: Guy Rouillier Subject: Re: High update activity, PostgreSQL vs BigDBMS In-reply-to: To: PostgreSQL Performance Message-id: <45A077FB.3010802@burntmail.com> MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1; format=flowed Content-transfer-encoding: 7bit X-Antivirus: avast! (VPS 0701-1, 01/05/2007), Outbound message X-Antivirus-Status: Clean References: <45935A49.6080602@burntmail.com> <459F0EA6.30403@burntmail.com> User-Agent: Thunderbird 1.5.0.9 (Windows/20061207) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.864 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_SOFTFAIL X-Spam-Level: X-Archive-Number: 200701/81 X-Sequence-Number: 22682 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. >> >> 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