db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Indexes grow over time insanly big and can't be shrunk
Date Tue, 15 Sep 2020 15:27:01 GMT
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
>
>


Mime
View raw message