>> 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. What do you mean by this ? How is a smaller page bad for the database ?
On Mon, Feb 10, 2014 at 2:43 PM, Hick Gunter <h...@scigames.at> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
