I was receiving warning messages of pgstat timeout.
I started looking for the solution for this problem and this doubt came out.
But it doesn't seem to be the problem.
Usually that just means that you are dirtying pages faster than your hard drives can write them out, leading to IO contention. Sometimes you can do something about this by changing the work-load to dirty pages in more compact regions, or by changing the configuration. Often the only effective thing you can do is buy more hard-drives.
I meant congestion, not contention.
Would it help if I changed the checkpoint_completion_target to something close to 0.7 (mine is the default 0.5)
Probably not. The practical difference between 0.5 and 0.7 is so small, that even if it did make a difference at one place and time, it would stop making a difference again in the future for no apparent reason.
or raise the checkpoint_segments (it is 32 now)?
Maybe. If you are dirtying the same set of pages over and over again, and that set of pages fits in shared_buffers, then lengthening the time between checkpoints could have a good effect. Otherwise, it probably would not.
Your best bet may be just to try it and see. But first, have you tried to tune shared_buffers?
What are you doing? Bulk loading? Frantically updating a small number of rows?
Do you have pg_xlog on a separate IO controller from the rest of the data? Do you have battery-backed/nonvolatile write cache?
Cheers,
Jeff
My shared_buffer is tuned for 25% of memory.
My pg_xlog is in the same device.
It is not bulk loading,
The battery I have to check, because the machine is not with me.