[ https://issues.apache.org/jira/browse/DERBY-6818?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14581947#comment-14581947 ]
Bryan Pendleton commented on DERBY-6818: ---------------------------------------- Thanks for the additional data. If "ID" is the primary key, then "ORDER BY F.ID" should not result in a sort, I believe. Derby is supposed to recognize when you ORDER BY a column which is already sorted, and avoid the sort in that case. Perhaps that algorithm is not working properly in this case? > 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)