db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5881) Limitation: Record of a btree secondary index cannot be updated or inserted due to lack of space on the page.
Date Wed, 25 Jul 2012 17:48:35 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5881?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13422453#comment-13422453

Mike Matrigali commented on DERBY-5881:

do you have the derby.log from when the error happened.  Usually this will have stack traces
that are useful, and we can see if there are nested exception that might
provide more information.  This might tell us if this is an expected result of a long key
being inserted or some bug.

Derby does have a hard limitation on the size of an index key with respect to the page size
of the index, it is something like 1/3 of the page size.  Is it likely that one of the rows
that you
are loading has index key that is longer than 10k?   Note that a well designed application
derby should try to have keys much smaller than this limit, as the derby index is going to
be very inefficient as key size approaches 1/3 size of the page.

What is the ddl for the index that you are believe is failing.
 Note that it is the page size of the index, not the base table that is affecting this issue.
 You can use the space table vti to verify that the base table and indexes have the page size
you expect.

For indexes you should be reducing the reserved space rather than increasing it to work around
this problem.   In the current derby implementation entries into indexes are never updated,
so would recommend a reserved space of 0 for indexes.  Updates of key columns are always internally
changed to a delete followed by an insert into the index.  Setting bigger reserve space effectively
means less of the page available for insert and so even shorter keys will be disallowed. 
It also means less rows per page and about the only reason I can see to set it to non-zero
for indexes is to provide very very slight concurrency increase as page latch would affect
less rows - i would not recommend it.
> Limitation: Record of a btree secondary index cannot be updated or inserted due to lack
of space on the page.
> -------------------------------------------------------------------------------------------------------------
>                 Key: DERBY-5881
>                 URL: https://issues.apache.org/jira/browse/DERBY-5881
>             Project: Derby
>          Issue Type: Bug
>          Components: Store
>    Affects Versions:,
>         Environment: Red Hat Enterprise Linux Server release 6.1 (Santiago) -x86_64
> and
> OpenSuse 11.3 -x86_64
>            Reporter: Tony Brusseau
>            Priority: Blocker
> I'm trying to load a large database. After about 18 million inserts I always get the
following error:
>  Exception: java.sql.BatchUpdateException: Limitation: Record of a btree secondary index
cannot be updated or inserted due to lack of space on the page.  Use the parameters derby.storage.pageSize
and/or derby.storage.pageReservedSpace to work around this limitation.
> Error Code: 20000
> I'm already using the maximum pageSize as stated in the documentation (32768) and I've
bumped up the reserved space to 25%. I've also gotten rid of every non-essential index for
loading so this is probably related to a primary key index (I had to leave most of them around).
Our loading process is very non-trivial and batched so I'm not even sure which insert/index
is causing the problem.
> Problems:
> 1. The error message doesn't give any information about which tables/columns/indexes
are causing this problem so I have no way of knowing if i can work around this problem somehow.
> 2.The database shouldn't die saying to adjust some magic numbers with no guidance on
how to adjust them and by how much. Even if I do adjust them, what is to guarantee I won't
get the same error at a later date...maybe even sometime during production which would be
extremely bad. I believe an automated correction to this limitation needs to be created.

This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message