Thread: Re: [BUGS] BUG #1347: Bulk Import stopps after a while (
Bahadur Singh wrote: > Finally I am able to force the driver to do the DEBUG > level output on the console. > > err.zip contains Debug level output. OK, it looks like it is the connection deadlock issue, but triggered by a case I hadn't considered. For some reason every query in the batch is creating a new named statement on the backend .. and the statement cleanup process that happens before query execution is flooding statement Close commands down the connection on the second batch execution, which appears to deadlock the connection. The exact behaviour will be fairly unpredicatable as it depends on when the JVM does garbage collection. I will try to find some time to investigate this properly tomorrow. -O
On Wed, 15 Dec 2004, Oliver Jowett wrote: > OK, it looks like it is the connection deadlock issue, but triggered by > a case I hadn't considered. For some reason every query in the batch is > creating a new named statement on the backend .. and the statement > cleanup process that happens before query execution is flooding > statement Close commands down the connection on the second batch > execution, which appears to deadlock the connection. The exact behaviour > will be fairly unpredicatable as it depends on when the JVM does garbage > collection. > Any news on this? In general it seems like the whole statement/portal cleanup is too lazy. These things can take up significant backend resources and I would have expected calling ResultSet.close() and/or Statement.close() to actually close something (perhaps not immediately, but at least on the next network trip). Kris Jurka
Kris Jurka wrote: > On Wed, 15 Dec 2004, Oliver Jowett wrote: > >>OK, it looks like it is the connection deadlock issue, but triggered by >>a case I hadn't considered. For some reason every query in the batch is >>creating a new named statement on the backend .. and the statement >>cleanup process that happens before query execution is flooding >>statement Close commands down the connection on the second batch >>execution, which appears to deadlock the connection. The exact behaviour >>will be fairly unpredicatable as it depends on when the JVM does garbage >>collection. > > Any news on this? I didn't get a chance to look at it before going on leave. I won't be able to look at it before early January. > In general it seems like the whole statement/portal > cleanup is too lazy. These things can take up significant backend > resources and I would have expected calling ResultSet.close() and/or > Statement.close() to actually close something (perhaps not immediately, > but at least on the next network trip). I don't have the code to hand, but from memory an explicit close of the Query does exactly this -- it clears the reference which causes it to be immeditately enqueued, and the next query will do cleanup when it polls the reference queue. The GC-driven enqueue only kicks in if the object holding the statement info becomes garbage without being closed. The reference queue stuff seems to be working as designed. The problem is that we should be taking the Close messages we send into account when trying to avoid protocol deadlocks, but currently we don't, so a big batch of Close messages at once break things. I was more worried about why a batch update was generating so many individual named statements.. it should either use zero (Statement.addBatch) or one (PreparedStatement.addBatch) named statement, not N statements. -O
On Thu, 23 Dec 2004, Oliver Jowett wrote: > I don't have the code to hand, but from memory an explicit > close of the Query does exactly this -- it clears the > reference which causes it to be immeditately enqueued, and > the next query will do cleanup when it polls the reference > queue. This is true for the Query/Portal objects, the problem is that the Statement/ResultSets aren't closing the underlying objects. Will fix. > I was more worried about why a batch update was generating > so many individual named statements.. it should either use > zero (Statement.addBatch) or one > (PreparedStatement.addBatch) named statement, not N statements. > Ahhh. The protocol is a problem, but this named statement misuse is triggering it. I can take a look at that. Kris Jurka
On Wed, 22 Dec 2004, Kris Jurka wrote: > Ahhh. The protocol is a problem, but this named statement misuse is > triggering it. I can take a look at that. > Ok, armed with this insight from Oliver, the problem was an easy find and fix. Bahadur, I've put up a new set of development jar files at: http://www.ejurka.com/pgsql/jars/bahadur/ if you'd like to test it out. Kris Jurka
Kris, The new updated jar from you works perfectly for my test environment and I did some tests with different batch sizes of (20000, 30000, 400000 etc), This works fine, even the time of import in various situations is consistent. If you need any feedback from me about logs /debug outputs. Please write me, I can send you. Many thanks for your support. Best regards Bahadur --- Kris Jurka <books@ejurka.com> wrote: > > > On Wed, 22 Dec 2004, Kris Jurka wrote: > > > Ahhh. The protocol is a problem, but this named > statement misuse is > > triggering it. I can take a look at that. > > > > Ok, armed with this insight from Oliver, the problem > was an easy find and > fix. > > Bahadur, I've put up a new set of development jar > files at: > > http://www.ejurka.com/pgsql/jars/bahadur/ > > if you'd like to test it out. > > Kris Jurka > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com