Re: High update activity, PostgreSQL vs BigDBMS

[Dave Cramer <pg(at)fastcrypt(dot)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