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
