db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From anthonyri <anthony.r...@inbox.com>
Subject Re: Database size larger than expected
Date Sat, 22 Oct 2011 08:01:04 GMT

Mike,

Thank you for the explanation.

When I scale up to holding a million records, then the database size is more
in line with what I would expect. As per your explanation, small databases
already have an 'empty' size, which appears to be why it was larger than I
expected.

Anthony


Mike Matrigali wrote:
> 
> There are a number of factors:
> 
> 1) an "empty" derby database takes up 770k in 10.9.  This is 71 system 
> catalogs and indexes on those system catalogs.  Even empty tables still
> have inital empty pages allocated and written, in the default they take
> at lease 8k each.  Each file in seg0 represents either a table or an
> index.  I assume you meant you have a Derby database with 2 tables, so
> only 2 of the files in seg0 have data for your tables.  They are likely
> the 2 biggest files in seg0.
> 
> 2) By default the system also reserves empty space in pages for future 
> updates.  I think the default in 20% reserved.  There are properties to
> override this.
> 
> 3) Each record has overhead and each field has overhead that is variable.
> 
> 4) The tables will begin allocating 8 pages at a time when they need
> a new empty page.  So worst case could be 7 empty pages at the end.
> 
> 
> anthonyri wrote:
>> David,
>> 
>> No indexes I am afraid, should have mentioned that, sorry.
>> 
>> Anthony
>> 
>> 
>> David Zanter wrote:
>>> Do you have Indexes on those Tables?
>>>
>>>
>>> On Fri, Oct 21, 2011 at 5:29 PM, anthonyri <anthony.rich@inbox.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> I have a Derby database with two records, the first has 5000 records
>>>> (of
>>>> int, double, double, double, double - so approx 34 bytes per record),
>>>> the
>>>> second has 1000 records (of int, varchar(10), varchar(40) - so approx
>>>> 102
>>>> bytes per record).
>>>>
>>>> 5000 * 34 = 166k
>>>> 1000 * 102 = 100k
>>>> Total = 266k (if it was in a text file)
>>>>
>>>> What I don't understand is why the database (seg0 folder) is 1.2MB?
>>>>
>>>> I have compacted the database using;
>>>>
>>>> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('APP', 'mapObject', 1, 1,
>>>> 1);
>>>> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'MAPOBJECT', 1);
>>>>
>>>> Any thoughts please?
>>>>
>>>> Anthony
>>>> --
>>>> View this message in context:
>>>> http://old.nabble.com/Database-size-larger-than-expected-tp32699233p32699233.html
>>>> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>>>>
>>>>
>>>
>> 
> 
> 
> 

-- 
View this message in context: http://old.nabble.com/Database-size-larger-than-expected-tp32699233p32700588.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message