Re: Indexes grow over time insanly big and can't be shrunk
Hi Gerrit, I don't have a theory about what caused this problem. Maybe COMPRESS_TABLE() has a serious bug. A couple questions: Q1) Do you have the results of SPACE_TABLE() for this situation? Q2) What value did you specify for the SEQUENTIAL argument of COMPRESS_TABLE()? Q3) Other than COMPRESS_TABLE(), what is the pattern of activity on this table? If this happens again, take a look at the ALTER TABLE DROP PRIMARY KEY and ALTER TABLE ADD CONSTRAINT commands. They may make it simpler to repair the damage. Thanks, -Rick On 9/15/20 5:14 AM, Hohl, Gerrit wrote: Hello everyone, we use Apache Derby v10.14.2.0 in our software and now have a problem with an installation. Every night we perform a compact on all tables - at least the ones which can be shrunk (SYSCS_DIAG.SPACE_TABLE() and SYSCS_UTIL.SYSCS_COMPRESS_TABLE(String, String, Smallint)). Normally that constellation works fine. But now faced an installation which ran out of free disk space without any reason. After some analysing we narrowed it down to one table which is meant for logging. Records are inserted and deleted often (the size of it is limited at 10.000 entries through the software). The maximum - based on the structure - should be around 40 MB. Not really much. So we were very surprised seeing that this thing took around 14 GB. I realized that I accumulated the table and its indexes to get that value. After splitting it up, I saw that the table itself really only took 40 MB. But two of the 6 indexes - one was the primary key index - took more than 13 GB of space. Apache Derby also said no NUMFREEPAGES and no ESTIMSPACESAVING. My solution then was shutting down the service (in-process database), connect to the database, dropping the indexes, create a new column for temporarily storing the primary key value, copying the values, also dropping that column, recreating the primary key column, copying everything back, dropping the temporary primary key column, creating all the indexes again. Now everything is back to normal and the table *including* its indexes is 40 MB. Any idea on this? Why did the index grow that big? What can I do preventing it? Seems reorganising isn't an option as Apache Derby itself doesn't realize that it can free space. Regards, Gerrit
Re: database in a jar : conglomerate does not exists
Thanks for letting us know how you resolved the issue.
On 9/14/20 11:08 PM, RAPPAZ Francois wrote:
Thank you all
At the end
- taking care for the version of derby to be the same when I build the
database or when I search
- deleting the old database (corrupted) and starting with a new one, then
creating the table and populating it
Solved the problem.
Archiving the database in a jar was not the reason.
Cheers
François
-Original Message-
From: Rick Hillegas
Sent: 12 September 2020 18:13
To: RAPPAZ Francois
Cc: derby-user@db.apache.org
Subject: Re: database in a jar : conglomerate does not exists
This error indicates that you are trying to boot a database with a lower
version of Derby than the one used to create the database. I believe you said
that the database was created by Derby 10.8.2.2. It looks like you are trying
to boot it with some version of Derby in the 10.4 family.
-Rick
On 9/11/20 12:33 AM, RAPPAZ Francois wrote:
If I start from a new database (named docentries), I packed it in a jar, I can
connect with ij and run a select command.
If I run the code from my java classe, I get the error
- SQLException -
SQL State: XJ040
Error Code: 4
Message:Failed to start database 'classpath:docentries', see the next
exception for details.
- SQLException -
SQL State: XCL20
Error Code: 2
Message:Catalogs at version level 'null' cannot be upgraded to version
level '10.4'.
I connect with db = new
DBConnector("jdbc:derby:classpath:docentries");
François
-Original Message-
From: Rick Hillegas
Sent: 11 September 2020 00:49
To: Derby Discussion ; RAPPAZ Francois
Subject: Re: database in a jar : conglomerate does not exists
Also, look inside the jar file for a directory called docentries/seg0.
Does it contain a file called c560.dat?
On 9/10/20 8:53 AM, Rick Hillegas wrote:
Sorry. Make that query:
SELECT s.schemaName, t.tableName, c.conglomerateName
FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t
WHERE c.conglomerateNumber = 1376
AND c.tableID = t.tableID
AND t.schemaID = s.schemaID
;
On 9/10/20 8:22 AM, Rick Hillegas wrote:
Hi François,
Do you have any information or theories about how your database
became corrupted? I have never encountered this situation before. A
database in a jar file should be read-only, so the only theory I
have is that the jar file itself was corrupted by some process outside Derby.
Please run the following query in order to find out what table/index
is corrupted:
SELECT s.schemaName, t.tableName, c.conglomerateName
FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t
WHERE c.conglomerateNumber = 376
AND c.tableID = t.tableID
AND t.schemaID = s.schemaID
;
Thanks,
-Rick
On 9/10/20 3:09 AM, RAPPAZ Francois wrote:
Hi
I have a one table database embedded in a jar file. I tried to
access it from ij with java -jar %DERBY_HOME%/lib/derbyrun.jar ij
-p ij.properties
ij.properties is
derby.ui.codeset=utf8
ij.connection.doc=jdbc:derby:jar:(U:/docs/OA/articles/zlib/autconv/
a
utconv.jar)docentries
I can see that my table (authors) is in the the database with SHOW
TABLES; I can see the columns
ij> DESCRIBE authors;
COLUMN_NAME
|TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
---
-
--
NAME1 |VARCHAR |NULL|NULL|20 |NULL |40 |NO
NAME2 |VARCHAR |NULL|NULL|20 |NULL |40 |NO
DATA |VARCHAR |NULL|NULL|50 |NULL |100
|YES AUTHOR_ID |INTEGER |0 |10 |10 |AUTOINCRE&|NULL
|NO
4 rows selected
But I can't run a select statement:
ij> select * from authors;
ERROR XSAI2: Le conglomerat (1,376) demande n'existe pas.
ij> exit;
I have derby 10.8.2.2
Thanks for any help.
François
.
Indexes grow over time insanly big and can't be shrunk
Hello everyone, we use Apache Derby v10.14.2.0 in our software and now have a problem with an installation. Every night we perform a compact on all tables - at least the ones which can be shrunk (SYSCS_DIAG.SPACE_TABLE() and SYSCS_UTIL.SYSCS_COMPRESS_TABLE(String, String, Smallint)). Normally that constellation works fine. But now faced an installation which ran out of free disk space without any reason. After some analysing we narrowed it down to one table which is meant for logging. Records are inserted and deleted often (the size of it is limited at 10.000 entries through the software). The maximum - based on the structure - should be around 40 MB. Not really much. So we were very surprised seeing that this thing took around 14 GB. I realized that I accumulated the table and its indexes to get that value. After splitting it up, I saw that the table itself really only took 40 MB. But two of the 6 indexes - one was the primary key index - took more than 13 GB of space. Apache Derby also said no NUMFREEPAGES and no ESTIMSPACESAVING. My solution then was shutting down the service (in-process database), connect to the database, dropping the indexes, create a new column for temporarily storing the primary key value, copying the values, also dropping that column, recreating the primary key column, copying everything back, dropping the temporary primary key column, creating all the indexes again. Now everything is back to normal and the table *including* its indexes is 40 MB. Any idea on this? Why did the index grow that big? What can I do preventing it? Seems reorganising isn't an option as Apache Derby itself doesn't realize that it can free space. Regards, Gerrit
RE: database in a jar : conglomerate does not exists
Thank you all
At the end
- taking care for the version of derby to be the same when I build the
database or when I search
- deleting the old database (corrupted) and starting with a new one, then
creating the table and populating it
Solved the problem.
Archiving the database in a jar was not the reason.
Cheers
François
-Original Message-
From: Rick Hillegas
Sent: 12 September 2020 18:13
To: RAPPAZ Francois
Cc: derby-user@db.apache.org
Subject: Re: database in a jar : conglomerate does not exists
This error indicates that you are trying to boot a database with a lower
version of Derby than the one used to create the database. I believe you said
that the database was created by Derby 10.8.2.2. It looks like you are trying
to boot it with some version of Derby in the 10.4 family.
-Rick
On 9/11/20 12:33 AM, RAPPAZ Francois wrote:
> If I start from a new database (named docentries), I packed it in a jar, I
> can connect with ij and run a select command.
>
> If I run the code from my java classe, I get the error
>
> - SQLException -
>SQL State: XJ040
>Error Code: 4
>Message:Failed to start database 'classpath:docentries', see the next
> exception for details.
>
> - SQLException -
>SQL State: XCL20
>Error Code: 2
>Message:Catalogs at version level 'null' cannot be upgraded to version
> level '10.4'.
>
> I connect with db = new
> DBConnector("jdbc:derby:classpath:docentries");
>
> François
>
> -Original Message-
> From: Rick Hillegas
> Sent: 11 September 2020 00:49
> To: Derby Discussion ; RAPPAZ Francois
>
> Subject: Re: database in a jar : conglomerate does not exists
>
> Also, look inside the jar file for a directory called docentries/seg0.
> Does it contain a file called c560.dat?
>
> On 9/10/20 8:53 AM, Rick Hillegas wrote:
>> Sorry. Make that query:
>>
>> SELECT s.schemaName, t.tableName, c.conglomerateName
>>
>> FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t
>>
>> WHERE c.conglomerateNumber = 1376
>>
>> AND c.tableID = t.tableID
>>
>> AND t.schemaID = s.schemaID
>>
>> ;
>>
>>
>> On 9/10/20 8:22 AM, Rick Hillegas wrote:
>>> Hi François,
>>>
>>> Do you have any information or theories about how your database
>>> became corrupted? I have never encountered this situation before. A
>>> database in a jar file should be read-only, so the only theory I
>>> have is that the jar file itself was corrupted by some process outside
>>> Derby.
>>>
>>> Please run the following query in order to find out what table/index
>>> is corrupted:
>>>
>>> SELECT s.schemaName, t.tableName, c.conglomerateName
>>>
>>> FROM sys.sysConglomerates c, sys.sysSchemas s, sys.sysTables t
>>>
>>> WHERE c.conglomerateNumber = 376
>>>
>>> AND c.tableID = t.tableID
>>>
>>> AND t.schemaID = s.schemaID
>>>
>>> ;
>>>
>>>
>>> Thanks,
>>> -Rick
>>>
>>> On 9/10/20 3:09 AM, RAPPAZ Francois wrote:
Hi
I have a one table database embedded in a jar file. I tried to
access it from ij with java -jar %DERBY_HOME%/lib/derbyrun.jar ij
-p ij.properties
ij.properties is
derby.ui.codeset=utf8
ij.connection.doc=jdbc:derby:jar:(U:/docs/OA/articles/zlib/autconv/
a
utconv.jar)docentries
I can see that my table (authors) is in the the database with SHOW
TABLES; I can see the columns
ij> DESCRIBE authors;
COLUMN_NAME
|TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
---
-
--
NAME1 |VARCHAR |NULL|NULL|20 |NULL |40 |NO
NAME2 |VARCHAR |NULL|NULL|20 |NULL |40 |NO
DATA |VARCHAR |NULL|NULL|50 |NULL |100
|YES AUTHOR_ID |INTEGER |0 |10 |10 |AUTOINCRE&|NULL
|NO
4 rows selected
But I can't run a select statement:
ij> select * from authors;
ERROR XSAI2: Le conglomerat (1,376) demande n'existe pas.
ij> exit;
I have derby 10.8.2.2
Thanks for any help.
François
>>>
>>
> .
