db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Semih ARAL (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3698) Bulk SQL INSERT, SELECT, UPDATE operations creates OutOfMemoryError after first Cleanup action on serverside
Date Sun, 08 Jun 2008 11:09:45 GMT

     [ 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.


Mime
View raw message