Re: [JDBC] BUG #1347: Bulk Import stopps after a while (

Kris Jurka wrote:
>
> On Mon, 13 Dec 2004, PostgreSQL Bugs List wrote:
>
>
>>The following bug has been logged online:
>>
>>Bug reference: 1347
>>PostgreSQL version: 8.0 Beta
>>Operating system: Windows XP
>>Description: Bulk Import stopps after a while ( 8.0.0. RC1)
>>
>> - I have written a java program to transfer data from SQL Server 2000 to
>>PosgresSQL 8.0.0 RC1 release. I am updating the data in batches.
>>If my batch size is 1000/2000 records at a time.. This works fine.. And if I
>>change this size to say 20,000, it does only finishes one loop.. and then
>>stays idle. The CPU usage down to 10 % which was before 100 % while applying
>>the first batch of 20, 000 records.
>>
>>
>>The execution of program is halting just at
>>int n [] = stmt.batchUpdate();
>>
>
>
> This may be a problem with the JDBC driver deadlocking as described in the
> below code comment. When originally written I asked Oliver about the
> estimate of MAX_BUFFERED_QUERIES and he felt confident in that number. It
> would be good to know if lowering this number fixes your problem. [...]

I'm not convinced that this is the problem as MAX_BUFFERED_QUERIES is
around 250 by default, and yet batches of 1000/2000 execute OK.

Note that I haven't seen much benefit to batch sizes above perhaps 50 on
a LAN anyway. Batch sizes of 20,000 are almost certainly excessive (but
the driver shouldn't fail!)

Are you executing the batch with autocommit on or off?

Anyway, there are two bits of debug output that would be useful to see:

1. A thread dump of the importing JVM when it gets stuck. On unix-like
systems you'd do this by sending the JVM a SIGQUIT. I don't know how
you'd do the same under Windows.

2. Driver debug output:

a. add "?logLevel=2" to the driver URL
b. set a log writer on DriverManager at the start of the run:

// Point this somewhere else, e.g. to a file,
// if it's more convenient.
java.sql.DriverManager.setLogWriter(
new java.io.PrintWriter(System.err)))

This should trace all the protocol messages being sent or received to
System.err. If the driver is wedging at the protocol level, it should
show up in that output. Send me this output off-list and compressed --
it'll be pretty big for 20,000+ queries!

-O