Re: High update activity, PostgreSQL vs BigDBMS

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