Skip site navigation (1) Skip section navigation (2)

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

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Bahadur Singh <bahadursingh(at)yahoo(dot)com>
Cc: Kris Jurka <books(at)ejurka(dot)com>, pgsql-bugs(at)postgresql(dot)org,pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] BUG #1347: Bulk Import stopps after a while (
Date: 2004-12-13 21:13:57
Message-ID: 41BE0615.2000203@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-jdbc
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

In response to

pgsql-bugs by date

Next:From: Kris JurkaDate: 2004-12-13 22:06:00
Subject: Re: BUG #1347: Bulk Import stopps after a while ( 8.0.0.
Previous:From: Oliver JowettDate: 2004-12-13 20:58:16
Subject: Re: [JDBC] BUG #1347: Bulk Import stopps after a while ( 8.0.0.

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2004-12-13 22:06:00
Subject: Re: BUG #1347: Bulk Import stopps after a while ( 8.0.0.
Previous:From: Oliver JowettDate: 2004-12-13 20:58:16
Subject: Re: [JDBC] BUG #1347: Bulk Import stopps after a while ( 8.0.0.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group