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
