572460 - Tracking: Ensure sqlite databases get vacuumed

Status: NEW

(Toolkit :: Storage, defect, P3)

Comment 1

[(dormant account)]

This is similar to bug 541373, except this is a tracking bug to ensure that each owner of an sqlite db vacuums when appropriate.

Comment 2

[(dormant account)]

Idea for autovacuuming algorithm:

Monitor *.sqlite file sizes such that every time the file size doubles, vacuum the db and record the new file size. This has an advantage over interval-based vacuums in that it's based on db usage.

In order to guard from overly-frequent vacuuming, i'd restrict the vacuums so they can't happen more than once a week.

The constants "doubles" and "once a week" are meant to get this work started, once someone finds better heuristics we can substitute those in.

Comment 3

[Mike Conley (:mconley) (:⚙️)]

Hey mak, is this still a useful idea?

Comment 4

[Marco Bonardo [:mak] (Away Apr 25 - May 5)]

(In reply to Mike Conley (:mconley) (:⚙️) (Totally backlogged on reviews and needinfos) from comment #3)
> Hey mak, is this still a useful idea?

it's complicate to answer the question for some reasons:
1. VACUUM doesn't always mean better perf, indeed a frequently vacuumed db would likely hit a performance penalty on writes, that are the most expensive operations already
2. the side of the database doesn't say anything about the need to vacuum a database
3. the size of the database long term could stay the same, but its internal fragmentation could grow

As such, is not clear whether a size based approach would really be better than a time based approach. For (3) I think it would not be.

The only valid stat to know whether a db needs to be vacuumed are fragmentation and the number of pages in the freelist. The latter is almost trivial to get (but not good alone, due to the chunking growth system we use), while the former is expensive, I'd suggest it only if we'd have a maintenance service that runs in background.

The idea that each consumer should be vacuumed sometimes, is clearly still sound.
Anyway, this really just matters for consumers that have a large db and an high traffic, in my profile those are places.sqlite, favicons.sqlite and webappsstore.sqlite, and all of them are managing their own vacuuming.
Other consumers like cookies, kinto and formhistory could maybe benefit from some sparse vacuuming (maybe they could just register with the vacuum manager, that while not being a perfect solution, should mostly do). 

Anyway, considered most compelling consumers are handling the problem, I don't think this is a top priority.