what is the optimized way to do the vaccum?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

what is the optimized way to do the vaccum?

Zhu, Liang [AUTOSOL/ASSY/US]
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
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: what is the optimized way to do the vaccum?

Gerry Snyder-4
On Wed, Dec 4, 2019, 9:28 PM Zhu, Liang [AUTOSOL/ASSY/US] <
[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: what is the optimized way to do the vaccum?

Zhu, Liang [AUTOSOL/ASSY/US]
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 <[hidden email]> On Behalf Of Gerry Snyder
Sent: Thursday, December 5, 2019 12:12 AM
To: SQLite mailing list <[hidden email]>
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] < [hidden email]> 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
> [hidden email]
> https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlit
> e.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8
> tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DF
> EINZ4E6I&m=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M&s=1HGpuZT8Hu2Bp
> siRzJ8yujtxh3m_XyAXLThncurjn-M&e=
>
_______________________________________________
sqlite-users mailing list
[hidden email]
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M&s=1HGpuZT8Hu2BpsiRzJ8yujtxh3m_XyAXLThncurjn-M&e= 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: what is the optimized way to do the vaccum?

Simon Slavin-3
On 5 Dec 2019, at 6:31pm, Zhu, Liang [AUTOSOL/ASSY/US] <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: what is the optimized way to do the vaccum?

David Raymond
In reply to this post by Zhu, Liang [AUTOSOL/ASSY/US]
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 <[hidden email]> On Behalf Of Zhu, Liang [AUTOSOL/ASSY/US]
Sent: Thursday, December 5, 2019 1:32 PM
To: SQLite mailing list <[hidden email]>
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 <[hidden email]> On Behalf Of Gerry Snyder
Sent: Thursday, December 5, 2019 12:12 AM
To: SQLite mailing list <[hidden email]>
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] < [hidden email]> 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
> [hidden email]
> https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlit
> e.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8
> tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DF
> EINZ4E6I&m=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M&s=1HGpuZT8Hu2Bp
> siRzJ8yujtxh3m_XyAXLThncurjn-M&e=
>
_______________________________________________
sqlite-users mailing list
[hidden email]
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=Q-3kQm4Cjo_MPKkH2cJZf86hGy0yr0vE6JtLc937A3M&s=1HGpuZT8Hu2BpsiRzJ8yujtxh3m_XyAXLThncurjn-M&e= 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: what is the optimized way to do the vaccum?

Roman Fleysher-2
In reply to this post by Simon Slavin-3
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 <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: Thursday, December 5, 2019 2:03 PM
To: SQLite mailing list <[hidden email]>
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] <[hidden email]> 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
[hidden email]
https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&amp;data=02%7C01%7Croman.fleysher%40einsteinmed.org%7C150f6e61d5e047dfe37e08d779b73fd1%7C9c01f0fd65e040c089a82dfd51e62025%7C0%7C0%7C637111700322791016&amp;sdata=x6sIZJFg33wns0NYU67N7cIyE%2FZsBC3N6Yp6P%2FuRFLo%3D&amp;reserved=0
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: what is the optimized way to do the vaccum?

Zhu, Liang [AUTOSOL/ASSY/US]
In reply to this post by Simon Slavin-3
We are using Journal_mode=delete,  sqlite3_close().

Liang

-----Original Message-----
From: sqlite-users <[hidden email]> On Behalf Of Simon Slavin
Sent: Thursday, December 5, 2019 2:04 PM
To: SQLite mailing list <[hidden email]>
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] <[hidden email]> 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
[hidden email]
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=QYaDMvjAXTSup0wv5mZP9nCIDsvmUkbkTWuYAtrJ6l8&s=Z4Qe515HCPlNxogmpfk3Z2O67uL7Hi9ifp1EmpU7oIg&e= 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: what is the optimized way to do the vaccum?

Simon Slavin-3
On 5 Dec 2019, at 8:07pm, Zhu, Liang [AUTOSOL/ASSY/US] <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: what is the optimized way to do the vaccum?

Richard Hipp-3
In reply to this post by Simon Slavin-3
On 12/5/19, Simon Slavin <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: what is the optimized way to do the vaccum?

Zhu, Liang [AUTOSOL/ASSY/US]
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 <[hidden email]> On Behalf Of Richard Hipp
Sent: Thursday, December 5, 2019 3:57 PM
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] [EXTERNAL] Re: what is the optimized way to do the vaccum?

On 12/5/19, Simon Slavin <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=jOURTkCZzT8tVB5xPEYIm3YJGoxoTaQsQPzPKJGaWbo&r=4Y1ZhFy9bpH-wvkl_u5WMmphQqcZcyyY0DFEINZ4E6I&m=1ENRPbn-L24iZuWFyfkwR0RwAWnSwaNCCh_MMvJN4Fs&s=UfFmqi9FEBt_M45aGHanxTL2DeM19UgHVighO3_nqKo&e= 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: what is the optimized way to do the vaccum?

Keith Medcalf
In reply to this post by Zhu, Liang [AUTOSOL/ASSY/US]

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users