Re: Segment best size

On Sun, Apr 14, 2013 at 7:34 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Saturday, April 13, 2013, Rodrigo Barboza wrote:
>
>>
>>
>>
>> On Sat, Apr 13, 2013 at 4:51 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>
>>> On Sat, Apr 13, 2013 at 10:29 AM, Rodrigo Barboza <
>>> rodrigombufrj(at)gmail(dot)com> wrote:
>>>
>>>>
>>>>
>>>> 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.