Closed Bug 394379 Opened 14 years ago Closed 12 years ago

vacuum all sqlite files after an upgrade ?

Categories

(Toolkit :: Storage, defect, P3)

defect

Tracking

()

RESOLVED DUPLICATE of bug 395020
mozilla1.9beta3

People

(Reporter: jo.hermans, Unassigned)

Details

I was thinking of a good way to shrink the various sqlite files when they become fragmented. Various bugs exists about it (bug 393965, bug 390244, bug 385834, ...), but the problem is that a full vacuum is very slow (too long ; an auto vacuum is faster and can be done on an idle timer (if the database was create with the program at least). Bug 385834 implemented it for places.sqlite, but there are also other files : content-prefs, cookies, downloads, formhistory, search, urlclassifier2, ... But do we now have to install idle timers everywhere ? Or do full vacuums once in a while, like bug 390244 ?

But it can be easier. What if we perform a full vacuum on all sqlite files, immediately after an upgrade ? That wouldn't bother the users too much (maybe with a progress dialog box), and we can be sure it's executed every 9 weeks or so. People who want to vacuum more often, might use storage-explorer (bug 394108).
Updates occur daily on the "nightly" channel...
Ah yes ...  didn't think about that. Maybe not for the nightly channel. Or not more than once per week.
Jo, interesting idea.  

I'm not sure I agree that doing this on upgrade is the right place, as the user experience (even with a progress dialog) may not be great.

perhaps sdwilsh has thoughts about generalizing the vacuum / idle work done in places and moving it to moz storage.

(also, consider moving this bug from firefox/installer to core/storage or firefox/general, as it is not an installer issue.  even if we did decide to do this from update, the change would be to mozilla/toolkit/mozapps/update/src/nsUpdateService.js.in, not the installer.)
well for nightly users, since it would be done daily...shouldn't take any time at all to vacuum in theory?
(In reply to comment #3)
> Jo, interesting idea.  
> 
> I'm not sure I agree that doing this on upgrade is the right place, as the user
> experience (even with a progress dialog) may not be great.
> 

The upgrade already interrupts my work and takes a reasonable amount of time.  Seems like the perfect time to do this!

Given the auto_vacuum doesn't defrag this seems like a really important thing to do.

> perhaps sdwilsh has thoughts about generalizing the vacuum / idle work done in
> places and moving it to moz storage.
> 

That's a really really difficult problem.  Besides the user clicking a "do it now" button there is no way to ensure you won't start a vacuum right before the user tries to do something and frustrate them.



Flags: blocking-firefox3?
This seems sensible to me, really, for the reasons that Schrep mentions. Ideally we'd make it part of the update process so that the time required is reflected in the progress meter, but that might mean modifying the updater binary itself.

I'm going to block on this, but there is a concern that I have which is ensuring that the time required to perform the operation isn't so high that it drastically extends the update-installation time. Right now vaccuuming is pretty quick, but that's probably because of bug 401726 :)
Flags: blocking-firefox3? → blocking-firefox3+
(In reply to comment #6)
> Right now vaccuuming is
> pretty quick, but that's probably because of bug 401726 :)
> 

Vacuuming copies all contents into a temporary database, so bug 401726 actually makes vacuum slower, due to the immense moz_places table (and it's indexes) that would need to be copied.

the original bug was about doing the vacuum immediately after an upgrade, as
part of the firefox process, and putting up some sort of progress.

but as schrep/beltzner point out, doing this during upgrade could be a better
time to do this, as you're already interrupted.

just some thoughts about this idea:

1)

the updater will need to know where your profile lives, so it can find the
places.sqlite file.

but what about other users or other profiles?  for example, if my windows
machine has 5 users (1 admin) and the admin does the upgrade.

would the updater vacuum all of the places.sqlite dbs?

2)  

doing a vacuum is synchronous, so we might need to do the sqlite work on a
separate thread so that we can the updater progress meter UI won't block.  we
may need to put it into an indeterminate mode, as we don't know how long it is
going to take. 
since were talking about doing more from the updater binary, bringing rs/bs into the discussion.
(In reply to comment #5)
> That's a really really difficult problem.  Besides the user clicking a "do it
> now" button there is no way to ensure you won't start a vacuum right before the
> user tries to do something and frustrate them.
I've put a bunch of thought on how to actually do this, but I haven't had the time or to really implement it.  It'd probably complicate our code a bit, but the idle service can tell us when a user isn't idle anymore, and we can tell sqlite to abort whatever it's doing, so we could possibly abort the vacuum.  This is a completely untested theory however.

(In reply to comment #6)
> This seems sensible to me, really, for the reasons that Schrep mentions.
> Ideally we'd make it part of the update process so that the time required is
> reflected in the progress meter, but that might mean modifying the updater
> binary itself.
The problem is that, as far as I can tell, there's no way to actually get the amount of time a vacuum will take.

(In reply to comment #7)
Well, the extension manager runs at the startup after an update, so we could just do it sometime like then, no?  We still don't know how long it will take though... :(
(In reply to comment #10)
> (In reply to comment #5)
> > That's a really really difficult problem.  Besides the user clicking a "do it
> > now" button there is no way to ensure you won't start a vacuum right before the
> > user tries to do something and frustrate them.
> I've put a bunch of thought on how to actually do this, but I haven't had the
> time or to really implement it.  It'd probably complicate our code a bit, but
> the idle service can tell us when a user isn't idle anymore, and we can tell
> sqlite to abort whatever it's doing, so we could possibly abort the vacuum. 
> This is a completely untested theory however.

Yea - I don't know if you can abort vacuums.  Another option would be at shutdown (not every time, but after n shutdowns) since it seems as safe as any other write:

http://www.mail-archive.com/sqlite-users@sqlite.org/msg26390.html

The problem with shutdown is that if someone tries to reopen the Firefox, they'll get a profile dialog because their profile will be locked :(
I like the progress being made so far, now that auto-vacuum will be switched off. But don't forget that places.sqlite isn't the only sqlite database in the profile folder, even though it's the busiest one. Especially the urlclassifier3.sqllite file (which is currently stored in Local Settings if you're on Windows) : bug 383031.
See also bug 395020, "VACUUM after idle [or shutdown or upgrade?] (even if we're doing incremental vacuuming) to defragment the database".
Software Update is only a slightly better component than the installer for this since it applies to all platforms.
Component: Installer → Software Update
QA Contact: installer → software.update
Target Milestone: --- → Firefox 3 M10
Priority: -- → P3
Target Milestone: Firefox 3 M10 → Firefox 3 M11
clearing blocking status, I don't think this is a blocker at all.

see bug #403702 (the initial comment) for some reasoning.
Flags: blocking-firefox3+
Product: Firefox → Toolkit
Over to storage since this would need to be done on app upgrade for each profile so app update can't provide this.
Component: Application Update → Storage
QA Contact: software.update → storage
Definitely the databases need to be re-org/vacuumed periodically.

Failing to do so hurts Firefox performance compared to its competitor browsers -- it reduces the adoption of Firefox.

It should be done automatically so that all Firefox users get the benefits.

Doing it on updates is a problem because updates occur with varying frequencies.  In large organizations updates may only occur every couple of years.  Whereas other people might be doing updates daily.

Why not do re-org/vacuum the databases each month, on the first start-up of the month for each profile?
Consolidating bugs...
Status: NEW → RESOLVED
Closed: 12 years ago
Resolution: --- → DUPLICATE
Duplicate of bug: vacuum-bikeshed
The vantage of vacuum along update, is that the user will open Firefox after update. "Oh God! Is much faster! Devs are doing their jobs.

At least about:config options. "vacuum along update" and "vacuum periodically"
You need to log in before you can comment on or make changes to this bug.