db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: [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 19:13:09 GMT
On 7/25/2012 11:00 AM, Tony Brusseau (JIRA) wrote:
>
>      [ 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.
see below, I don't expect any change in pageReservedSpace to help with 
this issue.
>
>>> 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?
The doc says 1/2 the page size.  I usually say 1/3 to be safe, as then 
you don't worry about the internal overheads per page, per row and per 
column.  The arch reason for this is for a btree tree to work it must 
have at least 2 rows per page so that it can branch at least once, but 
much better if it can branch many times. btree rows never "overflow" 
they must always fit on the page.

It is documented here:
http://db.apache.org/derby/docs/dev/ref/rrefsqlj20937.html

Allowing indexes on large varchar fields is a problem for DERBY. 
probably the best feature if anyone was interested would be to only 
index the first "n" characters of a varchar field no matter how long it
is.  This is what other systems do.  I believe a JIRA exists for this
but don't know it.
>
>>> 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?
http://db.apache.org/derby/javadoc/engine/org/apache/derby/diag/SpaceTable.html
>
>>> 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.
Actually I just checked the code, pageReservedSpace does not do anything 
for indexes.  It is explicitly set to 0 for indexes and any user setting 
is ignored.  The error message should be updated.

This is subtlely documented in link below, if you feel it could be 
better documented file a JIRA:
http://db.apache.org/derby/docs/dev/ref/rrefproper28026.html
>
>>> 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?
the parameters affect ddl while they are set either in environment or at 
db level.  So it depends on the ddl you use to create the table, which 
is why I asked for it.  If you use separate statements to create indexes 
then you could have different properties for the base table and indexes. 
  If you just do one create statement with primary key and/or unique 
constraint clauses then all tables and indexes will share same size.
>
>
>> 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: 10.8.2.2, 10.9.1.0
>>          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
>
>
>


Mime
View raw message