Last Comment Bug 395020 - (vacuum-bikeshed) VACUUM SQLite databases after major upgrade
(vacuum-bikeshed)
: VACUUM SQLite databases after major upgrade
Status: RESOLVED WONTFIX
:
Product: Firefox
Classification: Client Software
Component: General (show other bugs)
: Trunk
: All All
: -- normal with 19 votes (vote)
: Future
Assigned To: Nobody; OK to take it and work on it
:
Mentors:
https://wiki.mozilla.org/Firefox/Spri...
: 393965 394379 501363 (view as bug list)
Depends on: 385834 390244 541373
Blocks:
  Show dependency treegraph
 
Reported: 2007-09-05 08:30 PDT by (not reading, please use seth@sspitzer.org instead)
Modified: 2010-12-24 06:03 PST (History)
53 users (show)
justdave: wanted‑firefox3.6?
See Also:
Crash Signature:
(edit)
QA Whiteboard:
Iteration: ---
Points: ---
Has Regression Range: ---
Has STR: ---
-


Attachments

Description (not reading, please use seth@sspitzer.org instead) 2007-09-05 08:30:03 PDT
VACUUM after idle (even if we're doing incremental vacuuming) to defragment the database

from http://www.sqlite.org/lang_vacuum.html:

As of SQLite version 3.1, an alternative to using the VACUUM command is
auto-vacuum mode, enabled using the auto_vacuum pragma. When auto-vacuum is
enabled for a database, large deletes cause the size of the database file to
shrink. However, auto-vacuum also causes excess fragmentation of the database
file. And auto-vacuum does not compact partially filled pages of the database
as VACUUM does.

for profiles created before the fix for bug #385834 (pre-m8), we already do a full vacuum on idle (see bug #390244).

but for new profiles (or new migrations from fx 2) created after the fix for bug #385834, we do an incremental vacuum on idle.
Comment 1 (not reading, please use seth@sspitzer.org instead) 2007-09-05 08:35:01 PDT
I think the fix for this would be to nsNavHistory::PerformVacuumIfIdle()

we currently do:

     if (vacuum == 2) {
       rv = mDBConn->ExecuteSimpleSQL(
         NS_LITERAL_CSTRING("PRAGMA incremental_vacuum;"));
       NS_ENSURE_SUCCESS(rv, rv);
     }
     else {
       // if our database was created before incremental vacuuming
       // do a full vacuum on idle
       rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("VACUUM;"));
       NS_ENSURE_SUCCESS(rv, rv);
     }

I think we'd just want to fix that code so that upon 3x (or something?) of or idle, we'd do a full vacuum, no matter current "vacuum" was.
Comment 2 (not reading, please use seth@sspitzer.org instead) 2007-09-05 08:36:56 PDT
*** Bug 393965 has been marked as a duplicate of this bug. ***
Comment 3 (not reading, please use seth@sspitzer.org instead) 2007-09-05 16:30:46 PDT
just a heads up:  as bug #390244 points out, with a large places.sqlite file, a full vacuum may take a while.

a 122 MB places.sqlite from robcee (From the unit test tinderboxen) is taking me minutes to VACUUM (from within the SQLite Database Browser).
Comment 4 (not reading, please use seth@sspitzer.org instead) 2007-09-06 17:15:43 PDT
another heads up, steve, jim and jo reported problems when we'd vacuum.  see bug #390244 for more details.
Comment 5 (not reading, please use seth@sspitzer.org instead) 2007-10-02 11:35:22 PDT
This is going to be a tricky one, given that we know that VACUUMing a large database can take a long time.
Comment 6 Shawn Wilsher :sdwilsh 2007-10-02 15:08:47 PDT
We can always run it on a different thread, and use progress listeners to abort it if we want to shut down (yes, this will require some threaded code, which sucks, but what can you do :/)
Comment 7 Marco Bonardo [::mak] 2007-11-02 02:37:07 PDT
since sqlite uses freelists to speed up inserts, vacuum is not so useful if the db does not grow up insanely, and with the new expires (embedded after 10 days), it should not do it. 
So probably vacuum should not be done more than once a month, or it will cause a slowdown on inserts.

doing on idle will freeze the browser since it has to create a new db, attach it to the db connection and move everything, so maybe once a month, after an idle of 10 minutes a countdown (10seconds) could go out "Firefox is about to compact the history db. This process can take several minutes, click cancel to stop.", after countdown vacuum starts and a progress bar show where it is, so the user is warned before starting up.
Comment 8 Jesse Ruderman 2007-11-06 17:44:22 PST
See also bug 394379, "vacuum all sqlite files after an upgrade ?".  Should this bug be marked as a dup of that bug?
Comment 9 (not reading, please use seth@sspitzer.org instead) 2007-11-13 11:15:53 PST
I don't think this should block beta 2 or be a p1, based on comments from Marco and Dr. Hipp (see bug #390244 comment #32)

I'm going to proactively move it to be a p3 / m11 (like bug #394379)

dietrich, can you elaborate on why you may have made it a P1?

Comment 10 (not reading, please use seth@sspitzer.org instead) 2007-11-13 15:52:51 PST
clearing blocking status, I don't think this is a blocker at all.

see bug #403702 (the initial comment) for some reasoning.
Comment 11 (not reading, please use seth@sspitzer.org instead) 2007-12-14 10:26:10 PST
this should not be on the m11 radar, as we have no plans to implement it.

There might be a reason to do a vacuum on software update, but would probably have to do the vacuum the first time we run firefox after update, as otherwise how would we vacuum places.sqlite for other profiles / users?

See also #402076

I'm not going to wontfix this bug, so that we can keep it open for discussion, but moving to future as I don't think we should do anything for firefox 3.
Comment 12 Jo Hermans 2009-06-30 08:53:56 PDT
*** Bug 501363 has been marked as a duplicate of this bug. ***
Comment 13 Dave Miller [:justdave] (justdave@bugzilla.org) 2009-07-01 12:25:57 PDT
Failure to do this causes really poor user experience for people who browse a lot.  The Awesomebar has been regularly hanging Firefox for me for 3 to 5 seconds after I start typing in it, and continues to hang periodically as I continue to type, with a beach ball cursor for the last few months, which has been really annoying.  The length of time it hangs has been getting progressively worse over the last few months.  I saw someone post a facebook status today suggesting to open places.sqlite in sqlite and vacuum it, and after running that, it's nice and speedy again and finds things as fast as I can type them without hanging the browser at all.  For that kind of user interface performance impact, Firefox really should be doing that automatically once in a while.

Macro's countdown timer during an idle period described in comment 7 sounds like a good way to do it to me.
Comment 14 Dave Miller [:justdave] (justdave@bugzilla.org) 2009-07-01 12:31:47 PDT
See also bug 403702, which suggests to add a manual command to trigger this to the menu.  You could couple that with a notification bar to show up a month or two after the last time it was run asking if the user wants to compact the database in order to improve performance, and warning that it will take a few minutes to run.
Comment 15 Michael Morgan [:morgamic] 2009-07-01 18:09:14 PDT
I think it'd be appropriate to do this during a major update cycle, since after 6 months to a year of accumulating data you're bound to have some fragmentation in your db.  Could we sneak this into 3.5.1? :)
Comment 16 Keith Tarrant 2009-07-12 01:48:31 PDT
Definitely the databases need to be re-org/vacuumed periodically.

Failing to do so slows Firefox performance compared to its competitor browsers -- it reduces the adoption of Firefox and helps our competitors.

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

Why not do re-org/vacuum the databases each month, on the first start-up or shut-down of the month for each profile?  An informational "database compacting" message box should be displayed.

Or the re-org/vacuum could be scheduled to run on the first operating system start-up of the month.

There could be an option in about:config for "experts" who want to turn the re-orgs off.

Regular users are more concerned with the average delays in going from page-to-page, and the average start-up time, than they are with the peak start-up time.  

Provided there is not some mysterious 30+ second delay occurring with no messages, a delay once a month is going to be preferable to a delay on every start-up and every enter-key depression.

An ideal database re-org utility allows the DBA to specify how much space and how much contiguous space to reserve for database growth, and to have that allocated during re-org.  This prevents needless fragmentation.

Maybe the ability to reserve contiguous space for database growth is something Firefox developers should ask makers of SQLITE to provide.
Comment 17 Shawn Wilsher :sdwilsh 2009-07-12 07:01:27 PDT
*** Bug 394379 has been marked as a duplicate of this bug. ***
Comment 18 antistress 2009-07-16 16:48:41 PDT
Just to be clear & for the record :
doing that maintenance operation at the Firefox level implies that all other gecko based application (Liferea...) have to implement their own workaround
Comment 19 Jo Hermans 2009-07-27 12:58:02 PDT
(In reply to comment #15)
> I think it'd be appropriate to do this during a major update cycle, since after
> 6 months to a year of accumulating data you're bound to have some fragmentation
> in your db.  Could we sneak this into 3.5.1? :)

Not everyone is upgrading every 6 weeks :

- some users never upgrade
- some always uninstall, then install the new version
- some always download a new version, then install on top of the older version

So I think we still need a vacuum that is triggered by a timer.
Comment 20 Shawn Wilsher :sdwilsh 2009-07-27 13:30:43 PDT
(In reply to comment #19)
> So I think we still need a vacuum that is triggered by a timer.
And risk locking up their browser completely?  No way (there isn't a way around this, and canceling the operation can take a long time too).
Comment 21 Jo Hermans 2009-07-27 13:50:26 PDT
Not in the middle of a browsing session ofcourse. No, a proposal to run the vacuum when you start up on the first day of the month or similar. Or when you shut down. And you should be able to skip it ofcourse.
Comment 22 Marco Bonardo [::mak] 2009-07-27 14:15:49 PDT
actually, i can recall vacuuming places.sqlite in Firefox was taking minutes, while in other apps was taking seconds... i'm actually thinking could be that temporary moving the journal to memory while we vacuum could really speed it up for large dbs. We did use that trick in places to generate some big index. Experimenting with some temporary pragma could make vacuuming really fast, especially on modern hardware, we could set an upper limit to db size based on available memory on the device, and use memory where possible).

also we need some way to tell if a db needs to be vacuumed, skipping some useless work, unfortunatly sqlite does not provide this kind of info, but we could maybe get a guess using number of pages and number of empty (reusable) pages. There is an sqlite analyzer around that is able to calculate a % of defragmentation in the db, not sure how it does, though.

Comment 18 is correct, ideally vacuuming should be something done by storage on a regular basis, maybe on a separate thread, that would serve any storage user... but on the other side would be even harder to do, it would require to hot-backup the db, start caching all statements/params while they are executed on the original, vacuum the backed-up db, execute all cached statements on it, and then replace the original db with the vacuumed one.
Sounds like a crazy task that would be much better served by implementing real incremental vacuuming in sqlite itself, would really be just a complex storage workaround for something sqlite is missing but plan to implement in future.
Comment 23 Jesse Ruderman 2009-07-28 00:34:19 PDT
> > So I think we still need a vacuum that is triggered by a timer.
> And risk locking up their browser completely?  No way (there isn't a way around
> this, and canceling the operation can take a long time too).

I find your implication that it is okay to lock up Firefox during an update disturbing.  Security and UE want updates to be instant (bug 307181), not arbitrarily slower.  This bug, as summarized, should be WONTFIXed.
Comment 24 Unknown W. Brackets 2009-07-28 00:54:53 PDT
Well, shutdown is already awfully slow.  Obviously it should be faster, but this sort of thing could be put on shutdown (much like is done with, e.g. Outlook Express and similar.)

Another way would be to do it when clearing private data/"recent history".  Sure, it may not be frequent, but at least it would happen sometimes.  That said, maybe it's already done then or maybe it's also desirable for that to be as fast as possible.

It would be sorta silly to have a Tools -> Options -> Advanced -> Optimize Data, too...

-[Unknown]
Comment 25 Marco Bonardo [::mak] 2009-07-28 03:06:16 PDT
(In reply to comment #23)
> I find your implication that it is okay to lock up Firefox during an update
> disturbing.  Security and UE want updates to be instant (bug 307181), not
> arbitrarily slower.

If i read this bug correctly it is about MAJOR updates, that usually are not pushed for security reasons, when a major version is released old versions are still receiving instant security updates.
Comment 26 Marco Bonardo [::mak] 2009-07-28 03:22:33 PDT
there is still the possibility to do this on idle.
Monthly (or more), on idle (2 or 3 minutes so we won't hit standby) we could get the list of sqlite files in the profile dir, perform an analysis of the db to get if it needs to be vacuumed, saying the user "Firefox is going to compact profile's databases." with a 10s countdown and a cancel button, and then perform vacuum. This won't be transparent to the user as doing the same on a major update.
The basic point is that there is no need to do this each month, every 6 months can be more then enough, and maybe once we are able to detect status of a db they won't even need to be vacuumed at that point.
The process should in no way block the interaction of the user with the browser, so probably even doing this in background is bad if it's going to slowdown navigation. I can't see other solutions out of on-update or on-idle
Comment 27 Keith Tarrant 2009-07-28 06:24:04 PDT
To mark this bug as wontbefixed would be to waste the coding optimization efforts of Firefox programmers.  It would be doing a big favor to Microsoft and Opera.


What ordinary users care most about the the delay every time they press the enter key, and every time they start Firefox, therefore a suitable time has to be found to clean up the databases every few months or so.  

Doing it every time private data is cleared may be far too often, because some people clear private data on a daily basis.

Until sqlite (or a replacement database) can provide information on when the DBs need re-orging, perhaps a timer to ask the user (via pop-up) every 2 months whether they want to optimize the databases would be the answer.
Comment 28 Marco Bonardo [::mak] 2009-07-28 06:32:57 PDT
(In reply to comment #27)
> people clear private data on a daily basis.

or some user do never clear private data, like me.
Comment 29 Dave Miller [:justdave] (justdave@bugzilla.org) 2009-07-28 17:52:58 PDT
Every 2 months might not be frequent enough.  It was July 1st when I posted in here (comment 13) about my discovery of this workaround, and I'm already noticing my awesomebar getting slow again and it's only been a few weeks.
Comment 30 Marco Bonardo [::mak] 2009-07-29 01:12:47 PDT
(In reply to comment #29)
> Every 2 months might not be frequent enough.  It was July 1st when I posted in
> here (comment 13) about my discovery of this workaround, and I'm already
> noticing my awesomebar getting slow again and it's only been a few weeks.

do you have a lot of live bookmarks? or do you clear history often?
The defragmentation can be so bad only if there are a lot of daily changes to the database, in such a case also fixing the underlying problem could help. A db like places should not change at high rates. If it does would be interesting to find and mitigate causes.
Comment 31 Dave Miller [:justdave] (justdave@bugzilla.org) 2009-07-29 11:09:55 PDT
I do not have any live bookmarks at all, actually.  I also hardly ever clear my history.  I do a lot of web surfing though, my history has a lot in it.  I'm sure there's a sizable amount of stuff getting dropped every day due to the 90 day expiry.
Comment 32 Marco Bonardo [::mak] 2009-08-26 17:58:54 PDT
Dave, could you please execute these pragma on your db and give me the data they return?

PRAGMA freelist_count
PRAGMA page_count
also please give me the filesize
Comment 33 Marco Bonardo [::mak] 2009-08-26 18:09:45 PDT
i filed Bug 512854 to vacuum on idle, this could be wontfixed if that will work fine.
Comment 34 Dave Miller [:justdave] (justdave@bugzilla.org) 2009-08-26 20:45:13 PDT
(In reply to comment #32)
> Dave, could you please execute these pragma on your db and give me the data
> they return?
> 
> PRAGMA freelist_count
> PRAGMA page_count
> also please give me the filesize

where do I go to do the pragma thing?
Comment 35 Marco Bonardo [::mak] 2009-08-27 06:11:25 PDT
those are sql commands like VACUUM, so you can use the same way you did to run VACUUM on your db. either open places.sqlite with sqlite manager in a different profile, or use an external sqlite browser.
Comment 36 Michael Morgan [:morgamic] 2009-08-27 06:24:56 PDT
FWIW, there are a few extensions now that can help w/ this that were made after oremj made his blog post:
https://addons.mozilla.org/en-US/firefox/search?q=vacuum+places

Blog:
http://blog.oremj.com/2009/08/20/speedup-firefox-with-vacuum/

Go add-ons!
Comment 37 Keith Curtis 2009-09-27 07:30:40 PDT
This bug has been kicking around for a long period of time. If you don't have any great ideas, then I recommend doing what the Linux file system does: on every 25th boot, run this algorithm. I would implement this immediately, and then you can consider fancier stuff (in addition) later.

But don't let more years pass! I did a vacuum of my Firefox 3.0 profile and both startup and typing things into the address bar got dramatically faster. I went from being **** off with Firefox to being in love again.
Comment 38 Jo Hermans 2009-09-27 07:48:57 PDT
Keith, see comment 33 : bug 512854 is already checked in, and is now being tested. A vacuum will now happen every month (or forced every 2 months), even if you never upgrade.
Comment 39 Marco Bonardo [::mak] 2010-02-01 05:32:24 PST
imho, this bug has to be wontfixed in favor of a monthly vacuum in bug 541373.
There is no advantage in vacuuming on upgrade against once a month (the only advantage would be not having to wait to see a faster experience, that means the advantage is limited to less than 30 days, 15 days average), and there is a clear disadvantage in making the upgrade painfully slower than it needs to be.
Comment 40 Dietrich Ayala (:dietrich) 2010-02-02 10:07:07 PST
I agree that we should minimize the pain of upgrades as much as possible.

Maybe a compromise could be to have the timer look for whether an upgrade has occured recently, and do a vacuum earlier in that case?
Comment 41 Marco Bonardo [::mak] 2010-02-02 15:19:45 PST
(In reply to comment #40)
> Maybe a compromise could be to have the timer look for whether an upgrade has
> occured recently, and do a vacuum earlier in that case?

that would be possible and probably an interesting compromise.
Comment 42 Greg Lindahl 2010-05-05 12:05:52 PDT
Isn't this a privacy issue? If I clear my history, doesn't that leave some of the deleted data in the .sqlite file? And vacuuming reduces the amount of deleted data left over?
Comment 43 Jo Hermans 2010-05-05 12:49:39 PDT
(In reply to comment #42)
> Isn't this a privacy issue? If I clear my history, doesn't that leave some of
> the deleted data in the .sqlite file? And vacuuming reduces the amount of
> deleted data left over?

No, deleted data is always erased with zeroes.
Comment 44 Marco Bonardo [::mak] 2010-05-05 15:41:40 PDT
Vacuum only shrinks space that is already empty.
Comment 45 Ray Cathcart 2010-08-18 12:27:57 PDT
Instead of making it periodic, you could make it need-based. For instance, you could monitor the time that using the awesome bar locks up the GUI, and if it becomes excessive perform the vacuum upon the next shutdown, or prompt the user in some way - perhaps the information bar that is used for things like remembering passwords and blocking pop-ups?
Comment 46 Jo Hermans 2010-08-18 14:38:08 PDT
And how do you determine what is excessive. Whatever value you choose, it will always be too large or too small for someone .
Comment 47 Ray Cathcart 2010-08-19 06:52:52 PDT
Re: Jo Hermans. Measure it. Average and standard deviation the first 100 or so uses of the awesome bar. If at any point after this the time exceeds, say, 3 standard deviations - either vacuum or prompt for vacuum. Then, re-baseline.

It won't capture all cases, but I think that would improve usability overall.

For the severely picky, something could be put into about:config. A setting to turn on or off the behavior should do it. The nag box could also have a checkbox that says "Do not bother me again" or similar.
Comment 48 Ivan Ičin 2010-08-19 08:08:01 PDT
(In reply to comment #47)
> Re: Jo Hermans. Measure it. Average and standard deviation the first 100 or so
> uses of the awesome bar. If at any point after this the time exceeds, say, 3
> standard deviations - either vacuum or prompt for vacuum. Then, re-baseline.
There are some problems with this method:
- if there was some data in places, then it mighty be actually too slow and you are using that for benchmark
- if there was no data (fresh profile) the results are probably too good, as the amount of data is too small...

I guess it would make sense to run cleanup after some number of transactions (if possible deletes)
Comment 49 Ray Cathcart 2010-08-19 19:26:02 PDT
(In reply to comment #48)
>- if there was some data in places, then it mighty be actually too slow and you
are using that for benchmark

This is a good point, but I would start with a fresh install (or at least a fresh vacuum), which would - as you point out - likely err on the side of "too fast".

> - if there was no data (fresh profile) the results are probably too good, as
> the amount of data is too small...

This would self-correct if the average and standard deviation were zeroed after each vacuum operation.
Comment 50 (dormant account) 2010-10-01 15:24:12 PDT
I think we should prioritize vacuuming in one very specific case. A lot of our older profiles are still using 4K pages. We have since bumped to 8K and then to 32K. Yet many users are still stuck on 4K pages. So i think we should force a vacuum for any db with an obsolete page setting. Obsolete page size imples a) that it's an old profile and b) there is a perf win to be had.
Comment 51 Daniel Cater 2010-12-23 14:35:33 PST
As per the current summary, this would only happen on major upgrades, so if it lands in time for Fx 4 and doesn't land on the 3.6 branch, this would only take effect at a 4.x to 5.x upgrade right? Therefore if this misses 4.0 (and also doesn't subsequently ship in a 4.x minor release) it won't run until 5.x -> 6.x? I think that's a good enough reason to at least look at the blocking status.
Comment 52 Daniel Cater 2010-12-23 14:48:57 PST
Hmm, I definitely should have put more thought into that as it doesn't make sense (the new version will be running the code post upgrade, not the old version).

My point was supposed to be that a user only does a major upgrade once per cycle, so if this isn't fixed in the build they're upgrading to then it will be a long time until they next get a major upgrade. If the performance hit is severe for users on 4K pages (as opposed to 32K per Taras's comment 50) then this is not good. Of course major upgrades aren't usually offered in the .0 of a branch so that leaves more time, but only if this is something that would be accepted in a minor release.

Sorry for the double post.
Comment 53 Shawn Wilsher :sdwilsh 2010-12-24 06:03:23 PST
It's not even clear how we want to do this yet, and consumers can opt into a regularly scheduled vacuum with code added in bug 541373.  In fact, I'm going to say we don't want to do this, and bug 541373 is the way to go moving forward.

Note You need to log in before you can comment on or make changes to this bug.