Re: Blocking every 20 sec while mass copying.

Benjamin Dugast wrote:
> I'm working on Postgres 9.3.4 for a project.
>
>
> We are using Scala, Akka and JDBC to insert data in the database, we have around 25M insert to do
> which are basically lines from 5000 files. We issue a DELETE according to the file (mandatory) and
> then a COPY each 1000 lines of that file.
>
> DELETE request : DELETE FROM table WHERE field1 = ? AND field2 = ?;
>
> COPY request : COPY table FROM STDIN WITH CSV
>
>
> We have indexes on our database that we can't delete to insert our data.
>
>
> When we insert the data there is some kind of freezes on the databases between requests. Freezes occur
> about every 20 seconds.
>
>
> Here is a screenshot <http://tof.canardpc.com/view/c42e69c0-d776-4f93-a8a3-8713794a1a07.jpg> from
> yourkit.
>
>
> We tried different solutions:
>
>
> * 1 table to 5 tables to reduces lock contention
> * fillfactor on indexes
> * commit delay
> * fsync to off (that helped but we can't do this)
>
> We mainly want to know why this is happening because it slowing the insert too much for us.

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.

Yours,
Laurenz Albe