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 Katherine Marsden <km...@sbcglobal.net> on 2013/03/01 18:28:26 UTC
Re: How Much Memory for hash joins
On 2/28/2013 9:11 AM, Mike Matrigali wrote:
> In BackingStoreHashTableFromScan I see.
>>
>> this.max_inmemory_rowcnt = max_inmemory_rowcnt;
>> if( max_inmemory_rowcnt > 0)
>> max_inmemory_size = Long.MAX_VALUE;
>> else
>> max_inmemory_size = Runtime.getRuntime().totalMemory()/100;
>>
I have been reading the comments and trying to make sense of the logic
and understand all that is happening with max_inmemory_size. I don't
have a test case that goes through the else part of the condition above.
One thing I did notice is that Runtime.getRuntime().totalMemory()
returns really different things if -Xms is set large, for example with
nothing else going on with -Xms1048m -Xmx1048m I get:
Total Memory:1098907648 Free Memory:1088915600
With just -Xmx1048m
Total Memory:4194304 Free Memory:2750304
Two questions
1) Might hash joins use an unexpectedly large amount of memory if -Xms
is set large? I know at the user site where this was being set, they
were setting -Xms in the hopes of optimizing memory usage but I wonder
if it actually increased the amount of memory used by hash joins.
2) Is there a test case that goes through the else clause above that I
could use for my experimentation?
Thanks
Kathey
Re: How Much Memory for hash joins
Posted by Katherine Marsden <km...@sbcglobal.net>.
On 3/4/2013 5:21 AM, Knut Anders Hatlen wrote:
> Mike Matrigali <mi...@sbcglobal.net> writes:
>
>> Also note that these are all estimates within the system. As Knut
>> pointed out there are some known problems with the estimates. And
>> even with fixes he has suggested, the code is probably just guessing
>> with things like blobs/clobs.
> I haven't verified, but I think HashJoinStrategy uses
> DataTypeDescriptor.estimatedMemoryUsage() to estimate how much memory
> the hash table will consume. That method has no case for BLOB or CLOB,
> so it looks as if it will return zero for LOB columns. If that's so, it
> will definitely overestimate how many rows fits in maxMemoryPerTable
> kilobytes if the rows contain LOBs.
>
Thanks Knut for finding that, I filed DERBY-6096 for the issue.
Re: How Much Memory for hash joins
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Mike Matrigali <mi...@sbcglobal.net> writes:
> Also note that these are all estimates within the system. As Knut
> pointed out there are some known problems with the estimates. And
> even with fixes he has suggested, the code is probably just guessing
> with things like blobs/clobs.
I haven't verified, but I think HashJoinStrategy uses
DataTypeDescriptor.estimatedMemoryUsage() to estimate how much memory
the hash table will consume. That method has no case for BLOB or CLOB,
so it looks as if it will return zero for LOB columns. If that's so, it
will definitely overestimate how many rows fits in maxMemoryPerTable
kilobytes if the rows contain LOBs.
--
Knut Anders
Re: How Much Memory for hash joins
Posted by Mike Matrigali <mi...@sbcglobal.net>.
The goal was to use larger memory if it was available. At the time Java
did not provide much access to this info, so only totalMemory() was
available to use. I think this translated to current allocated memory.
So if you start the jvm with a lot of memory (even if you are not using
it), then derby will try to use 1% of this larger value. That is the
intent.
If you don't start with bigger memory, even if you have a mx that allows
for bigger memory, we won't use it for the 1% calculation. This was
probably coded when 1.4.2 was the latest jvm, so there may be better
interfaces now, not sure.
Also note that these are all estimates within the system. As Knut
pointed out there are some known problems with the estimates. And
even with fixes he has suggested, the code is probably just guessing
with things like blobs/clobs.
On 3/1/2013 9:28 AM, Katherine Marsden wrote:
> On 2/28/2013 9:11 AM, Mike Matrigali wrote:
>> In BackingStoreHashTableFromScan I see.
>>>
>>> this.max_inmemory_rowcnt = max_inmemory_rowcnt;
>>> if( max_inmemory_rowcnt > 0)
>>> max_inmemory_size = Long.MAX_VALUE;
>>> else
>>> max_inmemory_size = Runtime.getRuntime().totalMemory()/100;
>>>
>
>
> I have been reading the comments and trying to make sense of the logic
> and understand all that is happening with max_inmemory_size. I don't
> have a test case that goes through the else part of the condition above.
>
> One thing I did notice is that Runtime.getRuntime().totalMemory()
> returns really different things if -Xms is set large, for example with
> nothing else going on with -Xms1048m -Xmx1048m I get:
>
> Total Memory:1098907648 Free Memory:1088915600
>
> With just -Xmx1048m
> Total Memory:4194304 Free Memory:2750304
>
> Two questions
> 1) Might hash joins use an unexpectedly large amount of memory if -Xms
> is set large? I know at the user site where this was being set, they
> were setting -Xms in the hopes of optimizing memory usage but I wonder
> if it actually increased the amount of memory used by hash joins.
i don't think it is unexpected, the intent is to use more memory if more
memory is available. see above. I think the code would have used the
mx value if at the time it could have gotten at that through jvm interfaces.
> 2) Is there a test case that goes through the else clause above that I
> could use for my experimentation?
>
> Thanks
>
> Kathey
>
Re: How Much Memory for hash joins
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Katherine Marsden <km...@sbcglobal.net> writes:
> On 2/28/2013 9:11 AM, Mike Matrigali wrote:
>> In BackingStoreHashTableFromScan I see.
>>>
>>> this.max_inmemory_rowcnt = max_inmemory_rowcnt;
>>> if( max_inmemory_rowcnt > 0)
>>> max_inmemory_size = Long.MAX_VALUE;
>>> else
>>> max_inmemory_size = Runtime.getRuntime().totalMemory()/100;
>>>
>
>
> I have been reading the comments and trying to make sense of the logic
> and understand all that is happening with max_inmemory_size. I don't
> have a test case that goes through the else part of the condition
> above.
>
> One thing I did notice is that Runtime.getRuntime().totalMemory()
> returns really different things if -Xms is set large, for example with
> nothing else going on with -Xms1048m -Xmx1048m I get:
>
> Total Memory:1098907648 Free Memory:1088915600
>
> With just -Xmx1048m
> Total Memory:4194304 Free Memory:2750304
>
> Two questions
> 1) Might hash joins use an unexpectedly large amount of memory if -Xms
> is set large? I know at the user site where this was being set, they
> were setting -Xms in the hopes of optimizing memory usage but I wonder
> if it actually increased the amount of memory used by hash joins.
I think hash scans pass in a maximum row count to the
BackingStoreHashTable, so the totalMemory() calculation won't be used by
hash joins. The only use of the totalMemory() calculation I'm aware of,
is for scrollable result sets.
> 2) Is there a test case that goes through the else clause above that I
> could use for my experimentation?
Executing this command in ij exercises the else clause:
ij> get scroll insensitive cursor c as 'select * from sys.systables';
--
Knut Anders