urlclassifier3.sqlite woes on Firefox 3

Ever since I’ve started using Firefox 3, I’ve been noticing that the ‘profile folder’ (where FF stores all the user’s personal info, extensions info etc) was steadily growing in size. Right from my initial usage of Firefox, I’ve not been using the default folder path for this profile but using the firefox -p startup option, create a Firefox_Profile folder at a different location so that the personalization could be carried over from version to version. 

With Firefox 3, this profile folder started to bloat in size very often. Whilst with FF2 it was almost always nearabout 50 megs, with FF3 it grows to ~100 MB after a few hours of browsing. I looked into the individual files and bam! the culprit stood out to be a urlclassifier3.sqlite file which was topping out at 68 MB😮 . Alarmed at this, I Googled around and found this Mozilla Knowledge Base article on the same.

The urlclassifier2.sqlite file contains information on known phishing sites that is used to warn you should you visit one. The information is downloaded by default from a Google database once a day. This functionality can be modified or turned off in “Tools → Options → Security

Firefox 3 contacts Google at each startup to get an updated list of known phishing websites and updates the same in the urlclassifier3.sqlite file. Agreed this is what makes our browser safe and secure but why is it bloating up in size? Are the phishing sites on the web increasing as fast as the size of this folder? Naah! Apparently, there’s a known bug in FF specific to this urlclassifier file which says that it has a lot of vacuum/blank space within and there ought to be a mechanism to automatically compress it effectively.Whatever might be the cause, its rapid size increase is somewhat disconcerting. Added to it is the fact that upon each startup, FF contacts the Google servers to get an updated list, which might be a drain on the CPU resources and in turn the notebook batteries (if you’re using one i.e.). There have been discussions about it over at the Mozillazine Forums in this thread and this thread and there seem to be a lot of people disconcerted by it.

The Solution? Well, the knowledge bas article suggested turning off the Phishing options in FF, which would mean unchecking of the following options under Tools>Options>Security:

FF3_Security

Though that would mean compromising on the security aspect. But its alright of one isn’t too prone to making transactions over the web and visiting out-of-the-way websites. After unchecking the options, delete the urlclassifier3.sqlite file from the profile folder and it will never bother you again. A 0KB file may be created at the next firefox start but it won’t bloat in size as previously. I hope the bloating problem is fixed soon and we can have a very safe and secure browsing experience…minus the size increases etc.🙂

[Disclaimer: Delete the urlclassifier3.sqlite file from your firefox profile at your own risk. Please do not hold me responsible for anything untoward that might happen to your browser when you do that!]

45 thoughts on “urlclassifier3.sqlite woes on Firefox 3

  1. I’ve looked at the contents of the file, 75% of the file is filled with zeroes. So, obviously, much of the problem derives from the fact the the Mozilla guys chose a really really crappy database system to store the info.

  2. It’s not a “crappy database system” (it’s SQLite), it’s that they half-assed the implementation. They don’t bother doing housekeeping on the file, nor do they compact it.

    • 1) Wouldn’t this “phishing site” database (urlclassifier.sqllite) best be kept globally, in the firefox install directory?

      If SQLlite is used for multiple purposes

      2) @x, who is ‘they’ The Firefox developers? Can I compact the database myself with an SQLlite utility?

      Is there a bug report submitted for them to fix?

      Why would a database file need to be periodically compacted, when the software developers should be doing that automatically?

      I have a lot (thousands) of bookmarks, and one of my places.sqllite is hopelessly large, hosing the browser. If FF doesn’t compact the database, what’s the recommended method for cleanup?

      • Looking at the description it has to be modified by the end user. On systems that enforce security of the application directory it would not be possible to place it there.

        On a Linux system it could be possible to place it in a central location (traditionally somewhere in /var) but then something like a scheduled task will have to go off to maintain it. On a properly managed multi-user Linux or UNIX system this would make sense.

        It seems good for privacy that the checking is done locally, rather than asking a central server every time you visit a site. The latter would not require everyone to keep a copy of the database, but would introduce other problems.

        You can ask sqllite to vacuum the file. It reduced it from 55M to 44M so not much.

    • The problem I have with it is not the size, but how many thousands of read/writes it does to the file, causing quite a bit of thrashing.

  3. Pingback: Firefox Directory in RAM « Necoro’s Blog

  4. I was getting emails from the IT department at my work saying I was using too much disk space. I couldn’t understand how so went looking for large files. I realised it was the above file and shocked to learn it had grown to 922 GB (Yes GB not MB)! I’ve deleted it so my employers should be happier now!

    • 😮 922 GB?? Thats a jaw-dropping size! And if that file itself was 922 GB, how much disk space u had?

      • 922 GB is not that much. My office computer’s urlclassifier3 file grew to 219 TB (yes that’s terabyte, not MB, not GB… TERABYTE).

        I needed over 400 external hard drives just to store that file! My boss wasn’t very happy about it.

  5. The Firefox team seems to have fixed the lack of compaction. The file is now just 44 MB. However, the occasional total download still takes a long time because they probably didn’t optimize it for efficiency (few large-scale programmers have learned about techniques to improve efficiency–it’s not like the 1960s and 70s, when efficiency HAD TO BE a prime concern because of slow system speeds and small memory sizes).

  6. I would like to make a few additional points:
    FYI: I am not a developer; however some research shows:

    1) You can shrink the SQLITE files using SQLITE3 (external install). My urlclassifier3.sqlite went from 60 megs down to 20 megs. Using the syntax:
    sqlite3 urlclassifier3.sqlite VACUUM
    For this to work Firefox cannot be running; because the db file is locked.
    2) Using the “auto_vacuum pragma” might be work; but:

    “As of version 3.4.0 (the first version that supports incremental_vacuum) this feature is still experimental. Possible future changes include enhancing incremental vacuum to do defragmentation and node repacking just as the full-blown VACUUM command does. And incremental vacuum may be promoted from a pragma to a separate SQL command, or perhaps some variation on the VACUUM command. Programmers are cautioned to not become enamored with the current syntax or functionality as it is likely to change.”

    http://www.sqlite.org/pragma.html#pragma_incremental_vacuum

    3) This isn’t only a windows specific issue. It happens on Linux as well. A temporary workaround can be used by creating a batch file or shell script that:

    a) Detects if the Firefox process is running.
    b) Check for the number of profiles
    c) If not running then execute: sqlite3 urlclassifier3.sqlite VACUUM
    d) Run this in the Windows scheduler or as a cron job or manually from the command line.

    PS: Running sqlite3 xxx VACUUM on all files takes something like 10-16 seconds on a PIII 1 GigaHertz.

  7. Kinda late to the party, but.. If you already use CCleaner, there is an option to “compact databases” for Firefox and Chrome that does the same thing as the vacuum thing people have already mentioned(http://www.piriform.com/blog/2009/11/26/ccleaner-v226), but its a bit easier, especially if you already run CCleaner regularly. I just tested it for Firefox, and it dropped my urlclassifier file from 43mb to 23mb.

    • Correct Tim.

      I’ve been an old user oc CCleaner and with their newest versions, they include the ‘compact databases’ thingy that does a good job of keeping all the .sqlite folders prim n’ proper😉

  8. Does anyone here speak “mac”. I found the massive number of “etilqs_xxxxxxxxxxx files on my hard drive. Not in any file. I was experiencing slow downs and freezes (unusual for my mac) in mail, word processing, etc. I couldn’t even “force quit” some unresponsive apps, and could not restart. Had to shut down with the power switch. Simply moving the files to trash, empty, seemed to help. I rarely use firefox. Most always safari with google. Where are these files coming from, and how can I stop them. Remember, I’m on a mac. I don’t seem to have a “urlclassifier3 file ” or a profile file. My security tabs on the OSX and on Safari and FF don’t seem to match up with what you are suggesting either. Appreciate any help you can offer.

  9. I was wondering if you can change this by editing the prefs.js (unchecking those 2 options.) Does someone know if that’s possible?
    Cheers.

  10. I would advise running Piriform’s CCleaner (free!) every once in awhile. Cleans it right up. Also a lot of other Windows’ discarded work files that are no longer used and may be gunking up your hard drive.

    Here’s their link… http://www.piriform.com/ccleaner

    Google “review ccleaner” and make your own conclusions.

    Happy user, I am I am…!

    • Yep, I too have been using it since long and found it to be really useful. The latest versions have a ‘compact databases’ feature which I presume compacts the Firefox databases as well.

  11. Interesting and useful blog post. thanks.

  12. I’m using DropBox to “carry” my files around.
    And I notice just how big and troublesome this particular file at the sync process.
    Thank you for the article.
    You saved my day.

  13. i start FFx and after a while of being online the HDD goes nuts, i use process explorer and i froze the program (using PE) at 57Mb of reading and 81MB of writing, this file is the largest open file (checked the handles) but it is only 39.9mb.. unfreezing ffx only starts hdd activity. it will eventually stop but this is an EEE and writes should be limited.. unfortunately my diag programs dont tell me what file is getting the most writes…. it might also be related to the flash player.

  14. How about the fact that I don’t trust Google to keep my browsing history confidential. Should be renamed Snoople in my view.

    Therefore, I don’t want this feature to run at all, never mind the size of it. When I uncheck those two security options, they come back up checked the next time I open Firefox 4.0

    Even using about:config options doesn’t seem to have any effect on stopping this nosy critter.

    I delete it everytime I close Firefox, so it stay below 50 MB, but I am tired of doing that and I don’t want the checking done anyway with Snoople.

    Any ideas?

    Al

  15. Al Sequitor: Could be your admin policy won’t let you change it. Or if you are on a network, group policy won’t let you change it.

  16. Just in general, “Why is this even on my computer and not on the web somewhere?” Well another reason is it would take a centralized server somewhere, like google or at mozilla, and everyone’s copy of firefox would have to check with that server every time any of us clicks on a url. Imagine millions of db lookups every second. And who would be allowed to update the list? It would be a huge hacker/spamer target.

    perhaps a flag variable could be added to the DNS servers world wide that indicates a potentially bad site. Okay, again who decides and who is allowed to change that. Or why not just bar such sites from DNS. That wouldn’t stop them completely but it would slow them down quite a bit if your computer couldn’t find it.

    Please, what version of Firefox are you talking about? the behavior seems to be changing over time. My current version of FF is 8.0. I turned off the option, deleted the file but it reappears at 15 meg. Seems to me that file is being used for something else too.

    What does Chrome or Internet Explorer do?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s