Re: Blocking every 20 sec while mass copying.

Поиск
Список
Период
Сортировка
От Albe Laurenz
Тема Re: Blocking every 20 sec while mass copying.
Дата
Msg-id A737B7A37273E048B164557ADEF4A58B17D1D06B@ntex2010i.host.magwien.gv.at
обсуждение исходный текст
Ответ на Blocking every 20 sec while mass copying.  (Benjamin Dugast <bdugast@excilys.com>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Benjamin Dugast
Дата:
Сообщение: Blocking every 20 sec while mass copying.
Следующее
От: Guillaume Cottenceau
Дата:
Сообщение: Re: Blocking every 20 sec while mass copying.