From: Dave Cramer <pg@fastcrypt.com>
To: Guy Rouillier <guyr-ml1@burntmail.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: High update activity, PostgreSQL vs BigDBMS
Date: Sun, 7 Jan 2007 10:29:02 -0500
Message-ID: <86E1D954-72CB-4744-BF53-A7CB57623D83@fastcrypt.com> (raw)
In-Reply-To: <45A077FB.3010802@burntmail.com>
References: <45935A49.6080602@burntmail.com>
<bf54be870612280106n1247520dtb48e10cb4bdc43e9@mail.gmail.com>
<459F0EA6.30403@burntmail.com>
<BB4FC28F-BDC1-424F-B330-EE9204404F1A@fastcrypt.com>
<45A077FB.3010802@burntmail.com>
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.
>
>>>
>>> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pg@fastcrypt.com, guyr-ml1@burntmail.com
Subject: Re: High update activity, PostgreSQL vs BigDBMS
In-Reply-To: <86E1D954-72CB-4744-BF53-A7CB57623D83@fastcrypt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox