How does PRAGMA optimize or ANALYSE affect concurrency? Is there any risk that it might leave the database locked for the full duration of the scan?
On 4 October 2017 at 23:29, Jens Alfke <[email protected]> wrote: > > >> On Oct 4, 2017, at 10:30 AM, Richard Hipp <[email protected]> wrote: >> >> The PRAGMA optimize command is our effort to move further in the >> direction of a "smart" SQLite that always automatically "does the >> right thing" with respect to gathering and using database statistics. > > That’s a great move. Along the same lines, it would be nice if SQLite could > vacuum once in a while without being asked (like a good housemate ;) What I’m > doing now is based on some advice I read in a blog post*: > > // If this fraction of the database is composed of free pages, vacuum > it > static const float kVacuumFractionThreshold = 0.25; > // If the database has many bytes of free space, vacuum it > static const int64_t kVacuumSizeThreshold = 50 * MB; > > // After creating a new database: > exec("PRAGMA auto_vacuum=incremental”); > > // Just before closing a database: > exec("PRAGMA optimize"); > int64_t pageCount = intQuery("PRAGMA page_count"); > int64_t freePages = intQuery("PRAGMA freelist_count"); > if ((pageCount > 0 && (float)freePages / pageCount >= > kVacuumFractionThreshold) > || (freePages * kPageSize >= kVacuumSizeThreshold)) { > exec("PRAGMA incremental_vacuum"); > } > > (To forestall any retorts that “you don’t need to vacuum because SQLite will > reuse the free space later”: Yes, you do need to, on a space-constrained > device like a phone. Otherwise your app never reclaims any storage back to > the OS for use by other apps, and you get customer issues like “I deleted all > my old junk from the app but it’s still using 10GB of storage, please help my > phone is out of space”…) > > —Jens > > * https://blogs.gnome.org/jnelson/2015/01/06/sqlite-vacuum-and-auto_vacuum/ > _______________________________________________ > 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
