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 Alan Burlison <Al...@sun.com> on 2008/11/12 14:59:16 UTC
btree overflow during insert
We are doing inserts with Derby 10.4.2.0 into a table that has an index
on it, and during the insert we get the following errors:
*** Appending local Error for batch element #84: 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.***
*** Next Exception java.sql.SQLException: Error for batch element #155:
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.***
Appending nestedError for batch element #84: 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.
We've tried removing the index and re-adding it after the inserts, but
the result is the same. To stop this happening we have to up the
derby.storage.pageSize parameter to 16k, which is too big for what is
effectively an OLTP database, and the documentation for
derby.storage.pageReservedSpace says that it only applies to tables, bot
indexes.
Is this a bug, and if not, what should we do to avoid it, other than
increasing the DB page size to 16K?
Thanks,
--
Alan Burlison
--
Re: btree overflow during insert
Posted by Alan Burlison <Al...@sun.com>.
Knut Anders Hatlen wrote:
> Hmm... In the original post it looked like the errors happened while
> processing the batch (the messages said "Error for batch element
> #XX"). Are you sure that the index wasn't created implicitly before the
> inserts were performed? Constraints in the table definition may create a
> backing index even if CREATE INDEX hasn't been executed. In the example
> below, CREATE TABLE creates an index to back the UNIQUE constraint, so
> any inserts between CREATE TABLE and CREATE INDEX will still update a
> B-tree.
>
> ij> CREATE TABLE MY_TABLE(TEXT VARCHAR(4096) UNIQUE);
> 0 rows inserted/updated/deleted
> ij> CREATE INDEX MY_INDEX ON MY_TABLE(TEXT);
> 0 rows inserted/updated/deleted
> WARNING 01504: The new index is a duplicate of an existing index: SQL081114105207590.
Originally the index was created at the same time as the table - well, a
constraint was created, which created the backing index, as you said.
We also tried populating the table without creating the constraint, then
adding the constraint afterwards, but the result was the same, we got
the btree space issue. Increasing the DB block size seems to be the
workaround.
--
Alan Burlison
--
Re: btree overflow during insert
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Alan Burlison <Al...@Sun.COM> writes:
> Knut Anders Hatlen wrote:
>
>> No, it shouldn't allocate more space than what is actually used by the
>> column. That probably means that reducing the declared length of the
>> varchar column won't help. I thought there was a mechanism that
>> automatically picked a larger page size at table/index creation time if
>> the table had long columns, but it may be that it only kicks in for
>> CLOBs and BLOBs.
>
> That's a shame :-( We are going to give it a try in any case, just in
> case it does make a difference.
>
> The other thing we are doing is batching up the inserts using the JDBC
> addBatch functionality, but I assume that will have no relevance, as
> the index is being added after the inserts are complete.
Hmm... In the original post it looked like the errors happened while
processing the batch (the messages said "Error for batch element
#XX"). Are you sure that the index wasn't created implicitly before the
inserts were performed? Constraints in the table definition may create a
backing index even if CREATE INDEX hasn't been executed. In the example
below, CREATE TABLE creates an index to back the UNIQUE constraint, so
any inserts between CREATE TABLE and CREATE INDEX will still update a
B-tree.
ij> CREATE TABLE MY_TABLE(TEXT VARCHAR(4096) UNIQUE);
0 rows inserted/updated/deleted
ij> CREATE INDEX MY_INDEX ON MY_TABLE(TEXT);
0 rows inserted/updated/deleted
WARNING 01504: The new index is a duplicate of an existing index: SQL081114105207590.
--
Knut Anders
Re: btree overflow during insert
Posted by Alan Burlison <Al...@sun.com>.
Knut Anders Hatlen wrote:
> No, it shouldn't allocate more space than what is actually used by the
> column. That probably means that reducing the declared length of the
> varchar column won't help. I thought there was a mechanism that
> automatically picked a larger page size at table/index creation time if
> the table had long columns, but it may be that it only kicks in for
> CLOBs and BLOBs.
That's a shame :-( We are going to give it a try in any case, just in
case it does make a difference.
The other thing we are doing is batching up the inserts using the JDBC
addBatch functionality, but I assume that will have no relevance, as the
index is being added after the inserts are complete.
--
Alan Burlison
--
Re: btree overflow during insert
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Alan Burlison <Al...@Sun.COM> writes:
> Knut Anders Hatlen wrote:
>
>> I have logged DERBY-3947 and attached a reproducible test case.
>>
>> Note that it is possible to set derby.storage.pageSize just for a single
>> index by issuing
>>
>> CALL SYSCS_UTIL.SET_DATABASE_PROPERTY('derby.storage.pageSize', '32768')
>>
>> right before the index in question is created and resetting it with
>>
>> CALL SYSCS_UTIL.SET_DATABASE_PROPERTY('derby.storage.pageSize', null)
>>
>> afterwards.
>
> Thank-you very much for doing this, and the comments in the bug are
> useful too as they gives me a hint as to how we might change the table
> definition to avoid the problem altogether. We can probably work
> around this by reducing the length of the varchar column being
> indexed, which is a little aggressively-sized (32672). Failing that
> we can use the workaround you suggest.
>
> One thing I'm not quite clear on: does Derby allocate the full length
> of a varchar column in an index that uses it? In our case, although
> the column is varchar(32672), only a small fraction of it is used, 4Kb
> at most.
No, it shouldn't allocate more space than what is actually used by the
column. That probably means that reducing the declared length of the
varchar column won't help. I thought there was a mechanism that
automatically picked a larger page size at table/index creation time if
the table had long columns, but it may be that it only kicks in for
CLOBs and BLOBs.
--
Knut Anders
Re: btree overflow during insert
Posted by Alan Burlison <Al...@sun.com>.
Rick Hillegas wrote:
> Thanks for the explanation. I see that uniqueness is an issue for you.
> If you could tolerate the slight chance of false duplicates, you might
> want to experiment with generated columns now. I think that there is
> enough functionality checked into the development trunk that you could
> kick the tires. Something like the following might work for you:
Thanks for the suggestion, but the system in question is the membership
system for opensolaris.org, so we'll want to stick to released versions
of Derby - but again, thanks :-)
--
Alan Burlison
--
Re: btree overflow during insert
Posted by Rick Hillegas <Ri...@Sun.COM>.
Alan Burlison wrote:
> Rick Hillegas wrote:
>
>> Do you really need a 4KB key? That seems like a very big key. Is
>> there some reduction, summary, or checksum of the text which might
>> serve as a more compact key?
>
> The key is in fact a SSH key, and we want to prevent duplicates. We
> could generate a checksum I suppose, although that would need doing
> externally to Derby.
>
Hi Alan,
Thanks for the explanation. I see that uniqueness is an issue for you.
If you could tolerate the slight chance of false duplicates, you might
want to experiment with generated columns now. I think that there is
enough functionality checked into the development trunk that you could
kick the tires. Something like the following might work for you:
create function md5( rawValue varchar( 32672 ) )
returns varchar( 32 )
language java
deterministic
parameter style java
no sql
external name 'ChecksumCalculator.md5';
create table t( rawValue varchar( 32672 ), checksum varchar( 32 )
generated always as( md5( rawValue ) ) );
create index checksumIdx on t( checksum );
If this seems appealing, let us know how this works for you.
Thanks,
-Rick
Re: btree overflow during insert
Posted by Alan Burlison <Al...@sun.com>.
Rick Hillegas wrote:
> Do you really need a 4KB key? That seems like a very big key. Is there
> some reduction, summary, or checksum of the text which might serve as a
> more compact key?
The key is in fact a SSH key, and we want to prevent duplicates. We
could generate a checksum I suppose, although that would need doing
externally to Derby.
--
Alan Burlison
--
Re: btree overflow during insert
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Alan,
Do you really need a 4KB key? That seems like a very big key. Is there
some reduction, summary, or checksum of the text which might serve as a
more compact key?
Thanks,
-Rick
Alan Burlison wrote:
> Knut Anders Hatlen wrote:
>
>> I have logged DERBY-3947 and attached a reproducible test case.
>>
>> Note that it is possible to set derby.storage.pageSize just for a single
>> index by issuing
>>
>> CALL SYSCS_UTIL.SET_DATABASE_PROPERTY('derby.storage.pageSize', '32768')
>>
>> right before the index in question is created and resetting it with
>>
>> CALL SYSCS_UTIL.SET_DATABASE_PROPERTY('derby.storage.pageSize', null)
>>
>> afterwards.
>
> Thank-you very much for doing this, and the comments in the bug are
> useful too as they gives me a hint as to how we might change the table
> definition to avoid the problem altogether. We can probably work
> around this by reducing the length of the varchar column being
> indexed, which is a little aggressively-sized (32672). Failing that
> we can use the workaround you suggest.
>
> One thing I'm not quite clear on: does Derby allocate the full length
> of a varchar column in an index that uses it? In our case, although
> the column is varchar(32672), only a small fraction of it is used, 4Kb
> at most.
>
Re: btree overflow during insert
Posted by Alan Burlison <Al...@sun.com>.
Knut Anders Hatlen wrote:
> I have logged DERBY-3947 and attached a reproducible test case.
>
> Note that it is possible to set derby.storage.pageSize just for a single
> index by issuing
>
> CALL SYSCS_UTIL.SET_DATABASE_PROPERTY('derby.storage.pageSize', '32768')
>
> right before the index in question is created and resetting it with
>
> CALL SYSCS_UTIL.SET_DATABASE_PROPERTY('derby.storage.pageSize', null)
>
> afterwards.
Thank-you very much for doing this, and the comments in the bug are
useful too as they gives me a hint as to how we might change the table
definition to avoid the problem altogether. We can probably work around
this by reducing the length of the varchar column being indexed, which
is a little aggressively-sized (32672). Failing that we can use the
workaround you suggest.
One thing I'm not quite clear on: does Derby allocate the full length of
a varchar column in an index that uses it? In our case, although the
column is varchar(32672), only a small fraction of it is used, 4Kb at most.
--
Alan Burlison
--
Re: btree overflow during insert
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Kristian Waagan <Kr...@Sun.COM> writes:
> Alan Burlison wrote:
>> This is an important issue for us, and is a potential risk to us
>> deploying on Derby. Should I raise a bug? I've looked at the
>> source where this error is coming from, and it's scattered with
>> FIXME-type comments that suggests it's a known problem.
>
> Hi Alan,
>
> I don't remember this issue coming up before.
> If you can reproduce this, and maybe devote some time writing a repro,
> I would just log a bug right away.
> If not, I would suggest you wait for some more days in hope of a
> person having a deeper knowledge about this issue pitching in.
>
> FYI, issues/bug are logged at https://issues.apache.org/jira/browse/DERBY
I have logged DERBY-3947 and attached a reproducible test case.
Note that it is possible to set derby.storage.pageSize just for a single
index by issuing
CALL SYSCS_UTIL.SET_DATABASE_PROPERTY('derby.storage.pageSize', '32768')
right before the index in question is created and resetting it with
CALL SYSCS_UTIL.SET_DATABASE_PROPERTY('derby.storage.pageSize', null)
afterwards.
--
Knut Anders
Re: btree overflow during insert
Posted by Kristian Waagan <Kr...@Sun.COM>.
Alan Burlison wrote:
> This is an important issue for us, and is a potential risk to us
> deploying on Derby. Should I raise a bug? I've looked at the source
> where this error is coming from, and it's scattered with FIXME-type
> comments that suggests it's a known problem.
Hi Alan,
I don't remember this issue coming up before.
If you can reproduce this, and maybe devote some time writing a repro, I
would just log a bug right away.
If not, I would suggest you wait for some more days in hope of a person
having a deeper knowledge about this issue pitching in.
FYI, issues/bug are logged at https://issues.apache.org/jira/browse/DERBY
regards,
--
Kristian
>
> Thanks,
>
>> We are doing inserts with Derby 10.4.2.0 into a table that has an
>> index on it, and during the insert we get the following errors:
>>
>> *** Appending local Error for batch element #84: 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.***
>> *** Next Exception java.sql.SQLException: Error for batch element
>> #155: 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.***
>> Appending nestedError for batch element #84: 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.
>>
>> We've tried removing the index and re-adding it after the inserts,
>> but the result is the same. To stop this happening we have to up the
>> derby.storage.pageSize parameter to 16k, which is too big for what is
>> effectively an OLTP database, and the documentation for
>> derby.storage.pageReservedSpace says that it only applies to tables,
>> bot indexes.
>>
>> Is this a bug, and if not, what should we do to avoid it, other than
>> increasing the DB page size to 16K?
>
Re: btree overflow during insert
Posted by Alan Burlison <Al...@sun.com>.
This is an important issue for us, and is a potential risk to us
deploying on Derby. Should I raise a bug? I've looked at the source
where this error is coming from, and it's scattered with FIXME-type
comments that suggests it's a known problem.
Thanks,
> We are doing inserts with Derby 10.4.2.0 into a table that has an index
> on it, and during the insert we get the following errors:
>
> *** Appending local Error for batch element #84: 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.***
> *** Next Exception java.sql.SQLException: Error for batch element #155:
> 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.***
> Appending nestedError for batch element #84: 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.
>
> We've tried removing the index and re-adding it after the inserts, but
> the result is the same. To stop this happening we have to up the
> derby.storage.pageSize parameter to 16k, which is too big for what is
> effectively an OLTP database, and the documentation for
> derby.storage.pageReservedSpace says that it only applies to tables, bot
> indexes.
>
> Is this a bug, and if not, what should we do to avoid it, other than
> increasing the DB page size to 16K?
--
Alan Burlison
--