db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Quetion on out memory usage
Date Wed, 15 Feb 2012 17:41:23 GMT
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.

Mime
View raw message