That does not make any sense at all. How are you deleting old rows? The easiest way is to use the table rowid ...
delete from data where rowid < (select max(rowid) - 200000 from data); insert into data (... data but not rowid ...) values (...); This will explode after you have inserted 9223372036854775807 rows -- at 4 records per second that is 100614506283 years. You will end up with the database size stabilizing at a few pages more than the size of the data. If you can keep a count of the inserts (in a program variable) and only do the delete every pageful of rows or so, that will reduce I/O significantly (as will batching the inserts, of course). ie: static int c = 0; void insertRow(...) { c += 1; if (c % 1000 == 0) { delete from data where rowid < (select max(rowid) - 200000 from data); c = 0; } insert into data values (....); } -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Zhu, Liang [AUTOSOL/ASSY/US] >Sent: Thursday, 5 December, 2019 14:31 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the >vaccum? > >According to the SQLlite.org, the purpose of vacuum is as follows, >VACUUM command rebuilds the database file, repacking it into a minimal >amount of disk space. > >I am trying to resolving an issue, I am keeping the record count in >each row for the table with 200000 row, After the table fill up 200,000 >record, when I deleting the data and inserting new data. my record >count can get all over the place, the record count some time can be >incremented up to 200 from one record to the next. > I am thinking it might be related to vacuum. I am vacuum when >freelist_count reaches to 1000. > >Thank you, >Liang > >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Richard Hipp >Sent: Thursday, December 5, 2019 3:57 PM >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the >vaccum? > >On 12/5/19, Simon Slavin <slav...@bigfraud.org> wrote: >> >> VACUUM should not be saving you any space. > >It might, depending on what he is doing. > >If a single page of the database holds (say) 30 rows of data, and the OP >deletes 10 rows from that page, that leaves some empty space on the page. >That empty space is reused later, but only if new rows are inserted that >have keys that belong on the page in question. If new content is >appended to the table (for example, if this is a ROWID table with >automatically chosen rowids and new rows are inserted) then the empty >space freed up by deleted rows on interior pages will continue to go >unused. > >Once a sufficient number of rows are removed from a page, and the free >space on that page gets to be a substantial fraction of the total space >for the page, then the page is merged with adjacent pages, freeing up a >whole page for reuse. But as doing this reorganization is expensive, it >is deferred until a lot of free space accumulates on the page. (The >exact thresholds for when a rebalance occurs are written down some place, >but they do not come immediately to my mind, as the whole mechanism *just >works* and we haven't touched it in about >15 years.) > >So, if the OP is adding rows to the end of a table, intermixed with >deleting random rows from the middle of the table, then the table will >grow in size and VACUUM will restore it to the minimum size. > >But the OP is wrong on this point: The table does not grow *without >bound*. There is an upper bound on the amount of free space within a >table. If you go above that bound, then space is automatically >reclaimed. But, it might be that the upper bound is larger than what the >OP can tolerate. >-- >D. Richard Hipp >d...@sqlite.org >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >https://urldefense.proofpoint.com/v2/url?u=http- >3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite- >2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9 >bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=1ENRPbn- >L24iZuWFyfkwR0RwAWnSwaNCCh_MMvJN4Fs&s=UfFmqi9FEBt_M45aGHanxTL2DeM19UgHVig >hO3_nqKo&e= >_______________________________________________ >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