db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Emmanuel Cecchet <m...@frogthinker.org>
Subject Re: Problem with DELETE on big table
Date Tue, 10 Mar 2009 18:26:57 GMT
Hi Kristian,

I used the default heap size for Sun JRE 1.6.0 update 7 for Windows.
I did tests with -Xmx1g and the problem persists but it takes much 
longer (hours).

The table is pretty simple, only INT, BIGINT and VARCHAR(16), but it is 
very large (100 million rows). Note that the table has no index.
To reproduce the problem, just insert 100 million rows (might work with 
less), execute 'select count(*)' on the table (takes about 15 minutes) 
and then 'delete from table' will crash after about 1h30minutes with an 
OOM (but the data will be deleted). The size of the Derby folder on disk 
with the full table is approximately 16GB.

I think that even if this is not a bug, there could be an optimization 
similar to truncate in other databases that detects if you are going to 
drop all tuples. I think that for now doing a drop/create table would be 
much faster than a delete on such large table.
I will keep investigating if  I hit more issues with DELETE when I don't 
delete all tuples but a large portion of them.

Thanks for your help,

> Do you know the maximum allowed size of the heap and the page cache 
> size used when the OOME occurred? Also, have you overridden the page 
> size, or are you using Blob/Clob in your tables?
> I'm not saying this is caused by a maximum heap size that is too low, 
> it may still be a Derby bug. Knowing the values of the properties 
> above, may help us analyze the problem.
> FYI, others have seen this problem as well, for instance:
> http://www.nabble.com/Derby-DB---How-to-increase-Heap-Size---asadmin-start-database-td18958939.html

> Regards,

Emmanuel Cecchet
FTO @ Frog Thinker 
Open Source Development & Consulting
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet

View raw message