[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] auto_vacuum default setting
From:       _ph_ <hauptmannp () yahoo ! com>
Date:       2013-08-29 15:45:04
Message-ID: 1377791104725-70781.post () n5 ! nabble ! com
[Download RAW message or body]

auto_vacuum at full is slower:  

When you delete some data, SQLite would shrink the file size to the minimum.
If, in the next transaction, you add new data, SQLite would again have to
extend the file size by at least a page. This takes time. Without
auto-vacuum=FULL, the Insert statement would reuse the page that became
free.  

File system fragmentation (how the blocks of your file are arranged on disk)
also likely becomes worse. 

In-file fragmentation (how close related data is w.r.t. the logical file
offset) might also become worse, this should not be worse than random
inserts and deletes, though. 

---

auto_vacuum might be useful if disk space is very precious, or you have to
be "always on" with guaranteed response times, where even an infrequent
short "downtime" is problematic. 

If you are not sure, you can set auto_vacuum to INCREMENTAL to keep your
options open. This allows you to add incremental vaccum later, without
having to change database files. The overhead, AFAIK, is little. 

---

it makes more sense to trigger maintenance when it pays. 

You can always use pragma free_pages to query how many pages would be
released by vacuuming. 

For example: 

 - at some points - e.g. after a DELETE, after a while of inactivity or when
the user closes the application, check if there is a significant (!) number
of pages that would be freed by vacuuming. If yes, do an incremental vacuum. 

(Do not use icnremental vacuum after every delete! Only if there is
significant space to be freed!)

 - you can put a time limit on the incremental vacuum: if there's a
significant number of pages to be freed, run incremental_vacuum(1)
repeatedly until there are no more free pages, but also stop after a short
time (e.g. 50ms) has passed. This may not clear everything, but also does
nto interrupt the user

 - at a suitable point e.g. when the user is closing the application, and
you find there are really very very many pages that could be freed, start an
incremental or full vacuum of everything, but allow the user to cancel this.
(You might know something similar from closing outlook)

 - When doing a full vacuum, store the write counter in the database
somewhere. if the current write cunter is far ahead the one of the last full
vaccum, recommend a full rather than an incremental vacuum to the user.

---

We use SQLite as applicaiton file format, and in some common use cases, we
add and remove a lot of data. Furthermore, with the previosu solution,
people have complained about "databases not getting smaller" (we are talking
abotu hundreds of megabytes). So now I am using a combination of this:

 - when closing the app, and there's more than 1MB or more than 20% to 
reclaim, i release as many pages as possible for 50 ms. 
 - when after this, there's still a lot to reclaim (50% or 10MB, IIRC), I
continue calling incremental_vacuum, but allow the user to cancel the
process. 
 - There's a manual maintenance operation for integrity an validity check
and other cleanup. This also includes an option for a full VACUUM. When the
last full VACUUM was a long time ago (100k or a million writes or so), I
pre-select the "full vacuum" option. 






--
View this message in context: \
http://sqlite.1065341.n5.nabble.com/auto-vacuum-default-setting-tp70765p70781.html \
Sent from the SQLite mailing list archive at Nabble.com. \
_______________________________________________ sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[prev in list] [next in list] [prev in thread] [next in thread]