Speeding up Flocknote

Much of my development time at Flocknote is spent working on great new features to make communicating with your flock easier. However, I also get to spend some time here and there making Flocknote better in other ways. This summer I've been working hard to make Flocknote faster.

There are a few different things that I've been focusing on, and I wanted to jot down some thoughts on the process so others who run websites and large web applications could benefit.

Slow MySQL Queries

Flocknote's data is stored in a MySQL database. The database is growing fast, and some tables have more than a few million rows (mostly for logging and archiving purposes). We have more than 100,000 users now, and we have at least 40-70 active members at any given moment. Many pages on Flocknote require some rather complex queries to get all the data needed.

As an example, when a member visits the home page, Flocknote looks up all the member's subscriptions, gets all the information about all the lists and networks to which the member is subscribed, then displays that information on the page. Additionally, we look to see if there are any new notifications or required actions for the member, and we build the member's customized menu accordingly.

One of the queries that retrieved the member subscription data was taking over 100ms—and this query was run multiple times for some members.

The first thing I did to help this function was to statically cache the data retrieved by the query. Instead of PHP having to ask MySQL for the data each time this data was needed, PHP could simply grab the data from RAM, which is much faster. The second thing I did was to use MySQL's EXPLAIN feature to see what indexes the query was using (here's a good how-to for this). I found that one of the indexes being used was not optimal, so I added an index on the right columns, and reloaded the page. The query went from 100ms, querying over 1 million rows, to just under 1ms, querying only a few rows.

I went through the most-visited pages on the site and fixed any query that took more than 10ms, and cut out about six other common slow queries. Time savings? Over 5 seconds throughout the site. Multiply that by thousands of page requests per hour, and that's a huge time savings!

PHP Execution Time and Memory Usage (XHProf)

The other main culprit behind most of the performance problems seemed to be PHP optimization. I use XHProf and Drupal's Devel module to profile different page loads, and I noticed that some pages were taking three or four times longer to render the page content in PHP than they were taking to get all the data from the database. (Typically, database access should take longer than PHP's processing time.)

On a large majority of pages, a lot of time was being spent moving data around in RAM. We use APC caching to speed up PHP quite a bit, both for 'opcode' caching and 'user' caching, but there was a ton of data being passed back and forth between APC and PHP.

RAM by materod on Flickr

I found that there were two main culprits: Drupal core's caching of field data, and the Views module's caching of views data. Luckily, work was already underway to get these problems fixed: For Drupal core, there's an issue that fixes the memory problems with field data caching that's nearing completion; for Views, there's an issue that fixes memory problems with views data info loading that's also nearing completion. For both fixes, the main problem is that the cache was built more for sites with only a few fields or views; but sites with many fields and/or views benefit more from having individual caches for each field/view/entity, rather than sharing one cache for all fields/views/entities.

I've been working on testing and fixing the patches in those issues to get them ready for inclusion in Drupal core and in the Views module, and when those patches are added, they won't just benefit Flocknote, they'll benefit millions of other websites and web applications that use Drupal as well!

Front-End Improvements

The CPU, database, and memory improvements on Flocknote's side are all well and good, and already have more than halved the time it takes, on average, for a member to load any given page. But I like looking at the wider performance picture; once you get a Flocknote page in your browser, your browser has to render everything and apply any styling and JavaScript to get things looking and working great.

We already use mostly HTML5 and CSS3 techniques to avoid loading extraneous images and data, and we send appropriate cache headers and information to your browser so you don't have to reload everything on every page request.

But we've been working on making sure our CSS selectors are fast, and our JavaScript and jQuery code doesn't waste your computer or mobile phone's processor cycles.

Some of these tweaks are still ongoing—unfortunate souls still stuck on Internet Explorer 6 or 7 may find that a few pages on Flocknote (notably, the add/edit Note page) don't work quite as well—but we're working on making sure everything works great on whatever computer or browser you're using.

At Flocknote, we normally test things on the following devices and try to make sure that all are served well:

• Macs running OS X with Chrome, FireFox, and Safari.
• Windows PCs running Internet Explorer 8, 9 and 10.
• Android phones running Android 2.2.x and later.
• iPhones and iPads running iOS 5 and later.

Flocknote is used by a very diverse group, and it's important that we serve all of our customers well—especially those using mobile devices. We not only have a mobile app for iPhone (still under improvement!), but we also have a fully-responsive site layout to make Flocknote and your Notes look great and perform quickly no matter what device someone uses.

The Future

After a ton of great performance improvements this summer, we're going to be spending more time on new features again, but know that performance testing is built into everything we do at Flocknote—we strive to make Flocknote more useful while also making it faster.

Also see development update: Speedier Flocknote