Jeff’s Austin Blues

Amethyst quickly grows to be a CPU hog, even for a single user. I’m now working my way through speed ups. Database access has been tweaked a lot, yielding better than 10x speed ups in certain areas. (Bypassing ActiveRecord and JOIN tables for direct SQL and keeping the necessary data in the article to reconstruct the join on the fly. It works, I measured.) This was successful enough that rendering is now the bottleneck.

My first at fragment caching was by feed (each with up to 25 unread articles visible). The speed up was significant, a refresh was 3-4 times faster than the initial view. However, each channel is updated once an hour (1/12 of channels every 5 minutes). So the fragment cache quickly grows stale and is totally out of date in an hour.

The next stab was caching article fragments. Articles can persist for months and only need to be rendered once until some action is taken (click-thru, vote up/down, or hide, i.e. mark as seen but not read). The article fragment cache grows stale much slower. However, there are 12 times as many articles as channels. The speed up is less impressive, 2 times faster refreshes.

All in all, I’m sticking with the article fragment cache. (Note: all work has been done with the memory storage mechanism, essentially a hash.) I’ll be posting the details of how to cajole the 3 fragment caching mechanisms that don’t explicitly implement timestamp expiry to do it anyway (mem_cache already does). I tried posting the whole mess earlier and WordPress was barfing on permission problems. Hopefully it can handle smaller bites

I’ve been chasing a bug in Amethyst for over a week. Sometimes accented characters cause problems, sometimes they don’t. MySQL will often complain about a duplicate key in a multi-row INSERT. The INSERT is correct, but the duplicate key doesn’t appear in any of INSERTs! With enough examples I figured out that they are all the prefix of the key from one of the INSERTS, up to the first accented character. But not all accented characters cause problems, though all accented characters on one feed do cause problems. I’ve been tracing the incoming data starting with the data on the network and working my way through the system.  In  chatting with Greg Foster of  the Consumers Union at the Lone Star Ruby Conference, the topic came up and he mentioned that in spite of various feeds proclaiming that they are UTF-8, sometimes they contain Latin-1 characters. He said he had a conversion routine in Ruby he’d send me. Sure enough, I looked closer and there they were! Depending on what I used to look at them, they might render as expected, or as a ‘\361′ sequence.

Later in the conference I became impatient to fix the problem and Googled “Latin1 conversion UTF8 Ruby”. At the top of the list was
How-to fix ruby’s FeedTools latin-1 parsing. There is a bug in FeedTools, it converts numeric HTML entities under 256 to Latin1 characters instead of UTF-8 characters.  The blog entry includes some code to monkey patch FeedTools to correct the problem. I dropped the code in, deleted the corrupt data, refreshed the feed, and voila, problem fixed.

In the previous post I discussed how on the way to a planned re-implementation in C of some database access, achieved a nearly order of magnitude speed up by bypassing the Ruby on Rails ActiveRecord database access class and generated the SQL myself. Faster but still some performance problems. Benchmarking, profiling, and online research turned up hints that relational database joins are powerful, but slow. So I spent some time thinking about how to speed it up. There are several approaches, some I found in DSPAM, some I found online, and some that came to me in the middle of the night.

The associations are created (and destroyed) all at once. Rather than store them as many records in the database, how about one record, or even a field in the article record/row itself. DSPAM does this, and it is fast enough. Doing it in pure Ruby/Rails would involve marshaling an array or hash of string and integer pairs. Since Ruby allows the elements of an array (or hash) to not all be the same type, type information is required for each value. This could be slow. In this case all elements are the same, and the information is already in the item record, the article description itself. Can Rails/Ruby regenerate the information on the fly faster than it can read it from disk? The answer is yes. If I think outside the database and the usual way of doing things.

Associations/join table typically contain the record ID (the primary key by convention/default in Rails) of both sides of the association. But what if instead of using the ID as the lookup key, the word/token string itself is the lookup key. In MySQL, my benchmarking found that string lookup is approximately 10% slower than integer key lookup (both with indexes). With string lookup, the join table can be discarded completely.

When optimizing it must be kept in mind what is the expensive/scarce resource(s). For many projects, it is the programmer’s time (i.e, programmer salary and overhead, or time to market is the scarce resource). However, user’s time/patience must be kept in mind too. Moving away from DSPAM (in C) to a pure Rails and ActiveRecord solution put the latter on the critical path.

I have made the simplifying assumption that as few database calls as possible is a plausible way to go, keeping in mind that large joins are also expensive. With the string indexed lookup I eliminated the join table. By accumulating all changes and rolling all inserts into one SQL INSERT statement, all updates into one SQL UPDATE statement, I gained almost as much speedup as the first optimization. The combination is around 16 to 50 times, depending whether you measure elapsed time or CPU (times in seconds):

Better than I expected. Maybe it’s time to stop bit-twiddling and get back to adding features.