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(dot)janes(at)gmail(dot)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.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Saturday, August 30, 2014, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:

> On Sat, Aug 30, 2014 at 11:56 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com
> <javascript:_e(%7B%7D,'cvml','jeff(dot)janes(at)gmail(dot)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.

Regards,

Atri

--
Regards,

Atri
*l'apprenant*

On Sat, Aug 30, 2014 at 5:05 AM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:
>
>
> On Saturday, August 30, 2014, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>
>> On Sat, Aug 30, 2014 at 11:56 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)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 }

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

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(at)gmx(dot)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(at)gmx(dot)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?
>
>
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(dot)janes(at)gmail(dot)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