I just had an epiphany. I might understand why Apple removed MySQL from OS X Lion Server. It's because of all things, MySQL should be a free app, downloaded from the app store. That's not Apple's job, it's MySQL's job. This is how software works now.

We'll talk about the inclusion of PostgreSQL in a second, just hold on.

Here's what my problem with MySQL has always been, and it's actually the reason I got a Mac Mini Server: installing and upgrading MySQL is always a major pain in the ass, especially upgrading. But it shouldn't be hard at all. Minor releases of MySQL should update just as easily as any other software, so why don't they? I don't know!

In MySQL's defense, they try to make installation easy on a Mac, but you can tell their heart just isn't in it yet. But everything even the most anal system administrator does when upgrading MySQL can and should be be automated. Shutdown? Yes. Backups? Yes. Rollback if necessary? No. They should actually work to make sure rollbacks are never necessary. That's what testing is for.

Having said that, I'm sure Apple would be willing to support multiple major versions for server applications. It's not something the current apps require, but we get it, some people need MySQL 5.0 and can't move forward yet, fine, that's a separate app from 5.1 and 5.5. Maybe these versions could be "in app" purchases (which have no cost, for the free versions.) There could be one MySQL app that can be configured for a specific version, or maybe even multiple versions at once.

The point is this, the days of manual software installs are over, even for server software. If MySQL doesn't do it, you can bet within the next year one or more other database makers will.

The other thing to factor in is that Apple released a new Mac Mini Server with OS X Lion Server. This would indicate that they aren't giving up on the server market. It says that as usual, they are trying to change it, for the better. I hope that's what happens.

Oh, I almost forgot about the inclusion of PostgreSQL. Apple needs a database on the server to support the wiki feature, but there is no app store database ready yet. PostgreSQL may have even been chosen because it is the less used package, to encourage MySQL to take action. Or maybe PostgreSQL was chosen because they've already committed to making an app which Apple will use once it's ready.

Anyway, this is what I'd personally like to see, an app store database. We'll just have to wait and see what actually happens.

mytop -- a MySQL top implementation to show you why your server is so damn slow right now.
What Could Kill Elegant High-Value Participatory Project? -- The problem was not that the system was buggy or hard to use, but that it disrupted staff expectations and behavior. It introduced new challenges for staff [...]. Rather than adapt to these challenges, they removed the system. [...] No librarian would get rid of all the Harry Potter books because they are "too popular." No museum would stop offering an educational program that was "too successful." These are familiar challenges that come with the job and are seen to have benefit. But if tagging creates a line or people spend too much time giving you feedback? Staff at Haarlem Oost likely felt comfortable removing the tagging shelves because they didn't see the tagging as a patron requirement, nor the maintenance of the shelves as part of their job.
Gremlin -- a Turing-complete, graph-based programming language developed in Java 1.6+ for key/value-pair multi-relational graphs known as property graphs. Graph structures underly a lot of interesting data (citations, social networks, maps) and this is a sign that we're inching towards better systems for working with those graphs. (via Hacker News)
Anic -- programming language based on stream and latches. I still can't figure out whether it's an elaborate April Fool's Day joke that was released too soon, because the claim of "easier than *sh" is a bold one given the double-backslash and double-square-bracket-heavy syntax of the language. Important because it's built to be parallelised, and we're in transition pain right now between well-understood predictable languages for single CPUs (with hacks like pthreads for scaling) and experimental languages for multiple CPUs.

As you may have heard, the UK government released a fresh batch of MP expenses documents a week ago on Thursday. I spent that week working with a small team at Guardian HQ to prepare for the release. Here’s what we built:

http://mps-expenses2.guardian.co.uk/

It’s a crowdsourcing application that asks the public to help us dig through and categorise the enormous stack of documents—around 30,000 pages of claim forms, scanned receipts and hand-written letters, all scanned and published as PDFs.

This is the second time we’ve tried this—the first was back in June, and can be seen at mps-expenses.guardian.co.uk. Last week’s attempt was an opportunity to apply the lessons we learnt the first time round.

Writing crowdsourcing applications in a newspaper environment is a fascinating challenge. Projects have very little notice—I heard about the new document release the Thursday before giving less than a week to put everything together. In addition to the fast turnaround for the application itself, the 48 hours following the release are crucial. The news cycle moves fast, so if the application launches but we don’t manage to get useful data out of it quickly the story will move on before we can impact it.

ScaleCamp on the Friday meant that development work didn’t properly kick off until Monday morning. The bulk of the work was performed by two server-side developers, one client-side developer, one designer and one QA on Monday, Tuesday and Wednesday. The Guardian operations team deftly handled our EC2 configuration and deployment, and we had some extra help on the day from other members of the technology department. After launch we also had a number of journalists helping highlight discoveries and dig through submissions.

The system was written using Django, MySQL (InnoDB), Redis and memcached.

Asking the right question

The biggest mistake we made the first time round was that we asked the wrong question. We tried to get our audience to categorise documents as either “claims” or “receipts” and to rank them as “not interesting”, “a bit interesting”, “interesting but already known” and “someone should investigate this”. We also asked users to optionally enter any numbers they saw on the page as categorised “line items”, with the intention of adding these up later.

The line items, with hindsight, were a mistake. 400,000 documents makes for a huge amount of data entry and for the figures to be useful we would need to confirm their accuracy. This would mean yet more rounds of crowdsourcing, and the job was so large that the chance of getting even one person to enter line items for each page rapidly diminished as the news story grew less prominent.

The categorisations worked reasonably well but weren’t particularly interesting—knowing if a document is a claim or receipt is useful only if you’re going to collect line items. The “investigate this” button worked very well though.

We completely changed our approach for the new system. We dropped the line item task and instead asked our users to categories each page by applying one or more tags, from a small set that our editors could control. This gave us a lot more flexibility—we changed the tags shortly before launch based on the characteristics of the documents—and had the potential to be a lot more fun as well. I’m particularly fond of the “hand-written” tag, which has highlighted some lovely examples of correspondence between MPs and the expenses office.

Sticking to an editorially assigned set of tags provided a powerful tool for directing people’s investigations, and also ensured our users didn’t start creating potentially libellous tags of their own.

Breaking it up in to assignments

For the first project, everyone worked together on the same task to review all of the documents. This worked fine while the document set was small, but once we had loaded in 400,000+ pages the progress bar become quite depressing.

This time round, we added a new concept of "assignments". Each assignment consisted of the set of pages belonging to a specified list of MPs, documents or political parties. Assignments had a threshold, so we could specify that a page must be reviewed by at least X people before it was considered reviewed. An editorial tool let us feature one "main" assignment and several alternative assignments right on the homepage.

Clicking “start reviewing” on an assignment sets a cookie for that assignment, and adds the assignment’s progress bar to the top of the review interface. New pages are selected at random from the set of unreviewed pages in that assignment.

The assignments system proved extremely effective. We could use it to direct people to the highest value documents (our top hit list of interesting MPs, or members of the shadow cabinet) while still allowing people with specific interests to pick an alternative task.

Get the button right!

Having run two crowdsourcing projects I can tell you this: the single most important piece of code you will write is the code that gives someone something new to review. Both of our projects had big “start reviewing” buttons. Both were broken in different ways.

The first time round, the mistakes were around scalability. I used a SQL “ORDER BY RAND()” statement to return the next page to review. I knew this was an inefficient operation, but I assumed that it wouldn’t matter since the button would only be clicked occasionally.

Something like 90% of our database load turned out to be caused by that one SQL statement, and it only got worse as we loaded more pages in to the system. This caused multiple site slow downs and crashes until we threw together a cron job that pushed 1,000 unreviewed page IDs in to memcached and made the button pick one of those at random.

This solved the performance problem, but meant that our user activity wasn’t nearly as well targeted. For optimum efficiency you really want everyone to be looking at a different page—and a random distribution is almost certainly the easiest way to achieve that.

The second time round I turned to my new favourite in-memory data structure server, redis, and its SRANDMEMBER command (a feature I requested a while ago with this exact kind of project in mind). The system maintains a redis set of all IDs that needed to be reviewed for an assignment to be complete, and a separate set of IDs of all pages had been reviewed. It then uses redis set intersection (the SDIFFSTORE command) to create a set of unreviewed pages for the current assignment and then SRANDMEMBER to pick one of those pages.

This is where the bug crept in. Redis was just being used as an optimisation—the single point of truth for whether a page had been reviewed or not stayed as MySQL. I wrote a couple of Django management commands to repopulate the denormalised Redis sets should we need to manually modify the database. Unfortunately I missed some—the sets that tracked what pages were available in each document. The assignment generation code used an intersection of these sets to create the overall set of documents for that assignment. When we deleted some pages that had accidentally been imported twice I failed to update those sets.

This meant the “next page” button would occasionally turn up a page that didn’t exist. I had some very poorly considered fallback logic for that—if the random page didn’t exist, the system would return the first page in that assignment instead. Unfortunately, this meant that when the assignment was down to the last four non-existent pages every single user was directed to the same page—which subsequently attracted well over a thousand individual reviews.

Next time, I’m going to try and make the “next” button completely bullet proof! I’m also going to maintain a “denormalisation dictionary” documenting every denormalisation in the system in detail—such a thing would have saved me several hours of confused debugging.

Exposing the results

The biggest mistake I made last time was not getting the data back out again fast enough for our reporters to effectively use it. It took 24 hours from the launch of the application to the moment the first reporting feature was added—mainly because we spent much of the intervening time figuring out the scaling issues.

This time we handled this a lot better. We provided private pages exposing all recent activity on the site. We also provided public pages for each of the tags, as well as combination pages for party + tag, MP + tag, document + tag, assignment + tag and user + tag. Most of these pages were ordered by most-tagged, with the hope that the most interesting pages would quickly bubble to the top.

This worked pretty well, but we made one key mistake. The way we were ordering pages meant that it was almost impossible to paginate through them and be sure that you had seen everything under a specific tag. If you’re trying to keep track of everything going on in the site, reliable pagination is essential. The only way to get reliable pagination on a fast moving site is to order by the date something was first added to a set in ascending order. That way you can work through all of the pages, wait a bit, hit “refresh” and be able to continue paginating where you left off. Any other order results in the content of each page changing as new content comes in.

We eventually added an undocumented /in-order/ URL prefix to address this issue. Next time I’ll pay a lot more attention to getting the pagination options right from the start.

Rewarding our contributors

The reviewing experience the first time round was actually quite lonely. We deliberately avoided showing people how others had marked each page because we didn’t want to bias the results. Unfortunately this meant the site felt like a bit of a ghost town, even when hundreds of other people were actively reviewing thing[…]