SQLite, Firefox and Vacuuming Databases

As I mentioned in my post on persisting data on iPhone, Firefox uses SQLite under the bonnet for a variety of tasks.  I use Firefox on all platforms and from time to time I find that it gets sluggish.  Unfortunately this is largely down to SQLite.  As with any database, even SQLite needs managing over time, and this is something that Firefox doesn’t seem to do well.

To write data into a row, a database will allocate some space and then write into it.  As a database grows, so this hapens over and over.  Usually a table will have grabbed a volume of space when it was created and initially rows are written sequentially into this block.  When it fills, a new block is added and so on.

To retrieve information, your RDBMS will seek through the records stored in those blocks of data, scanning through record by record, unless of course your data is indexed in which case it can jump much more accurately to the correct location.

When you delete a row of data, the information is dropped from your database, but the space allocated to that row remains in place.  When the database scans through, there is an overhead to recognise that space and jump on to the next location.  Also over time, the blocks of data dedicated to each group of rows in your table get dispersed over your disk.  This all makes for higher overhead when you come to read your data.

Just as most disk drives need the occasional ‘defrag’ run on them, so a database does too.  In PostgreSQL and SQLite this is achieved using the ‘vacuum’ command, simply:-

vacuum myTable;

or just:-

vacuum;

…to defragment the entire database.   MySQL also requires defragmenting but this is done using the ‘optimize’ command.  This process will rewrite your data into a new, sequential block, clearing out any space left by redundant data.  After a vacuum you should see a reduction in the storage space allocated to each table.

Returning to the point of this post, Firefox can get quite tardy if it is not periodically vacuum’d.  To manage this, on Windows and Linux download and install the SQLite command line tool, go to the directory where your profile is stored and then issue the following command (for Linux/Mac):-

for i in *.sqlite; do echo "VACUUM;" | sqlite3 $i ; done

or Windows:

for %i in (*.sqlite) do @echo VACUUM; | sqlite3 %i

Another option is to go into Tools -> Error Console and paste in this line:-

Components.classes["@mozilla.org/browser/nav-history-service;1"].getService(Components.interfaces.nsPIPlacesDatabase).DBConnection.executeSimpleSQL("VACUUM");

…and click ‘Evaluate’. The interface will freeze for a few seconds while the vacuum is perfomed, then the console will close to signal completion.

A tip for using Firefox on Mac, make sure that you clean your ‘Downloads’ list regularly, letting this list grow can also bring Firefox to a standstill!  You don’t need to move the files, just clear the list from the Downloads window.

If things had become sluggish then hopefully you’ll notice an improvement!  From a development perspective, if you use SQLite in your own projects, make sure you manage the database by issuing the occasional vacuum on it!  Done frequently, a vacuum takes almost no time at all.  Left for a long time, this process can lock up the database for a noticeable time.

Posted in Development, SQL0 Comments


Advert

For more information about our services…

Contact Us

Wordle of the Day

Image from software at http://wordle.net
Data by Web Trends Now

Categories