Last Comment Bug 441481 - excessive IO on urlclassifier3.sqlite
: excessive IO on urlclassifier3.sqlite
: perf
Product: Toolkit
Classification: Components
Component: Safe Browsing (show other bugs)
: unspecified
: x86 Windows XP
-- normal with 6 votes (vote)
: Firefox 13
Assigned To: Nobody; OK to take it and work on it
: François Marier [:francois]
Depends on: 673470
  Show dependency treegraph
Reported: 2008-06-24 00:01 PDT by GWu
Modified: 2014-05-27 12:25 PDT (History)
12 users (show)
See Also:
Crash Signature:
QA Whiteboard:
Iteration: ---
Points: ---
Has Regression Range: ---
Has STR: ---


Description User image GWu 2008-06-24 00:01:08 PDT
User-Agent:       Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9) Gecko/2008052906 Firefox/3.0
Build Identifier: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9) Gecko/2008052906 Firefox/3.0

There is heavy IO access on urlclassifier3.sqlite in the profile folder every two or three minutes. Bytes read/written to this file exceed the actual file size by orders of magnitude. This causes heavy lags in Firefox and on OS side on slow clients (i.e. old Laptops with slow disks).

If you disable "Tell me if the site I'm visiting is a suspected attack site / forgery" in Options/Security, urlclassifier3.sqlite is not accessed anymore and the system load is normal.

The problem is consistently reproducable on XP and W2K with FF3.0.

Reproducible: Always

Steps to Reproduce:
1. enable "Tell me if the site I'm visiting is a suspected attack site / forgery" in Options/Security
2. watch system load with an appropiate tool: perfmon.msc, Counter "Physical Disk % idle time" and trace firefox.exe with "Process Monitor" (
3. watch the IO load in perfmon for about 3 minutes, when load IO load goes up, look at the path of the file accessed in process monitor: urlclassifier3.sqlite 
4. disable "Tell me if the site I'm visiting is a suspected attack site / forgery" in Options/Security
5. no more heavy IO load....
Actual Results:  
if "Tell me if the site I'm visiting is a suspected attack site / forgery" in Options/Security is enabled, heavy IO load every few minutes.

Expected Results:  
reasonable IO

There is similar problem reported for linux and ext3 filesystems, but this is also reproducable on W2K and XP on NTFS. Looking at the access patterns in process monitor it does not look like a problem with certain filesystems but like a problem in the implentation of the access to the sqllite database (maybe SQL, commit issue).

It is not reasonable that a 20MB file causes 2GB IO.
Comment 1 User image Jo Hermans 2008-06-24 02:24:04 PDT
When freshly installed, Firefox needs to download the full file (currently 55MB !), and that can take a while. After it's downloaded, it's supposed to stop ; updates are not that frequent (see bug 402469). Most I/O will then come from actual lookups in the database.

If this was an older profile that was used for the betas before, then maybe you need to create a new profile. There have been changes before in the database (different page sizes etc ..), that might need a regeneration of the file.
Comment 2 User image GWu 2008-06-24 03:01:28 PDT
As I said: this happens consistently, not only after a fresh install. I tried with a new profile as well as with an existing one on twi different PCs. 

I also did a complete uninstall of FF2 on one machine and an upgrade on the other, so there should be no dependency on that.

The IO happens every 2 to 3 minutes, not only once. And the load also happens with an "idle" FF (no user activity). Please note, that the load is only "feelable" if you have a slow HD, but for older PCs (i.e. laptops) this makes FF3 unusable.

Have you tried to track this with some monitoring tool as I suggested/did?
Comment 3 User image Jo Hermans 2008-06-24 06:53:00 PDT
If you have a 20MB urlclassifier3.sqlite file, then it's not yet complete.
Comment 4 User image GWu 2008-06-24 23:06:10 PDT
This was just an example to show the order of magnitude ...

FF3 is installed on my machine for 3 days nows and running for about 20hours or more. According to the file size, the file should be complete.

Actual sizes: 
 55.091.200 urlclassifier3.sqlite
  2.023.264 urlclassifier3.sqlite-journal

The journal file grows up to about 20MB and is removed after each "I/O burst", which still happens every 1-3 minutes. 

If it helps, I can upload the logfile of Process Monitor in CSV format.
Comment 5 User image Mark Nienberg 2008-07-01 14:17:37 PDT
I have a 25 user network with firefox profiles stored on a linux samba server.  Four Windows XP workstations were upgraded from firefox 2 to firefox 3.  A couple days later, all had "urlclassifier.sqlite" files in their profiles of approx 50 Mb.  The server slowed to a crawl, with sporadic load averages of about 2-4.  Reviewing sar reports showed abnormal high I/O waits.  Disabling "Tell me if the site I'm visiting..." and then erasing the "urlclassifier3.sqlite" file in each workstation profile returned the server to normal load averages of about 0.30.

Luckily, I only upgraded 4 workstations as a test, not the whole lot.  If I had, the network probably would have been unusable.

It seems this high I/O problem mentioned in bug reports for the beta releases is still present in the final 3.0.  Maybe it is tolerable if the profile is on a local workstation, but with multiple profiles on a network server the disk thrashing is extreme.
Comment 6 User image Lucas Malor (mail: c6kfnkn2uc AT snkmail DOT c0m) 2008-07-20 15:39:38 PDT

*** This bug has been marked as a duplicate of bug 435642 ***
Comment 7 User image Lucas Malor (mail: c6kfnkn2uc AT snkmail DOT c0m) 2008-07-20 16:06:19 PDT
Mmmhh, sorry, bug 435642 is invalid, I reopen this bug
Comment 8 User image Johan 2008-09-10 12:56:45 PDT
Hi everyone,

(which bug is the "good one"? This one or #435642? So, I post here, I hope it is the right place)

For me too, it is "urlclassifier3.sqlite"......

Yesterday (09/09/08) after the update from FF to FF 3.0.1 through the regular update process, the hard drive had been stressed during more than 4 hours, following this sequence: about 1 minute at full load and 30 seconds quiet.

Since that, at each FF startup, the sqlite process starts its activities, following the same kind of sequence. And I don't always need to surf the web for that, I only have about 6 to 8 tabs automatically reloaded (with Tab Mix Plus) at startup.

Today (09/10/08), after about 8 cumulated hours using FF 3.0, "urlclassifier3.sqlite" contains about 766900   records (609210  for the "moz_subs" table and 157700  for the "moz_classifier" table) and the file is about 51MB. I did not make any kind of "heavy web surfing", mainly local surfing (my station is a web development station, with Apache/PHP/MySQL running on it). Sometimes, FF is very slow, sometimes unusable for a few seconds... The only way to quiet it down is either not surfing at all for a few minutes (unfortunatly it doesn't stop sqlite activities!) or not using FF (I'm not sure it could be a good idea ^^)!

My extensions are:
    * Adblock Plus
    * Add N Edit Cookies
    * CuteMenus - Crystal SVG 1.9.3
    * Dictionnaire HunSpell en Français (réforme 1990) 2.0
    * Firebug 1.2.0
    * Java Console 6.0.07
    * MR Tech Toolkit (formerly Local Install) 6.0.1
    * SQL Injection! 1.2
    * SQLite Manager 0.3.8
    * Tab Mix Plus 0.3.7pre.080830

So, how long this "game" with sqlite will last and when will it be corrected? I don't want to downgrade to FF 2.0 and I don't have any problem using neither Opera (except with its render engine in some cases) nor Safari (not even IE7 with WinXP; IE8 is another story)...

Additional infos: my test station runs an old Win2k SP4 and for the moment, regarding this bug, I wait a bit to upgrade FF 2.0 on other stations that run WinXP (SP2 and SP3). FF temp size is set to 50MB.

My FF user agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; fr; rv: Gecko/2008070208 Firefox/3.0.1

(And I'm sorry for my english......)


Comment 9 User image 2008-09-26 04:00:44 PDT
This anti-phishing feature is a real show-stopper in a multi-user environment. Having a couple of hundred users hitting a 50MB database simultaneously isn't nice on the disksystem. I suggest an installation feature so that users using bulk-installation methods can disable this feature at install time.
Comment 10 User image Asa Dotzler [:asa] 2011-11-18 15:22:28 PST
Is urlclassifier3.sqlite going away per this and other discussions about re-evaluating our use of sqlite in some features?
Comment 11 User image Dietrich Ayala (:dietrich) 2011-11-18 15:43:24 PST
Yes, see dependent bug.
Comment 12 User image (dormant account) 2011-12-01 12:23:18 PST
marking this is a p2 since this should happen soon.
Comment 13 User image Gian-Carlo Pascutto [:gcp] 2012-02-05 22:24:18 PST
Bug 673470, replacing SQLite and making all I/O serial.

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