Please keep the list on CC: in your responses. Benjamin Dugast wrote: > 2014-07-18 13:11 GMT+02:00 Albe Laurenz <laurenz.albe@xxxxxxxxxx>: >> 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
