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
