The following is my log of the OSGrid asset conversion saga.

Day 1: Today we’re taking a third attempt at doing the big fragstore conversion for OSGrid, for those not following the Saga of the Asset Server – about two months ago we started noticing major scalibility issues surrounding assets. Right now they are thrown into MySQL as a blob table, resulting in large amounts of waste both in duplicate content and in the fact we’re storing a filesystem inside of a relational database – you can read up on the earlier design decisions leading to FragStore here.

The previous two conversion attempts have suffered “mysterious MySQL glitches” which we assume may be related to various bugs with long running commands. Apparently the proper course of action when running a query that takes more than 60 seconds to process the command, is to freeze up entirely and stop processing requests – for now and evermore.

In an attempt to make this run a bit smoother – we’ve broken up the process into 2,000 batches of 1,000 assets each – previously our batch mechanism was using MySQL LIMIT X, Y which has the side effect of getting slower and slower as you progress down the table (thus causing the above); so we’ve shifted to using a numeric ID on the assets table. Putting the numeric ID on there allows us to at least index sequential accesses – LIMIT unfortunately will not use any form of index hinting.

mysql> ALTER TABLE `assets`
    -> ADD COLUMN `numericID`  int(11) UNSIGNED NOT NULL AUTO_INCREMENT AFTER `access_time`,
    -> DROP PRIMARY KEY,
    -> ADD PRIMARY KEY (`numericID`),
    -> ADD UNIQUE INDEX `assetID` (`id`);

Query OK, 1623826 rows affected (1 hour 11 min 2.54 sec)
Records: 1623826  Duplicates: 0  Warnings: 0

An hour and a bit later, the difference between the speed of processing before and after is pretty astounding

mysql> select id from assets limit 540000,10;
10 rows in set (11.53 sec)

mysql> select id from assets where numericID between 540000 AND 540009;
10 rows in set (0.42 sec)

It doesnt need a whole bunch of explanation to figure the above may help with our situation. Running the revised and simplified “AssetConverterMarkII” appears to go without a hitch – data is stored into the database, the metadata table is being filled correctly – all in all it appears to be functional. With one minor teeny little problem.

Only the first 4096 bytes of data are being written to the backend store. The remaining sectors of data are written – but consist entirely of zeros. Retrieving the data results in a buffer of the same length as the original stored asset – but often half the data is completely missing. An hour later, it looks like the data is being sent to the backend voldestore correctly, but either on the way there or on the way back, it loses something. Unfortunately it looks like the problem is outside the purview of the client adapter and is somewhere in the deep murk of the backend storage server.

Day 2: Rethinking time – after spending quite some time hunting for some alternatives, the simplest solution looks to be the best.

While I am keen to use Project Voldemort in the long term – in the short term debugging our implementation details are just not on my agenda. We use a IKVM cross-compiled connector library from Java, and the problem looks like it is sitting in there somewhere. Unfortunately debugging Java IKVM libraries from within .NET is painful at best, and not something easily fitting into our timescale.

The simplest solution is to throw the asset blobs onto the filesystem – filesystems are after all developed to handle tiny little files. Directories will slow down when there is more than about 3,000 entries within them, so we’re breaking storage up into “/b1/b2/hash.blob” – this means assuming an even distribution, approximately 30 files per directory at current size, scaling us up to a capacity of 100 million assets before we need to rethink the situation.

Distribution and redundancy are both things I am still keen to employ – putting us on the filesystem does allow us to look at things such as KosmosFS which provide transparent distributed filesystems on Linux, and also gives us the opportunity to look at commercial filestores down the road if we ever win the lottery.

Rewriting fragstore to use filesystem components where voldemort was employed took all of an hour and the asset converter was up and running – a lot faster too. Our conversion transfer rate on Voldemort was 66 assets per second. FragstoreFS?

10,000 Assets Processed (102.04 asset(s)/sec): 0 error(s) so far.

The second thing I wanted to test was just how big a savings we were getting from using Content Addressable Storage – with 10,000 processed, we ended up with 613 duplicates eliminated (6.1%). With 20,000 – 1390 (6.9%), with 90,000 – 8962 (9.95%). We’re hoping as the full dataset is processed – the % of duplicates eliminated continues to increase.

Fig 1. CAS Duplicate Savings

The next issue to present itself was a slowdown as the conversion occured – the number above (102.4) held firm for the first 10% of the conversion, then conversion speed began to massively taper off, first down to 71.39/sec, then down to 50.22/sec by 150,000 converted. My fears were a reprise of the situation we thought fixed on day 1 – slowdowns on accessing as we move further down the table.

Nebadon suggested that this infact might be actually because as OSGrid has become more popular the average size of an asset has increased over time – so we skipped a million rows down the table and started converting some of the later rows. Conversion speed? 68.31/sec. This indicates that yes, later assets are more expensive to process – but the conversion speed should still average the 60 or so per second we need to be able to convert the entire database in under 24 hours.

Appearing somewhat happy with the results, conversion on the complete database has started, but we wont know how well it has worked until tommorow.

Day 3: Stay tuned!