Postgres Pro
Downloads
Home   >   mailing lists

Re: Blocking every 20 sec while mass copying. - Mailing list pgsql-performance

From Benjamin Dugast
Subject Re: Blocking every 20 sec while mass copying.
Date
Msg-id CAB9ggRq3fA+=SYwHtxi5c=VVi68Z3cgP2tSm8-iKY+3dPpCkCA@mail.gmail.com
Whole thread Raw
In response to Re: Blocking every 20 sec while mass copying.  (Albe Laurenz <laurenz.albe@wien.gv.at>)
List pgsql-performance
Tree view
Finally we solved our problem by using a kind of trick

We have 2 kind of table : online table for read and temp table to mass insert our data

We work on the temp tables (5 different tables) to insert every data without any index that goes really fast compared to the previous method
then we create index on these tables simultanously,
then we drop online tables(also 5 tables) and rename the temp tables to online (takes less than 1 sec)

This is the faster way to insert our data that we found.
On our config it goes pretty fast, we reduce our execution time to 50% and there is no more need of many maintenance on the database.

Thanks for all answer that you give us.



2014-07-21 10:02 GMT+02:00 Albe Laurenz <laurenz.albe@wien.gv.at>:
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

pgsql-performance by date:

Previous
From: johno
Date:
Subject: Re: Slow query with indexed ORDER BY and LIMIT when using OR'd conditions
Next
From: Rural Hunter
Date:
Subject: Very slow planning performance on partition table