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 <[email protected]> On
>Behalf Of Zhu, Liang [AUTOSOL/ASSY/US]
>Sent: Thursday, 5 December, 2019 14:31
>To: SQLite mailing list <[email protected]>
>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 <[email protected]> On
>Behalf Of Richard Hipp
>Sent: Thursday, December 5, 2019 3:57 PM
>To: SQLite mailing list <[email protected]>
>Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the
>vaccum?
>
>On 12/5/19, Simon Slavin <[email protected]> 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
>[email protected]
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
