Monday, May 28, 2012

MySQL Replication Challenges

If you've got a server running MySQL that's creaking under the weight of database reads, the obvious solution is to set up replication.  MySQL replication seems to work fine, but one thing I didn't appreciate at first is the changes that probably need to be made to your code (and not just changing which server it reads data from).

I imagine that most web pages, if they're anything like mine (oh dear) implicitly rely on the database being updated immediately.  My web pages usually have a similar structure:-

1: Read any get/post values and update the database
2: Display the new updated data as part of the web page

This seems fine, and it is if your database is updated immediately (since stage 2 is reading the data that has just been changed by stage 1).  If you're using replication, then you'll be updating the master database in stage 1, but then reading the data from a slave for stage 2.  However, if the slave hasn't updated itself from the master by the time stage 2 is running (and we're probably only talking millionths of a second) then stage 2 will display old data, and your users will cock their head to side wondering what's going on and why the system has ignored their commands.

Another similar problem I experienced is with an automatic routine that transfered data from one table to another.  For various good reasons, this routine would read the first 100 records (from the slave, obv), append them to another table, and then delete the originals (on the master).  And this worked fine until there was a small glitch in the communication between the master and slave.  Glitches in communication aren't normally a problem since you can specify how long the master retains logs for, so you can turn a slave off for a few days, turn it on, and it will (eventually) update itself.

However, due to the glitch, the slave wasn't updating itself.  It read the first 100 records from one table (on the slave), wrote them to another table (on the master), and then deleted the records from the original table (on the master).  Since it was reading the records from the (non-updating) slave, the next 100 records it read were the same 100 records as last time, because they were still there.

Being an automatic routine that runs overnight, this process was duplicating identical records at a hysterical rate, eventually eating up all the disk space and so the whole system went down within just a few minutes.  Oh dear.

So, in summary, replication technically works very well, but you can't just plug it in and turn it on and expect all your problems to be solved.  You probably need to think carefully about when to read from the slave or master, and what will happen if the slave is too far behind the master.

Thursday, May 17, 2012

Recording Videos of my Android Apps

If there's one thing I've learnt (or learned - which is it?) over the last month, it's that people like to be able to see what an Android app looks like before they hand over any hard cash.  I recently created a video of a few of my apps: Chromakey Photo Edit, Worldcrafter and Stellar Forces.  And blow me down with a feather if sales and/or downloads didn't increase!

So how did I create the videos?  I do have access to two phones, both of which are capable of recording videos, so the obvious answer was to use one phone to record another.  However, keeping both phones steady while recording and playing was harder than I thought; the phone that is recording doesn't need to be held by a person, but it does need to be positioned between the player ("hello") and the phone I'm playing on.  Asking a fellow human to hold the recording-phone tends to feel a bit ridiculous as they have to reach around you and keep the phone super-steady, which isn't that easy.

The solution hit me like a birthday present when, for my birthday, I got a present that turned out to be a phone holder for my car.  Perfect!  Its suction cup held it securely to a table while I played the games around it.  The only challenge now is to make a vaguely entertaining video that's short but also shows off the best features of the games.  And also where my thumb isn't always in the way.

Here's the Worldcrafter one:-

Since the game involves tilting the phone, that provided an extra challenge for keeping the phone in-view.