Hello! I am currently evaluating the minimum possible size which sqlite is able to provide in the case when many blobs are inserted into the database. There was some discussion with the title "disk locality (and delta storage)" in this mailinglist which i followed - it provided some very interesting information about how sqlite handles memory pages in the file. In the discussion Mr. Hipp explained that sqlite only allocates a new page from the disk, when there is no space left in the existing pages. So i tried to verify that with a simple experiment using a db with a page_size of 1024 bytes: - I created a database and added 50.000 blobs with 800 bytes each - the resulting file size was 52.520.960 bytes - I created another database and added 50.000 blobs with 950 bytes each - the resulting file size was 52.520.960 bytes (the same as above) To this point, the results are as expected, as there is no possible way for the db to add two blobs to one page. Then i created a third database and added 50.000 blobs with 800 bytes each and then 50.000 blobs with 150 bytes each - the resulting file was 61.681.664 bytes. This result was not expected, as i assumed that the 150 bytes of each of the smaller blobs could be inserted into an already allocated memory page. Unfortunately, that doesn't seem to be the case. I seem to have understood something wrong, as i can't explain these results - can anybody enlighten this behaviour? Any comments are greatly appreciated :-) For reference, here is the excerpt from the mentioned discussion: _______________________________________________________________ >>Nathaniel Smith <[EMAIL PROTECTED]> wrote: >> >> So and rows are basically written to the file in the same order that >> the INSERT statements are executed?
> Right. If there are no free pages in the database file (which is the usual > case for Monotone, I expect) then new pages are allocated from the end of the > file. If the INSERT is small and will fit on an existing page, then no new > page allocations are required and the data gets inserted in exactly the right > spot. > But when inserting large blobs, as monotone does, you typically will require a > least one new page and that page will come at the end. _______________________________________________________________ Kind regards, Hendrik Bock
