[
https://issues.apache.org/jira/browse/DERBY-6818?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14586055#comment-14586055
]
Mattias commented on DERBY-6818:
--------------------------------
The WHERE F.ID = ( SELECT MAX( ID ) FROM TEST ) trick seems to be working!
I also tried to add
{code}
CREATE UNIQUE INDEX DESC_ID ON TEST( ID DESC );
ALTER TABLE TABLE
ADD PRIMARY KEY
(ID);
{code}
But this seems not to make any difference. Do you have any other ideas how I
can change my schema to achieve the same result? I would rather change the
schema as the the select statement is built in a library which is used against
a DB2 database as well as Derby.
> java.lang.OutOfMemoryError
> --------------------------
>
> Key: DERBY-6818
> URL: https://issues.apache.org/jira/browse/DERBY-6818
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.11.1.1
> Environment: Using Derby disk database with JBoss eap 6.4.
> Reporter: Mattias
>
> We got a 'java.lang.OutOfMemoryError: Java heap space' when using a Derby
> disk database with the following schema.
> {code}
> CREATE TABLE TEST ( ID BIGINT NOT NULL
> , NEXT _ID BIGINT
> , PREV_ ID BIGINT
> , TEST_XML XML);
> ALTER TABLE TABLE
> ADD PRIMARY KEY
> (ID);
> {code}
> I opened the heap dump with Eclipse Memory Analyzer and found that tree
> instances of org.apache.derby.impl.store.access.sort.MergeInserter occupies
> 290 MB of memory (that is 66 % of the total heap)
> The problem seems to be that MergeInserter holds a sort buffer that contains
> complete rows and the TEST_XML is about 160 kB each. MergeInserter has an
> advanced algorithm to calculate the allowed size of the buffer, but is it
> necessary store the complete row in the buffer, i.e. the non-indexed values
> (TEST_XML is this case)?
> Is there some property I can set to limit the buffer size or is it possible
> to rewrite the SQL schema someway to exclude the XML type from the buffer?
> Following is the dominator tree from Eclipse Memory Analyzer:
> {code}
> org.apache.derby.impl.store.access.sort.MergeInserter @ 0xe3f72e20
>
>
> |
> 72 | 127 533 080 | 30,76%
> |- org.apache.derby.impl.store.access.sort.SortBuffer @ 0xe3f73018
>
>
> |
> 48 | 127 532 792 | 30,76%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fed5e0
>
>
> |
> 40 | 164 384 | 0,04%
> | | '- org.apache.derby.iapi.types.DataValueDescriptor[4] @ 0xee3da280
>
>
> |
> 32 | 164 344 | 0,04%
> | | |- org.apache.derby.iapi.types.SQLClob @ 0xee3da2a0
>
>
> |
> 56 | 164 240 | 0,04%
> | | | |- java.lang.String @ 0xee3da2d8 < ?xml version="1.0"?...|
> 24 | 164 160 | 0,04%
> | | | |- char[1][] @ 0xee3da2f0
>
>
> |
> 24 | 24 | 0,00%
> | | | '- Total: 2 entries
>
>
> |
> | |
> | | |- org.apache.derby.iapi.types.SQLLongint @ 0xee3da308
>
>
> |
> 24 | 24 | 0,00%
> | | |- org.apache.derby.iapi.types.SQLLongint @ 0xee3da320
>
>
> |
> 24 | 24 | 0,00%
> | | |- org.apache.derby.iapi.types.SQLLongint @ 0xee3da338
>
>
> |
> 24 | 24 | 0,00%
> | | '- Total: 4 entries
>
>
> |
> | |
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fed608
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fed630
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fed658
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fed680
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fed6a8
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fed6d0
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fed6f8
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fed720
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fedd50
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fedd78
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fedda0
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3feddc8
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fedff8
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fef5c8
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fef5f0
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3fef618
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3ff01f8
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3ff04c8
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3ff0608
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3ff0630
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3ff0658
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3ff0680
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3ff06a8
>
>
> |
> 40 | 164 384 | 0,04%
> | |- org.apache.derby.impl.store.access.sort.Node @ 0xe3ff06d0
>
>
> |
> 40 | 164 384 | 0,04%
> | '- Total: 25 of 780 entries; 755
> {code}
> This is a snippet of my jboss standalone.xml:
> {code:xml}
> <datasources>
> <xa-datasource jndi-name="java:/jdbc/MYDB"
> pool-name="mydb_Pool" enabled="true">
> <xa-datasource-property name="DatabaseName">
> mydb
> </xa-datasource-property>
> <xa-datasource-property name="CreateDatabase">
> create
> </xa-datasource-property>
> <driver>derby-embedded</driver>
> <xa-pool>
> <is-same-rm-override>false</is-same-rm-override>
> </xa-pool>
> <security>
> <user-name>sa</user-name>
> <password>admin</password>
> </security>
> </xa-datasource>
>
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
