NOTE TO READERS: I wrote this post to draw attention to a common pitfall when using SQLite DBs in extension and Mozilla code. I am not suggesting people stop using the addons mentioned below. The list of top sources of slow SQL is skewed by various factors: more popular add-ons are more likely to be in this list, add-ons that do slow operations against their own DBs will be in the list but add-ons that do slow operations against Firefox DBs will not, etc etc.

The perf team has been using Telemetry data to find and fix performance problems in Firefox code, but the same data can also be used to find performance problems in add-ons.

The Telemetry data on long-running SQL statements, in particular, contains information about slow SQL operations carried out by add-ons. The aggregated reports are publicly accessible from the Metrics dashboard  (BrowserID account needed). If you navigate to the slow SQL section or click on this link directly, you will see stats on SQL statements that took longer than 100ms to execute on the main thread across at least 100 browsing sessions. In general, main thread I/O (including SQL) is a major performance problem in Firefox as the duration of I/O operations is essentially unbounded and any long running operation on the main thread causes browser unresponsiveness. As a privacy precaution, Telemetry never reports statement parameters and only reports the name of the DB if the SQL was executed against an extension DB. Note that the dashboard doesn’t specify whether an SQL string was executed by Firefox or add-on code, but SQL executed against extension DBs has the form “Untracked SQL for <extension DB name>.sqlite”. As can be seen from that dashboard, the majority of main-thread SQL work is still coming from Mozilla code, but addon SQL is well represented.

These are the top 20 addons doing main thread DB work*, found by sorting the slow SQL table on frequency (“Total Doc”):

  1. Various extensions which use the customizable toolbar code. These toolbars sometimes ship with malware, but they also seem to be used in legitimate AMO extensions
  2. The popular ForecastFox weather extension
  3. Fast Dial visual bookmarks
  4. Evernote Web Clipper
  5. Zotero research tool
  6. toolbar
  7. MailCheck + MailCheck
  8. Yoono sidebar
  9. Yandex toolbar
  10. Lazarus form data recovery
  11. DownThemAll! downloader
  12. Xmarks Sync bookmarking
  13. Various extensions using Brand Thunder themes code
  14. Ant Video Downloader
  15. TRUSTe Tracker Protection
  16. a DB which might be associated with PrivacySuite and Targeted Advertising Cookie Opt-Out
  17. GMail Checker
  18. Brief RSS reading
  19. Screenshot Pimp + MediaPimp + Notifications […] for Facebook
  20. Form History Control

* The top offender is actually SQL executed against “cache.sqlite”. This is the name of the DB used by the Lightning calendaring addon for Thunderbird. Its inclusion in the Firefox SQL list might be a bug in the dashboard or it might be an intentionally misleading name for a spyware DB. There were other highly ranked DBs I could not identify: addonstat.sqlite, store-pp.sqlite, livemargins_appcenter.sqlite, database.sqlite.

We will need a joint effort between Mozilla developers and add-on authors to make Firefox snappier for users. Our next step is to reach out to the authors of the above addons and ask them to change how they use SQLite DBs. We’ll also need to improve our documentation on best-practices for extension developers.

UPDATE: A few commenters pointed out that I never mentioned the async SQL APIs in my post. Indeed, developers should be using the asynchronous APIs to execute SQL statements off the main thread: