Fighting fragmentation: SQLite

Thanks for all of those who commented on previous post on fragmentation. My first fragmentation fix has landed. In current nightlies and future releases the main Firefox databases will grow more aggressively to avoid fragmentation. This should translate into better history/awesomebar/cookie performance for our most dedicated users.

Unfortunately fixing existing profiles is hard from within Firefox. In the meantime advanced users on non-Windows platforms who are suffering from fragmentation can manually copy *.sqlite files to another directory and back.

Windows: Ahead of the pack

Evidence suggests that the Windows fragmentation situation is slightly better than on other platforms. Firefox fragmentation behavior on Windows is similar to other OSes but Windows periodically defragments Firefox files opened on startup. So one ends up with a cycle of deteriorating performance, followed by better performance(ie right after defrag), followed by deteriorating performance, etc.

I haven’t observed Windows defragmenting files for me, but it seems to do this for most users. Would love to learn more on how/when it decides to defragment files.

Horror Stories

I found a few other places that are horridly affected by fragmentation, will be blogging about those as I fix them. Fragmentation is an interesting problem to optimize because it affects dedicated users most, yet it is very tricky to replicate in a developer environment. Furthermore, there are a lot of misconceptions floating around:

  1. Fragmentation is a Windows problem that Linux is immune to due to having awesomer filesystems.
  2. Mac OSX automatically defragments files, so fragmentation isn’t a problem there.
  3. Fragmentation isn’t a problem on SSDs

To which I say:

  1. Linux might be good at avoiding fragmentation for server workloads. It sucks for desktop users.
  2. OSX will defragment small files, but big ones hurt most.
  3. Cheap SSDs suck at tiny reads caused by fragmentation resulting in spectacularly bad IO. More on this in a future post.

To summarize: there are a lot of misleading stories floating around. I am always happy to hear more measurements/docs/bugs/etc on this subject, but I have zero patience for folk stories and speculation.

I should also mention that the fragmentation problem isn’t limited to Firefox. Other browsers suffer from it too.


  1. On Vista & 7, automatic defrag runs are scheduled. By default weekly, Wednesday at 3AM. Check the Task Scheduler, or the defrag UI on 7.

    If it can’t start at that time, it’ll start anytime the machine is idle for 3 minutes. The moment the machine isn’t idle anymore the process is interrupted until next idle.

    It’s pretty easy to spot it if you notice a lot of HDD activity once you leave the machine which stops the moment you come back.

    Additionally Windows 7 won’t defrag SSD drives. Either drives that are reported as such, or drives that read at more than 8 megabytes per second when reading randomly scattered 4KB blocks.

    I don’t know about XP. I don’t think it auto-defragged.

    Also I don’t think Vista/7 will move data outside of these defragmentation runs. It might prefetch the data before Firefox is even launched though.

  2. Stupid question — can we easily detect fragmentation on these platforms, possibly during idle cycles, and display a, “Let us optimize your profile for you!” dialog? Users love the idea of defragging their hard disk making things magically faster, maybe we could play to that defrag == faster-for-free mentality.

  3. Chris: at least on windows and linux, there are system APIs to detect fragmentation.

  4. Thanks for the info in the first comment.

    Chris, problem is that we can not easily detect this. On OSX/Windows/(Linux with XFS) you need admin rights for it. Only problem where this is viable is Linux on non-xfs.