Обсуждение: COPY and heap_sync
If you insert tuples with COPY into a table created or truncated in the same transaction, at the end of the COPY it calls heap_sync.
But there cases were people use COPY in a loop with a small amount of data in each statement. Now it is calling heap_sync many times, and if NBuffers is large doing that gets very slow.
Could the heap_sync be safely delayed until the end of the transaction, rather than the end of the COPY?
Cheers,
Jeff
On Sat, Aug 30, 2014 at 11:56 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>
> If you insert tuples with COPY into a table created or truncated in the same transaction, at the end of the COPY it calls heap_sync.
>
> But there cases were people use COPY in a loop with a small amount of data in each statement. Now it is calling heap_sync many times, and if NBuffers is large doing that gets very slow.
>
> Could the heap_sync be safely delayed until the end of the transaction, rather than the end of the COPY?
Wouldn't unconditionally delaying sync until end of transaction
>
> If you insert tuples with COPY into a table created or truncated in the same transaction, at the end of the COPY it calls heap_sync.
>
> But there cases were people use COPY in a loop with a small amount of data in each statement. Now it is calling heap_sync many times, and if NBuffers is large doing that gets very slow.
>
> Could the heap_sync be safely delayed until the end of the transaction, rather than the end of the COPY?
Wouldn't unconditionally delaying sync until end of transaction
can lead to burst of I/O at that time especially if there are many
such copy commands in a transaction, leading to delay in some
other operation's that might be happening concurrently in the
system.
On Saturday, August 30, 2014, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Sat, Aug 30, 2014 at 11:56 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>
> If you insert tuples with COPY into a table created or truncated in the same transaction, at the end of the COPY it calls heap_sync.
>
> But there cases were people use COPY in a loop with a small amount of data in each statement. Now it is calling heap_sync many times, and if NBuffers is large doing that gets very slow.
>
> Could the heap_sync be safely delayed until the end of the transaction, rather than the end of the COPY?
Wouldn't unconditionally delaying sync until end of transactioncan lead to burst of I/O at that time especially if there are manysuch copy commands in a transaction, leading to delay in someother operation's that might be happening concurrently in thesystem.
I agree with that but then, it can provide us the same benefits like group commit,especially when most of the copy commands touch pages which are nearby,hence reducing the seek time overhead.
We could look at making it optional through a GUC, since it is useful albeit for some specific usecases.
Regards,
Atri
--
Regards,
Atri
l'apprenant
On Sat, Aug 30, 2014 at 5:05 AM, Atri Sharma <atri.jiit@gmail.com> wrote:
>
>
> On Saturday, August 30, 2014, Amit Kapila <amit.kapila16@gmail.com> wrote:
>>
>> On Sat, Aug 30, 2014 at 11:56 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> >
>> > If you insert tuples with COPY into a table created or truncated in the same transaction, at the end of the COPY it calls heap_sync.
>> >
>> > But there cases were people use COPY in a loop with a small amount of data in each statement. Now it is calling heap_sync many times, and if NBuffers is large doing that gets very slow.
>> >
>> > Could the heap_sync be safely delayed until the end of the transaction, rather than the end of the COPY?
>>
>> Wouldn't unconditionally delaying sync until end of transaction
>> can lead to burst of I/O at that time especially if there are many
>> such copy commands in a transaction, leading to delay in some
>> other operation's that might be happening concurrently in the
>> system.
>>
>>
>>
>
> I agree with that but then, it can provide us the same benefits like group commit,especially when most of the copy commands touch pages which are nearby,hence reducing the seek time overhead.
>
> We could look at making it optional through a GUC, since it is useful albeit for some specific usecases.
>
It's interesting... maybe something analogous to "SET CONSTRAINTS DEFERRED"...
SET COPY COMMIT { IMMEDIATE | DEFERRED }
or
SET COPY MODE { IMMEDIATE | DEFERRED }
SET COPY MODE { IMMEDIATE | DEFERRED }
Just some thoughts!
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
On 8/30/14 2:26 AM, Jeff Janes wrote: > But there cases were people use COPY in a loop with a small amount of > data in each statement. What would be the reason for doing that?
On Sun, Aug 31, 2014 at 10:10 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
>
> On 8/30/14 2:26 AM, Jeff Janes wrote:
> > But there cases were people use COPY in a loop with a small amount of
> > data in each statement.
>
> What would be the reason for doing that?
>
I used that to the same thing many times. In a company that I was employed we developed scripts to migrate data from one database do another.
The first version we used INSERT statements and was very very slow. Then we wrote a second version changing the INSERT by COPY statements. The performance was very better, but we believe that could be better, so in the third version we created some kind of "cache" (using arrays) to accumulate the records in memory then after N rows we build the COPY statement with the cache contents and run it. This was a really good performance improvement.
It's my use case to we have a feature to postpone the heap_sync in COPY statements. I don't know if it's a feature that a lot of people wants, but IMHO it could be nice to improve the bulk load operations.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
On Sun, Aug 31, 2014 at 6:10 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
On 8/30/14 2:26 AM, Jeff Janes wrote:What would be the reason for doing that?
> But there cases were people use COPY in a loop with a small amount of
> data in each statement.
As far as I can tell, DRY. They need code to do bulk inserts anyway. So, just use that everywhere even when it is not in bulk.
Also, you can't interleave a copy command with other queries on the same connection. So you code it to start a COPY, use it until you discover you need to run a query (because you encounter something not in you local cache), end the COPY and do that query, then restart the query.
Under some conditions, the interruption occurs very seldom, under other conditions it is pretty much every row.
Cheers,
Jeff
On Sat, Aug 30, 2014 at 2:26 AM, Jeff Janes <jeff.janes@gmail.com> wrote: > If you insert tuples with COPY into a table created or truncated in the same > transaction, at the end of the COPY it calls heap_sync. > > But there cases were people use COPY in a loop with a small amount of data > in each statement. Now it is calling heap_sync many times, and if NBuffers > is large doing that gets very slow. > > Could the heap_sync be safely delayed until the end of the transaction, > rather than the end of the COPY? I don't think there's any data integrity problem with that, but if the fsync() should fail it would be reported at commit time rather than in response to the COPY. That might be OK though. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company