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 Mamta Satoor <ms...@gmail.com> on 2010/02/16 19:15:53 UTC

Inserting large LOB as a stream...

Hi,

I am working on writing a test for INSERT statement which will cause
INSERT trigger to fire. The INSERT is being done into a table with a
large BLOB column. The table definition looks as follows create table
table1 (id int, status smallint, bl blob(2G))

My goal is to run this INSERT statement inside a java program with a
limited heap to see if I will run into any OOMs during trigger
execution because we are trying to stream the data into memory. The
way I am trying to insert large LOB is as follows
		System.out.println("Inserting into table1 to cause insert trigger to fire");
		PreparedStatement ps = conn.prepareStatement(
			"insert into table1(id, status, bl) values(101, 0, ?)");
		byte[] arr = new byte[300000*1024];
		for (int i = 0; i < arr.length; i++)
		arr[i] = (byte)4;
		ps.setBinaryStream(1, new ByteArrayInputStream(arr), arr.length);
		ps.executeUpdate();
		conn.commit();
But the above code runs into OOM for byte[] arr = new
byte[300000*1024]; because I do not have enough heap available. I was
wondering if there was a way for me to use a stream to insert a pretty
large LOB into my table without having a physical file on the disk
from which I will stream the data in. I do not care about the actual
data. As shown above, I am just inserting a large quantity of (byte)4
in the LOB. Would appreciate if anyone knows of a way for me to stream
large data into LOB while running with limited heap.

thanks,
Mamta

Re: Inserting large LOB as a stream...

Posted by Kristian Waagan <Kr...@Sun.COM>.
On 18.02.10 05:56, Dag H. Wanvik wrote:
> Mamta Satoor<ms...@gmail.com>  writes:
>
>    
>> Dag, thanks for refering me to lowmem suite. Does this suite get run
>> on a regular basis or is it part of the junit all suite? It will be
>>      
> To the first question, I am not sure, I'll ask Ole.  The tests are
> also run as part of the junit all suits, but then with more memory so
> it's not that interesting..
>    

FYI, I run this whenever I run the other tests with 'ant junit-lowmem', 
but it would be very nice to know if it is being run nightly. If not, we 
should try to get it included :)
Currently the suite runs in around 5 minutes on my machines (18 test runs).

>    
>> nice to incoporate my tests in this suite but in a disabled mode at
>> this point since they will run into OOM.
>>      
> +1
>
>    
>> Also, the minimum memory requirement for my test is 100M whereas
>> lowmem seems to be running with 16M. Maybe i should rework the data
>> in my test to work with 16M rather than 100M?
>>      
> Well, I guess that would be good, unless you want to verify that the
> other tests are still valid/useful if you increase the heap size..
>    

Three of the tests run with a pageCacheSize of 100.
I would prefer to run with a smaller heap (and suitable pageCacheSize), 
otherwise the test will take so long to insert test data etc.


Regards,
-- 
Kristian

> Thanks,
> Dag
>    


Re: Inserting large LOB as a stream...

Posted by Myrna van Lunteren <m....@gmail.com>.
On Mon, Feb 22, 2010 at 5:50 PM, Dag H. Wanvik <Da...@sun.com> wrote:
> Knut Anders Hatlen <Kn...@Sun.COM> writes:
>
>> Actually, that depends on what you mean by "junit all". If you refer to
>> suites.All, that's correct, the tests run but not with a restricted
>> heap. However, there is a junit-all ant target that depends on
>> junit-lowmem and does in fact run the tests with a small heap, as far as
>> I can see.
>
> Thats good.  I am not sure if that's how our nightlies are being run,
> though. Ole said that they do *not* run junit-lowmem directly, at
> least.
>
> Dag
>
For what it's worth, I don't run the junit-all target at all, at the
moment, nor have I set it up for the nightlies at IBM.

Myrna

Re: Inserting large LOB as a stream...

Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Knut Anders Hatlen <Kn...@Sun.COM> writes:

> Actually, that depends on what you mean by "junit all". If you refer to
> suites.All, that's correct, the tests run but not with a restricted
> heap. However, there is a junit-all ant target that depends on
> junit-lowmem and does in fact run the tests with a small heap, as far as
> I can see.

Thats good.  I am not sure if that's how our nightlies are being run,
though. Ole said that they do *not* run junit-lowmem directly, at
least.

Dag

Re: Inserting large LOB as a stream...

Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Dag.Wanvik@Sun.COM (Dag H. Wanvik) writes:

> Mamta Satoor <ms...@gmail.com> writes:
>
>> Dag, thanks for refering me to lowmem suite. Does this suite get run
>> on a regular basis or is it part of the junit all suite? It will be
>
> To the first question, I am not sure, I'll ask Ole.  The tests are
> also run as part of the junit all suits, but then with more memory so
> it's not that interesting..

Actually, that depends on what you mean by "junit all". If you refer to
suites.All, that's correct, the tests run but not with a restricted
heap. However, there is a junit-all ant target that depends on
junit-lowmem and does in fact run the tests with a small heap, as far as
I can see.

-- 
Knut Anders

Re: Inserting large LOB as a stream...

Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Mamta Satoor <ms...@gmail.com> writes:

> Dag, thanks for refering me to lowmem suite. Does this suite get run
> on a regular basis or is it part of the junit all suite? It will be

To the first question, I am not sure, I'll ask Ole.  The tests are
also run as part of the junit all suits, but then with more memory so
it's not that interesting..

> nice to incoporate my tests in this suite but in a disabled mode at
> this point since they will run into OOM.

+1

> Also, the minimum memory requirement for my test is 100M whereas
> lowmem seems to be running with 16M. Maybe i should rework the data
> in my test to work with 16M rather than 100M?

Well, I guess that would be good, unless you want to verify that the
other tests are still valid/useful if you increase the heap size..

Thanks,
Dag

Re: Inserting large LOB as a stream...

Posted by Mamta Satoor <ms...@gmail.com>.
Dag, thanks for refering me to lowmem suite. Does this suite get run
on a regular basis or is it part of the junit all suite? It will be
nice to incoporate my tests in this suite but in a disabled mode at
this point since they will run into OOM. Also, the minimum memory
requirement for my test is 100M whereas lowmem seems to be running
with 16M. Maybe i should rework the data in my test to work with 16M
rather than 100M?

thanks,
Mamta

On Tue, Feb 16, 2010 at 1:51 PM, Dag H. Wanvik <Da...@sun.com> wrote:
> Mamta Satoor <ms...@gmail.com> writes:
>
>
>> trying to see if I can run this with limited heap to do the insert
>>         ps.setBinaryStream(1, new LoopingAlphabetStream(300000*1024),
>> 300000*1024);
>
> You shold be able to insert, yes, cf. BlobMemTest running as part of
> the lowmem suite
>
> org.apache.derbyTesting.functionTests.tests.memory._Suite
>
> cf. junit-lowmem ant target.
>
> Dag
>
>>
>>
>> On Tue, Feb 16, 2010 at 11:54 AM, Mamta Satoor <ms...@gmail.com> wrote:
>>> Yes, I just ran into it and trying it. thanks, Dag.
>>>
>>> On Tue, Feb 16, 2010 at 11:51 AM, Dag H. Wanvik <Da...@sun.com> wrote:
>>>> Mamta Satoor <ms...@gmail.com> writes:
>>>>
>>>>
>>>>> Hi,
>>>>>
>>>>> I am working on writing a test for INSERT statement which will cause
>>>>> INSERT trigger to fire. The INSERT is being done into a table with a
>>>>> large BLOB column. The table definition looks as follows create table
>>>>> table1 (id int, status smallint, bl blob(2G))
>>>>>
>>>>> My goal is to run this INSERT statement inside a java program with a
>>>>> limited heap to see if I will run into any OOMs during trigger
>>>>> execution because we are trying to stream the data into memory. The
>>>>> way I am trying to insert large LOB is as follows
>>>>>               System.out.println("Inserting into table1 to cause insert trigger to fire");
>>>>>               PreparedStatement ps = conn.prepareStatement(
>>>>>                       "insert into table1(id, status, bl) values(101, 0, ?)");
>>>>>               byte[] arr = new byte[300000*1024];
>>>>>               for (int i = 0; i < arr.length; i++)
>>>>>               arr[i] = (byte)4;
>>>>>               ps.setBinaryStream(1, new ByteArrayInputStream(arr), arr.length);
>>>>>               ps.executeUpdate();
>>>>>               conn.commit();
>>>>> But the above code runs into OOM for byte[] arr = new
>>>>> byte[300000*1024]; because I do not have enough heap available. I was
>>>>> wondering if there was a way for me to use a stream to insert a pretty
>>>>> large LOB into my table without having a physical file on the disk
>>>>> from which I will stream the data in. I do not care about the actual
>>>>> data. As shown above, I am just inserting a large quantity of (byte)4
>>>>> in the LOB. Would appreciate if anyone knows of a way for me to stream
>>>>> large data into LOB while running with limited heap.
>>>>
>>>> You might use the same method as used in BlobMemTest, perhaps?
>>>> (LoopingAlphabetStream).
>>>>
>>>> Thanks,
>>>> Dag
>>>>
>>>
>>
>
> --
> Dag H. Wanvik, staff engineer
> Sun Microsystems, Java Core and Desktop - Java DB/Derby
> Haakon VII gt. 7b, N-7485 Trondheim, Norway
> Tel: x43496/+47 73842196, Fax:  +47 73842101
> Sun IM: dw136674, Yahoo IM: dag_h_wanvik
>

Re: Inserting large LOB as a stream...

Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Mamta Satoor <ms...@gmail.com> writes:


> trying to see if I can run this with limited heap to do the insert
>         ps.setBinaryStream(1, new LoopingAlphabetStream(300000*1024),
> 300000*1024);

You shold be able to insert, yes, cf. BlobMemTest running as part of
the lowmem suite

org.apache.derbyTesting.functionTests.tests.memory._Suite

cf. junit-lowmem ant target.

Dag

>
>
> On Tue, Feb 16, 2010 at 11:54 AM, Mamta Satoor <ms...@gmail.com> wrote:
>> Yes, I just ran into it and trying it. thanks, Dag.
>>
>> On Tue, Feb 16, 2010 at 11:51 AM, Dag H. Wanvik <Da...@sun.com> wrote:
>>> Mamta Satoor <ms...@gmail.com> writes:
>>>
>>>
>>>> Hi,
>>>>
>>>> I am working on writing a test for INSERT statement which will cause
>>>> INSERT trigger to fire. The INSERT is being done into a table with a
>>>> large BLOB column. The table definition looks as follows create table
>>>> table1 (id int, status smallint, bl blob(2G))
>>>>
>>>> My goal is to run this INSERT statement inside a java program with a
>>>> limited heap to see if I will run into any OOMs during trigger
>>>> execution because we are trying to stream the data into memory. The
>>>> way I am trying to insert large LOB is as follows
>>>>               System.out.println("Inserting into table1 to cause insert trigger to fire");
>>>>               PreparedStatement ps = conn.prepareStatement(
>>>>                       "insert into table1(id, status, bl) values(101, 0, ?)");
>>>>               byte[] arr = new byte[300000*1024];
>>>>               for (int i = 0; i < arr.length; i++)
>>>>               arr[i] = (byte)4;
>>>>               ps.setBinaryStream(1, new ByteArrayInputStream(arr), arr.length);
>>>>               ps.executeUpdate();
>>>>               conn.commit();
>>>> But the above code runs into OOM for byte[] arr = new
>>>> byte[300000*1024]; because I do not have enough heap available. I was
>>>> wondering if there was a way for me to use a stream to insert a pretty
>>>> large LOB into my table without having a physical file on the disk
>>>> from which I will stream the data in. I do not care about the actual
>>>> data. As shown above, I am just inserting a large quantity of (byte)4
>>>> in the LOB. Would appreciate if anyone knows of a way for me to stream
>>>> large data into LOB while running with limited heap.
>>>
>>> You might use the same method as used in BlobMemTest, perhaps?
>>> (LoopingAlphabetStream).
>>>
>>> Thanks,
>>> Dag
>>>
>>
>

-- 
Dag H. Wanvik, staff engineer
Sun Microsystems, Java Core and Desktop - Java DB/Derby
Haakon VII gt. 7b, N-7485 Trondheim, Norway
Tel: x43496/+47 73842196, Fax:  +47 73842101
Sun IM: dw136674, Yahoo IM: dag_h_wanvik

Re: Inserting large LOB as a stream...

Posted by Mamta Satoor <ms...@gmail.com>.
trying to see if I can run this with limited heap to do the insert
        ps.setBinaryStream(1, new LoopingAlphabetStream(300000*1024),
300000*1024);


On Tue, Feb 16, 2010 at 11:54 AM, Mamta Satoor <ms...@gmail.com> wrote:
> Yes, I just ran into it and trying it. thanks, Dag.
>
> On Tue, Feb 16, 2010 at 11:51 AM, Dag H. Wanvik <Da...@sun.com> wrote:
>> Mamta Satoor <ms...@gmail.com> writes:
>>
>>
>>> Hi,
>>>
>>> I am working on writing a test for INSERT statement which will cause
>>> INSERT trigger to fire. The INSERT is being done into a table with a
>>> large BLOB column. The table definition looks as follows create table
>>> table1 (id int, status smallint, bl blob(2G))
>>>
>>> My goal is to run this INSERT statement inside a java program with a
>>> limited heap to see if I will run into any OOMs during trigger
>>> execution because we are trying to stream the data into memory. The
>>> way I am trying to insert large LOB is as follows
>>>               System.out.println("Inserting into table1 to cause insert trigger to fire");
>>>               PreparedStatement ps = conn.prepareStatement(
>>>                       "insert into table1(id, status, bl) values(101, 0, ?)");
>>>               byte[] arr = new byte[300000*1024];
>>>               for (int i = 0; i < arr.length; i++)
>>>               arr[i] = (byte)4;
>>>               ps.setBinaryStream(1, new ByteArrayInputStream(arr), arr.length);
>>>               ps.executeUpdate();
>>>               conn.commit();
>>> But the above code runs into OOM for byte[] arr = new
>>> byte[300000*1024]; because I do not have enough heap available. I was
>>> wondering if there was a way for me to use a stream to insert a pretty
>>> large LOB into my table without having a physical file on the disk
>>> from which I will stream the data in. I do not care about the actual
>>> data. As shown above, I am just inserting a large quantity of (byte)4
>>> in the LOB. Would appreciate if anyone knows of a way for me to stream
>>> large data into LOB while running with limited heap.
>>
>> You might use the same method as used in BlobMemTest, perhaps?
>> (LoopingAlphabetStream).
>>
>> Thanks,
>> Dag
>>
>

Re: Inserting large LOB as a stream...

Posted by Mamta Satoor <ms...@gmail.com>.
Yes, I just ran into it and trying it. thanks, Dag.

On Tue, Feb 16, 2010 at 11:51 AM, Dag H. Wanvik <Da...@sun.com> wrote:
> Mamta Satoor <ms...@gmail.com> writes:
>
>
>> Hi,
>>
>> I am working on writing a test for INSERT statement which will cause
>> INSERT trigger to fire. The INSERT is being done into a table with a
>> large BLOB column. The table definition looks as follows create table
>> table1 (id int, status smallint, bl blob(2G))
>>
>> My goal is to run this INSERT statement inside a java program with a
>> limited heap to see if I will run into any OOMs during trigger
>> execution because we are trying to stream the data into memory. The
>> way I am trying to insert large LOB is as follows
>>               System.out.println("Inserting into table1 to cause insert trigger to fire");
>>               PreparedStatement ps = conn.prepareStatement(
>>                       "insert into table1(id, status, bl) values(101, 0, ?)");
>>               byte[] arr = new byte[300000*1024];
>>               for (int i = 0; i < arr.length; i++)
>>               arr[i] = (byte)4;
>>               ps.setBinaryStream(1, new ByteArrayInputStream(arr), arr.length);
>>               ps.executeUpdate();
>>               conn.commit();
>> But the above code runs into OOM for byte[] arr = new
>> byte[300000*1024]; because I do not have enough heap available. I was
>> wondering if there was a way for me to use a stream to insert a pretty
>> large LOB into my table without having a physical file on the disk
>> from which I will stream the data in. I do not care about the actual
>> data. As shown above, I am just inserting a large quantity of (byte)4
>> in the LOB. Would appreciate if anyone knows of a way for me to stream
>> large data into LOB while running with limited heap.
>
> You might use the same method as used in BlobMemTest, perhaps?
> (LoopingAlphabetStream).
>
> Thanks,
> Dag
>

Re: Inserting large LOB as a stream...

Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Mamta Satoor <ms...@gmail.com> writes:


> Hi,
>
> I am working on writing a test for INSERT statement which will cause
> INSERT trigger to fire. The INSERT is being done into a table with a
> large BLOB column. The table definition looks as follows create table
> table1 (id int, status smallint, bl blob(2G))
>
> My goal is to run this INSERT statement inside a java program with a
> limited heap to see if I will run into any OOMs during trigger
> execution because we are trying to stream the data into memory. The
> way I am trying to insert large LOB is as follows
> 		System.out.println("Inserting into table1 to cause insert trigger to fire");
> 		PreparedStatement ps = conn.prepareStatement(
> 			"insert into table1(id, status, bl) values(101, 0, ?)");
> 		byte[] arr = new byte[300000*1024];
> 		for (int i = 0; i < arr.length; i++)
> 		arr[i] = (byte)4;
> 		ps.setBinaryStream(1, new ByteArrayInputStream(arr), arr.length);
> 		ps.executeUpdate();
> 		conn.commit();
> But the above code runs into OOM for byte[] arr = new
> byte[300000*1024]; because I do not have enough heap available. I was
> wondering if there was a way for me to use a stream to insert a pretty
> large LOB into my table without having a physical file on the disk
> from which I will stream the data in. I do not care about the actual
> data. As shown above, I am just inserting a large quantity of (byte)4
> in the LOB. Would appreciate if anyone knows of a way for me to stream
> large data into LOB while running with limited heap.

You might use the same method as used in BlobMemTest, perhaps?
(LoopingAlphabetStream).

Thanks,
Dag