With a record size of a little over 4K (note that the on-disk space requirement of a integer+4k Blob row is not always 4k+8) and a page size of 2K you are storing 1 row in 3 pages (close to 50% overhead). Deleting a record will give you 3 pages of free space, which will be reused quickly; some of it for the higher levels of the B-Tree. Note that choosing a page size smaller than the typical row size means that the bottom level of the BTree degrades to 1 row per node.
Changing to 4k or 8k will increase overhead to near 100% (as you now need 2 Pages of 4k or one page of 8k for each row). 16k pages ( 3 rows/page) reduce this to 25.00% while deleting 5 adjacent rows is guaranteed to free up 1 page. 32k pages ( 7 rows/page) reduce this to 12.50% but requires 13 adjacent deletes for 1 guaranteed free page. 64k pages (15 rows/page) reduce this to 6.25% but requires 29 adjacent deletes for 1 guaranteed free page. You can choose the source of fragmentation: loosing close to 1 row per page (better in bigger pages) or having ununsed space due to nonadjacent deletes (better in smaller pages) -----Ursprüngliche Nachricht----- Von: Raheel Gupta [mailto:raheel...@gmail.com] Gesendet: Montag, 10. Februar 2014 07:14 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Free Page Data usage Hi, I tried the same database I had and used a 2KB page size. It works much faster and also the pages are reused immediattly to the extent of 95%. If only the number of pages could be increased somehow. Does anyone think its practical to make the pageNo from a 32 bit int to a 64 bit Unsigned Integer. I do understand that VACUUM is not a good option for me. On Sun, Feb 9, 2014 at 4:48 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 9 Feb 2014, at 10:45am, RSmith <rsm...@rsweb.co.za> wrote: > > > On 2014/02/09 12:06, Raheel Gupta wrote: > >> Sir, I have only one auto increment primary key. > >> Since the new rows will always have a higher number will the pages > >> which have some free space by deleting rows with lower numbered > >> keys > never > >> be reused ? e.g. If row with ROWID "1" was deleted and freed, will > >> it > not > >> be used to store the NEW row which will be assigned ROWID 10001 ? > > > > Yes. That is the point of AutoIncrement, every new Key will always > > be > higher than any previous key ever used, and always exactly one higher > than the highest ever previously used key. As such, it cannot be > re-used within pages that are half filled from deletion (except maybe > the last page), and I believe pages that go completely empty may be > re-used without the need to vacuum etc. (need someone to confirm this). > > You are correct, depending on this PRAGMA: > > <http://www.sqlite.org/pragma.html#pragma_auto_vacuum> > > auto_vacuum = NONE > > A page which has all its data deleted is added to the 'free pages' > list and eventually reused. > > auto_vacuum = FULL > > A page which has all its data deleted is replaced by the last page of > the file. The file is then truncated to release the space of the last > page for use in other files. > > auto_vacuum = INCREMENTAL > > A page which has all its data deleted is replaced by the last used > page of the file. When you issue "PRAGMA incremental_vacuum(N)" the > file is truncated to release unused pages at the end for use in other files. > > As in previous discussion, all this is about reclaiming space at the > page > level: releasing entire pages of space. It has nothing to do with > reclaiming space within a page. And also as in previous discussion, > the fastest of these is "auto_vacuum = NONE". Copying one page to > another, releasing filespace and claiming it back again are slow and > require much reading and writing. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
