db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tony Brusseau (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 18:00:46 GMT

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

Tony Brusseau commented on DERBY-5881:

>> do you have the derby.log from when the error happened. 

I just started another test run which overwrote the derby.log file, woops! I'll try to get
it as soon as the next run fails. I'm updating reserved space to 100%. Guess I'll try to start
that again with a smaller reserved space size.

>> Is it likely that one of the rows that you are loading has index key that is longer
than 10k? 
It is quite possible. One of the tables has an indexed VARCHAR(32600) column. Are you saying
that the max indexed VARCHAR should be 10k or less for a 32k page size?

>> 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. 
Sorry, but can you give more explicit directions for this?

>> For indexes you should be reducing the reserved space rather than increasing it to
work around this problem. 
Ah, I didn't know this, I'll try playing with a smaller reserved size.

>> 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. 
How would you set the reserved size separately for indexes vs table data?

> 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