Using The Rails MemoryStore Cache to Store ActiveRecord Objects in Development Mode

Despite the length of the title up there, wanting to use the Rails cache to store an ActiveRecord object isn't something that's too advanced or unreasonable. It makes sense to store relatively static objects in some kind of cache to save a round trip to whatever is actually storing them proper; since that store is usually a database of some kind it's likely that these static objects will be based on AR seeing as that's the default ORM doohickey in Rails.

That said, I'm not actually using proper AR models. The data for my models is coming over the wire from a magical place that has no databases, so in theory I shouldn't be using AR anyway. However a lot of non-ORM good stuff comes with AR (like validations and other convention-based magic), and seeing how some clever chap went to the trouble of writing a plugin to allow us to use "tableless" AR models I couldn't help but oblige and use it.

But I digress. This post is about what happens when we try to cache said AR object using Rails' built in cache functionality (when the cache is based on the hash-like MemoryStore). In short, the answer is a terrible disaster of immense (and typically agile-like if I'm allowed to make such a dig) proportions. Here's the technical bit: any instance methods you define on your AR models get lost when you retrieve them from the cache.

What the heck? Well, let's create a simple example. We'll start with a model representing a person - one who has a first name and a surname as fields/columns in the database. AR is magical in that it will look at the DB and create accessors for first name and surname for us. However we want something slightly more than that and so we define a method that will return a concatenation of the two. We'll call this method name, but I'll spare you the code.

Now we only have ten people in the system, and since these are guaranteed not to change in the lifetime of the application it makes sense to cache them after retrieving them from the DB. Let's ignore the fact that we could define them in the application itself (in my opinion, data belongs in the DB no matter how static it is). So let's store it in the cache:


all_persons = Person.get_all_persons
all_persons[0].name #returns a concatenated name
Rails.cache.write("all_persons", all_persons)


where get_all_persons returns a simple array of persons after getting them from the DB.

A Rails request later, and instead of calling get_all_persons we use the following:


all_persons = Rails.cache.read("all_persons")


Et voila, we once again have an array of all persons.

Except when we try the following something goes wrong and:


all_persons[0].name


results in:


NoMethodError: undefined method `name' for #Person


But... but my source code says the method is still there. What's gone wrong?

The trouble stems from how Rails loads classes. There is another (magical, naturally) option which tells Rails to watch source files and reload classes if any of them change. This is handy for development, since it means you can make a change and see it without restarting the Rails server. The way in which Rails does this magic is even cleverer - it literally reaches into the code of the class and modifies the methods which it has on offer. Amazing, no?

Well maybe. See, it's this exact mechanism which first strips an AR class of all its instance methods (something put in to deal with a memory leak) and then reload them later with the same magic used to create accessor methods. Except, for some reason, it misses out the ones defined explicitly like name, above. Yes, I was just as bemused.

But let's get our story straight; there are a few red herrings and caveats here. Firstly, this has nothing to do with Rails.cache - that just acts as a mechanism to keep objects around long enough to have their classes redefined. New objects created in the lifetime of a request use the original, and so correct, class definitions - which is why name gets called correctly the first time. No, it's Rails' class reloading (or rather, redefining) mechanism which is at fault here, or rather the way it deals with reloading AR classes.

Knowing this much presents us with a few options. The first and most obvious is to disable class reloading - indeed non of these problems occur in the predefined test and production environments where class reloading is disabled (or rather class caching is enabled). This would make development a pain though and take away one of Rails' biggest pros: it's facilitating of rapid development.

The second solution is a bit more sneaky. As we've discussed, freshly created objects in a request are loaded fine, and so if we somehow recreated the objects in the cache (perhaps via a copy) and use those, everything would be hunky dory. There is actually a more straightforward way to do this, and that's to use Marshal to serialise objects coming in and out of the cache; unmarshalling creates new objects to load up the data into. In fact since other cache stores (memcached and filestore) already do this marshalling by default (since they have to, whereas MemoryStore can just hold objects, well, natively), we don't see this issue (albeit by accident).

There are drawbacks here. Marshalling brings with it an unnecessary overhead - the marshalling isn't actually required, we just want to use its implicit "newing" behaviour. This means that when you push the code to test and productions, we're not doing things as efficiently as we could be (remember, we don't reload classes in those environments).

For now, I'll be marshalling explicitly when using a MemoryStore cache. Perhaps if I move to another cache I'll remove this redundancy, but for now I'll leave it in if it means I get to keep Rails' reloading of classes. Oh and for more info on this behaviour, see here.

Oh and before I forget, yes, I am now using Ruby on Rails to further my project. This flies in the face of a previous post but I don't mind contradicting that. I've not looked back since moving to Rails, but more on that in a later post.

Picking a Python Web Framework

A big reason I decided to work on this current project of mine was to learn new things. For that reason I had pretty much settled on using Python on the web server side - it's a programming language with lots of support and mindshare, and happens to be pretty fashionable right now too (which probably means it won't be once I get to grips with it).

There are many frameworks for creating Python-based web applications. After reading up on them all the two I narrowed my decision down to was the ever-popular Django and TurboGears in its latest and bleeding edge version 2 guise. Since I had come as far as I could merely reading about them, I decided that the best way to proceed would be to spend some time actually playing with them.

I first played with Django. I have to admit that it was a pleasure to use: the tutorial made sense, everything installed and worked first time, I felt that I was learning and could imagine how I could leverage it to do what I wanted to do.

TurboGears 2 was a bit more difficult to come to grips with. The documentation was out of date, the install broke a few times and I had to join an IRC channel to get some help and I felt like it was a general struggle to get to grips with. That said I could see how it offered more potential in the long run, having including JSON libraries, widgets and AJAX support.

This pretty much reduced my decision to the following question: do I want to to get on with things now simply and easily only to possibly reach a head at a later date, or invest in something with a steep learning curve that will allow me to do almost anything I wanted?

With time at stake I've decided to go with Django. I think I'm at a stage in my project where progress in the short term is more important than in the long term and I also think it's important that I'm actually enjoying what I'm doing. That's not to say I don't appreciate either - in fact there would have been a time when I would have relished the learning challenges TurboGears brings... Perhaps I've just become lazy in my hey day.

EDIT: It seems that there is a third option; that of Ruby on Rails. Ooo. Time for another tutorial I think.

Choosing an RPC Mechanism

In my opinion it's always worth separating components on an application level. Rather than developing libraries to be used locally in an application domain, I imagine software to be built up of many "serverclients" communicating over TCP/IP. There are some pretty compelling benefits in "serving" this functionality instead:

• You can scale particular parts of the overall application as required.
• You can plug and unplug components without too much hassle.
  
• You can genuinely use disparate platforms (as opposed to when you're really compiling them all to the same low level language).
• You have separation, isolation and decoupling built into your design and implementation.

Of course none of this comes for free - the main drawback come with the implicit use of some kind of remote procedure call, or RPC, mechanism. RPC is a generic term describing the manner in which these components would talk to each other and since it's inherently something not "native", it generally brings with it issues of interoperability (you can't use a language feature of a remote API if you don't support it yourself) and overhead (due to the transporting of the actual procedure call and its results).

Still, I think it's worth looking into anyway for the benefits. For my current project I've looked at a few RPC mechanisms:

• Java RMI. Nails the remoting bit on the head but at the cost of interoperability. Although I'm coding most of my components in Java I feel RMI doesn't help with what I want RPC for. It would require the least amount of effort though since RMI would know exactly how to serialise and transport my Java classes. Everything else will need to be told.
  
• CORBA. Standard, established and interoperable. But totally nasty, so no.
  
• Webservices. Promising: again established across mutliple platforms, but not really lightweight though - it requires a webserver for a start and further uses XML (something I've never really seen the advantages of in cases like these) resulting in lots of traffic for the simplest of things.
• JSON-RPC. An RPC mechanism based on the relatively lightweight data-interchange format JSON. This solves the XML problem seen in webservices, and since it can run over a tcp socket it should be pretty quick too. It's new (and thus cool) so there's lacking support for it so far.

I've chosen JSON-RPC and am using the jAbsorb library to implement it. I've ended up using it over HTTP though and this requires a webserver (which I wanted to avoid having to use) but the embedded jetty webserver isn't too bad. As mentioned I'll have to create some custom serialisers to transform my classes into JSON (and back again) but that shouldn't be too difficult.

A Union Algorithm For Sorted Lists

To union two sets is to create a new one which has all the members of both. For example, using a list (or technically a set since there aren't any duplicates) of numbers:

[1,2,3] U [2,3,4] = [1,2,3,4]

Let's say that we have two Hashmaps, L and R, that we wish to join into a larger collection. By definition, the collections of keys on these Hashmaps are sets, and so a union operation would be perfect to construct the new larger Hashmap. In practice this is simply achieved by adding each of the items in R to L at cost O(1) each time, handling collisions as appropriate (by implementation default you'd take R's, but there's no reason why you can't handle that differently). To create a new set from Hashmaps of size n and m respectively, this takes time O(n+m), O(n) to add L's items and O(m) to add R's.

But since we're all now using sorted lists for our indexed item needs, the cost of inserting is now O(log n) and so the algorithm above results in a total time of O(n log n + m log (m+n)). Not great, but perhaps there's something we can do to better this.

The trick is in leveraging the sorted property of a sorted list. Imagine if we had two piles of sorted cards in front of us - it would be easy enough to create a union of these by repeatedly taking the lower value card off the top of each and placing it in a new pile, while chucking away any duplicates. Since we only go through each list once, we would do this in O(n+m) time. Super! And again, as a bonus, the resultant set would have been sorted by key.

Although it's a straightforward enough algorithm translating the above to code can be tricky. What follows is some pseudocode which tries to nail it; ListL/R represent the sorted lists, while IndexL/R are pointers (integers) to items in the list:

if ListL.Size is zero, then return ListR;
otherwise if ListR.Size is zero, then return ListL;
otherwise
  create an empty ListU for the results;
  set IndexL = 0; set IndexR = 0;
  while (true)
    if ListL[IndexL] is the same as ListR[IndexR]
      add ListL[IndexL] into ListU;
      //or ListR[IndexR],
      //or manage the conflict.
      increment IndexL; increment IndexR;
    else if ListL[IndexL] is lower than ListR[IndexR]
      add ListL[IndexL] into ListU;
      increment IndexL;
    else if ListL[IndexL] is higher than ListR[IndexR]
      add ListR[IndexR] into ListU;
      increment IndexR;
    if IndexL is greater than ListL.Size
    or IndexR is greater than ListR.Size
    //invalid indexes
      quit the loop;
  restart loop
  if IndexL is still pointing to a valid item in ListL
    add the remainder of ListL to ListU;
  if IndexR is still pointing to a valid item in ListR
    add the remainder of ListR to ListU;
  return ListU
end;


Still not convinced? Well for completeness, let's prove that this algorithm actually works. The parts outside the main loop can be proven trivially; the union of two empty sets is empty, and L U Empty is L too.

For the final two cases we can rely on the following observation:

if max(ListU) < min(ListL) then ListU U ListL = ListU + ListL

This is true since it's guaranteed that there will be no items shared between U and L.

Which leaves us with the loop proper. For this, we can use induction; the above prove the trivial or base cases, so let's just consider when we're in the thick of things:

Let IndexL and IndexR point to somewhere in the middle of ListL and ListR respectively. Assume that the loop has been working correctly so far: so all lists are sorted, last(ListU) < min(ListL[IndexL], ListR[IndexR]), and ListU is the union of all items already seen. Let's call these observations the inductive property - we're going to go through the loop and see if they all still hold.

Now, three things could happen. Let Index+ denote an incremented index:

1. ListL[IndexL] = ListR[IndexR]. Here we add ListL[IndexL] to ListU, and increment both Indexes. By definition of our sorted list and its unique keys, ListL[IndexL] = ListR[IndexR] < ListL[IndexL+] and ListL[IndexL] = ListR[IndexR] < ListR[IndexR+]. Since last(ListU) = ListL[IndexL], last(ListU) < ListL[IndexL+] and last(ListU) < ListR[IndexR+], or last(ListU) < min(ListL[IndexL+], ListR[IndexR+]). Since ListL[IndexL] is greater than all the items originally in ListU, ListU is stil sorted. ListL and ListR are still sorted, trivially. And finally, since ListU now contains ListL[IndexL], it's the union of all items seen already.
2. ListL[IndexL] < ListR[IndexR]. Here we add ListL[IndexL] to ListU, and increment IndexL. By definition of our sorted list and its unique keys, ListL[IndexL] < ListL[IndexL+]. Since last(ListU) = ListL[IndexL], last(ListU) < ListL[IndexL+], and so last(ListU) < min(ListL[IndexL+], ListR[IndexR]). Since ListL[IndexL] is greater than all the items originally in ListU, ListU is stil sorted. ListL and ListR are still sorted, trivially. And finally, since ListU now contains ListL[IndexL], it's the union of all items seen already.
3. ListR[IndexR] < ListL[IndexL]. Similar to the above case, except for ListR instead.

In all three cases, the inductive property holds. Hence when loop exits, ListU will be the union of all items already seen.

Phew. Still here? I'd congratulate you, but I'm wondering exactly how bored you are.

Nevertheless you might also be wondering what the whole point of the above is. Although what we want to do is simple enough, it's sometimes good to think about why an algorithm works, if anything to back up the original hunch that it does. Conversely, if you struggle to prove a simple algorithm, that might just indicate that it's not quite as "obviously correct" as you think it might be. Now that I've proven the union algorithm (albeit informally, since there is still a whole bunch missing) I can remain confident that it actually does what it's supposed to.

When Are Sorted Lists Better Than Hashmaps?

Maps are wonderful things. Unlike arrays or lists, they are keyed and so allow O(1) access, which means their size doesn't affect how long it takes to retrieve a specific (that is, known) item. In an array (of key/value pairs) you'd have to check each and every item for what you're looking for (which is in O(n), or depends on the length of the list).

This can be improved upon if the array is sorted in which case a Binary Search (check the middle value and see if what you're looking for is in the first or second half and then repeat) which would give an access time of O(log n). Unfortunately sorting takes some effort too - either by searching for where to insert a new value (using binary search, above) in which case you'd also have to worry about shifting existing values up a space (if we've implemented using an array, say) or sorting on retrieval (which then only has to be done the one time after each insert). Either way, sorting takes a massive O(n log n) time.

So to recap:

Hashmap: Insert O(1); Retrieval O(1)
Sorted List: Insert O(log n); Retrieval O(log n)


Hmm. It's not looking very good for a sorted list then, is it?

There are issues with maps though. First up, their access speed comes at a cost of space - generally to manage the keys and values store. This may not be an issue for those of us with plenty of memory, although for enterprise applications dealing with millions of items it may be something to think about.

Keys in a hashmap also need to be hashable, or in other words reducible to a unique (or at least relatively rare) number or index, and this can be a hard problem if keys are technically very similar. Having said that, we can define our own hashing functions if we know what kind of keys we'll have.

But there is another use of a hashmap where it doesn't do as well as a list would, and that's in iteration. Although traversing items takes O(n) time in both hashmaps and sorted lists, the straightforwardness of a list will make it faster in most, if not all, cases which require processing of each item in it.

Finally, generally in the real world talking about big-oh or O(n) time can be a bit misleading as the hidden constants involved may outweigh the benefits taken from a good algorithm. So if it takes ages to figure out the hash of a key, it may just be worth a binary search instead (provided the cost of comparing is low too).

So to recap, although a hashmap is generally the best way to store data you'll need random access to, there are times when a sorted list might be better. Things to consider are:

1. The number of times you'll be randomly accessing items in the collection.
   
2. The amount of available space.
3. The cost of hashing a key versus comparing them during a binary search.
4. The number of items being stored (a binary search on a hundred items won't take very long).
   
5. The relative staticness of the data - if a list isn't added to after initialisation, it only has to be sorted once.
6. The need to iterate through each item of a data structure.
7. The need to have instant access to a sorted view of the data.
   

In my current project, I will be using regular hashmaps for static and globally accessible caches, and sorted lists for those situations where a collection will be repeatedly created (to save space), regularly iterated over, and rarely randomly accessed.

Going Back To Java

I wrote some Java today. A whopping seven years after I last did so.

Java was pretty much what I cut my programming teeth on (well in a production sense at least - good ol' Haskell was what I had really lost my virginity to), it being the academic flavour of the month during the 90s. Dotnet was it its infancy, C# some theoretical beta language that Microsoft were planning to take over the world with.

Java was a wonderful language to work with back then, despite the awkward graphical user interfaces (does Swing still exist?) and runtime considerations. It was one of the first mainstream languages to place as much importance on a complementary framework as it did the language itself - for many it wasn't the portability that made it so popular but the brilliant and complete class library. Conversely C++ is often closed off to newcomers not because of its syntax or memory semantics but due to a lack of any real standard way of using it.

I wrote my final year project in the language and had a paper published based on that work. I thought Java would be my future and threw myself at it. But it wasn't really meant to be.

Since both my jobs were total Microsoft houses, my commercial programming experience to date amounts to around seven years C# and dotnet, apparently an impressive amount given the age of the language. Still, that rich experience cost me any proficiency in Java I had and although I sincerely believe that a good programmer can code in any language, I did find it a shame that I didn't ever have a commercial use for it.

Until now of course. My insistence on making this project as cheap, open and standard as possible means no Windows; and no Windows means no dotnet.

Frankly after so much exposure to dotnet I was expecting to be thrown back into the stone ages with anything else, but seven years is a long time in the land of computing and I was shocked to see how far Java has come. The scene is now awash with a frankly confusing number of swanky IDEs, application frameworks and servers, many open source and free. Community support, opinion and mindshare is also high, at least as much as with the equivalent Microsoft technologies.

In fact I spent most of today not coding but getting my head around the dazzling range of options that comes with the platform (and if anyone is interested for now I've settled on Glassfish for an application server and am looking at the usefulness of the Spring framework in my work). And I've not even looked at any of the new language features. Dependency injection? Oh yes please.

I'm actually pretty excited, especially after the underwhelming experience I had with MySQL. Even the obligatory "Hello World!" application I just had to write had me grinning like a kid.

Using STRAIGHT_JOIN To Work Around The Optimiser

As explained in a previous post, I had an issue with an overzealous MySQL optimiser, which then decided to return incorrect results after I had chastised it. I was therefore stuck between incredibly slow (read: unusable) performance and unreliability and had no choice but to work my design around the behaviour.

I had already come across the STRAIGHT_JOIN keyword, but only as used as a replacement for a more regular JOIN. The idea is to tell the optimiser to join the tables in the order in which they appear in the query. On its own, JOIN is both commutative and associative, in that you can do them in any order. Left to its own devices the optimiser may choose to take what you have written and turn it upside down if it thinks the whole thing will be faster, but STRAIGHT_JOIN instructs it to stick to how it's written.

Outer joins, however, aren't commutative and you'd most likely get different result if you reversed one and for this reason the optimiser will never try to. Since the view that I was having difficulty with was made up of three outer joins, I didn't bother looking into STRAIGHT_JOIN much. Sure, I could have applied it to base views in order to cut short the work the optimiser had to do anyway, and this probably would have helped in this particular case too. But since the base views seemed to be working okay in other cases I chose to avoid doing this - I may as well take advantage of the optimiser when it works as it should do.

What I didn't realise was that you can selectively ask the optimiser to STRAIGHT_JOIN on demand. You do this by calling it during the select itself:

select STRAIGHT_JOIN a,b,c from rest-of-query;

This instructs the optimiser to STRAIGHT_JOIN as much as possible while evaluating the query. Since this implies one path, you don't have to adjust OPTIMIZER_SEARCH_DEPTH to restrict itself globally either - it can be left at 62 (or whatever you found it useful to be) and should, in theory, continue to optimise wheres and other parts of your query.

So my strategy is now the following - to write and use views as I am currently, with some consideration on what order to join the tables/views within them in. If ever, when consuming a view at a higher level (say, in a procedure or function), I find them to take more than a few seconds to complete, I will add the STRAIGHT_JOIN keyword to effectively disable join optimisation but only when that particular procedure or function is called.

Hopefully this will allow me to take advantage of the optimiser when I can, and tell it to bugger off when it can't quite handle the job it's supposed to. The only flaw in this plan is if STRAIGHT_JOINing results in a sub-optimal execution plan and makes a query slower than it could have been (albeit with a correctly functioning optimiser). That's something I'll have to leave to later, when I have a substantial amount of data to play around with.

Error Code : 2013

Another day and another issue with joining complex views. However where I solved the previous problem of slow queries by instructing MySQL to not spend too much time looking for an optimal execution plan, this latest issue seems much more grave.

When trying to execute a join I immediately get returned an Error 2013, "Lost connection to MySQL server during query". This occurs whether I run it on a client or server. Changing the query slightly to omit part of a where clause (simply an is null check) causes the query to run correctly. I have a hunch that this is related to my complex views once again. The fact that even the EXPLAIN returns this error is pretty worrying.

Checking the syslog on the server, it appears that MySQL is immediately crashing out. Unfortunately I don't have much more to go on since the promised stack backtrace (used to understand where in code MySQL was when it went titsup) doesn't appear in the log.

No answers in the community - although since I suspect that this a unique error I wasn't expecting much. It seems my only real option is to debug MySQL. However since this requires recompiling the whole thing from source, this solution is probably best left for a time when I'm not as tied up developing (so probably never). Again my confidence in MySQL is such that I'm sure I'll have to face this issue sooner rather than later so I'll have to do the recompilation eventually.

In the meantime I've managed to come across some pretty interesting behaviour regarding optimizer_search_depth. Setting this back to 4 (from 0) does allow the same query to complete, albeit with an incorrect result set (I'm left joining a table with one row on its own and end up with zero results). 62 may work better, but since that still makes the query unbearably long, I've not checked this. Recompiling the view also changes the behaviour of the query. It's all terribly inconsistent and bizarre.

So as it stands I have a choice between crashing the server, completing with incorrect results, or spending an hour waiting for correct results. I'm certainly not impressed and for the first time am wondering if MySQL is the best choice for this job (or indeed any other that requires a database!). I'm even considering switching to MSSQL (and so Windows with it), something I never hear other people having to do.

For the time being though, I've decided to become a bit more pragmatic about MySQL and its features. I now accept that the ONLY reason I'm now using it is because it's free: I've certainly never managed to crash MSSQL, and I've definitely found the community support for it to be better than what I've seen of MySQL's.

So now, instead of complaining about how rubbish MySQL is, I'm going to accept its flaws, use the workarounds given to me and create my own. Yes, my database's design may suffer for it, and yes code written against it may have to be ugly, but I'm guessing that this is the MySQL/OSS way of getting things done.

This isn't all as fatal as I'm making it sound though. The way MySQL works has also forced me to reassess the bad parts of my design and so has prompted more positive changes. For example, there doesn't seem to be way to pass around table results in MySQL, be it via selecting the results of a proc or allowing a function to return variables (in fact there's no table variables at all here). I've had to use scalar functions in a more creative manner instead, which in turn highlighted some inefficiencies in my code that once I saw was able to massage out.

In short, I'm hoping that the progress I make will distract me from the irritations the platform is bringing me.

My Initial Impressions Of MySQL

Ah. MySQL. The M in the almost ubiquitous LAMP stack that the Internet seems to be running on nowadays. Apparently the fact that it's free is supposed to be a bonus to how it's a kick-arse database system. I'm sure some of you already know what I think about that - for the rest let me list some of the things I've found with MySQL.

First up, it's how MyISAM tables don't support foreign keys. That, in my eyes, immediately discounts MyISAM as a choice for any kind of RDBMS (that R stands for relational by the way). Luckily MySQL allows tables of different types like the InnoDB tables I'm using which do support foreign keys. The fact that MySQL supports different tables is a good thing even though it exposes the inner workings of the RDBMS to the developer.

As I explained in a previous post, MySQL doesn't seem comfortable dealing with views, something that has already severely bitten me in the bum. It also doesn't support optional or default parameters in procedures, which means the developer has to be a bit clever in how they write procs in order to not end up with a whole bunch of overloaded ones.

But there are some good things too. Procedure calls can now be nested, which is superace. Statements are delimited which is a nice cosmetic feature. It offers AUTO_INCREMENTING columns as a typical primary key, and I've made heavy use of them while raising my DB to 6NF. Joins can be written using the USING keyword to join on similarly named keys. Oh and you can now increment variables inside a select list - useful for counts or groupings.

I do miss MSSQL quite a bit though, especially the Query Analyser. And that's something I never thought I'd admit to! SQLyog is pretty neat, but it does have its annoyances: poor tab support for one, and a total lack of file drag and drop for another. There seems to be a few bugs when scripting the database, although this seems to be a problem with the server rather than the client itself. Intellisense is nice (when it works!).

So a bit of a mixed reaction then? Well yes. To be fair the only thing that's really annoyed me is the pitiful support for views and the fact that I've come across it so early in my work; I can imagine it and other other similar operational issues rearing their ugly heads later on. The rest is just MySQL's way of doing things and something I'll just have to get used to.

Why Open Source Software Sucks

Most of us should have used open source software by now. Firefox, for example, is pretty huge and used by loads of people, most possibly not even aware that it's OSS. And I'd say that a great deal of software I use has been downloaded from SourceForge, one of the biggest (if not the biggest) repositories of OSS. It's not about it being free either - Internet Explorer is just as free as Firefox and yet it's the latter that's proving to be comparably popular. It's clear that OSS is a good thing for obvious reasons.

Or so I thought. Around 60% of my time on this project so far has been wrestling with the quirks of the major bits of OSS that I've been trying to use and I like to believe that this isn't just because I'm not accustomed to it ("you just need to get used to it" is the top reason given for my pain). Although most of my troubles have been with MySQL (enough to warrant a whole new post after this one), I've also had some issues with Ubuntu.

First up, it's all so poorly supported. I've found that the "community" that people often rave about to be a bit of a myth. The community doesn't "just" answer your questions for a variety of reasons: either they're too easy, too obscure or not sexy enough. Either way it's a bit of a struggle to get a direct solution to any problem you may have - you have to find your own answer by piecing together scraps you find all over the place. Still, I've found newsgroups to be more helpful than web forums, although only just.

Related to this is the poor documentation. So much knowledge, often basic, seems to be assumed. This means you can't even get started since you're unable to equip yourself with the fundamentals. For example, even after installing quite a few applications on to my Ubuntu build I'm still buggered if I know where a particular application has been installed, keeps its config, or what the heck the etc folder is for. And I can't even find these things out.

Other things seem to be hallmarks of some OSS. Its incomplete (you sometimes have to resort to a text file to edit something because a config screen hasn't been updated), inconsistent (file and configuration locations) and arbitrary (for the same application some things will be in a start menu while others will have to be sudo'd in a terminal).

On the other hand Ubuntu and MySQL at least seem to be just as stable and responsive as anything commercial I've used, sometimes even less so - for all its criticisms I've had to reboot my Vista install less than my Ubuntu, while applications take just as long to start up on each.

Okay, I'll admit that a lot of this does indeed have to do with my not being familiar with the tools I'm trying to use. Maybe I've forgotten the pain I went through learning to use Windows or MySQL. But then on the other hand I have to learn something new each time I use a new application - there was no esoteric quirkiness when using Firefox for the first time, for example. And learning isn't free, it costs time, and I expect some kind of return for that time. So far I've not seen any with Ubuntu or MySQL.

No, the only real benefit I see is with cost - the lack of commercial licences for OSS makes it a very appealing base on which to build a technology business. It's just a shame that the general principle of getting what you pay for seems to hold fully with OSS too.

Hitting Developer Traction

I finally feel that I've found some kind of "developer traction", that state of mind in a project where you know where you want to be and so can more easily code toward it. You see, good code mostly writes itself as the demands of a system become more clear and defined - formally we're even taught to write code from a pre-considered and thought out design. In practice however, time and interest usually makes us jump the gun a bit code blindly.

The start of a project when everything is so abstract and fuzzy in your head can be a hard time, especially for someone like me who needs all the answers to all possible questions before he sets out on a task. Those that know me will see this overbearing delay in getting anything started as one of my general characteristics/flaws and perhaps I need to get over this fear of making mistakes, trying things multiple times or throwing away first goes. Perhaps two steps back is sometimes the fastest way to move one step forward?

At this point I reckon I've spent way too much time on planning things, both technical and business-wise. I'm sure this planning was useful, particularly the technical specifications and designs I created, but even with those I've had to change as I code quite a bit as the process of development forced me to consider situations I never would have at a theoretical level.

Getting on with things also, perhaps obviously, helps with progression itself. For example I was struggling to think of what access procs I'd need for an API I had yet to write - I mean how can you possibly figure out what functionality a client would need? Well it is possible, just a bit difficult, especially when working alone. The answer is to write a client yourself and and create an API "on demand", that is to work on the hierarchy vertically rather than horizontally. What usually happens is that a single high level requirement sparks off the creation of a library that would be useful for other higher level requirements. From now on I'm going to try to keep as high level as possible when describing goals and targets, and leave the details to be determined practically. I wonder how long I'll last?

Anyway, since I'm not worrying about progress as much as I was before (I'm even playing MGS4 while working!), I can start talking a bit about what I've found so far. I can already sense the eyes glazing over for the majority of you and I apologise in advance for that. But I think it'll be helpful for me to put it all down (as well as the two other people who actually read and enjoy my computing posts), and I'll try to make them vaguely interesting to read if I can.

Confusing The Hell Out Of The MySQL Optimiser

Since I'm more academic and theorist than implementer, I made the decision to use as many good principles as possible whilst designing the database underlying my new project.

"Good" is subjective of course, but in this case, amongst other things, it meant placing the database in as high normal form as possible (although since that's all terribly confusing I'm not entirely sure how high I currently am; somewhere between 5th and 6th I reckon), to not access any tables directly and instead use views-on-views and to only allow updates via stored procedure calls.

Most of this, although time consuming, is easy. Normalising data is trivial if you really know the data you're trying to represent, and writing update procs is as simple as writing the update statement itself. However it was the use of views which proved to be the biggest pain for me so far.

Now I must admit that I never took this approach in any of the work I did using MSSQL. For one thing I wasn't using a database of my own design: it wasn't completely normalised and so views weren't used. Secondly, it wasn't insisted upon in my place of work and so I never got into the habit. Still, I suspect that MSSQL would have no problem in the use of nested views.

MySQL, the database system I've used for the back end of this project seems to have big problems dealing with views, most of them to do with optimisation. Take my most recent problem for example. I have a complex view (where "complex" means it's been defined on further views) which for my test data returns a single row in 16ms. If I try to join this on itself using a single join criteria, I'll have to wait over an hour for any results to come back.

My first thought was that the join was occurring inefficiently - that table scans were happening recursively or something. Indeed the EXPLAIN for the query (used to display the execution plan for a query) indicated that keys were sometimes not being used in any of the joins (remember, the view itself had many joins within it).

This was a bit of a red herring though. For one thing, the EXPLAIN itself was taking an hour to return. For another, my test data was trivial with no table containing more than 10 rows. Even the slowest look up shouldn't take an hour to complete. Further research revealed that the optimiser was clever enough to skip using keys if table data is small, hence the results of my EXPLAIN.

So I turned to the server itself. According to its connection status screen, my query was stuck in the STATISTICS state for the bulk of the time which it was stalled. A quick search revealed this to be the stage where the optimiser tries to determine the best execution plan to follow. In short, MySQL spent an hour thinking of the best way of running my query, and then spent 16ms (or whatever) actually running it.

I'm not sure of the details, but it seems that some queries can confuse the optimiser so much that it ends up considering every possible order of any joins in them. I reckon that there were over 10 joins in all within my views, resulting in over 10! or 10000000000 possible execution plans. In a normal optimisation process, many of these would be rejected as being obviously slow, but just not in this case - most probably due to the number of views involved.

You can force MySQL to stop looking after a bit by setting optimizer_search_depth to a value lower than its (incredibly high in my opinion) default of 62. Setting it to 4 allowed my query to complete in 20ms or so. The down side is that the truly optimal path could be missed, something that could be a major issue once my data grows sufficiently. For now I'll leave it on its "auto" setting of 0.

So an unlucky bug then? Perhaps. It's worrying though, since I don't believe that what I'm doing is that advanced or undocumented - someone should have had this problem before, and it should have been fixed then. More scary is the advice I was originally given when asking for possible solutions to my problem; that was to get rid of my views and access the base tables directly. Such advice totally misses the point, and I'm hoping bad design won't be a prerequisite for using MySQL.