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 <[email protected]> >>> 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.
