Re: Blocking every 20 sec while mass copying.

Please keep the list on CC: in your responses.

Benjamin Dugast wrote:
> 2014-07-18 13:11 GMT+02:00 Albe Laurenz <laurenz.albe@wien.gv.at>:

>> This sounds a lot like checkpoint I/O spikes.
>>
>> Check with the database server log if the freezes coincide with checkpoints.
>>
>> You can increase checkpoint_segments when you load data to have them occur less often.
>>
>> If you are on Linux and you have a lot of memory, you might hit spikes because too
>> much dirty data are cached; check /proc/sys/vm/dirty_ratio and /proc/sys/dirty_background_ratio.

> The checkpoint_segments is set to 64 already
>
> the dirty_ration was set by default to 10 i put it down to 5
> the dirty_background_ratio was set to 5 and I changed it to 2
>
> There is less freezes but the insert is so slower than before.

That seems to indicate that my suspicion was right.

I would say that your I/O system is saturated.
Have you checked with "iostat -mNx 1"?

If you really cannot drop the indexes during loading, there's probably not much more
you can do to speed up the load.
You can try to increase checkpoint_segments beyond 64 and see if that buys you anything.

Tuning the file system write cache will not reduce the amount of I/O necessary, but it
should reduce the spikes (which is what I thought was your problem).

Yours,
Laurenz Albe