Optimization requires taking a step back to observe, measure, and experiment. Somehow it seems like a luxury, and features or bug fixes are always the priority. This release though, thanks to the survey and GetSatisfaction feedback you’ve been sending, speed was a primary focus.

dbradley, pvh, jacklu, kreeger and myself had a lot of fun working on performance problems that had been annoying us. Our goal was to improve basic every-day library management, and the results are a huge step forward.

Scrolling is much much smoother, searching is thousands of times faster, databases are 60% smaller, and importing is nearly twice as fast and many times more efficient. Songbird is much more pleasant to use, and can now easily handle 20,000+ track libraries. In fact, it seems to scale about as well as other popular players.

OBLIGATORY INFOPORN

This is (mostly) the output from a new performance lab that marcc and toks set up. Every night we get performance reports for the library API, allowing us to verify progress and avoid regressions. Don’t worry about the times, these are stress tests, not actual user use-cases. The lower the time, the better the performance.

Read on if you’re interested in how these improvements were made.

SCROLLING

The main library view is a XUL Tree widget, just like the bookmarks in Firefox and mail view in Thunderbird, so we were concerned that Songbird used nearly twice as much CPU and scrolled much more slowly than these applications. Simply moving the mouse in a circle over the tree would consume up to 80% CPU.

Profiling with Shark showed a lot of wasted effort reading item metadata and firing events while painting the tree. A little work to adjust how the selection is maintained and refactor the caching code significantly improved library scrolling.

Next was a paging hiccup every 1000 items when scrolling large libraries. Logging and timing queries showed that secondary sorting was to blame (first sort by artist, then by album, then by disc number and track number). Getting sorted media item IDs for 1000 rows could require hundreds of queries, each with a cost proportional to the library size.

Since secondary sorting is not user configurable we now pre-compute another sort value for each property. For example, when album metadata for a track changes, the secondary sort value for the artist property is rebuilt and indexed, making sorting by artist, album, disc, track number essentially free.

SEARCH + FILTERING

In 0.7 we moved to SQLite Full Text Search (FTS3), and found that in most cases it was extremely fast. Unfortunately though search time grew exponentially with the number of match results, and searching for “a” in large library could hang for up to 30 seconds. Lame.

Half of the problem was the secondary sorting issue that had been slowing down scrolling. Each query had to include the search and filter constraints, so a slight cost increase would be multiplied by 50 to 100x. Removing this multiplier helped a great deal.

The other half turned out to be the way we were joining the FTS3 table to the properties. Query optimization is mysterious, so testing and measurement are critical. Query logging, our performance test harness, and the SQLite ‘EXPLAIN QUERY PLAN’ command helped us determine that joining the FTS table, then the properties, then the media items is an order of magnitude faster than equivalent queries.

Finally, since library filter lists only show a small number of values, we now fetch all rows at once, removing the need for a separate count query of approximately the same cost.

Lessons learned:

• Table joining order is extremely important. As a rule, join the most restricting or most expensive to join table first. Whatever you do, test a few permutations.
• Joining with virtual and temporary tables prevents the use of an index for both joining and sorting. ‘EXPLAIN QUERY PLAN’ shows that an index can still help with a join, but sorting has to be done the hard way.
• Occasionally the query optimizer will make bad decisions, and forcing it to use a specific index can help quite a bit. Placing the unary plus operator (+) in front of a column name indicates that an index should not be used.

Recommended links:

• SQLite Query Optimizer Overview
• SQLite Optimization FAQ
• SQLite performance tuning and optimization on embedded systems

ADDING TRACKS

Importing large libraries was slow, and required an obscene amount of memory. With 1.0 it still isn’t ideal, but it’s fast, and the memory usage is 10% of what it was in 0.7. Some of the improvement came from the database changes described below, but significant progress was made simply by avoiding unnecessary work.

The first pass removed unnecessary code paths. 0.7 computed an MD5 hash for every track, doubling IO costs, and would frequently loop over and copy the list of tracks. This behaviour was redundant, and required a huge amount of memory when importing 50k+ tracks.

The second pass cleaned up library change listeners. Tracing with the DTrace JavaScript probes and profiling with Venkman revealed that the smart playlist system and album art scanner ignored batching hints. Batch notification was added after the initial listener API was created, and for backwards compatibility it is opt-in, making it easy to forget that a single JavaScript listener called 50,000 times can hang the UI. The listeners were fixed, but we need an API that is easier to use correctly.

DATABASE ENGINE

Experimenting with BloatView and Instruments, we established that the large library memory problems in 0.7 were mostly caused by our database engine.

Any time Songbird operates on a large set of media items (adding, deleting, enumerating) it builds multiple queries per item, enqueues them, and then executes them. In 0.7 these queries were constructed as strings, which, multiplied by a library of 50k+ tracks, could require hundreds of megabytes.

The problem was solved by adding prepared statement support to the database engine and library code. Now a single compiled query instance can be used with a large set of parameters, and the number of media items involved is barely a factor.

Discoveries along the way:

• NS_ProxyRelease should be treated with caution, since it can cause objects to pile up.
• A string query or prepared statement with a “column IN (?, ?, …)” clause is much faster than an equivalent set of “column = ?” prepared statements.
• The SQLite ‘ANALYZE’ command doesn’t need to be run all the time. Dump values from a typical database and include hardcoded results with the schema.

DATABASE SIZE

The easiest way to improve database performance is to just have less data. Seems obvious, but it’s easy to overlook.

In 0.7, a 10,000 track library required a 50MB database, of which 60% went to storing indices (according to the sqlite analyzer). Indices store a complete copy of the indexed data, and are expensive to maintain. They are also difficult to remove, as you have to be sure you wont hurt performance. Using ‘EXPLAIN QUERY PLAN’ to figure out which indices were used, and the performance test suite to avoid regressions, we dropped three indices for an overall size reduction of 40%.

We also noticed that many values were stored as UTF-16, despite never containing Unicode (since our generic property schema stores everything as strings). With a little experimentation we discovered that using UTF-8 databases did not impact performance, even for large libraries of entirely Unicode metadata.

Switching to UTF-8 gave another 40% reduction, and an average 10,000 track library in 1.0 is now 18MB.

UP NEXT

Where are the new pain points? If you’ve been using the 1.0 RC builds please let us know how things are working, and where you think we should focus. Songbird can never be “too fast” or “too lean” and we’re committed to improving performance in every release going forward.