You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bergquist, Brett" <BB...@canoga.com> on 2012/02/15 17:45:44 UTC
Quetion on out memory usage
I had a user in our production server that performed a query through a web service interface that requested to retrieve and perform a multi-level sort on probably 30 million records. The tables being queried on are in a UNION (basically one table for each week of the year and the query was working across the this week and the previous week so it probably could not use the indexes to avoid the sort.
Derby reported OutOfMemory errors. In derby.log are errors such as:
Tue Feb 14 20:15:12 EST 2012 Thread[DRDAConnThread_68,5,main] (XID = 861337977), (SESSIONID = 45137761), (DATABASE = csemdb), (DRDAID = ????????.??-866660124046835337{112060}), Failed Statement is: SELECT r.MasterIp, BootCount, TestResultId, ProfileName, r.ProfileRefId, LatencyBucketsInUse, JitterBucketsInUse, UseOutOfSequence, StartDateTime, TestResultDescription, TestResultDescription2, PacketsSent, RoundTripPackets, OutOfOrderPackets, MinRoundTripLatency, MaxRoundTripLatency, TotalRoundTripLatency, LatencyBucketValue1, LatencyBucketValue2, LatencyBucketValue3, LatencyBucketValue4, LatencyBucketValue5, LatencyBucketValue6, LatencyBucketValue7, LatencyBucketValue8, LatencyBucketValue9, LatencyBucketValue10, JitterMeasurement, LocalRemoteJitterBucketValue1, LocalRemoteJitterBucketValue2, LocalRemoteJitterBucketValue3, LocalRemoteJitterBucketValue4, LocalRemoteJitterBucketValue5, LocalRemoteJitterBucketValue6, LocalRemoteJitterBucketValue7, LocalRemoteJitterBucketValue8, LocalRemoteJitterBucketValue9, MinLocalRemoteJitter, MaxLocalRemoteJitter, TotalLocalRemoteJitter, RemoteLocalJitterBucketValue1, RemoteLocalJitterBucketValue2, RemoteLocalJitterBucketValue3, RemoteLocalJitterBucketValue4, RemoteLocalJitterBucketValue5, RemoteLocalJitterBucketValue6, RemoteLocalJitterBucketValue7, RemoteLocalJitterBucketValue8, RemoteLocalJitterBucketValue9, MinRemoteLocalJitter, MaxRemoteLocalJitter, TotalRemoteLocalJitter, c1.Circuit as Circuit1, c2.Circuit as Circuit2 FROM PCS_V1.NPAResults r JOIN PCS_V1.NpaResultsProfile p on r.PROFILEREFID = p.PROFILEREFID JOIN PCS_V1.NPAResultsAddress a on r.ADDRESSREFID = a.ADDRESSREFID JOIN PCS_V1.NPAResultsCircuit c1 on r.CIRCUIT1REFID = c1.CIRCUITREFID JOIN PCS_V1.NPAResultsCircuit c2 on r.CIRCUIT2REFID = c2.CIRCUITREFID WHERE ReportKey >= '2012-02-03 12:45:00.0' AND ReportKey <= '2012-02-14 14:00:00.0' AND (r.ResultFlag = 0 OR (r.ResultFlag <> 0 AND r.RoundTripPackets > 0)) ORDER BY TestResultDescription, Circuit1, TestResultDescription2, ProfileName, StartDateTime
java.lang.OutOfMemoryError: GC overhead limit exceeded
Tue Feb 14 23:48:28 EST 2012 Thread[DRDAConnThread_122,5,main] (XID = 861195891), (SESSIONID = 45103057), (DATABASE = csemdb), (DRDAID = ????????.??-448669783631952048{111972}), Failed Statement is: SELECT r.MasterIp, BootCount, TestResultId, ProfileName, r.ProfileRefId, LatencyBucketsInUse, JitterBucketsInUse, UseOutOfSequence, StartDateTime, TestResultDescription, TestResultDescription2, PacketsSent, RoundTripPackets, OutOfOrderPackets, MinRoundTripLatency, MaxRoundTripLatency, TotalRoundTripLatency, LatencyBucketValue1, LatencyBucketValue2, LatencyBucketValue3, LatencyBucketValue4, LatencyBucketValue5, LatencyBucketValue6, LatencyBucketValue7, LatencyBucketValue8, LatencyBucketValue9, LatencyBucketValue10, JitterMeasurement, LocalRemoteJitterBucketValue1, LocalRemoteJitterBucketValue2, LocalRemoteJitterBucketValue3, LocalRemoteJitterBucketValue4, LocalRemoteJitterBucketValue5, LocalRemoteJitterBucketValue6, LocalRemoteJitterBucketValue7, LocalRemoteJitterBucketValue8, LocalRemoteJitterBucketValue9, MinLocalRemoteJitter, MaxLocalRemoteJitter, TotalLocalRemoteJitter, RemoteLocalJitterBucketValue1, RemoteLocalJitterBucketValue2, RemoteLocalJitterBucketValue3, RemoteLocalJitterBucketValue4, RemoteLocalJitterBucketValue5, RemoteLocalJitterBucketValue6, RemoteLocalJitterBucketValue7, RemoteLocalJitterBucketValue8, RemoteLocalJitterBucketValue9, MinRemoteLocalJitter, MaxRemoteLocalJitter, TotalRemoteLocalJitter, c1.Circuit as Circuit1, c2.Circuit as Circuit2 FROM PCS_V1.NPAResults r JOIN PCS_V1.NpaResultsProfile p on r.PROFILEREFID = p.PROFILEREFID JOIN PCS_V1.NPAResultsAddress a on r.ADDRESSREFID = a.ADDRESSREFID JOIN PCS_V1.NPAResultsCircuit c1 on r.CIRCUIT1REFID = c1.CIRCUITREFID JOIN PCS_V1.NPAResultsCircuit c2 on r.CIRCUIT2REFID = c2.CIRCUITREFID WHERE ReportKey >= '2012-02-03 12:45:00.0' AND ReportKey <= '2012-02-14 14:00:00.0' AND (r.ResultFlag = 0 OR (r.ResultFlag <> 0 AND r.RoundTripPackets > 0)) ORDER BY TestResultDescription, Circuit1, TestResultDescription2, ProfileName, StartDateTime
java.lang.OutOfMemoryError: Java heap space
The server is a Oracle M3000 and the Derby engine heap is setup to be 8Gb.
I know the user should not have run that query and I can correct and prevent that.
I am curious as to why however that the query just did not consume disk space for on disk sorting and take a really long time to return. The result of the query is being read by a Java application using a ResultSet that is configured as (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY). Each record is being read and is producing an XML file from the data.
I would assume that Derby could query and sort this many results and present them through the record set interface although it may take a long time and may take significant disk space. Am I wrong about this?
Any comments will be most appreciated.
Brett
RE: Quetion on out memory usage
Posted by "Bergquist, Brett" <BB...@canoga.com>.
I forgot this is Derby 10.8.2.2
From: Bergquist, Brett [mailto:BBergquist@canoga.com]
Sent: Wednesday, February 15, 2012 11:46 AM
To: derby-dev@db.apache.org
Subject: Quetion on out memory usage
I had a user in our production server that performed a query through a web service interface that requested to retrieve and perform a multi-level sort on probably 30 million records. The tables being queried on are in a UNION (basically one table for each week of the year and the query was working across the this week and the previous week so it probably could not use the indexes to avoid the sort.
Derby reported OutOfMemory errors. In derby.log are errors such as:
Tue Feb 14 20:15:12 EST 2012 Thread[DRDAConnThread_68,5,main] (XID = 861337977), (SESSIONID = 45137761), (DATABASE = csemdb), (DRDAID = ????????.??-866660124046835337{112060}), Failed Statement is: SELECT r.MasterIp, BootCount, TestResultId, ProfileName, r.ProfileRefId, LatencyBucketsInUse, JitterBucketsInUse, UseOutOfSequence, StartDateTime, TestResultDescription, TestResultDescription2, PacketsSent, RoundTripPackets, OutOfOrderPackets, MinRoundTripLatency, MaxRoundTripLatency, TotalRoundTripLatency, LatencyBucketValue1, LatencyBucketValue2, LatencyBucketValue3, LatencyBucketValue4, LatencyBucketValue5, LatencyBucketValue6, LatencyBucketValue7, LatencyBucketValue8, LatencyBucketValue9, LatencyBucketValue10, JitterMeasurement, LocalRemoteJitterBucketValue1, LocalRemoteJitterBucketValue2, LocalRemoteJitterBucketValue3, LocalRemoteJitterBucketValue4, LocalRemoteJitterBucketValue5, LocalRemoteJitterBucketValue6, LocalRemoteJitterBucketValue7, LocalRemoteJitterBucketValue8, LocalRemoteJitterBucketValue9, MinLocalRemoteJitter, MaxLocalRemoteJitter, TotalLocalRemoteJitter, RemoteLocalJitterBucketValue1, RemoteLocalJitterBucketValue2, RemoteLocalJitterBucketValue3, RemoteLocalJitterBucketValue4, RemoteLocalJitterBucketValue5, RemoteLocalJitterBucketValue6, RemoteLocalJitterBucketValue7, RemoteLocalJitterBucketValue8, RemoteLocalJitterBucketValue9, MinRemoteLocalJitter, MaxRemoteLocalJitter, TotalRemoteLocalJitter, c1.Circuit as Circuit1, c2.Circuit as Circuit2 FROM PCS_V1.NPAResults r JOIN PCS_V1.NpaResultsProfile p on r.PROFILEREFID = p.PROFILEREFID JOIN PCS_V1.NPAResultsAddress a on r.ADDRESSREFID = a.ADDRESSREFID JOIN PCS_V1.NPAResultsCircuit c1 on r.CIRCUIT1REFID = c1.CIRCUITREFID JOIN PCS_V1.NPAResultsCircuit c2 on r.CIRCUIT2REFID = c2.CIRCUITREFID WHERE ReportKey >= '2012-02-03 12:45:00.0' AND ReportKey <= '2012-02-14 14:00:00.0' AND (r.ResultFlag = 0 OR (r.ResultFlag <> 0 AND r.RoundTripPackets > 0)) ORDER BY TestResultDescription, Circuit1, TestResultDescription2, ProfileName, StartDateTime
java.lang.OutOfMemoryError: GC overhead limit exceeded
Tue Feb 14 23:48:28 EST 2012 Thread[DRDAConnThread_122,5,main] (XID = 861195891), (SESSIONID = 45103057), (DATABASE = csemdb), (DRDAID = ????????.??-448669783631952048{111972}), Failed Statement is: SELECT r.MasterIp, BootCount, TestResultId, ProfileName, r.ProfileRefId, LatencyBucketsInUse, JitterBucketsInUse, UseOutOfSequence, StartDateTime, TestResultDescription, TestResultDescription2, PacketsSent, RoundTripPackets, OutOfOrderPackets, MinRoundTripLatency, MaxRoundTripLatency, TotalRoundTripLatency, LatencyBucketValue1, LatencyBucketValue2, LatencyBucketValue3, LatencyBucketValue4, LatencyBucketValue5, LatencyBucketValue6, LatencyBucketValue7, LatencyBucketValue8, LatencyBucketValue9, LatencyBucketValue10, JitterMeasurement, LocalRemoteJitterBucketValue1, LocalRemoteJitterBucketValue2, LocalRemoteJitterBucketValue3, LocalRemoteJitterBucketValue4, LocalRemoteJitterBucketValue5, LocalRemoteJitterBucketValue6, LocalRemoteJitterBucketValue7, LocalRemoteJitterBucketValue8, LocalRemoteJitterBucketValue9, MinLocalRemoteJitter, MaxLocalRemoteJitter, TotalLocalRemoteJitter, RemoteLocalJitterBucketValue1, RemoteLocalJitterBucketValue2, RemoteLocalJitterBucketValue3, RemoteLocalJitterBucketValue4, RemoteLocalJitterBucketValue5, RemoteLocalJitterBucketValue6, RemoteLocalJitterBucketValue7, RemoteLocalJitterBucketValue8, RemoteLocalJitterBucketValue9, MinRemoteLocalJitter, MaxRemoteLocalJitter, TotalRemoteLocalJitter, c1.Circuit as Circuit1, c2.Circuit as Circuit2 FROM PCS_V1.NPAResults r JOIN PCS_V1.NpaResultsProfile p on r.PROFILEREFID = p.PROFILEREFID JOIN PCS_V1.NPAResultsAddress a on r.ADDRESSREFID = a.ADDRESSREFID JOIN PCS_V1.NPAResultsCircuit c1 on r.CIRCUIT1REFID = c1.CIRCUITREFID JOIN PCS_V1.NPAResultsCircuit c2 on r.CIRCUIT2REFID = c2.CIRCUITREFID WHERE ReportKey >= '2012-02-03 12:45:00.0' AND ReportKey <= '2012-02-14 14:00:00.0' AND (r.ResultFlag = 0 OR (r.ResultFlag <> 0 AND r.RoundTripPackets > 0)) ORDER BY TestResultDescription, Circuit1, TestResultDescription2, ProfileName, StartDateTime
java.lang.OutOfMemoryError: Java heap space
The server is a Oracle M3000 and the Derby engine heap is setup to be 8Gb.
I know the user should not have run that query and I can correct and prevent that.
I am curious as to why however that the query just did not consume disk space for on disk sorting and take a really long time to return. The result of the query is being read by a Java application using a ResultSet that is configured as (ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY). Each record is being read and is producing an XML file from the data.
I would assume that Derby could query and sort this many results and present them through the record set interface although it may take a long time and may take significant disk space. Am I wrong about this?
Any comments will be most appreciated.
Brett
RE: Quetion on out memory usage
Posted by "Bergquist, Brett" <BB...@canoga.com>.
"I am having trouble finding the thread, so perhaps I dreamed all this"
I have days like that :)
Thanks for your help.
Brett
From: Katherine Marsden [mailto:kmarsdenderby@sbcglobal.net]
Sent: Wednesday, February 15, 2012 1:06 PM
To: derby-dev@db.apache.org
Subject: Re: Quetion on out memory usage
On 2/15/2012 9:41 AM, Bergquist, Brett wrote:
Thanks for the reply Kathrine. So in theory however if Derby's estimate works correctly, the sorting should spill to disk and there should be no OOM errors, right?
I was looking for the thread about this but can't find it just now and apologize for being so vague. Perhaps though this will jog someone else's memory.
As I recall, the scenario that was causing trouble was one where to start out with there was enough memory for the sort and then during processing other things consumed memory meaning there no longer was room. Having chosen to go down this path there was no turning back and Derby would run out of memory on the query. As to whether it "should" spill to disk in that case I don't know, I think the determination was at least that no one knew how to do do it. Again, I am having trouble finding the thread, so perhaps I dreamed all this....
Kathey
Re: Quetion on out memory usage
Posted by Katherine Marsden <km...@sbcglobal.net>.
On 2/15/2012 9:41 AM, Bergquist, Brett wrote:
>
> Thanks for the reply Kathrine. So in theory however if Derby's
> estimate works correctly, the sorting should spill to disk and there
> should be no OOM errors, right?
>
I was looking for the thread about this but can't find it just now and
apologize for being so vague. Perhaps though this will jog someone
else's memory.
As I recall, the scenario that was causing trouble was one where to
start out with there was enough memory for the sort and then during
processing other things consumed memory meaning there no longer was
room. Having chosen to go down this path there was no turning back and
Derby would run out of memory on the query. As to whether it "should"
spill to disk in that case I don't know, I think the determination was
at least that no one knew how to do do it. Again, I am having trouble
finding the thread, so perhaps I dreamed all this....
Kathey
RE: Quetion on out memory usage
Posted by "Bergquist, Brett" <BB...@canoga.com>.
Thanks for the reply Kathrine. So in theory however if Derby's estimate works correctly, the sorting should spill to disk and there should be no OOM errors, right?
I will get a copy of the database and setup to get the query plan.
Thanks.
Brett
From: Katherine Marsden [mailto:kmarsdenderby@sbcglobal.net]
Sent: Wednesday, February 15, 2012 12:10 PM
To: derby-dev@db.apache.org
Subject: Re: Quetion on out memory usage
On 2/15/2012 8:45 AM, Bergquist, Brett wrote:
I had a user in our production server that performed a query through a web service interface that requested to retrieve and perform a multi-level sort on probably 30 million records. The tables being queried on are in a UNION (basically one table for each week of the year and the query was working across the this week and the previous week so it probably could not use the indexes to avoid the sort.
Derby reported OutOfMemory errors. In derby.log are errors such as:
Tue Feb 14 20:15:12 EST 2012 Thread[DRDAConnThread_68,5,main] (XID = 861337977), (SESSIONID = 45137761), (DATABASE = csemdb), (DRDAID = ????????.??-866660124046835337{112060}), Failed Statement is: SELECT r.MasterIp, BootCount, TestResultId, ProfileName, r.ProfileRefId, LatencyBucketsInUse, JitterBucketsInUse, UseOutOfSequence, StartDateTime, TestResultDescription, TestResultDescription2, PacketsSent, RoundTripPackets, OutOfOrderPackets, MinRoundTripLatency, MaxRoundTripLatency, TotalRoundTripLatency, LatencyBucketValue1, LatencyBucketValue2, LatencyBucketValue3, LatencyBucketValue4, LatencyBucketValue5, LatencyBucketValue6, LatencyBucketValue7, LatencyBucketValue8, LatencyBucketValue9, LatencyBucketValue10, JitterMeasurement, LocalRemoteJitterBucketValue1, LocalRemoteJitterBucketValue2, LocalRemoteJitterBucketValue3, LocalRemoteJitterBucketValue4, LocalRemoteJitterBucketValue5, LocalRemoteJitterBucketValue6, LocalRemoteJitterBucketValue7, LocalRemoteJitterBucketValue8, LocalRemoteJitterBucketValue9, MinLocalRemoteJitter, MaxLocalRemoteJitter, TotalLocalRemoteJitter, RemoteLocalJitterBucketValue1, RemoteLocalJitterBucketValue2, RemoteLocalJitterBucketValue3, RemoteLocalJitterBucketValue4, RemoteLocalJitterBucketValue5, RemoteLocalJitterBucketValue6, RemoteLocalJitterBucketValue7, RemoteLocalJitterBucketValue8, RemoteLocalJitterBucketValue9, MinRemoteLocalJitter, MaxRemoteLocalJitter, TotalRemoteLocalJitter, c1.Circuit as Circuit1, c2.Circuit as Circuit2 FROM PCS_V1.NPAResults r JOIN PCS_V1.NpaResultsProfile p on r.PROFILEREFID = p.PROFILEREFID JOIN PCS_V1.NPAResultsAddress a on r.ADDRESSREFID = a.ADDRESSREFID JOIN PCS_V1.NPAResultsCircuit c1 on r.CIRCUIT1REFID = c1.CIRCUITREFID JOIN PCS_V1.NPAResultsCircuit c2 on r.CIRCUIT2REFID = c2.CIRCUITREFID WHERE ReportKey >= '2012-02-03 12:45:00.0' AND ReportKey <= '2012-02-14 14:00:00.0' AND (r.ResultFlag = 0 OR (r.ResultFlag <> 0 AND r.RoundTripPackets > 0)) ORDER BY TestResultDescription, Circuit1, TestResultDescription2, ProfileName, StartDateTime
java.lang.OutOfMemoryError: GC overhead limit exceeded
I have not personally encountered the GC overhead limit, but google search indicates it is related to the amount of time spent on garbage collection ("too much") and can be overridden.
http://www.petefreitag.com/item/746.cfm
Tue Feb 14 23:48:28 EST 2012 Thread[DRDAConnThread_122,5,main] (XID = 861195891), (SESSIONID = 45103057), (DATABASE = csemdb), (DRDAID = ????????.??-448669783631952048{111972}), Failed Statement is: SELECT r.MasterIp, BootCount, TestResultId, ProfileName, r.ProfileRefId, LatencyBucketsInUse, JitterBucketsInUse, UseOutOfSequence, StartDateTime, TestResultDescription, TestResultDescription2, PacketsSent, RoundTripPackets, OutOfOrderPackets, MinRoundTripLatency, MaxRoundTripLatency, TotalRoundTripLatency, LatencyBucketValue1, LatencyBucketValue2, LatencyBucketValue3, LatencyBucketValue4, LatencyBucketValue5, LatencyBucketValue6, LatencyBucketValue7, LatencyBucketValue8, LatencyBucketValue9, LatencyBucketValue10, JitterMeasurement, LocalRemoteJitterBucketValue1, LocalRemoteJitterBucketValue2, LocalRemoteJitterBucketValue3, LocalRemoteJitterBucketValue4, LocalRemoteJitterBucketValue5, LocalRemoteJitterBucketValue6, LocalRemoteJitterBucketValue7, LocalRemoteJitterBucketValue8, LocalRemoteJitterBucketValue9, MinLocalRemoteJitter, MaxLocalRemoteJitter, TotalLocalRemoteJitter, RemoteLocalJitterBucketValue1, RemoteLocalJitterBucketValue2, RemoteLocalJitterBucketValue3, RemoteLocalJitterBucketValue4, RemoteLocalJitterBucketValue5, RemoteLocalJitterBucketValue6, RemoteLocalJitterBucketValue7, RemoteLocalJitterBucketValue8, RemoteLocalJitterBucketValue9, MinRemoteLocalJitter, MaxRemoteLocalJitter, TotalRemoteLocalJitter, c1.Circuit as Circuit1, c2.Circuit as Circuit2 FROM PCS_V1.NPAResults r JOIN PCS_V1.NpaResultsProfile p on r.PROFILEREFID = p.PROFILEREFID JOIN PCS_V1.NPAResultsAddress a on r.ADDRESSREFID = a.ADDRESSREFID JOIN PCS_V1.NPAResultsCircuit c1 on r.CIRCUIT1REFID = c1.CIRCUITREFID JOIN PCS_V1.NPAResultsCircuit c2 on r.CIRCUIT2REFID = c2.CIRCUITREFID WHERE ReportKey >= '2012-02-03 12:45:00.0' AND ReportKey <= '2012-02-14 14:00:00.0' AND (r.ResultFlag = 0 OR (r.ResultFlag <> 0 AND r.RoundTripPackets > 0)) ORDER BY TestResultDescription, Circuit1, TestResultDescription2, ProfileName, StartDateTime
java.lang.OutOfMemoryError: Java heap space
The server is a Oracle M3000 and the Derby engine heap is setup to be 8Gb.
For a heap out of memory, I have seen occasional cases where Derby's estimate of whether it can sort in memory is wrong. You'd need to look at the plan and do some debugging with the actual query if you are able to reproduce to find out if that is the case here.
RE: Quetion on out memory usage
Posted by "Bergquist, Brett" <BB...@canoga.com>.
I will try that when I get the database locally. It is 20Gb compressed, so it is taking a while for the customer to get me a copy.
Brett
-----Original Message-----
From: Kristian Waagan [mailto:kristian.waagan@oracle.com]
Sent: Wednesday, February 15, 2012 4:15 PM
To: derby-dev@db.apache.org
Subject: Re: Quetion on out memory usage
On 15.02.2012 19:59, Bergquist, Brett wrote:
> Thanks for the reply Kristian.
>
> The ++HeapDumpOnOutOfMemoryError is of no use because up to now, I have not found any tool that can read the heap dump when the heap is configured to 8Gb. I have tried with this before.
What about getting just the heap histogram?
I'm assuming it takes a while to fill the heap when it's 8 GB, so a script fetching the histogram every now and then may give us a clue to what's going on.
--
Kristian
>
> The stack traces when the " java.lang.OutOfMemoryError: GC overhead limit exceeded" is reported are in the Derby.log, so it is Derby that is trying to put "more stuff on the heap than what can fit there". Note that Derby is running in its own JVM with an 8Gb heap. The system actually has 32Gb of physical memory and the app server is constrained to 12Gb.
>
> I am in the process of getting the database back locally onto a test system. I will probably turn down the heap size to 2Gb and try the query. Maybe then a tool can analyze the heap.
>
> Again, I appreciate the response and will post what I find.
>
> Brett
>
> -----Original Message-----
> From: Kristian Waagan [mailto:kristian.waagan@oracle.com]
> Sent: Wednesday, February 15, 2012 1:28 PM
> To: derby-dev@db.apache.org
> Subject: Re: Quetion on out memory usage
>
> On 15.02.2012 18:09, Katherine Marsden wrote:
>> On 2/15/2012 8:45 AM, Bergquist, Brett wrote:
> < snip>
>>> java.lang.OutOfMemoryError: GC overhead limit exceeded
>>>
>> I have not personally encountered the GC overhead limit, but google
>> search indicates it is related to the amount of time spent on garbage
>> collection ("too much") and can be overridden.
>> http://www.petefreitag.com/item/746.cfm
> I've seen this several times and for practical purposes I have always considered this the same as "Java heap space": for whatever reason you're trying to put more stuff on the heap than what can fit there.
>
>
> < snip>
>>> The server is a Oracle M3000 and the Derby engine heap is setup to
>>> be 8Gb.
>>>
>> For a heap out of memory, I have seen occasional cases where Derby's
>> estimate of whether it can sort in memory is wrong. You'd need to
>> look at the plan and do some debugging with the actual query if you
>> are able to reproduce to find out if that is the case here.
>>
> Not sure, but the code in impl/store/access/sort/MergeInserter may be relevant and a good starting point.
>
> If you're debugging this, it may be useful to specify
> -XX:+HeapDumpOnOutOfMemoryError (and possibly
> |-XX:HeapDumpPath=/your/dump/path).
>
> |
Re: Quetion on out memory usage
Posted by Kristian Waagan <kr...@oracle.com>.
On 15.02.2012 19:59, Bergquist, Brett wrote:
> Thanks for the reply Kristian.
>
> The ++HeapDumpOnOutOfMemoryError is of no use because up to now, I have not found any tool that can read the heap dump when the heap is configured to 8Gb. I have tried with this before.
What about getting just the heap histogram?
I'm assuming it takes a while to fill the heap when it's 8 GB, so a
script fetching the histogram every now and then may give us a clue to
what's going on.
--
Kristian
>
> The stack traces when the " java.lang.OutOfMemoryError: GC overhead limit exceeded" is reported are in the Derby.log, so it is Derby that is trying to put "more stuff on the heap than what can fit there". Note that Derby is running in its own JVM with an 8Gb heap. The system actually has 32Gb of physical memory and the app server is constrained to 12Gb.
>
> I am in the process of getting the database back locally onto a test system. I will probably turn down the heap size to 2Gb and try the query. Maybe then a tool can analyze the heap.
>
> Again, I appreciate the response and will post what I find.
>
> Brett
>
> -----Original Message-----
> From: Kristian Waagan [mailto:kristian.waagan@oracle.com]
> Sent: Wednesday, February 15, 2012 1:28 PM
> To: derby-dev@db.apache.org
> Subject: Re: Quetion on out memory usage
>
> On 15.02.2012 18:09, Katherine Marsden wrote:
>> On 2/15/2012 8:45 AM, Bergquist, Brett wrote:
> < snip>
>>> java.lang.OutOfMemoryError: GC overhead limit exceeded
>>>
>> I have not personally encountered the GC overhead limit, but google
>> search indicates it is related to the amount of time spent on garbage
>> collection ("too much") and can be overridden.
>> http://www.petefreitag.com/item/746.cfm
> I've seen this several times and for practical purposes I have always considered this the same as "Java heap space": for whatever reason you're trying to put more stuff on the heap than what can fit there.
>
>
> < snip>
>>> The server is a Oracle M3000 and the Derby engine heap is setup to be
>>> 8Gb.
>>>
>> For a heap out of memory, I have seen occasional cases where Derby's
>> estimate of whether it can sort in memory is wrong. You'd need to
>> look at the plan and do some debugging with the actual query if you
>> are able to reproduce to find out if that is the case here.
>>
> Not sure, but the code in impl/store/access/sort/MergeInserter may be relevant and a good starting point.
>
> If you're debugging this, it may be useful to specify -XX:+HeapDumpOnOutOfMemoryError (and possibly
> |-XX:HeapDumpPath=/your/dump/path).
>
> |
RE: Quetion on out memory usage
Posted by "Bergquist, Brett" <BB...@canoga.com>.
Thanks for the reply Kristian.
The ++HeapDumpOnOutOfMemoryError is of no use because up to now, I have not found any tool that can read the heap dump when the heap is configured to 8Gb. I have tried with this before.
The stack traces when the " java.lang.OutOfMemoryError: GC overhead limit exceeded" is reported are in the Derby.log, so it is Derby that is trying to put "more stuff on the heap than what can fit there". Note that Derby is running in its own JVM with an 8Gb heap. The system actually has 32Gb of physical memory and the app server is constrained to 12Gb.
I am in the process of getting the database back locally onto a test system. I will probably turn down the heap size to 2Gb and try the query. Maybe then a tool can analyze the heap.
Again, I appreciate the response and will post what I find.
Brett
-----Original Message-----
From: Kristian Waagan [mailto:kristian.waagan@oracle.com]
Sent: Wednesday, February 15, 2012 1:28 PM
To: derby-dev@db.apache.org
Subject: Re: Quetion on out memory usage
On 15.02.2012 18:09, Katherine Marsden wrote:
> On 2/15/2012 8:45 AM, Bergquist, Brett wrote:
< snip>
>>
>> java.lang.OutOfMemoryError: GC overhead limit exceeded
>>
>
> I have not personally encountered the GC overhead limit, but google
> search indicates it is related to the amount of time spent on garbage
> collection ("too much") and can be overridden.
> http://www.petefreitag.com/item/746.cfm
I've seen this several times and for practical purposes I have always considered this the same as "Java heap space": for whatever reason you're trying to put more stuff on the heap than what can fit there.
< snip>
>>
>> The server is a Oracle M3000 and the Derby engine heap is setup to be
>> 8Gb.
>>
> For a heap out of memory, I have seen occasional cases where Derby's
> estimate of whether it can sort in memory is wrong. You'd need to
> look at the plan and do some debugging with the actual query if you
> are able to reproduce to find out if that is the case here.
>
Not sure, but the code in impl/store/access/sort/MergeInserter may be relevant and a good starting point.
If you're debugging this, it may be useful to specify -XX:+HeapDumpOnOutOfMemoryError (and possibly
|-XX:HeapDumpPath=/your/dump/path).
|
--
Kristian
Re: Quetion on out memory usage
Posted by Kristian Waagan <kr...@oracle.com>.
On 15.02.2012 18:09, Katherine Marsden wrote:
> On 2/15/2012 8:45 AM, Bergquist, Brett wrote:
< snip>
>>
>> java.lang.OutOfMemoryError: GC overhead limit exceeded
>>
>
> I have not personally encountered the GC overhead limit, but google
> search indicates it is related to the amount of time spent on garbage
> collection ("too much") and can be overridden.
> http://www.petefreitag.com/item/746.cfm
I've seen this several times and for practical purposes I have always
considered this the same as "Java heap space": for whatever reason
you're trying to put more stuff on the heap than what can fit there.
< snip>
>>
>> The server is a Oracle M3000 and the Derby engine heap is setup to be
>> 8Gb.
>>
> For a heap out of memory, I have seen occasional cases where Derby's
> estimate of whether it can sort in memory is wrong. You'd need to
> look at the plan and do some debugging with the actual query if you
> are able to reproduce to find out if that is the case here.
>
Not sure, but the code in impl/store/access/sort/MergeInserter may be
relevant and a good starting point.
If you're debugging this, it may be useful to specify
-XX:+HeapDumpOnOutOfMemoryError (and possibly
|-XX:HeapDumpPath=/your/dump/path).
|
--
Kristian
Re: Quetion on out memory usage
Posted by Katherine Marsden <km...@sbcglobal.net>.
On 2/15/2012 8:45 AM, Bergquist, Brett wrote:
>
> I had a user in our production server that performed a query through a
> web service interface that requested to retrieve and perform a
> multi-level sort on probably 30 million records. The tables being
> queried on are in a UNION (basically one table for each week of the
> year and the query was working across the this week and the previous
> week so it probably could not use the indexes to avoid the sort.
>
> Derby reported OutOfMemory errors. In derby.log are errors such as:
>
> Tue Feb 14 20:15:12 EST 2012 Thread[DRDAConnThread_68,5,main] (XID =
> 861337977), (SESSIONID = 45137761), (DATABASE = csemdb), (DRDAID =
> ????????.??-866660124046835337{112060}), Failed Statement is: SELECT
> r.MasterIp, BootCount, TestResultId, ProfileName, r.ProfileRefId,
> LatencyBucketsInUse, JitterBucketsInUse, UseOutOfSequence,
> StartDateTime, TestResultDescription, TestResultDescription2,
> PacketsSent, RoundTripPackets, OutOfOrderPackets, MinRoundTripLatency,
> MaxRoundTripLatency, TotalRoundTripLatency, LatencyBucketValue1,
> LatencyBucketValue2, LatencyBucketValue3, LatencyBucketValue4,
> LatencyBucketValue5, LatencyBucketValue6, LatencyBucketValue7,
> LatencyBucketValue8, LatencyBucketValue9, LatencyBucketValue10,
> JitterMeasurement, LocalRemoteJitterBucketValue1,
> LocalRemoteJitterBucketValue2, LocalRemoteJitterBucketValue3,
> LocalRemoteJitterBucketValue4, LocalRemoteJitterBucketValue5,
> LocalRemoteJitterBucketValue6, LocalRemoteJitterBucketValue7,
> LocalRemoteJitterBucketValue8, LocalRemoteJitterBucketValue9,
> MinLocalRemoteJitter, MaxLocalRemoteJitter, TotalLocalRemoteJitter,
> RemoteLocalJitterBucketValue1, RemoteLocalJitterBucketValue2,
> RemoteLocalJitterBucketValue3, RemoteLocalJitterBucketValue4,
> RemoteLocalJitterBucketValue5, RemoteLocalJitterBucketValue6,
> RemoteLocalJitterBucketValue7, RemoteLocalJitterBucketValue8,
> RemoteLocalJitterBucketValue9, MinRemoteLocalJitter,
> MaxRemoteLocalJitter, TotalRemoteLocalJitter, c1.Circuit as Circuit1,
> c2.Circuit as Circuit2 FROM PCS_V1.NPAResults r JOIN
> PCS_V1.NpaResultsProfile p on r.PROFILEREFID = p.PROFILEREFID JOIN
> PCS_V1.NPAResultsAddress a on r.ADDRESSREFID = a.ADDRESSREFID JOIN
> PCS_V1.NPAResultsCircuit c1 on r.CIRCUIT1REFID = c1.CIRCUITREFID JOIN
> PCS_V1.NPAResultsCircuit c2 on r.CIRCUIT2REFID = c2.CIRCUITREFID WHERE
> ReportKey >= '2012-02-03 12:45:00.0' AND ReportKey <= '2012-02-14
> 14:00:00.0' AND (r.ResultFlag = 0 OR (r.ResultFlag <> 0 AND
> r.RoundTripPackets > 0)) ORDER BY TestResultDescription, Circuit1,
> TestResultDescription2, ProfileName, StartDateTime
>
> java.lang.OutOfMemoryError: GC overhead limit exceeded
>
I have not personally encountered the GC overhead limit, but google
search indicates it is related to the amount of time spent on garbage
collection ("too much") and can be overridden.
http://www.petefreitag.com/item/746.cfm
> Tue Feb 14 23:48:28 EST 2012 Thread[DRDAConnThread_122,5,main] (XID =
> 861195891), (SESSIONID = 45103057), (DATABASE = csemdb), (DRDAID =
> ????????.??-448669783631952048{111972}), Failed Statement is: SELECT
> r.MasterIp, BootCount, TestResultId, ProfileName, r.ProfileRefId,
> LatencyBucketsInUse, JitterBucketsInUse, UseOutOfSequence,
> StartDateTime, TestResultDescription, TestResultDescription2,
> PacketsSent, RoundTripPackets, OutOfOrderPackets, MinRoundTripLatency,
> MaxRoundTripLatency, TotalRoundTripLatency, LatencyBucketValue1,
> LatencyBucketValue2, LatencyBucketValue3, LatencyBucketValue4,
> LatencyBucketValue5, LatencyBucketValue6, LatencyBucketValue7,
> LatencyBucketValue8, LatencyBucketValue9, LatencyBucketValue10,
> JitterMeasurement, LocalRemoteJitterBucketValue1,
> LocalRemoteJitterBucketValue2, LocalRemoteJitterBucketValue3,
> LocalRemoteJitterBucketValue4, LocalRemoteJitterBucketValue5,
> LocalRemoteJitterBucketValue6, LocalRemoteJitterBucketValue7,
> LocalRemoteJitterBucketValue8, LocalRemoteJitterBucketValue9,
> MinLocalRemoteJitter, MaxLocalRemoteJitter, TotalLocalRemoteJitter,
> RemoteLocalJitterBucketValue1, RemoteLocalJitterBucketValue2,
> RemoteLocalJitterBucketValue3, RemoteLocalJitterBucketValue4,
> RemoteLocalJitterBucketValue5, RemoteLocalJitterBucketValue6,
> RemoteLocalJitterBucketValue7, RemoteLocalJitterBucketValue8,
> RemoteLocalJitterBucketValue9, MinRemoteLocalJitter,
> MaxRemoteLocalJitter, TotalRemoteLocalJitter, c1.Circuit as Circuit1,
> c2.Circuit as Circuit2 FROM PCS_V1.NPAResults r JOIN
> PCS_V1.NpaResultsProfile p on r.PROFILEREFID = p.PROFILEREFID JOIN
> PCS_V1.NPAResultsAddress a on r.ADDRESSREFID = a.ADDRESSREFID JOIN
> PCS_V1.NPAResultsCircuit c1 on r.CIRCUIT1REFID = c1.CIRCUITREFID JOIN
> PCS_V1.NPAResultsCircuit c2 on r.CIRCUIT2REFID = c2.CIRCUITREFID WHERE
> ReportKey >= '2012-02-03 12:45:00.0' AND ReportKey <= '2012-02-14
> 14:00:00.0' AND (r.ResultFlag = 0 OR (r.ResultFlag <> 0 AND
> r.RoundTripPackets > 0)) ORDER BY TestResultDescription, Circuit1,
> TestResultDescription2, ProfileName, StartDateTime
>
> java.lang.OutOfMemoryError: Java heap space
>
> The server is a Oracle M3000 and the Derby engine heap is setup to be
> 8Gb.
>
For a heap out of memory, I have seen occasional cases where Derby's
estimate of whether it can sort in memory is wrong. You'd need to look
at the plan and do some debugging with the actual query if you are able
to reproduce to find out if that is the case here.