On Tue, 1 Apr 2008, mark wrote:
current settings all default #checkpoint_segments = 3 #checkpoint_timeout = 5min #checkpoint_completion_target = 0.5 #checkpoint_warning = 30sthis is what I have on pg_stat_bgwriter ; how much should I increase checkpoint_segment & checkpoint_completion_target to? thanks a lot! postgres=# select * from pg_stat_bgwriter; checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | 42 | 1051 | 8035125 | 21954 | maxwritten_clean | buffers_backend | buffers_alloc 42 | 241519 | 10394696
That means that 96% of the time, you are hitting a checkpoint because of activity in less than 5 minutes after the previous one. And 97% of the dirty buffers written out are being done by the checkpoint process. Your system is spending its entire life doing checkpoint work and it's no wonder response time is slow quite regularly.
Increasing checkpoint_segments uses up more disk space for the WAL files and increases the amount of time it will take to recover from a crash. If neither of those two things matter to you, you can easily bump that parameter up to 100 or more. I'd suggest starting with an order of magnitude change and increase from 3 to 30. You have to restart the server to make that change stick. It's impossible to predict how much that will change your situation, to know if that's enough or you really need to increase it further.
-- * Greg Smith gsmith@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
