Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of peter.ondruska@gmail.com
 designates 209.85.219.212 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        b=XCA3ArBKHyhyxntd/7Kmc3cAoX9fRTUdbCxfeAfY2N2G8INV3obTmz7QQFdWlV32jC
         NEYZBWmjD/zXFTIXA+TPDFB3x2a9WbsCWDqqiC2QAjDL9fK43JRwkLdm0EV9n0vwlJV+
         5EplWQzM7tOoshAI2pYQHXHFk3i8ffeOUt5pE=
MIME-Version: 1.0
In-Reply-To: <F269E3CF-8B05-40EB-86A6-C78122BD8DCE@gmail.com>
References: <E337D445-5890-465F-A8AB-BBC05472D468@gmail.com>
	 <4d2670741002080621o3409f084t288587d3088738aa@mail.gmail.com>
	 <F269E3CF-8B05-40EB-86A6-C78122BD8DCE@gmail.com>
Date: Mon, 8 Feb 2010 15:37:59 +0100
Message-ID: <4d2670741002080637u4483e31scd1065ce32c78904@mail.gmail.com>
Subject: Re: OutOfMemoryErrors on group by select
From: =?UTF-8?Q?Peter_Ondru=C5=A1ka?= <peter.ondruska@gmail.com>
To: Derby Discussion <derby-user@db.apache.org>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Try creating a non-unique index on the column you group by. Have you
tried more recent Derby version if you can reproduce that there?

On Mon, Feb 8, 2010 at 3:25 PM, Ronald Rudy <ronchalant@gmail.com> wrote:
> Version is 10.4.2.0, and no I don't - I am actually trying the group by/e=
tc. on another column that is indexed to see if there's any memory benefits=
.. =C2=A0The table isn't optimized for the below statement because in produ=
ction it will never be executed like that, I'm just trying to monitor some =
counts while the app is running..
>
>
> On Feb 8, 2010, at 9:21:29 AM, Peter Ondru=C5=A1ka wrote:
>
>> What Derby version is this? Do you have an index on groupCol?
>>
>> On Mon, Feb 8, 2010 at 3:11 PM, Ronald Rudy <ronchalant@gmail.com> wrote=
:
>>> I've gotten to the point where I seem to be able to frequently induce O=
utOfMemoryErrors when executing a statement like this one:
>>>
>>> SELECT groupCol, count(*) FROM myTable GROUP BY groupCol;
>>>
>>> The total number of records doesn't seem terribly large (about 400,000 =
records) though while I'm executing the above statement there are likely so=
me inserts and deletes happening concurrently. =C2=A0I don't need anything =
transactional here, I'm really just monitoring a table from a separate netw=
ork connection.
>>>
>>> In our app the database runs in its own JSVC daemon as a NetworkServer =
with 512MB allocated to it. =C2=A0Page cache size is set to 2000. =C2=A0 =
=C2=A0Page size should be Derby default (4096 I think).
>>>
>>> I cannot be 100% sure that the above is specifically what is causing th=
e error, but I can say that I haven't seen memory errors until I started mo=
nitoring this table frequently and even then I really only saw it when the =
table size started getting a bit bigger (not big by any stretch, but > 300k=
 rows).
>>>
>>> The table itself is not really big - there are 7 varchar columns along =
with an ID column.
>>>
>>> In this production-level situation stability is more important than per=
formance. =C2=A0I'd like Derby configured so that no matter what is request=
ed SQL wise the daemon itself is not impacted.
>>>
>>> I'd also like to be able to build in some handling code perhaps that if=
/when an OutOfMemoryError is encountered, it will automatically restart - f=
or this I might need to add a script with -XX:OnOutOfMemoryError as a JVM o=
ption unless there's some way to handle this internally?
>>>
>>> -Ron
>
>