db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Problem with DELETE on big table
Date Mon, 23 Mar 2009 12:27:40 GMT
Emmanuel Cecchet wrote:
> 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.

As far as I can see, Derby schedules a post-commit work item for each 
page where a record is deleted. These work items are stored in a list, 
which grows dynamically.
The basic workarounds when using DELETE are:
 o allocate a larger heap (memory tradeoff)
 o delete parts of the table and commit in between (time/processing 
 o drop table instead of using DELETE

You could vote for and comment on 
https://issues.apache.org/jira/browse/DERBY-268 (truncate table), in 
hope that somebody picks it up.
Another idea would be to report the problem through Jira [1] , so that 
we can better track the issue.


[1] https://issues.apache.org/jira/browse/DERBY

> Thanks for your help,
> Emmanuel
>> 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,

View raw message