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
tradeoff)
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.
Regards,
--
Kristian
[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,
