[
https://issues.apache.org/jira/browse/DERBY-3698?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Semih ARAL updated DERBY-3698:
------------------------------
Urgency: (was: Blocker)
The migration was terminated. The data were transferred to derby DB with a new
structure and constraints.
I write this comment to share my experience with derby DB users and developers.
In bulk operations derby reacts well in the beginning, but after several
minutes it slows down.
So if one use jdbc connect/disconnect for each operation, after several
minutes, derby easily rejects a new connection, because it is busy.
This stops the bulk operations, so the connection must be open one time and
must be kept open till the end of the operations.
Derby has a problem of memory management for bulk operations, after 5-10
minutes it gives OutOfemory error:
At first from the derby.log, I guessed that there is a cleanup process which
creates OutOfMemory error, I had a look at the code, and saw that the word
cleanup appears just in the log, I was not sure about the relation between
cleanup and OutOfMemory error.
To prevent OutOfMemory error for my special case, I added System.gc() in the
begining of GenericStatement.prepMinion on derby server, of course this slowed
down the derby, but time to get OutOfMemory was prolonged enough.
During the migration I got several exceptions on the server side and derby
created some class files, I don't know these files are useful or not but to
help the derby developers I will put all them to attachments.
For derby users, in bulk operations I advice to use import/export functions if
it is possible, because I haven't seen any OutOfMemory error in import/export
of very big tables.
Good luck.
> Bulk SQL INSERT, SELECT, UPDATE operations creates OutOfMemoryError after
> first Cleanup action on serverside
> ------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-3698
> URL: https://issues.apache.org/jira/browse/DERBY-3698
> Project: Derby
> Issue Type: Bug
> Components: Network Server
> Affects Versions: 10.4.1.3
> Environment: Windows XP, JDK 1.4
> Reporter: Semih ARAL
>
> In the migration of an DB,
> By using JDBC (connection is open one time in the begining)
> after many INSERT, SELECT,UPDATE statement executions (more than 3500)
> I got the following Exception on the client side:
> SELECT PK_SEQ, NAME, URL FROM EUPROJ.FIRM WHERE NAME ='INSTITUTE OF EDUCATION
> University OF LONDON'
> com.javaDevHome.ladyBeetle.exception.MicroJDBCException:
> java.sql.SQLException: DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001,
> SQLERRMC: java.lang.OutOfMemoryErrorXJ001.U
> at
> com.javaDevHome.ladyBeetle.MicroJDBCConnection.executeQuery(MicroJDBCConnection.java:251)
> at
> com.javaDevHome.ladyBeetle.MicroJDBCDBase.selectRowSilent(MicroJDBCDBase.java:133)
> at euProj.transfer.Transfer.procFirmNew(Transfer.java:259)
> at euProj.transfer.Transfer.procshortListNew(Transfer.java:200)
> at euProj.transfer.Transfer.readConsortiums(Transfer.java:524)
> at euProj.transfer.Transfer.procContract(Transfer.java:107)
> at euProj.transfer.Transfer.readLine(Transfer.java:554)
> at euProj.transfer.Transfer.main(Transfer.java:594)
> Caused by: java.sql.SQLException: DERBY SQL error: SQLCODE: -1, SQLSTATE:
> XJ001, SQLERRMC: java.lang.OutOfMemoryErrorXJ001.U
> at
> org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.SqlException.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.Statement.executeQuery(Unknown Source)
> at
> com.javaDevHome.ladyBeetle.MicroJDBCConnection.executeQuery(MicroJDBCConnection.java:247)
> ... 7 more
> Caused by: org.apache.derby.client.am.SqlException: DERBY SQL error: SQLCODE:
> -1, SQLSTATE: XJ001, SQLERRMC: java.lang.OutOfMemoryErrorXJ001.U
> at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
> at
> org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown
> Source)
> at
> org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown
> Source)
> at
> org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown
> Source)
> at
> org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
> at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
> at org.apache.derby.client.am.Statement.executeQueryX(Unknown Source)
> ... 9 more
> in derby.log on the server side :
> 2008-05-29 20:33:04.578 GMT Thread[DRDAConnThread_5,5,main] (XID = 171959),
> (SESSIONID = 4), (DATABASE = euProj03), (DRDAID =
> NF000001.H1CF-4339779978832484505{5}), Begin compiling prepared statement:
> SELECT PK_SEQ, NAME, URL FROM EUPROJ.FIRM WHERE NAME ='INSTITUTE OF EDUCATION
> University OF LONDON' :End prepared statement
> 2008-05-29 20:33:04.609 GMT Thread[DRDAConnThread_5,5,main] (XID = 171959),
> (SESSIONID = 4), (DATABASE = euProj03), (DRDAID =
> NF000001.H1CF-4339779978832484505{5}), End compiling prepared statement:
> SELECT PK_SEQ, NAME, URL FROM EUPROJ.FIRM WHERE NAME ='INSTITUTE OF EDUCATION
> University OF LONDON' :End prepared statement
> 2008-05-29 20:33:07.499 GMT Thread[DRDAConnThread_5,5,main] (XID = 171959),
> (SESSIONID = 4), (DATABASE = euProj03), (DRDAID =
> NF000001.H1CF-4339779978832484505{5}), Cleanup action starting
> 2008-05-29 20:33:07.499 GMT Thread[DRDAConnThread_5,5,main] (XID = 171959),
> (SESSIONID = 4), (DATABASE = euProj03), (DRDAID =
> NF000001.H1CF-4339779978832484505{5}), Failed Statement is: SELECT PK_SEQ,
> NAME, URL FROM EUPROJ.FIRM WHERE NAME ='INSTITUTE OF EDUCATION University OF
> LONDON'
> java.lang.OutOfMemoryError
> Cleanup action completed
> 2008-05-29 20:33:37.359 GMT Thread[DRDAConnThread_6,5,main] (XID = 169430),
> (SESSIONID = 6), (DATABASE = euProj03), (DRDAID =
> NF000001.H1D1-4255618960795994455{7}), Rolling back
> 2008-05-29 20:33:37.359 GMT Thread[DRDAConnThread_6,5,main] (XID = 169430),
> (SESSIONID = 6), (DATABASE = euProj03), (DRDAID =
> NF000001.H1D1-4255618960795994455{7}), Rolling back
> 2008-05-29 20:33:37.359 GMT Thread[DRDAConnThread_4,5,main] (XID = 143043),
> (SESSIONID = 5), (DATABASE = euProj03), (DRDAID =
> NF000001.H1D0-4255900435772705908{6}), Rolling back
> 2008-05-29 20:33:37.359 GMT Thread[DRDAConnThread_4,5,main] (XID = 143043),
> (SESSIONID = 5), (DATABASE = euProj03), (DRDAID =
> NF000001.H1D0-4255900435772705908{6}), Rolling back
> in the derby.log there is no other Cleanup action, this is the first and the
> only one.
> I have started the server by default heap memory options:
> java -Dderby.system.home=C:\derby -jar %DERBY_HOME%\lib\derbyrun.jar server
> start
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
