Heap and stack size for Derby server

[Robert Enyedi <ro...@intland.com>]

Hi,

I've recently tested Derby with a SELECT statement containing 5000 
elements inside an IN clause. The query looked something like this:

SELECT field1 FROM table1 WHERE field1 IN (0, 1, 2, ..., 4999)

This failed with a server side StackOverflowError. True, I got past this 
issue by starting Derby with the "-Xss1024k" JVM option, but this was a 
trial and error approach.

Are there any recommendations on how to set the size of the heap and 
stack for a Derby instance? Is there a way to estimate the memory 
requirements of a Derby database? Did anyone had such experiences?

Regards,
Robert

PS: I've been using the Derby 10.2.1.6 release in networked mode running 
on Sun JRE 1.5.0_09.

Re: Heap and stack size for Derby server

[Mike Matrigali <mi...@sbcglobal.net>]

I would report this as a separate issue and possibly link it to
DERBY-47, that way anyone who fixes it can also run your test case
to make sure it is also addressed.  It may be fixed by DERBY-47
but may not.  Including a stack trace with the simple test program will
help a lot in understanding where the issue is (ie. did the stack 
overflow while compiling the 5000 term IN list, or did it overflow 
during execution of the generated queryplan).

In 10.2 a lot of work was done by Dan on reducing the resources during
compile time of queries that had a lot of terms.  Without the stack
trace it is hard to guess if this is a compile or execution issue.

Do note that currently in derby IN list queries are probably not going
to perform as you expect.  They do not do a probe using the index for
each term.  There are a few workarounds, which require rewriting the
query.

Bryan Pendleton wrote:
>> My problems came from a simple query containing an IN clause with 5000 
>> items in it. I went over this easily by increasing the stack size 
>> limit to 1024 KB.
> 
> 
> Thanks Robert! That definitely sounds like DERBY-47. If you have the time,
> it'd be great to have some help in working on improving this part of Derby.
> 
> I'm glad you were able to find a workaround to the problem.
> 
>> The question I'm asking is if there are some best practicing in sizing 
>> the heap and stack for the Derby process based on the query 
>> complexities, number of database objects and estimated amount of data.
> 
> 
> I think that one reason you haven't had a lot of response on this is that
> many people aren't experiencing a lot of problems in this area. In my
> case, for example, my Derby application has been running quite happily,
> 24/7, for several years, in the default heap and stack.
> 
> thanks,
> 
> bryan
> 
> 
> 

Re: Heap and stack size for Derby server

[John Embretsen <Jo...@Sun.COM>]

Robert Enyedi wrote:

> Thanks for all of your replies, but I'm not saying that we have a bug 
> here. It's just that for large IN clauses there are many levels of 
> recursive calls in the server.

In some cases, whether it is a bug or not depends on the eye of the beholder. As 
someone already mentioned, one reason why you have not seen many "best 
practices" when it comes to heap/stack size may be that it usually works.

In the event that someone encounters a case like this (StackOverflow), it is 
nice to see that derby developers catch interest and start investigating to find 
out more. Hopefully the issue can be resolved so that the best practices can 
continue to be "use the defaults" in most environments.

> This reminded me of the other runtime memory limit, the heap. And so I 
> asked for some best practices on sizing these.

The only one I can think of is "make sure your Java heap can fit your page cache".


-- 
John

Re: Heap and stack size for Derby server

[Robert Enyedi <ro...@intland.com>]

Thanks for all of your replies, but I'm not saying that we have a bug 
here. It's just that for large IN clauses there are many levels of 
recursive calls in the server.

This reminded me of the other runtime memory limit, the heap. And so I 
asked for some best practices on sizing these.

Regards,
Robert

Army wrote:
> Bryan Pendleton wrote:
>>> I don't think this is DERBY-47. DERBY-47 is the issue that the plan 
>>> generated by an IN query is inefficient.
>>>
>>> This issue is that a query with a large number of IN parameters 
>>> fails to compile due to a stack overflow error.
>
> Do we know for sure that it's a compile time error and not an 
> execution error? I haven't been following this that closely so sorry 
> if that's been stated somewhere.
>
>> Is this issue already known, then? Or would it be helpful for Robert to
>> file a new Jira issue to track it?
>
> Sort of kind of maybe seems similar to DERBY-634 (don't let the title 
> of that issue throw you).  But of course, DERBY-634 is marked as 
> resolved in 10.2.1.6, which is the version Robert is using.  So that's 
> not *the* issue...but maybe it's related?
>
> Army
>
>

Re: Heap and stack size for Derby server

[Army <qo...@gmail.com>]

Bryan Pendleton wrote:
>> I don't think this is DERBY-47. DERBY-47 is the issue that the plan 
>> generated by an IN query is inefficient.
>>
>> This issue is that a query with a large number of IN parameters fails 
>> to compile due to a stack overflow error.

Do we know for sure that it's a compile time error and not an execution error? 
I haven't been following this that closely so sorry if that's been stated somewhere.

> Is this issue already known, then? Or would it be helpful for Robert to
> file a new Jira issue to track it?

Sort of kind of maybe seems similar to DERBY-634 (don't let the title of that 
issue throw you).  But of course, DERBY-634 is marked as resolved in 10.2.1.6, 
which is the version Robert is using.  So that's not *the* issue...but maybe 
it's related?

Army

Re: Heap and stack size for Derby server

[Bryan Pendleton <bp...@amberpoint.com>]

> I don't think this is DERBY-47. DERBY-47 is the issue that the plan 
> generated by an IN query is inefficient.
> 
> This issue is that a query with a large number of IN parameters fails to 
> compile due to a stack overflow error.

Good point.

Is this issue already known, then? Or would it be helpful for Robert to
file a new Jira issue to track it?

thanks,

bryan

Re: Heap and stack size for Derby server

[Daniel John Debrunner <dj...@apache.org>]

Bryan Pendleton wrote:
>> My problems came from a simple query containing an IN clause with 5000 
>> items in it. I went over this easily by increasing the stack size 
>> limit to 1024 KB.
> 
> Thanks Robert! That definitely sounds like DERBY-47. If you have the time,
> it'd be great to have some help in working on improving this part of Derby.

I don't think this is DERBY-47. DERBY-47 is the issue that the plan 
generated by an IN query is inefficient.

This issue is that a query with a large number of IN parameters fails to 
compile due to a stack overflow error.

Dan.

Re: Heap and stack size for Derby server

[Bryan Pendleton <bp...@amberpoint.com>]

> My problems came from a simple query containing an IN clause with 5000 
> items in it. I went over this easily by increasing the stack size limit 
> to 1024 KB.

Thanks Robert! That definitely sounds like DERBY-47. If you have the time,
it'd be great to have some help in working on improving this part of Derby.

I'm glad you were able to find a workaround to the problem.

> The question I'm asking is if there are some best practicing in sizing 
> the heap and stack for the Derby process based on the query 
> complexities, number of database objects and estimated amount of data.

I think that one reason you haven't had a lot of response on this is that
many people aren't experiencing a lot of problems in this area. In my
case, for example, my Derby application has been running quite happily,
24/7, for several years, in the default heap and stack.

thanks,

bryan

Re: Heap and stack size for Derby server

[Robert Enyedi <ro...@intland.com>]

Bryan,

My problems came from a simple query containing an IN clause with 5000 
items in it. I went over this easily by increasing the stack size limit 
to 1024 KB.
The question I'm asking is if there are some best practicing in sizing 
the heap and stack for the Derby process based on the query 
complexities, number of database objects and estimated amount of data.

Regards,
Robert

Bryan Pendleton wrote:
>> I guess someone with more knowledge about Derby's internals may be 
>> able to tell you why your specific query requires a larger stack size.
>
> I think that the query was noted to use an IN clause, so he
> may be encountering either http://issues.apache.org/jira/browse/DERBY-47
> or http://issues.apache.org/jira/browse/DERBY-713.
>
> Robert, can you tell us more about the particular query that is giving
> you trouble?
>
> thanks,
>
> bryan
>
>

Re: Heap and stack size for Derby server

[Bryan Pendleton <bp...@amberpoint.com>]

> I guess someone with more knowledge about Derby's internals may be able 
> to tell you why your specific query requires a larger stack size.

I think that the query was noted to use an IN clause, so he
may be encountering either http://issues.apache.org/jira/browse/DERBY-47
or http://issues.apache.org/jira/browse/DERBY-713.

Robert, can you tell us more about the particular query that is giving
you trouble?

thanks,

bryan

Re: Heap and stack size for Derby server

[John Embretsen <Jo...@Sun.COM>]

Robert Enyedi wrote:
> Does the lack of answers mean that there are no best practices in 
> setting the stack and heap limits for Derby?

Well, not specifically, that I know of. It all depends on how you (and clients 
connecting to your server) are using Derby.

Default stack sizes work very well in most cases. I have little experience with 
StackOverflowErrors myself, but there are several Jira issues you can check out 
for comments on adjusting _heap_ size, for example DERBY-1713 
(http://issues.apache.org/jira/browse/DERBY-1713) and DERBY-1564.

I guess someone with more knowledge about Derby's internals may be able to tell 
you why your specific query requires a larger stack size.

As for heap size, I would not run the network server with any less than 32 MB of 
max heap space, regardless of the client code. Most of the tests I've run 
against Derby work fine with default heap size (usually minimum 64 MB max size, 
depending on JVM and hardware). Your mileage may vary...


-- 
John

Re: Heap and stack size for Derby server

[Robert Enyedi <ro...@intland.com>]

Does the lack of answers mean that there are no best practices in 
setting the stack and heap limits for Derby?

Regards,
Robert

Robert Enyedi wrote:
> Hi,
>
> I've recently tested Derby with a SELECT statement containing 5000 
> elements inside an IN clause. The query looked something like this:
>
> SELECT field1 FROM table1 WHERE field1 IN (0, 1, 2, ..., 4999)
>
> This failed with a server side StackOverflowError. True, I got past 
> this issue by starting Derby with the "-Xss1024k" JVM option, but this 
> was a trial and error approach.
>
> Are there any recommendations on how to set the size of the heap and 
> stack for a Derby instance? Is there a way to estimate the memory 
> requirements of a Derby database? Did anyone had such experiences?
>
> Regards,
> Robert
>
> PS: I've been using the Derby 10.2.1.6 release in networked mode 
> running on Sun JRE 1.5.0_09.
>