Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?
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) - 20 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) - 20 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 On
>Behalf Of Zhu, Liang [AUTOSOL/ASSY/US]
>Sent: Thursday, 5 December, 2019 14:31
>To: SQLite mailing list
>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 20 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 On
>Behalf Of Richard Hipp
>Sent: Thursday, December 5, 2019 3:57 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the
>vaccum?
>
>On 12/5/19, Simon Slavin 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=DwIGaQ=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo=4Y1ZhFy9
>bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I=1ENRPbn-
>L24iZuWFyfkwR0RwAWnSwaNCCh_MMvJN4Fs=UfFmqi9FEBt_M45aGHanxTL2DeM19UgHVig
>hO3_nqKo=
>___
>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
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 20 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 On Behalf Of Richard Hipp Sent: Thursday, December 5, 2019 3:57 PM To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum? On 12/5/19, Simon Slavin 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=DwIGaQ=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I=1ENRPbn-L24iZuWFyfkwR0RwAWnSwaNCCh_MMvJN4Fs=UfFmqi9FEBt_M45aGHanxTL2DeM19UgHVighO3_nqKo= ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?
On 12/5/19, Simon Slavin 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 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?
On 5 Dec 2019, at 8:07pm, Zhu, Liang [AUTOSOL/ASSY/US] wrote: > We are using Journal_mode=delete, sqlite3_close(). Please check that when all your connections are closed, all temporary files are deleted. So if your database is called 'database.sql' then there should be no other file starting with that name, for instance 'database.sql-wal', 'database.sql-shm', or 'database.sql-journal'. Does your application add data, then delete data, then add more data, etc. ? Or does it just add data ? VACUUM should not be necessary. And it can use a lot of space while it's working. We need to find out why you are having to use it. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?
We are using Journal_mode=delete, sqlite3_close(). Liang -Original Message- From: sqlite-users On Behalf Of Simon Slavin Sent: Thursday, December 5, 2019 2:04 PM To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum? On 5 Dec 2019, at 6:31pm, Zhu, Liang [AUTOSOL/ASSY/US] wrote: > If I do not do Vacuum, my database size just keep raising, eventually the > database size gets to over 90% of storage size, I can save data to the > database any more. VACUUM should not be saving you any space. And VACUUM puts a lot of traffic through your storage device which will eventually kill it. What journal mode are you using ? In other words, what does the command PRAGMA journal_mode output ? Does your application close the connection correctly ? In other words, do you call sqlite3_close() or sqlite3_close_v2() and check the result code it returns ? ___ 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=DwIGaQ=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I=QYaDMvjAXTSup0wv5mZP9nCIDsvmUkbkTWuYAtrJ6l8=Z4Qe515HCPlNxogmpfk3Z2O67uL7Hi9ifp1EmpU7oIg= ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?
A side note about VACUUM: If I remember correctly, tables which do not have INTEGER PRIMARY KEY will have their rowid column reassigned. Be careful if you are using rowid. Roman From: sqlite-users on behalf of Simon Slavin Sent: Thursday, December 5, 2019 2:03 PM To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum? CAUTION: This email comes from an external source; the attachments and/or links may compromise our secure environment. Do not open or click on suspicious emails. Please click on the “Phish Alert” button on the top right of the Outlook dashboard to report any suspicious emails. On 5 Dec 2019, at 6:31pm, Zhu, Liang [AUTOSOL/ASSY/US] wrote: > If I do not do Vacuum, my database size just keep raising, eventually the > database size gets to over 90% of storage size, I can save data to the > database any more. VACUUM should not be saving you any space. And VACUUM puts a lot of traffic through your storage device which will eventually kill it. What journal mode are you using ? In other words, what does the command PRAGMA journal_mode output ? Does your application close the connection correctly ? In other words, do you call sqlite3_close() or sqlite3_close_v2() and check the result code it returns ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einsteinmed.org%7C150f6e61d5e047dfe37e08d779b73fd1%7C9c01f0fd65e040c089a82dfd51e62025%7C0%7C0%7C637111700322791016sdata=x6sIZJFg33wns0NYU67N7cIyE%2FZsBC3N6Yp6P%2FuRFLo%3Dreserved=0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?
SQLite is pretty good at using free space inside the file. So if inserting something is going to run you out of space, then it's going to run you out of space whether the file was previously vacuumed or not. Also reminder that when vacuum is run, SQLite makes a brand new copy of the database, then goes through and updates the pages of the original file, which requires writes to the rollback journal. So if your database is size n. Then the worst case scenario is that vacuum will peak out at using 3n worth of disk space. (Original file, copy, journal) So if your database is already 90% of your storage, then you're gonna have a hard time vacuuming it anyway. You could consider using incremental vacuum to clean up free space without re-creating the whole file, but that has to be enabled when the database file is created. Again though, that only frees up unused space. If an insert is making your database size bigger, then you don't have any unused space to clean up. -Original Message- From: sqlite-users On Behalf Of Zhu, Liang [AUTOSOL/ASSY/US] Sent: Thursday, December 5, 2019 1:32 PM To: SQLite mailing list Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum? If I do not do Vacuum, my database size just keep raising, eventually the database size gets to over 90% of storage size, I can save data to the database any more. Thank you, Liang -Original Message- From: sqlite-users On Behalf Of Gerry Snyder Sent: Thursday, December 5, 2019 12:12 AM To: SQLite mailing list Subject: [EXTERNAL] Re: [sqlite] what is the optimized way to do the vaccum? On Wed, Dec 4, 2019, 9:28 PM Zhu, Liang [AUTOSOL/ASSY/US] < liang@emerson.com> wrote: > All Sqlite Expert, > > I have one table, I am inserting and deleting record to and from this > table very 250ms. I always maintain 1000 rows in this table. I have > another table, I am inserting and deleting data to and from this > table every 1s. The data record in this table maintains at 200,000 rows. > Can I get some recommendation on what is optimized technique to do the > vaccum for my database? > > Thank you, > Liang > Why do you think that you need to vacuum at all? > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlit > e.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=jOURTkCZzT8 > tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DF > EINZ4E6I=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M=1HGpuZT8Hu2Bp > siRzJ8yujtxh3m_XyAXLThncurjn-M= > ___ 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=DwIGaQ=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M=1HGpuZT8Hu2BpsiRzJ8yujtxh3m_XyAXLThncurjn-M= ___ 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
Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?
On 5 Dec 2019, at 6:31pm, Zhu, Liang [AUTOSOL/ASSY/US] wrote: > If I do not do Vacuum, my database size just keep raising, eventually the > database size gets to over 90% of storage size, I can save data to the > database any more. VACUUM should not be saving you any space. And VACUUM puts a lot of traffic through your storage device which will eventually kill it. What journal mode are you using ? In other words, what does the command PRAGMA journal_mode output ? Does your application close the connection correctly ? In other words, do you call sqlite3_close() or sqlite3_close_v2() and check the result code it returns ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?
If I do not do Vacuum, my database size just keep raising, eventually the database size gets to over 90% of storage size, I can save data to the database any more. Thank you, Liang -Original Message- From: sqlite-users On Behalf Of Gerry Snyder Sent: Thursday, December 5, 2019 12:12 AM To: SQLite mailing list Subject: [EXTERNAL] Re: [sqlite] what is the optimized way to do the vaccum? On Wed, Dec 4, 2019, 9:28 PM Zhu, Liang [AUTOSOL/ASSY/US] < liang@emerson.com> wrote: > All Sqlite Expert, > > I have one table, I am inserting and deleting record to and from this > table very 250ms. I always maintain 1000 rows in this table. I have > another table, I am inserting and deleting data to and from this > table every 1s. The data record in this table maintains at 200,000 rows. > Can I get some recommendation on what is optimized technique to do the > vaccum for my database? > > Thank you, > Liang > Why do you think that you need to vacuum at all? > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlit > e.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=jOURTkCZzT8 > tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DF > EINZ4E6I=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M=1HGpuZT8Hu2Bp > siRzJ8yujtxh3m_XyAXLThncurjn-M= > ___ 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=DwIGaQ=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M=1HGpuZT8Hu2BpsiRzJ8yujtxh3m_XyAXLThncurjn-M= ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
