Implementing most popular content listings in Drupal powered by Google Analytics data

We recently implemented a feature to display ‘most popular’ content on the WRAP and Zero Waste Scotland websites in order to improve the visibility of popular content on their existing taxonomy pages and complement the existing ‘Latest content’ listings.

A high level overview of the feature can be found on my recent Torchbox.com blog post. This post is the technical compliment to it where I can elaborate on some of the decisions and implementations of what went into the feature.

Choosing a data source:

Drupal comes with its own internal statistics module which can be used to track how many users have viewed a given node. It’s not particularly sophisticated and relies on a session of some variety to be able to associate a view event to a node. With something like Pressflow (or Drupal 7) anonymous users no longer have an anonymous session cookie set to ensure compatibility with HTTP caches such as Varnish.

In the same manner, contrib modules such as radioactivity also rely on an active user session (anonymous or otherwise) and can also add a significant level of database load (update queries on every node view event) on high traffic sites. Even by queueing some of these updates in-memory through something like memcached and flushing to the database at less frequent intervals there is still a significant performance consideration on top of incompatibility with Varnish.

WRAP and ZWS had well established Google Analytics profiles and Analytics has a great API. The downsides are that Analytics doesn’t understand which paths are nodes, which are views based and what taxonomy term a certain path relates to. On balance, this is perfectly acceptable, and this is covered a little later in the post.

Given the richness of the data stored by Analytics, the ease of access and compatibility with Varnish (Analytics events/calls are client side), and variety of filters and metrics, it was became fairly clear that using Analytics to provide the data for scoring our content according to unique page views would be a good fit.

Introducing logical separations of concern in the whole process

Nobody likes a ‘superman’ function or class that tries to do everything. We’ve probably all seen them at one stage or another…

function mymodule_update_node_score($node, $score) claims to do one discrete task, but when you look inside we might find it updates one thing, clears the cache, maybe even loads another, related node and updates something in that too.

This kind of approach might well result in functional code, but in the long term it makes the end to end process brittle (how do errors get handled? does the return type (if any) change depending on the context of the current task?), harder to diagnose/follow and therefore more expensive to maintain.

So, we split the problem into a few logical tasks:

Structuring the Analytics query:

We used the Google Analytics Query Explorer tool provided by Google to help test the various criteria we needed to include against the existing data. In this case, we were asking something along the lines of:

“Please can I have the domain, page path and unique page views for this Analytics profile from the last three months… but don’t count anything that hasn’t got a unique page view in that period of time and ignore anything with these legacy URL paths.”

Talking to Google Analytics:

The google_analytics_reports module was originally written to grab data from Analytics and display them in Drupal. We’re only interested in the included google_analytics_api module as it does the heavy lifting of communicating with the Analytics API based on the query format we fine tuned in the analytics query explorer.

Storing the result set from Analytics:

For better or worse the google_analytics_api module gave us a pretty large result object (>1MB of text when serialized in PHP). So much so that it caused a failure when trying to store it in memcached which has a maximum size per object of 1MB. So the memcached daemon complained and the Drupal memcache module quietly failed.

To get around this we decided to configure the Drupal memcache module to keep the results from our query in the database by submitting a patch for the google_analytics_reports module. The use of a drush make file for this project allows modules, themes, libraries and patches submitted to, or hosted on drupal.org very easy to incorporate, manage and version control.

Processing the result set

Providing we had cached data to work with we would go through each result, check the path and see if it was a node and if so take its corresponding unique page views value and add it to our list of things to update.

Finally, once we were done with the result set we could quickly iterate over our list of things to update and turn these into SQL commands to update our table. A few thousand inserts/updates on a simple, indexed table structure doesn’t take long at all.

Scoring nodes?

At first we got caught up in the procedural aspect of interrogating Analytics about paths from the site. For example, a mindset of: “For each taxonomy term, work out what nodes are tagged, get their paths, match these against Analytics and then update their scores”.

It didn’t take long to see the flaw in that process. Not only was it complex in the detail, it would be inefficient and likely teeter on the edge of the daily Analytics API quotas; either by querying per-node (yuck) or getting far too much (>10k rows) data per request.

However, by reducing the query size to a sensible date range, carefully filtering out clear non-node/404/obsolete URL paths we were left with a decent query that provided unique page views over the last 3 months for all WRAP/ZWS domains. By using a 3 month date range we ensured both that the overall result set wasn’t insanely large and also has the benefit of providing a natural expiry point for results. For example, a press release may generate a spike in page views over a one week period, but in the event that page views tailed off it would eventually slip from the rankings. In contrast, consistently popular content would retain a high ranking and would reflect the interest in it.

Abstracting this further and removing the Drupal ‘internals’ from the problem we realised if we kept a score (ie: unique page views) against each node we could let Drupal concern itself with selecting content by domain/language/taxonomy term/publish state. As all the sites were were concerned with were hosted on the same database and managed with the domain access module, this was generally something we could leave to Drupal views.

Storing the node scores:

One option was to add a new integer CCK field to each content type on the site. This would immediately integrate it with views (to power the listings) and would also benefit from field permissions.

We opted to keep scores in a database table in the interests of performance. The daily cron task to update node scores from the result set from Google Analytics would involve several thousand sequential node_load() and node_save() operations and this would not help the site’s performance. A wider concern was that node_save() invokes the cache_clear_all() function before it returns. Several thousand sequential full cache clears on a large site? No thanks!

Accepting that we introduced a custom schema, we needed to integrate it with the bits of Drupal we were hoping to use. A few minutes extra work implementing hook_views_data() exposed our node_score table to views and allowed us to use that data to sort our nodes in our listings. It also meant that updating this table with several thousand results takes 2-3 seconds and doesn’t have a discernable impact in site performance at all.

Frond end tweaks

By this stage we had our data from Analytics stored in and available to Drupal. We made a new view block (based on the latest content listing) and changed the criteria required for selecting and sorting nodes. For the most part, all the existing CSS styles were re-used and existing JavaScript to control the listing filters remained valid.

The final custom work required was some additional Javascript to provide a toggle between the two displays and manipulate the DOM slightly to allow us to fit the original design and pretend we didn’t have two completely independent views blocks. This Javascript was very much a progressive enhancement rather than graceful degradation. Without Javascript the pages and listings still function as they need to but appear one above the other.

All custom code (server side and client side) was managed within a single custom Drupal module, meaning that in the event the module was switched off the collection pages would revert back to their previous state.

We also made use of the block caches to further lower the resource footprint of this functionality. The content is only refreshed nightly, so we didn’t see the need to regenerate the markup over and over each time a logged in user views the page or the Varnish cache expires. Every little helps in that respect.

New machine, new hostname to choose…

After months of minor annoyances with Linux Mint I recently converted back to a MBP/OS X for day to day work.

Most machines at Torchbox have some kind of clever hostname… for Macs you’ll find machines such as aroni, kerel etc… my old Dell was monte.

All the good names had been taken for prefixes of ‘Mac’… so I opted for something grey, round(ish) and powerful… and an amusing childhood story sprang to mind: The Glerp.

I am now johan@glerp

Varnish and Drupal 7.20/21 DoS vulnerability handling

The recent security releases for Drupal 7.20 and 7.21 fix a security vulnerability in the form of a possible denial of service attack where large numbers of requests to generate on-demand image styles could result in very high CPU loads.

The fix introduces a token that is appended to the path for an image derivative, for example, previous URLs like

/sites/default/files/styles/thumbnail/public/field/image/example.png
now have a token such as
/sites/default/files/styles/thumbnail/public/field/image/example.png?itok=zD_VaCaD.

The upshot of this being each request can be validated server side and invalid/DoS requests can be safely discarded, reducing the potential load on a server.

The downside is that any kind of HTTP accellerator or cache that relies on URL based identification is more or less invalidated as each request to the same image path will have a different, unique token.

The 7.21 release makes some effort to limit the impact by allowing site administrators to set the

$conf['image_allow_insecure_derivatives'] = TRUE;

Drupal variable. This provides partial protection by safeguarding against the most serious form of the vulnerability, but permits requests to standard image derivatives without the token validation.

Changing the Varnish hashing algorithm

We employ the use of Varnish as an HTTP accellerator for most of our clients’ sites. We also wanted to ensure that anonymous requests to /sites/default/files/styles/thumbnail/public/field/image/example.png and /sites/default/files/styles/thumbnail/public/field/image/example.png?itok=zD_VaCaD are treated as the same cached resource instead of having two separate cache items. You can do this with the following adjustment to your VCL file:

sub vcl_hash {
  set req.hash += req.http.host;
  set req.hash += regsub(req.url, "\?(.*)itok=.+[^&]", "\1");
  return (hash);
}

In plain terms, this configuration uses a regular expression to effectively ignore the itok token when generating a hash value for the item when it’s cached.

Debugging your regex

There isn’t really a console for debugging your Varnish regsub regex. One option is to add a custom HTTP header in vcl.fetch which lets you examine what your regex is doing. For instance:

sub vcl_fetch {
  set beresp.http.X-Regex = regsub("Your URL to check", "Your regex", "Any substitutions");
} 

It’s then easy enough to check the response headers with any decent HTTP debugger or curl and examine the value of that header.

Varnish: safely checking/using your VCL file

If you’re running Varnish 2.1 or earlier, be very careful when looking to test your VCL file configuration before reloading/restarting the service. Most documentation you’ll find through search results will advise the following:

$ sudo varnishd -C -f /path/to/mysetup.vcl # Don't do this!

This runs the Varnish daemon in compile mode, the idea being if your VCL file is invalid compilation will fail and the daemon exits. When successful, you get the output of your VCL file. Apparently, the project maintainers for Varnish feel this is an adequate method of checking syntax. I don’t, but it doesn’t appear to be changing anytime soon.

Varnish 2.1 doesn’t have the -C option, but it will output the VCL file anyway (looking like a success) and more importantly (and much less obvious!) - you’ll end up spawning a new Varnish daemon. Depending on your default settings it will end up allocating a fixed chunk of memory for it’s use.

Let’s assume that default is 1GB of memory and you are busy tweaking your VCL file. You run varnishd in what you think is compile (and exit) mode, but what’s really happening is you’re inadvertently spawning new Varnish daemons. Eventually you run out of memory and the server dies.

Oh my!

Safely testing/reloading the VCL file

The safest way to test a VCL file is to use varnishadm:

$ sudo varnishadm -T 127.0.0.1:6082 -S /etc/varnish/secret vcl.load /
<some-identifier-for-the-vlc-file>

this loads (and parses) the VCL file and adds it to a buffer (identified with the identifier you give it).

$ sudo varnishadm -T 127.0.0.1:6082 -S /etc/varnish/secret vcl.use /
<your-unique-identifier>

This command tells Varnish to use the VCL file from the buffer of your choice. The great thing here is it will hot-swap in the new VCL file without the need to restart the service, which will clear your existing cache.

On Debian machines you can sometimes find this script which will check your VCL file:

$ sudo /usr/share/varnish/reload-vcl

This runs both the aforementioned vcl.load and vcl.use. If you only want to parse/check the syntax of your VCL file, add the -c parameter to only run vcl.load.

Even if you’re running a recent version of Varnish you’re likely to be better off with this approach than simply firing up the daemon or restarting the service.

Time to move to MariaDB?

Torchbox recently sent several developers to DrupalCamp London 2013, who came back with tales of VMs paved with gold, Drupal Themers laying slain by the roadside and a new fangled work of the devil - MariaDB!

But seriously, an excellent time was had by all. It’s always interesting to see where your company’s practices bench against others and one area that piqued our interest was speeding up our development VM using nginx and possibly MariaDB.

MariaDB is a replacement for MySQL with one eye on a possible future where Oracle take a bad turn with MySQL development and strand loyal users. The suggestion to use it instead of MySQL indicated there were performance benefits to be had, but having look around for benchmark stats we’ve found some conflicting results.

Exhibit A from DimitriK indicates MySQL 5.5 just beats MariaDB 5.5 but MySQL 5.6 destroys it: 

http://dimitrik.free.fr/blog/archives/2013/02/mysql-performance-mysql-56-vs-mysql-55-vs-mariadb-55.html

Exhibit B from MariaDB indicates even the older MariaDB 5.3 seems a worthy adversary to MySQL 5.6:

http://blog.mariadb.org/mariadb-5-3-optimizer-benchmark/

Exhibit C, also from MariaDB shows MariaDB 10 (still in alpha) benching very close to MySQL 5.6: 

http://blog.mariadb.org/sysbench-oltp-mysql-5-6-vs-mariadb-10-0/

Exhibit D, from mysqlperformanceblog suggesting MariaDB’s Hash Joins offer huge benefits, but not in all situations:

http://www.mysqlperformanceblog.com/2012/05/31/a-case-for-mariadbs-hash-joins/

Granted these are not all based on the same points of comparison but the general picture is confused and we’re no closer to making a decision. Clearly MariaDB has benefits, but if Drupal is brimming with join queries of the like that MariaDB can’t handle well, it wouldn’t be a wise move.

If anyone has examples of MariaDB benching well in the wild with Drupal, we’d love to hear from you.

We’ll update when we learn more.

Be aware when caching large data objects in Drupal

TL;DR: Memcache fails silently when trying to store data greater than its defined slab size. You can also specify what cache bins are used and which are ignored and pushed through to the database.

Most of the websites we put into production make use of the memcache module to take some of the strain off the database server. By its nature Drupal is very database centric and although it offers the ability to cache a variety of things there is always the underlying overhead of disk I/O or network latency when communicating with a database. Memcache allows Drupal to store its cached data in memory which is much faster.

A recent project involved retrieving a relatively large amount of data from Google Analytics API using the google_analytics_reports module, caching it and using it to update content rankings. While the query itself succeeded and we received its payload, the data was mysteriously unavailable when trying to retrieve it from the cache. Cue a hunt to track it down!

Look in your database tables, duh?

Hmm… no data there. Not surprising really, because we were using memcache :)

Telnet then?

Yes, you could do this to peek at what memcache has (nice overview of how). What’s probably easier is to use drush eval to natively ask Drupal to run cache_get(‘some-identifier’) which means you don’t need to faff with dealing with whatever memcache/database/cardboard box instance you happen to be using to store things in.

Still no data!?

cache_get() did not show any results either and that was indeed odd. A quick test of another query with a single result DID show up. This started to ring a few bells…

The size of the original query payload returned was big enough to exceed the 1MB slab size memcache uses. Evidently, it was failing to insert a cache item but that failure was not communicated or was not visible to the person/process invoking cache_set().

Next steps:

While it would be preferable to reduce the query payload size to be something more reasonable this would involve picking apart the code in the google_analytics_reports contrib module… and we didn’t want to suggest removing the large ‘data’ component of it’s response object if someone else had a genuine use for it. We could also consider narrowing the query to return less data… or maybe retrieve it in batches. 

We could also consider increasing the memcache slab size to cover the size of our data, but this didn’t feel right given the single, niche requirement and the real possibility of getting the memcache memory handling settings wrong and running out of memory further down the line.

In the end…

• I submitted a patch to the google_analytics_reports module which allows the cache options parameter to specify which cache bin to use.
• The memcache settings in settings.php were changed to ensure that things using the cache_analytics bin were passed back to the database, which can store a larger volume of data per cache item.

We’re also considering reporting the silently failing cache_insert to the memcache maintainers.

J-P, Helen and Justine at Drupalcamp London this weekend →

Torchbox have got three tickets to Drupalcamp London. Hope to see you there!

Drupal’s Solr config and false matching

Drupal’s Apache Solr module comes with a configuration file to add to Solr, called solrconfig.xml. There’s a lot of complex default settings in there that help to fine-tune Solr results. And while that means that Solr is a powerful solution for Drupal search, it also means that its configuration can be quite opaque.

One thing we’ve found is that by default the search results are very generous: if you search for e.g. “cute cat”, you will get back all responses that contain “cat” OR “cute”; the only concession to the two keywords is that matches on both will be considered more relevant, and appear towards the top.

This is the result of the mm or “minimum matches” parameter. In solrconfig.xml, you can change this for a given requestHandler: most likely you’ll want to do it for the default one, whose name attribute is drupal!

To change mm, look for the string:

<requestHandler name="drupal" default="true">
  ...
  <str name="mm"><!-- Some value in here --></str>

And change that value as you see fit

Note that percentage matching is not the same as numeric matching: 100% does not equal 1. Rather: a number N means “if M terms are searched for, must match at least min(M, N)”; whereas a percentage P means “if M terms are searched for, must match at least M*P”. So P=100% is the strictest possible match.

Twitter pull loses time_ago

Drupal’s Twitter Pull module is a useful one: not just for its own UI elements, but also for getting tweets to format yourself. However, in a recent version change, it lost its $tweet->time_ago property on the tweet objects.

You can recreate this straightforwardly using the following PHP:

$tweet->time_ago = format_interval(time() - $t->timestamp);

It’s a minor pain but if you’ve exposed your own theme implementations to preprocess hooks - and why wouldn’t you? - then it should be easy to put in e.g. your template.php.

RobotsTxt, robots.txt and /robots.txt

The RobotsTxt module effectively requires a hack to Drupal core in order to function. This is because core Drupal contains a static robots.txt file, and webservers like Apache are configured by Drupal’s .htaccess file to serve static files preferentially to asking Drupal for the content. Every time Drupal is upgraded (or you deploy the site to a new staging or development instance), that hack has to be repeated.

One solution which Johan hit upon a while ago was to patch Drupal’s core .htaccess file, to send any request beginning /robots.txt to Drupal rather than serving any file on disk. It still constitutes a hack to core, but as a patch file accessible at a URL it can be incorporated into e.g. drush make files, and applied automatically when Drupal core is upgraded.

The patchfile-based hack is a big improvement, but it still leaves the RobotsTxt module complaining that there’s a problem. This is because it checks for the robots.txt file on disk, not for the /robots.txt URL, which is the real acid test for whether the module is working properly. So now we’ve also added this patch for robotstxt_requirements(), which checks the URL instead.