Re: Server Freezing

 

> -----Mensaje original-----
> De: Waldomiro
> 
> I´m using PostgreSQL 8.1, 

Sorry, log_checkpoints isn't supported till 8.3

> and my settings are:
> 
> checkpoint_segments=50
> checkpoint_timeout=300
> checkpoint_warning=30
> commit_delay=0
> commit_siblings=5
> archive_command= cp -i %p/BACKUP/LOGS/%f autovacuum=off
> bgwriter_all_maxpages=5
> bgwriter_all_percent=0.333
> bgwriter_delay=200
> bgwriter_lru_maxpages=5
> bgwriter_lru_percent=1
> fsync=on
> full_page_writes=on
> stats_block_level=on
> stats_command_string=on
> stats_reset_on_server_start=off
> stats_row_level=on
> stats_start_collector=on
> 

As tempting as it is to decrease checkpoint_segments, better confirm it is a
checkpoint related problem before fiddling with these settings.

I recommend reading Greg Smith's post on checkpoints & bg writer. It's about
8.3 improvements but it includes good advice on how to diagnose checkpoint
issues on prior versions:
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm

In fact, one of his recomendations should be very helpful here: set
checkpoint_warning=3600 and log_min_duration_statement=1000, that way you
should see in the log if statements over 1 sec occur simultaneously with
checkpoints being reached.

Pay attention to the chapter on the bg_writer too.

Regards,
Fernando.