From: | "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar> |
---|---|
To: | "'Waldomiro'" <waldomiro(at)shx(dot)com(dot)br> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Server Freezing |
Date: | 2009-12-01 15:50:34 |
Message-ID: | B724119EE9C94FD489C4610C38685190@iptel.com.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> -----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.
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Neill | 2009-12-01 18:52:13 | Order by (for 15 rows) adds 30 seconds to query time |
Previous Message | Waldomiro | 2009-12-01 11:18:54 | Re: Server Freezing |