Bryan Pendleton wrote:
Is it really realistic, though, to allow a setting of 2 million,
which gives the optimizer permission to try to create a
in-memory hash table of 2 gigabytes? I mean, I'd need a 64-bit JVM...
Related, is it worth putting a line or two in this doc telling the
user that it would be foolish to set this value to something which
is higher than the actual physical memory that they have on their
machine -- if I have a 256 Mb machine, telling the optimizer to do
a 512 Mb hash join in "memory" is probably performance suicide, no?
I think these are both excellent points, and I think that Yes, it would be a
good idea to add the appropriate notes to the documentation. When I answered
the question of "max value", I was just looking at it from a programmatic
standpoint--but as you've pointed out, there are some practical considerations
here, as well. Note that "performance suicide" is the right term here--as
described below, the query *should* theoretically still execute, but it might
suffer from a spill-to-disk performance penalty.
Also, perhaps we should have a line suggesting that if the
user decides to increase this value, they should check their JVM
memory settings and make sure they've authorized the JVM to use
that much memory (e.g., on Sun JVMs, -XmxNNNm should be set).
Sounds good to me--thanks for pointing that out. Note though, that if the user
does not give the JVM more memory, the query should (in theory) still work--see
below--but it might take a performance hit at execution time.
Lastly, what is the symptom if the user sets this too high? If
they tell the optimizer that it's allowed to make, for example,
a 200 Mb hash table by setting this value to 200,000, then what
happens if the runtime evaluation of the query plan finds that
it can't allocate that much memory? Does the query fail? Does
Derby crash?
Yet another very good question. I haven't done thorough testing, but my *guess*
is that this issue is indirectly addressed by some "follow-up" changes that I
posted for DERBY-1007: in particular, see the BackingStoreHashtable changes in
d1007_followup_v1.patch, which is attached to that issue. The changes there do
a very rough (and only slightly meaningful, unit-wise) comparison of estimated
row count to max in-memory size, and if it thinks there's not enough memory in
the JVM, it will create a smaller hash table and then spill the excess to disk.
I looked at the relevant code in the BackingStoreHashtable and the value of
max_inmemory_size is set as follows:
if( max_inmemory_rowcnt > 0)
max_inmemory_size = Long.MAX_VALUE;
else
max_inmemory_size = Runtime.getRuntime().totalMemory()/100;
where max_inmemory_rowcnt is received from the optimizer. So far as I can tell
from the quick tests I did, in cases where we're creating a hash table for a
hash join as determined by the optimizer, max_inmemory_rowcnt is always -1 and
thus max_inmemory_size is set based on the available JVM memory at the time the
backing store hash table is created. In such a case the BackingStoreHashtable
will receive the estimated row count from the optimizer. If the
maxMemoryPerTable was set "too high", then the estimated row count that we get
could be insanely high, as well (if maxMemoryPerTable was lower, the optimizer
would have rejected the hash join and we never would have made it here). That
said, the DERBY-1007 follow-up changes added logic to change the way a hash
table is built if the estimated row count is "too high": namely, if it's equal
to or larger than the max in-memory bytes, we'll create a hash table that is
only as large as { max in-memory bytes / estimated size of first row }.
And that, I think, means that we should only create a hash table that will
actually fit in the available JVM memory; if we need a larger hash table, then
we'll spill to disk and take the performance hit, but the query should still
execute. In theory.
Of course, all of that is assuming that the "red flag" for a large hash table is
a high row count. If it turns out that we have a low row count but each row is
extremely large, the changes for DERBY-1007 will not be triggered. I think,
however, that we should still be okay, but for a different reason. In this
scenario, the hash table itself will not be very large, so we should have enough
memory to create it (empty). Then, the BackingStoreHashtable code keeps track
of how much JVM memory is available after each insertion into the hash table.
So if we have a couple of very large rows, the available memory will decrease
with each row and the optimizer will start spilling to disk before the hash
table is full--which should keep us from running out of memory...
I think. If I've got any of this wrong, someone please feel free to correct me!
So to get back to your final question of "what is the symptom if the user sets
this too high?", the theoretical answer is that the query performance might end
up being lousy because the hash table will spill to disk. A more practical (and
admittedly uglier) answer is that the query might perform poorly OR the user
might encounter memory problems (including OutOfMemory errors) at execution time
when Derby is building the hash table. Theoretically the OutOfMemory conditions
shouldn't arise (as explained above), and I haven't been able to produce any
since DERBY-1007 was closed, but I guess the possibility is still there. If we
choose to document the possibility, then a user who changes maxMemoryPerTable
and suddenly hits an OutOfMemory error might find it useful to have that
behavior documented as a "potential" problem with a maxMemoryPerTable value that
is too large.
Well, that took a lot longer to write than I thought it would. Hopefully the
answers to your questions are somewhere in there...?
Army
