You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Emmanuel Cecchet <ma...@frogthinker.org> on 2009/03/09 18:06:00 UTC
Problem with DELETE on big table
Hi,
I am running some tests trying to see the limits of Derby. I am using
Derby 10.4.2.0 on JDK 1.6.0 update 7, Windows Vista.
I created a table with 1 millions rows (no index) and then executed
DELETE * FROM table.
No error on the Derby console.
I got the following exception in the client app (running locally on the
same machine):
java.sql.SQLException: Network protocol exception: actual code point,
4,692, does not match expected code point, 9,224. The connection has
been terminated.
at
org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
at org.apache.derby.client.am.Statement.execute(Unknown Source)
at org.frogthinker...
Caused by: org.apache.derby.client.am.DisconnectException: Network
protocol exception: actual code point, 4,692, does not match expected
code point, 9,224. The connection has been terminated.
at
org.apache.derby.client.net.Reply.parseLengthAndMatchCodePoint(Unknown
Source)
at
org.apache.derby.client.net.NetConnectionReply.parseSQLCARD(Unknown Source)
at
org.apache.derby.client.net.NetConnectionReply.parseRDBCMMreply(Unknown
Source)
at
org.apache.derby.client.net.NetConnectionReply.readLocalCommit(Unknown
Source)
at
org.apache.derby.client.net.ConnectionReply.readLocalCommit(Unknown Source)
at
org.apache.derby.client.net.NetConnection.readLocalCommit_(Unknown Source)
at org.apache.derby.client.am.Connection.readCommit(Unknown Source)
at org.apache.derby.client.am.Connection.readAutoCommit(Unknown Source)
at org.apache.derby.client.am.Statement.flowExecute(Unknown Source)
at org.apache.derby.client.am.Statement.executeX(Unknown Source)
... 3 more
Any suggestion on why this is happening?
Thanks,
Emmanuel
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet
Re: Problem with DELETE on big table
Posted by Kristian Waagan <Kr...@Sun.COM>.
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,
>
>
Re: Problem with DELETE on big table
Posted by Emmanuel Cecchet <ma...@frogthinker.org>.
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,
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,
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet
Re: Problem with DELETE on big table
Posted by Kristian Waagan <Kr...@Sun.COM>.
Emmanuel Cecchet wrote:
> Hi Kathey,
>
> I re-checked derby.log and there was an OOM error.
Hello 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,
--
Kristian
> What is interesting is that the delete completed anyway.
>
> 2009-03-09 05:31:34.731 GMT Thread[DRDAConnThread_3,5,main] (XID =
> 18628704), (SESSIONID = 21), (DATABASE = nhmnetflowdb), (DRDAID =
> NF000001.C4E3-4182998392339954695{22}), Failed Statement is: DELETE
> FROM "SA"."NETFLOWDATA"
> java.lang.OutOfMemoryError: Java heap space
> at java.util.Arrays.copyOf(Unknown Source)
> at java.util.Arrays.copyOf(Unknown Source)
> at java.util.ArrayList.ensureCapacity(Unknown Source)
> at java.util.ArrayList.add(Unknown Source)
> at
> org.apache.derby.impl.store.raw.xact.Xact.addPostCommitWork(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.RAMTransaction.addPostCommitWork(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.heap.HeapController.queueDeletePostCommitWork(Unknown
> Source)
> at
> org.apache.derby.impl.store.access.conglomerate.GenericConglomerateController.delete(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.RowChangerImpl.deleteRow(Unknown
> Source)
> at
> org.apache.derby.impl.sql.execute.DeleteResultSet.collectAffectedRows(Unknown
> Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown
> Source)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
> Source)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown
> Source)
> at org.apache.derby.impl.drda.DRDAConnThread.parseEXCSQLIMM(Unknown
> Source)
> at
> org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown Source)
> at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
>
> Thanks for the help,
> Emmanuel
>
>>>
>>> I am running some tests trying to see the limits of Derby. I am
>>> using Derby 10.4.2.0 on JDK 1.6.0 update 7, Windows Vista.
>>> I created a table with 1 millions rows (no index) and then executed
>>> DELETE * FROM table.
>>> No error on the Derby console.
>>>
>>> I got the following exception in the client app (running locally on
>>> the same machine):
>>> java.sql.SQLException: Network protocol exception: actual code
>>> point, 4,692, does not match expected code point, 9,224. The
>>> connection has been terminated.
>> Usually a protocol exception is indicative of a bug, but it is
>> unusual for it to come on a delete, which should be a fairly straight
>> forward operation from a protocol perspective. Was there anything of
>> use in the derby.log? Do you have reproduction for the issue that
>> you can post to Jira?
>>
>> Thanks
>>
>> Kathey
>>
>>
>
>
Re: Problem with DELETE on big table
Posted by Emmanuel Cecchet <ma...@frogthinker.org>.
Hi Kathey,
I re-checked derby.log and there was an OOM error. What is interesting
is that the delete completed anyway.
2009-03-09 05:31:34.731 GMT Thread[DRDAConnThread_3,5,main] (XID =
18628704), (SESSIONID = 21), (DATABASE = nhmnetflowdb), (DRDAID =
NF000001.C4E3-4182998392339954695{22}), Failed Statement is: DELETE FROM
"SA"."NETFLOWDATA"
java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Unknown Source)
at java.util.Arrays.copyOf(Unknown Source)
at java.util.ArrayList.ensureCapacity(Unknown Source)
at java.util.ArrayList.add(Unknown Source)
at
org.apache.derby.impl.store.raw.xact.Xact.addPostCommitWork(Unknown Source)
at
org.apache.derby.impl.store.access.RAMTransaction.addPostCommitWork(Unknown
Source)
at
org.apache.derby.impl.store.access.heap.HeapController.queueDeletePostCommitWork(Unknown
Source)
at
org.apache.derby.impl.store.access.conglomerate.GenericConglomerateController.delete(Unknown
Source)
at
org.apache.derby.impl.sql.execute.RowChangerImpl.deleteRow(Unknown Source)
at
org.apache.derby.impl.sql.execute.DeleteResultSet.collectAffectedRows(Unknown
Source)
at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown
Source)
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeUpdate(Unknown
Source)
at org.apache.derby.impl.drda.DRDAConnThread.parseEXCSQLIMM(Unknown
Source)
at org.apache.derby.impl.drda.DRDAConnThread.processCommands(Unknown
Source)
at org.apache.derby.impl.drda.DRDAConnThread.run(Unknown Source)
Thanks for the help,
Emmanuel
>>
>> I am running some tests trying to see the limits of Derby. I am using
>> Derby 10.4.2.0 on JDK 1.6.0 update 7, Windows Vista.
>> I created a table with 1 millions rows (no index) and then executed
>> DELETE * FROM table.
>> No error on the Derby console.
>>
>> I got the following exception in the client app (running locally on
>> the same machine):
>> java.sql.SQLException: Network protocol exception: actual code point,
>> 4,692, does not match expected code point, 9,224. The connection has
>> been terminated.
> Usually a protocol exception is indicative of a bug, but it is unusual
> for it to come on a delete, which should be a fairly straight forward
> operation from a protocol perspective. Was there anything of use in
> the derby.log? Do you have reproduction for the issue that you can
> post to Jira?
>
> Thanks
>
> Kathey
>
>
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development & Consulting
--
Web: http://www.frogthinker.org
email: manu@frogthinker.org
Skype: emmanuel_cecchet
Re: Problem with DELETE on big table
Posted by Kathey Marsden <km...@sbcglobal.net>.
Emmanuel Cecchet wrote:
> Hi,
>
> I am running some tests trying to see the limits of Derby. I am using
> Derby 10.4.2.0 on JDK 1.6.0 update 7, Windows Vista.
> I created a table with 1 millions rows (no index) and then executed
> DELETE * FROM table.
> No error on the Derby console.
>
> I got the following exception in the client app (running locally on
> the same machine):
> java.sql.SQLException: Network protocol exception: actual code point,
> 4,692, does not match expected code point, 9,224. The connection has
> been terminated.
Usually a protocol exception is indicative of a bug, but it is unusual
for it to come on a delete, which should be a fairly straight forward
operation from a protocol perspective. Was there anything of use in the
derby.log? Do you have reproduction for the issue that you can post to
Jira?
Thanks
Kathey