Rustici Engine has supported xAPI (the Experience API) since 2011 when we created the initial version for ADL. We always expected the volume and type of data to cause scalability issues. Although, our understanding of how to address those issues has grown as well. The last big overhaul we made to xAPI data storage in Engine was in 2015. After almost 10 years since that overhaul, it was time for another big change that would ultimately come with some big challenges.
The Problem
The xAPI standard requires us to support a variety of ways to fetch statements. Using one structure to support them all requires some inefficient lookups. Particularly when “related” options are used such as “related_agents” and “related_activities” – more common than you’d expect due to default permissions. In the worst case: many but not all statements meet the “related_agent” criteria, and the ones that do meet that criteria appear late in the needed sort order. In this worst case a query can take hours to run.
Can’t we just add indexes?
Well, yes indexes can help but – they have their limits. The index of a book is actually a really good metaphor. If you’re trying to find one specific thing, and you have a matching index (in a book this will be alphabetical by keyword), you can quickly find the index entry with what you want and then turn to the relevant page in the book. Now let’s try to think about the “related_agent” issue in that way. We’re actually looking for: the most recent 100 statements, related to a particular agent. We have an index on when statements were stored, and we also have an index mapping related agents to statements.
Think about those as indexes in books for a bit. You look up statements by the related agent and find one index entry referencing thousands of different pages. You’d have to turn to each referenced page, note the matching statements on that page, build a sorted list and take the top 100 of that list. You could start with the index that lists statements by when they’re stored. Depending on just how many statements there are, and how many match the related agent criteria, that could be better! Start with the entry for “now” and work backwards. Turn to pages in the underlying book to find the statements that match the “related_agent” criteria and stop when you get 100. If most match, that will wind up being reasonably fast, but if many don’t match, even that approach is going to be very, very slow.
Computers flip through these “books” faster, but when the amount of data is large, it starts to become a big problem. What we actually need is an index that exactly matches the task at hand. We need to be able to lookup by related agent + stored. But that’s not the only search we do, we also need to be able to lookup by related activity + stored, or related activity + verb + stored or related agent + activity + stored … and so on.
So can’t we just add lots and lots of indexes?
Well, yes, and in a way, we did. Let’s think about books again, and keep in mind that for these books, people keep adding content (statements), and they need to do that quickly. The statements are coming in frequently and usually one-by-one or in small batches, and every time that happens we need to update each and every book(index). For the amount of indexes we need, the speed with which we could add statements would have suffered too much.
The Solution
Fortunately, xAPI was designed with the idea that an LRS might store statements faster than it was prepared to return them and provides “X-Experience-API-Consistent-Through” for LRSs to declare how caught up they are. Time and time again we’ve thought about taking advantage of that in Engine, but concluded the gains wouldn’t be worth the added complexity.
With cmi5 starting to pick up adoption – including Engine using it internally to handle media content – more and more customers are creating more xAPI data. It was time. So Engine 23 has “write-oriented” and “read-oriented” tables for xAPI, and a process to keep the “read-oriented” tables (with all those indexes) very close, but not quite up-to-date. We still have to update all those extra tables and indexes, but these are very narrow tables (each row only has a few small ids), and we control the batch size – a very big deal for efficiency.
We also eliminated the use of Statement ID ( a UUID) as a primary key, which had been slowing down writes.
Things to watch out for
- Upgrades are going to take some time for existing Engine customers. Since we’ve completely changed how xAPI data is stored, it’s all going to have to be re-written. We do provide a phased approach to do much of that work while your old system is still running.
- If you’ve been reading xAPI statements from an Engine based LRS, you now need to think about “X-Experience-API-Consistent-Through”. It may be you don’t need to know if you have the very latest statements – if so you don’t have to do anything different. But if you send in a statement and expect to get it back right away – you might need to check “X-Experience-API-Consistent-Through” and wait.
Did it work?
We’ll do another post soon with lots more numbers. Here, I’ll just call out that worst-case query I mentioned previously. In a moderately large set of test data (millions of rows) on Java / MySql, we shaved off 99.8% of the time for that query, without slowing down writes. To picture how the change can be so dramatic, let’s go back to the example of looking up the most recent 100 statements for a particular “related agent” in a book. Since we now have an index specifically for “related agent + stored”, we can quickly find the entry for the related agent in question closest to “now” – just as one finds any other entry in an index, and then can scan through the first 100 entries in that index, which will each point out the page of a statement we need to return.
If you’re using xAPI heavily and wishing it was faster, or if you’re planning to start doing so soon – now’s the time to upgrade to Rustici Engine 23. Reach out to our team and we will help you get started with the latest release.
