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 "LEE YAN SHEN, ITDO" <YS...@psa.com.sg> on 2006/06/20 10:22:08 UTC
DB File Size
Hi,
I have been doing stress test on the database doing lots of
insert/update/delete/query rapidly and found that one of the files is
growing rapidly although the number of records in the db remain more or
less constant during the test. In fact, it grew 100 over megabytes after
about 350,000 transactions. The only way to reduce the file size was to
do a compress table which made it reduce to only 2 MB.
Is it the expected behaviour? From the documentation, derby should be
zero admin and be able to reuse space made available after a record is
deleted. Hence I do not expect the file size to grow constantly with the
same number of records.
Can anyone advise please?
Thanks.
This communication contains confidential or privileged information. If you are not the intended recipient, please notify us IMMEDIATELY that you have received it and destroy it. We are not liable for any unauthorised sending of or interference with this communication.
Re: DB File Size
Posted by Lee Yan Shen <ys...@psa.com.sg>.
I think it is due to the issue described here
http://issues.apache.org/jira/browse/DERBY-691
(Link given by Stanley and posted by you?)
Let me run the test again without any insert abortion.
Thanks.
Re: DB File Size
Posted by Mike Matrigali <mi...@sbcglobal.net>.
Any chance you can post the test. If not can you describe the test
a little more:
o number of tables (ie. is it only one?)
o what is the ddl
o what is the expected life of a row and the distribut
o what version are you using?
I added some work to do better automatic space reclamation of blob/clobs
in the trunk awhile back.
Otherwise deleted space is automatically reclaimed after (in the case of
non blob/clob):
1) the deleted row transaction commits
2) all rows on a page are deleted.
Note that space is never automatically returned to the OS, only
automatically made available for future inserts. So worst case is
do all the inserts first and then do the deletes (but that does not
sound like what your test does.)
LEE YAN SHEN, ITDO wrote:
> Hi,
>
> I have been doing stress test on the database doing lots of
> insert/update/delete/query rapidly and found that one of the files is
> growing rapidly although the number of records in the db remain more or
> less constant during the test. In fact, it grew 100 over megabytes after
> about 350,000 transactions. The only way to reduce the file size was to
> do a compress table which made it reduce to only 2 MB.
>
> Is it the expected behaviour? From the documentation, derby should be
> zero admin and be able to reuse space made available after a record is
> deleted. Hence I do not expect the file size to grow constantly with the
> same number of records.
>
> Can anyone advise please?
>
> Thanks.
>
>
> This communication contains confidential or privileged information. If
> you are not the intended recipient, please notify us IMMEDIATELY that
> you have received it and destroy it. We are not liable for any
> unauthorised sending of or interference with this communication.
>
Re: DB File Size
Posted by Stanley Bradbury <St...@gmail.com>.
LEE YAN SHEN, ITDO wrote:
>
> Hi,
>
> I have been doing stress test on the database doing lots of
> insert/update/delete/query rapidly and found that one of the files is
> growing rapidly although the number of records in the db remain more
> or less constant during the test. In fact, it grew 100 over megabytes
> after about 350,000 transactions. The only way to reduce the file size
> was to do a compress table which made it reduce to only 2 MB.
>
> Is it the expected behaviour? From the documentation, derby should be
> zero admin and be able to reuse space made available after a record is
> deleted. Hence I do not expect the file size to grow constantly with
> the same number of records.
>
> Can anyone advise please?
>
> Thanks.
>
>
> This communication contains confidential or privileged information. If
> you are not the intended recipient, please notify us IMMEDIATELY that
> you have received it and destroy it. We are not liable for any
> unauthorised sending of or interference with this communication.
>
Hi -
What version of Derby are you using? There are several enhancements
filed to improve automated space reclamation for tables. Some have been
implemented and some have not. If you are using a recent snapshot of
Derby and still seeing this behavior then please check JIRA and see if
your situation is described in one of the open enhancements. If not
please file an additional enhancement request describing the processing
that is causing this growth.
Here are some of the issues that are still open:
The 'general' entry for ongoing work:
http://issues.apache.org/jira/browse/DERBY-512
Problem reclaiming space when an insert in aborted:
http://issues.apache.org/jira/browse/DERBY-691
=== Some resolved issues:
Reclamation of LOB columns
http://issues.apache.org/jira/browse/DERBY-670
In place compress ( SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE):
http://issues.apache.org/jira/browse/DERBY-132
>> NOTE: I do not think that the work has been done to have the system
run this internally.
HTH