Hi All, The reason I consider to use VACUUM is that: when I insert 10k and delete 10k records for a number of times, the db file size keeps constant in each iteration. On the other hand, if I terminate the program manually and start the iteration again, the db file size increase once in the first iteration. Then it keeps file size constant afterwards. For example, 1. when no manual termination of program, the file size keeps constant 100kB 2. when I terminate the program and start the iteration again, the file size changes to 200kB in the first iteration. 3. After the first iteration, the file size keeps at 200kB in later iterations.
Does anyone can advise me that which parts I am missing for the Amalgamation compilation of USING *** OTHER VFS ***? On Sat, Sep 2, 2017 at 5:55 PM, Jacky Lam <jacky...@gmail.com> wrote: > Thanks for the answer. I have a clear picture now. > I would also like to know for Android SQLite case; > does the default value for auto vacuum is OFF. > Is it the same default compiling config with sqlite.c source code? > In other words, does it need to do vacuum manually? > > > > 從我的 Samsung Galaxy 智能手機發送。 > > -------- 原始訊息 -------- > 由： David Raymond <david.raym...@tomtom.com> > 日期： 17/9/2 上午2:43 (GMT+08:00) > 收件人： SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > 主題： Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0 > > Other notes in addition to the previous responses. > > File size will never go down without some sort of vacuum operation. > Basically since you can only truncate files, and not snip out sections in > the middle. If you delete from the front of a large file, in order to > shrink it you'd have to re-write the entire file from that point to the > end. So instead of doing that it just recycles pages when possible, and > only expands the file when it runs out of free space and needs more. > > Here's the blurb on autovacuum: http://www.sqlite.org/pragma. > html#pragma_auto_vacuum > > INCREMENTAL mode will allow you free up unused pages when you explicitly > tell it to, FULL mode will do it automatically. > > Autovacuum does not do the extra nifty steps that a regular vacuum does. > From the above linked blurb: > > "When the auto-vacuum mode is 1 or "full", the freelist pages are moved to > the end of the database file and the database file is truncated to remove > the freelist pages at every transaction commit. Note, however, that > auto-vacuum only truncates the freelist pages from the file. Auto-vacuum > does not defragment the database nor repack individual database pages the > way that the VACUUM command does. In fact, because it moves pages around > within the file, auto-vacuum can actually make fragmentation worse." > > A full fledged vacuum (http://www.sqlite.org/lang_vacuum.html) will > re-write the entire file (twice actually) and do extra things, for example > re-ordering all of the records of a table so that they're all nicely packed > together in a continguous section, already in primary key order, and no > longer semi-randomly scattered throughout. > > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Jacky Lam > Sent: Friday, September 01, 2017 12:42 PM > To: SQLite mailing list > Subject: Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0 > > Hi All, > While using my own implemented file system, the db file size will only > expand and not prune even remove record from the db. > Could anyone advise me that what I am missing in order to pruning the db > size when removing a number of records? > Jacky > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
