db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Hohl, Gerrit" <g.h...@aurenz.de>
Subject Indexes grow over time insanly big and can't be shrunk
Date Tue, 15 Sep 2020 12:14:49 GMT
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