[
https://issues.apache.org/jira/browse/DERBY-5415?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Robert Hoffmann updated DERBY-5415:
-----------------------------------
Description:
Hi,
I) Description
When making thousands of simple queries to one table using PreparedStatement, I
have noticed quickly increasing memory usage (hundreds of MB within a few
dozens of seconds): CASE A.
I found that memory usage is NORMAL when I keep the PreparedStatement OPEN for
all queries (CASE B).
CASE A ("Closing and preparing statement -> leaking"):
>>
while(true) {
PreparedStatement ps = con.prepareStatement("SELECT * from t where a=?");
ps.setInt(1, r);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
rs.getInt("b");
}
rs.close();
ps.close();
}
<<
CASE B ("Keep prepared statement open -> steady memory"):
>>
PreparedStatement ps = con.prepareStatement("SELECT * from t where a=?");
while(true) {
ps.setInt(1, r);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
rs.getInt("b");
}
rs.close();
// keep open: ps.close(); // close later
}
<<
II) Reproducibility and heap histogram
I can easily reproduce this problem in our production environment. And the heap
of both cases is very distinct:
CASE A:
num #instances #bytes class name
----------------------------------------------
1: 1133492 57289984 [Ljava.lang.Object;
2: 1035688 53548872 [C
3: 249501 33051904 [I
4: 152208 21917952
org.apache.derby.impl.jdbc.EmbedPreparedStatement40
5: 59773 20561912
org.apache.derby.impl.sql.execute.BulkTableScanResultSet
6: 750585 18014040 java.util.ArrayList
7: 674840 16196160 java.lang.String
8: 989684 15834944 org.apache.derby.iapi.types.SQLInteger
9: 391939 15677560 org.apache.derby.impl.sql.GenericParameter
10: 538700 14375272
[Lorg.apache.derby.iapi.types.DataValueDescriptor;
11: 59775 13389600
org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet
12: 59775 12433200
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet
13: 59775 9085800
org.apache.derby.impl.store.access.btree.index.B2IForwardScan
14: 179325 8607600
org.apache.derby.impl.store.raw.data.BaseContainerHandle
15: 351721 8441304 java.util.HashMap$Entry
16: 239117 7651744 java.util.HashMap$KeyIterator
17: 59775 6694800 org.apache.derby.impl.jdbc.EmbedResultSet40
18: 239119 5738856
org.apache.derby.impl.store.access.heap.HeapRowLocation
19: 179325 5738400
org.apache.derby.impl.store.access.conglomerate.OpenConglomerateScratchSpace
20: 119550 5738400
org.apache.derby.impl.store.access.heap.OpenHeap
21: 119548 5738240
[[Lorg.apache.derby.iapi.types.DataValueDescriptor;
...
CASE B:
num #instances #bytes class name
----------------------------------------------
1: 224186 9471600 [C
2: 21030 8223200 [I
3: 105020 5553016 [Ljava.lang.Object;
4: 43650 4931368 <constMethodKlass>
5: 201157 4827768 java.lang.String
6: 174474 4187376 java.util.HashMap$Entry
7: 43650 3846512 <methodKlass>
8: 7654 3317816 [B
9: 65633 2663504 <symbolKlass>
10: 16143 2481304 [Ljava.util.HashMap$Entry;
11: 3442 2056408 <constantPoolKlass>
12: 79290 1902960 java.util.ArrayList
13: 3442 1554272 <instanceKlassKlass>
14: 45596 1459072
org.apache.derby.impl.store.raw.data.StoredRecordHeader
15: 2890 1281888 <constantPoolCacheKlass>
16: 25536 1225728 at.intelservice.ie.IS_SText$SIsland
17: 45566 1093584
org.apache.derby.impl.store.raw.data.RecordId
18: 28649 916768 java.util.LinkedHashMap$Entry
19: 1795 734400
[Lorg.apache.derby.impl.store.raw.data.StoredRecordHeader;
20: 4025 611800
org.apache.derby.impl.store.access.btree.index.B2IForwardScan
21: 14614 584560 java.util.HashMap
22: 12075 579600
org.apache.derby.impl.store.raw.data.BaseContainerHandle
23: 34005 544080 java.lang.Integer
24: 4817 539504 org.apache.derby.impl.jdbc.EmbedResultSet40
...
III) Simple test app
Unfortunately, I am unable to create a simple test that would work on my
desktop. However if I set derby.language.statementCacheSize=0 then I get a
similar phenotype as on our production server (i.e. CASE A).
IV) Workaround
Right now I am keeping the PreparedStatement open as a workaround but I am
afraid this might lead to other problems.
I hope this will help you to make Derby even better!
Thank you very much for this great product and best regards,
Robert
was:
Hi,
I) Description
When making thousands of simple queries to one table using PreparedStatement, I
have noticed quickly increasing memory usage (hundreds of MB within a few
dozens of seconds): CASE A.
I found that memory usage is NORMAL when I keep the PreparedStatement OPEN for
all queries (CASE B).
CASE A ("Closing and preparing statement -> leaking"):
>>
while(true) {
PreparedStatement ps = con.prepareStatement("SELECT * from t where a=?");
ps.setInt(1, r);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
rs.getInt("b");
}
rs.close();
ps.close();
}
<<
CASE B ("Keep prepared statement open -> steady memory"):
>>
PreparedStatement ps = con.prepareStatement("SELECT * from t where a=?");
while(true) {
ps.setInt(1, r);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
rs.getInt("b");
}
rs.close();
// keep open: ps.close(); // close later
}
<<
II) Reproducibility and heap histogram
I can easily reproduce this problem in our production environment. And the heap
of both cases is very distinct:
CASE A:
num #instances #bytes class name
----------------------------------------------
1: 224186 9471600 [C
2: 21030 8223200 [I
3: 105020 5553016 [Ljava.lang.Object;
4: 43650 4931368 <constMethodKlass>
5: 201157 4827768 java.lang.String
6: 174474 4187376 java.util.HashMap$Entry
7: 43650 3846512 <methodKlass>
8: 7654 3317816 [B
9: 65633 2663504 <symbolKlass>
10: 16143 2481304 [Ljava.util.HashMap$Entry;
11: 3442 2056408 <constantPoolKlass>
12: 79290 1902960 java.util.ArrayList
13: 3442 1554272 <instanceKlassKlass>
14: 45596 1459072
org.apache.derby.impl.store.raw.data.StoredRecordHeader
15: 2890 1281888 <constantPoolCacheKlass>
16: 25536 1225728 at.intelservice.ie.IS_SText$SIsland
17: 45566 1093584
org.apache.derby.impl.store.raw.data.RecordId
18: 28649 916768 java.util.LinkedHashMap$Entry
19: 1795 734400
[Lorg.apache.derby.impl.store.raw.data.StoredRecordHeader;
20: 4025 611800
org.apache.derby.impl.store.access.btree.index.B2IForwardScan
21: 14614 584560 java.util.HashMap
22: 12075 579600
org.apache.derby.impl.store.raw.data.BaseContainerHandle
23: 34005 544080 java.lang.Integer
24: 4817 539504 org.apache.derby.impl.jdbc.EmbedResultSet40
...
CASE B:
num #instances #bytes class name
----------------------------------------------
1: 1133492 57289984 [Ljava.lang.Object;
2: 1035688 53548872 [C
3: 249501 33051904 [I
4: 152208 21917952
org.apache.derby.impl.jdbc.EmbedPreparedStatement40
5: 59773 20561912
org.apache.derby.impl.sql.execute.BulkTableScanResultSet
6: 750585 18014040 java.util.ArrayList
7: 674840 16196160 java.lang.String
8: 989684 15834944 org.apache.derby.iapi.types.SQLInteger
9: 391939 15677560 org.apache.derby.impl.sql.GenericParameter
10: 538700 14375272
[Lorg.apache.derby.iapi.types.DataValueDescriptor;
11: 59775 13389600
org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet
12: 59775 12433200
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet
13: 59775 9085800
org.apache.derby.impl.store.access.btree.index.B2IForwardScan
14: 179325 8607600
org.apache.derby.impl.store.raw.data.BaseContainerHandle
15: 351721 8441304 java.util.HashMap$Entry
16: 239117 7651744 java.util.HashMap$KeyIterator
17: 59775 6694800 org.apache.derby.impl.jdbc.EmbedResultSet40
18: 239119 5738856
org.apache.derby.impl.store.access.heap.HeapRowLocation
19: 179325 5738400
org.apache.derby.impl.store.access.conglomerate.OpenConglomerateScratchSpace
20: 119550 5738400
org.apache.derby.impl.store.access.heap.OpenHeap
21: 119548 5738240
[[Lorg.apache.derby.iapi.types.DataValueDescriptor;
...
III) Simple test app
Unfortunately, I am unable to create a simple test that would work on my
desktop. However if I set derby.language.statementCacheSize=0 then I get a
similar phenotype as on our production server (i.e. CASE A).
IV) Workaround
Right now I am keeping the PreparedStatement open as a workaround but I am
afraid this might lead to other problems.
I hope this will help you to make Derby even better!
Thank you very much for this great product and best regards,
Robert
Kristian, you are right, I labelled the heap reports wrong. In line with the
description, CASE A indeed uses more memory.
I'll check an older version right now.
Thank you,
Robert
> Memory leak in statement cache of PreparedStatement
> ---------------------------------------------------
>
> Key: DERBY-5415
> URL: https://issues.apache.org/jira/browse/DERBY-5415
> Project: Derby
> Issue Type: Bug
> Components: JDBC, Services
> Affects Versions: 10.8.1.2
> Environment: Linux, java 1.6.0_27-b07
> Reporter: Robert Hoffmann
> Priority: Critical
>
> Hi,
> I) Description
> When making thousands of simple queries to one table using PreparedStatement,
> I have noticed quickly increasing memory usage (hundreds of MB within a few
> dozens of seconds): CASE A.
> I found that memory usage is NORMAL when I keep the PreparedStatement OPEN
> for all queries (CASE B).
> CASE A ("Closing and preparing statement -> leaking"):
> >>
> while(true) {
> PreparedStatement ps = con.prepareStatement("SELECT * from t where a=?");
> ps.setInt(1, r);
> ResultSet rs = ps.executeQuery();
> while (rs.next()) {
> rs.getInt("b");
> }
> rs.close();
> ps.close();
> }
> <<
> CASE B ("Keep prepared statement open -> steady memory"):
> >>
> PreparedStatement ps = con.prepareStatement("SELECT * from t where a=?");
> while(true) {
> ps.setInt(1, r);
> ResultSet rs = ps.executeQuery();
> while (rs.next()) {
> rs.getInt("b");
> }
> rs.close();
> // keep open: ps.close(); // close later
> }
> <<
> II) Reproducibility and heap histogram
> I can easily reproduce this problem in our production environment. And the
> heap of both cases is very distinct:
> CASE A:
> num #instances #bytes class name
> ----------------------------------------------
> 1: 1133492 57289984 [Ljava.lang.Object;
> 2: 1035688 53548872 [C
> 3: 249501 33051904 [I
> 4: 152208 21917952
> org.apache.derby.impl.jdbc.EmbedPreparedStatement40
> 5: 59773 20561912
> org.apache.derby.impl.sql.execute.BulkTableScanResultSet
> 6: 750585 18014040 java.util.ArrayList
> 7: 674840 16196160 java.lang.String
> 8: 989684 15834944 org.apache.derby.iapi.types.SQLInteger
> 9: 391939 15677560 org.apache.derby.impl.sql.GenericParameter
> 10: 538700 14375272
> [Lorg.apache.derby.iapi.types.DataValueDescriptor;
> 11: 59775 13389600
> org.apache.derby.impl.sql.execute.IndexRowToBaseRowResultSet
> 12: 59775 12433200
> org.apache.derby.impl.sql.execute.ProjectRestrictResultSet
> 13: 59775 9085800
> org.apache.derby.impl.store.access.btree.index.B2IForwardScan
> 14: 179325 8607600
> org.apache.derby.impl.store.raw.data.BaseContainerHandle
> 15: 351721 8441304 java.util.HashMap$Entry
> 16: 239117 7651744 java.util.HashMap$KeyIterator
> 17: 59775 6694800
> org.apache.derby.impl.jdbc.EmbedResultSet40
> 18: 239119 5738856
> org.apache.derby.impl.store.access.heap.HeapRowLocation
> 19: 179325 5738400
> org.apache.derby.impl.store.access.conglomerate.OpenConglomerateScratchSpace
> 20: 119550 5738400
> org.apache.derby.impl.store.access.heap.OpenHeap
> 21: 119548 5738240
> [[Lorg.apache.derby.iapi.types.DataValueDescriptor;
> ...
> CASE B:
> num #instances #bytes class name
> ----------------------------------------------
> 1: 224186 9471600 [C
> 2: 21030 8223200 [I
> 3: 105020 5553016 [Ljava.lang.Object;
> 4: 43650 4931368 <constMethodKlass>
> 5: 201157 4827768 java.lang.String
> 6: 174474 4187376 java.util.HashMap$Entry
> 7: 43650 3846512 <methodKlass>
> 8: 7654 3317816 [B
> 9: 65633 2663504 <symbolKlass>
> 10: 16143 2481304 [Ljava.util.HashMap$Entry;
> 11: 3442 2056408 <constantPoolKlass>
> 12: 79290 1902960 java.util.ArrayList
> 13: 3442 1554272 <instanceKlassKlass>
> 14: 45596 1459072
> org.apache.derby.impl.store.raw.data.StoredRecordHeader
> 15: 2890 1281888 <constantPoolCacheKlass>
> 16: 25536 1225728 at.intelservice.ie.IS_SText$SIsland
> 17: 45566 1093584
> org.apache.derby.impl.store.raw.data.RecordId
> 18: 28649 916768 java.util.LinkedHashMap$Entry
> 19: 1795 734400
> [Lorg.apache.derby.impl.store.raw.data.StoredRecordHeader;
> 20: 4025 611800
> org.apache.derby.impl.store.access.btree.index.B2IForwardScan
> 21: 14614 584560 java.util.HashMap
> 22: 12075 579600
> org.apache.derby.impl.store.raw.data.BaseContainerHandle
> 23: 34005 544080 java.lang.Integer
> 24: 4817 539504
> org.apache.derby.impl.jdbc.EmbedResultSet40
> ...
> III) Simple test app
> Unfortunately, I am unable to create a simple test that would work on my
> desktop. However if I set derby.language.statementCacheSize=0 then I get a
> similar phenotype as on our production server (i.e. CASE A).
> IV) Workaround
> Right now I am keeping the PreparedStatement open as a workaround but I am
> afraid this might lead to other problems.
> I hope this will help you to make Derby even better!
> Thank you very much for this great product and best regards,
> Robert
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
