384917 - Excessive CPU and real time use converting between bookmarks.html and sqlite format

Status: RESOLVED WORKSFORME

(Firefox :: Bookmarks & History, defect)

Description

[Robert Bradbury]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.4) Gecko/20070609 Firefox/2.0.0.4
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.4) Gecko/20070609 Firefox/3.0a6pre

It takes nearly 30 minutes to get into and exit from Firefox 3.0a6pre when it is used with an old 13MB bookmarks.html file.  On a 2.8 GHz Pentium IV, the CPU usage is pegged at 70+% for the better part of 20 minutes.  It appears to be ~12+ minutes getting in (the initial conversion of bookmarks.html) to the new database format and at least 8+ minutes exiting.

The initial conversion time from the old bookmarks format to the new database format only seems to be required on the 1st time conversion.  Subsequent Firefox starts are immediate.  There should be some kind of progress meter during the conversion so people will know their system isn't hung or in some infinite loop.  The conversion time is *extremely* excessive as it takes seconds to copy a 13MB file -- I am wondering the conversion process is being done with indexes on the columns in the database.  The proper way to do a conversion is to insert all of the data from bookmarks.html into the database and then add the indexes only *after* all the insertions have been done.  If that isn't the problem then you have a *significant* problem using sqlite for large bookmark/history databases.

The Firefox exit time does *not* go away after the database conversion process.  It always takes 8+ minutes for Firefox to completely exit (the window is closed immediately but Firefox sits around sucking up CPU time for a very long time).  It would appear that this may be due to converting the database back into a bookmarks.html file.  (If it takes 8 CPU minutes to read ~25,000 rows and convert them to HTML you have serious efficiency problems.)

Reproducible: Always

Steps to Reproduce:
1. Find an old (large -- > 10MB) bookmarks.html file, perhaps a big history file as well.
2. Install them in a firefox 2.0 profile directory.
3. Startup firefox 3.0a6pre.
Actual Results:  
Extremely slow startup and shutdown times.
Excessive CPU usage.

Expected Results:  
Many minutes of CPU time should not be required to convert, index and deconvert 10+MB of data from a database.  If Firefox cannot manage it efficiently, then external standalone utilities should be provided which can convert to *and* from the new database formats.  (This is highly desirable in *any* case.)

It would be desirable for a general purpose database interface for bookmarks and history records be provided which could interface to system databases (e.g. mysql) which may be active, reside in memory and be much more efficient than standalone database libraries (e.g. sqlite) are.

Comment 1

[Robert Bradbury]

In poking around in the sources it seems the directory extensions/sql may be the database interface.  If so, then there may already be interfaces for mysql, pgsql and odbc.  Is there any documentation on this, esp. how to compile Firefox enabling say mysql vs. sqlite so that tests may be done to determine whether the problems with excessive CPU consumption are with the database system or with the Firefox interface and use of the database system?

Has the conversion code (to convert bookmarks.html to the database format, and maintain the old bookmarks.html file on exiting) been tested with databases *other* than sqlite?

Comment 2

[(not reading, please use [e-mail] instead)]

confirming.

robert, do you feel confortable sharing your large bookmarks.html file with me over email?

Comment 3

[Mike Connor [:mconnor]]

extensions/sql is something different.  This is likely a fair bit of missing optimization work.  I know that we've fixed some export on shutdown issues since this was reported, are we still seeing the same timings?

Comment 4

[Dietrich Ayala (:dietrich)]

I tried upgrading from a Fx2 profile with a 3.5mb bookmarks.html file, and it took about 15 seconds, and pegged CPU. These numbers are skewed because I'm running a debug build. Some level of the hang is there regardless.



> There should be some kind of progress meter
> during the conversion so people will know their system isn't hung or in some
> infinite loop. 

bug 329736, targeted to be fixed for Fx3

> The conversion time is *extremely* excessive as it takes
> seconds to copy a 13MB file -- I am wondering the conversion process is being
> done with indexes on the columns in the database.  The proper way to do a
> conversion is to insert all of the data from bookmarks.html into the database
> and then add the indexes only *after* all the insertions have been done.  If
> that isn't the problem then you have a *significant* problem using sqlite for
> large bookmark/history databases.

Creating indexes pre- vs post-migration is discussed in bug 389762.

> 
> The Firefox exit time does *not* go away after the database conversion process.
>  It always takes 8+ minutes for Firefox to completely exit (the window is
> closed immediately but Firefox sits around sucking up CPU time for a very long
> time).  It would appear that this may be due to converting the database back
> into a bookmarks.html file.  (If it takes 8 CPU minutes to read ~25,000 rows
> and convert them to HTML you have serious efficiency problems.)

This was fixed in bug 381378. Using a 3.5 mb bookmarks.html file, I do see a short CPU spike, but shutdown of the app only takes a couple of seconds. Please comment if you're still experiencing excessive shutdown times.

There are other changes we should make to improve shutdown time, such as fixing bug 389987.

Comment 5

[Mike Connor [:mconnor]]

clearing blocking in the absence of further comment from the reporter, pleaes renominate if current trunk is still behaving the same.

Comment 6

[Robert Bradbury]

The current CVS of firefox fails to build under Linux.

c++ -o gfxPDFSurface.o -c -fvisibility=hidden -DIMPL_THEBES -DMOZILLA_INTERNAL_API -DOSTYPE=\"Linux2.6\" -DOSARCH=Linux  -I../../../dist/include/cairo -I../../../dist/include/libpixman -I../../../dist/include/string -I../../../dist/include/pref -I../../../dist/include/xpcom -I../../../dist/include/unicharutil -I../../../dist/include/lcms -I../../../dist/include   -I../../../dist/include/thebes -I../../../dist/include/nspr  -DMOZ_PNG_READ -DMOZ_PNG_WRITE   -I../../../dist/sdk/include       -fno-rtti -fno-exceptions -Wall -Wconversion -Wpointer-arith -Wcast-align -Woverloaded-virtual -Wsynth -Wno-ctor-dtor-privacy -Wno-non-virtual-dtor -Wno-long-long -fshort-wchar -pthread -pipe  -DNDEBUG -DTRIMMED -O2 -g2 -I/usr/include/cairo -I/usr/include/freetype2 -I/usr/include/libpng12   -I/usr/include/gtk-2.0 -I/usr/lib/gtk-2.0/include -I/usr/include/atk-1.0 -I/usr/include/cairo -I/usr/include/pango-1.0 -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include -I/usr/include/freetype2 -I/usr/include/libpng12   -I/usr/include/pango-1.0 -I/usr/include/glib-2.0 -I/usr/lib/glib-2.0/include -I/usr/include/cairo -I/usr/include/freetype2 -I/usr/include/libpng12     -DMOZILLA_CLIENT -include ../../../mozilla-config.h -Wp,-MD,.deps/gfxPDFSurface.pp gfxPDFSurface.cpp
gfxPDFSurface.cpp: In member function 'virtual nsresult gfxPDFSurface::EndPage()':
gfxPDFSurface.cpp:94: error: 'cairo_surface_show_page' was not declared in this scope
gmake[5]: *** [gfxPDFSurface.o] Error 1

If you would produce a version of Firefox which actually builds, I would be more than happy to confirm whether or not you have fixed the conversion CPU problem.  I strongly suspect you have not.

Comment 7

[Robert Bradbury]

Mike, claearing a bug which is well documented -- excessive CPU time upon converting large bookmarks files from 2.0 to 3.0. is just crude shunting things aside.  I.e. you are burying well documented bugs.

I put the case down in stone -- one has a bookmarks file > 10 MB.  Not unusual for someone who may have used Firefox for a year or more.  I claimed that it took an excessive amount of CPU time to convert such a state from Firefox 2.0 to Firefox 3.0 due to the conversion to SQLite.

If you can demonstrate to me that Firefox 3.0 can convert the old bookmarks format into its new bookmarks format in a time scale reasonably more than the time it takes to read the data from the disk I will be satisfied that this is not a bug which should be fixed.

It should not be up to me to prove this.  You should be proving it before you release the product.

I.e. the "startup time from a Firefox 2.0 profile to a Firefox 3.0 profile should be less than XX seconds) even with a 10+MB bookmarks file.

If you make that assertion -- then you will have my attention.

Comment 8

[:Gavin Sharp [email: [e-mail]]]

I agree that the blocking flag on this bug shouldn't have been outright removed, given the comment to renominate if it's still an issue.

Robert, the Linux tinderbox machines at http://tinderbox.mozilla.org/showbuilds.cgi?tree=Firefox are building the current trunk fine, so the problem must be with something specific to your setup. You might try asking for help on IRC if you're still not able to get your build to succeed.

Even if you can't get a build to succeed, you should be able to test a nightly build from ftp://ftp.mozilla.org/pub/firefox/nightly/latest-trunk , right?

Comment 9

[Robert Bradbury]

To make more a case in point.  You have to produce a CVS which compiles on an up-to-date Gentoo Linux distribution.  The current Firefox distribution does not satisfy this criteria.  You should select a set of Linux distributions on which the current CVS compiles, then if I run them as VM on my machine I would expect to receive the same results

Give me the data.  It takes X minutes to convert X MB of bookmarks file from 2.0 to 3.0 in Firefox 3.X.  That at least shows me you are aware of the problem and have documented it.  To push it under the rug suggests you have devolved from the Microsoft or similar camps.

Comment 10

[Robert Bradbury]

Mike, claearing a bug which is well documented -- excessive CPU time upon converting large bookmarks files from 2.0 to 3.0. is just crude shunting things aside.  I.e. you are burying well documented bugs.

I put the case down in stone -- one has a bookmarks file > 10 MB.  Not unusual for someone who may have used Firefox for a year or more.  I claimed that it took an excessive amount of CPU time to convert such a state from Firefox 2.0 to Firefox 3.0 due to the conversion to SQLite.

If you can demonstrate to me that Firefox 3.0 can convert the old bookmarks format into its new bookmarks format in a time scale reasonably more than the time it takes to read the data from the disk I will be satisfied that this is not a bug which should be fixed.

It should not be up to me to prove this.  You should be proving it before you release the product.

I.e. the "startup time from a Firefox 2.0 profile to a Firefox 3.0 profile should be less than XX seconds) even with a 10+MB bookmarks file.

If you make that assertion -- then you will have my attention.

Comment 11

[Mike Connor [:mconnor]]

Testing on a MBP, same config as dietrich's:

33 MB bookmarks.html, with 27 000 bookmarks, took just under three minutes to import and start up, finishing with a 4 MB places.sqlite file.  Robert's numbers would suggest an order of magnitude slower (20+ minutes for a 13 MB file on a slower system).

This is probably not as fast as it could be, but creating 150 bookmarks per second doesn't seem at all unreasonable as long as there is a clear indicator of progress, especially given that this is a one-time process.  I don't think we could or should block on some arbitrary per second rate, especially as most of the info I've seen indicates that very few users have a bookmarks file large enough for this to be more than ten seconds (ten seconds would be 500-1500 bookmarks, depending on system, which is on the high end for bookmark count).  If it was more like 15 per second I would be all about making that faster, but as it stands I believe there are bigger fish to fry.

Robert, from the look of things your import was at least an order of magnitude slower than mine (even allowing for my machine being twice as fast as yours, which I don't believe is quite true).  No one else in this bug has reported or reproduced a massive import lag into tens of minutes (no dupes on this bug, no one else has reproduced) on this scale.  As far as I'm concerned, you're claiming a level of performance that is not reproducable by others, and I'm not inclined to worry about any builds other than our officially-supported builds.

If you can reproduce on one of our official nightly builds, and can then provide Seth at least with your bookmarks.html so we can determine why your import is massively slower than all other tests, that would be helpful.  We've tried your STR, we're not seeing anywhere close to what you're seeing, so there's some next steps to further identify possible causes, that we can't do because we can't see the magnitude of the issue you're reporting.  In the meantime, your case appears to be isolated at the current time, so I'm going to clear the blocking nomination until we have more data.

Comment 12

[Robert Bradbury]

If Mike's numbers are accurate for a Prescott then they are significantly improved.  3 minutes is not an unreasonable number though for 4-5 profiles its a bit of a pain.

My bookmarks are running 10-15 MB so I might be a bit faster.

It is also worth noting that I am running Linux at 100%+ paging capacity and that its paging performance sucks.  I'm looking at the memory usage monitor now and I'm using all of it.  Yet in terms of paging (swapping) I'm using relatively little of the system.  I.e. I use all of the memory and none of the swapping capability.  So one is only using half or less of the total system.
Let me be very clear about this fact "swapping under Linux sucks".

Regarding Gavin's comments, I have not yet been able to check the most recent build.  It builds but I have not tested a 2.0 to 3.0 conversion with my specific history/bookmarks yet.

More info at a future date.

Comment 13

[Robert Bradbury]

I have run a test on Firefox 3.0pre compiled from CVS today 29 Mar 08.  It takes approximately 35-40 CPU seconds to convert a bookmarks file containing ~46,700 bookmarks (bookmarks.html is ~13.5 MB) into an ~16.4 MB "places.sqlite" file on a Pentium 4 (prescott).  This is not an unreasonable amount of time so I would consider this bug resolved.  The release notes may want to note that converting a large 2.0 bookmarks file to 3.0 format may take several minutes on older (slower) CPUs.

It also appears that the time it takes to open such a large "Bookmarks" set in a new window (for sorting, editing, etc.) is significantly improved over Firefox 2.0.

Comment 14

[Gervase Markham [:gerv]]

Bug 451915 - move Firefox/Places bugs to Firefox/Bookmarks and History. Remove all bugspam from this move by filtering for the string "places-to-b-and-h".

In Thunderbird 3.0b, you do that as follows:
Tools | Message Filters
Make sure the correct account is selected. Click "New"
Conditions: Body   contains   places-to-b-and-h
Change the action to "Delete Message".
Select "Manually Run" from the dropdown at the top.
Click OK.

Select the filter in the list, make sure "Inbox" is selected at the bottom, and click "Run Now". This should delete all the bugspam. You can then delete the filter.

Gerv