Re: [HACKERS] COPY and heap_sync
On Sat, Aug 30, 2014 at 2:26 AM, Jeff Janes jeff.ja...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY and heap_sync
On Sun, Aug 31, 2014 at 6:10 AM, Peter Eisentraut pete...@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? 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
Re: [HACKERS] COPY and heap_sync
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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY and heap_sync
On Sun, Aug 31, 2014 at 10:10 AM, Peter Eisentraut pete...@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
[HACKERS] 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
Re: [HACKERS] COPY and heap_sync
On Sat, Aug 30, 2014 at 11:56 AM, Jeff Janes jeff.ja...@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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] COPY and heap_sync
On Saturday, August 30, 2014, Amit Kapila amit.kapil...@gmail.com wrote: On Sat, Aug 30, 2014 at 11:56 AM, Jeff Janes jeff.ja...@gmail.com javascript:_e(%7B%7D,'cvml','jeff.ja...@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. Regards, Atri -- Regards, Atri *l'apprenant*
Re: [HACKERS] COPY and heap_sync
On Sat, Aug 30, 2014 at 5:05 AM, Atri Sharma atri.j...@gmail.com wrote:
On Saturday, August 30, 2014, Amit Kapila amit.kapil...@gmail.com wrote:
On Sat, Aug 30, 2014 at 11:56 AM, Jeff Janes jeff.ja...@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 }
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
