Optimal Database Backups

published 31 May 2013

No one enjoys database backups. They usually involve a load spike and a lot of table locking (even in best-case scenarios) which can be felt on live servers. Some sites bring their services down to perform backups, others slow to a halt. It is a very important aspect of running an online service, and finding an optimal and elegant solution is usually very specific to the infrastructure and nature of services being offered.

I am very serious about keeping live backups. The osu! database is replicated to a slave server, providing a real-time fallback in case the main server happened to fail. This is already ample to handle any server software/hardware issues – for instance if a drive was to fail. This alone does unfortunately leave the possibility of human mistake – where the database is damaged internally – which while I’d love to say doesn’t happen, is generally unavoidable (especially when expanding the size of the team which is working with database access). In the case of a human mistake, both the master and slave server’s data is in a bad state, making slaves of this nature useless.

In the case of human error, my current backup solution was to take database snapshots from the slave server. This results in very minimal effect on the front-facing service, as user based actions rarely require a query to the slave database, but the storage requirements, the IO requirements and the general clunkiness of snapshots has always bugged me. It also means that as backups were only made once a week, in the case we needed to recover data it may be up to seven days old, which is not acceptable.

Incremental snapshots is one way to avoid this pitfall, but does require all database tables to be of InnoDB engine. I regularly test InnoDB (or in this day and age, XtraDB) but am still getting better overall performance with the arguably less reliable MyISAM, so this is not an option.

Introduce a delayed slave to the equation. This is a separate server which is initialised as a slave to the master database, but maintains a time distance from the live data. This is easily done using the Percona Toolkit’s pt-slave-delay, which runs as a daemon and allows specifying a period for which it should delay sql operations by.

There are a few amazing advantages here:

  • There is no added load to any of the live servers, apart from the network overhead of streaming binlogs.
  • It is a continuous backup. You can’t get better than this. No snapshots to worry about; only the assurance that you can always recover.
  • Because binlogs are always sent instantly, this slave instance can also be replayed to any particular point in history within the delayed duration. So if it is running 24 hours behind by default, it could be asked to catch up to 12 hours, or even removing the delay – making it a potential real-time backup slave in case of failures.
  • If you already have replication setup, initialising the new slave can be done with zero front-facing impact by using an existing slave as the point of initialisation.

To initialise the pt-slave-delay command, it’s as simple as ensuring replication is started, then specifying the delay and check interval. I am currently using the following, which should be run at system startup if you want it to persist:

#!/bin/sh
mysql -e 'start slave;'
pt-slave-delay --delay 24h --interval 5s --no-continue localhost

Take note that while replication is stopped, you will now be able to see how many seconds behind the server is using the SHOW SLAVE STATUS command. As I regularly use this for monitoring the slave delay, I had to use an alternative method to find the delay. For me, the easiest way was to select the MAX(timestamp) from a table with high activity and compare this to CURRENT_TIMESTAMP as follows:

SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP) - UNIX_TIMESTAMP(max(`date`)) AS seconds FROM `osu`.`osu_scores`;

I really enjoy databases and the optimisation of them at low and high levels. osu! is still relatively simple when it comes to database infrastructure but it is rapidly expanding. Keeping up with the increasing load is an interesting and very fun process. I hope to post more articles like this delving into the slightly more technical side of things going forward.

Update:

I just found out that as of MySQL5.6 (which I am actually running, so have switched to this method) you no longer need the pt-slave-delay script as this is built-in functionality. You can add a delay with one simple command (make sure to STOP SLAVE; first):

CHANGE MASTER TO MASTER_DELAY = 14400; --delays 4 hours

SUPER IMPORTANT NOTE: If you are delaying further back than the master has stored in binary logs, running a CHANGE MASTER TO like this will cause the world to fall apart, as it resets all slave relay logs. Make sure to carefully read the documentation – specifically:

CHANGE MASTER TO deletes all relay log files and starts a new one, unless you specify RELAY_LOG_FILE or RELAY_LOG_POS. In that case, relay log files are kept; the relay_log_purge global variable is set silently to 0.

p.s. I haven’t forgotten about the “this week in osu!” series, but some of the things i planned on writing about have been lost in my forgetful mind. I’ll try and knock one out along with the next public release, which I am hard at work on getting finalised. I am trying to livestream as much as I can, so if you are interested in the development of osu!, make sure to tag along and say hi in chat :).

comments

distraction-reduced iphone

published 17 Apr 2013

I recently tweeted about a life-hack article titled “The distraction-free iPhone (or ‘Why I’m happier since I disabled Safari’)”. The general gist is that our phones are taking over our lives, and acting as not only a procrastination device, but a distraction from what is going on around us.

“Checking email, checking Twitter, checking news. Wondering if something interesting was happening anywhere in the world. Wondering if anybody was thinking about me.”

Although I love how powerful our phones have become, this is definitely something I have started to feel over the last couple of years. It is especially disturbing when going out to lunch with fellow software engineers and only making conversation when someone comes across an interesting tweet, or an amusing image on instagram. This consumption of information is somewhat of an addiction, and while I tend to focus on consuming only what is relevant from the business side of things – I avoid the social aspects of twitter and similar services – am still guilty of indulging in it.

On reading this article I decided it was definitely a time for a change. Due to my commitments to osu!, osu!stream and puush, I knew that I could not completely lock myself out of services which brought incoming real-time communications which could require immediate response, so after some very deep consideration I began to form an action plan. I felt it was worth writing up exactly what I have decided on, and my reasoning behind each decision.

So, this is my iPhone’s only home screen:

my new home screen

Using Springtomize (a jailbreak tweak) I am able to hide not only icon labels, but any apps which I don’t want appearing on my home screen. I am also able to disable spotlight and pagination completely. Using this technique, I am able to hide Tweetbot and Facebook while still receiving incoming notifications via notification centre. There is no way to access these application (which would be the main sources of information) as long as I do not receive incoming communications.

Let’s run through each of the apps I have left visible and installed, from top-left to bottom-right:

  • Messages my primary method of communication with nearby friends.
  • authy two factor authentication for services such as google/gmail, cloudflare, dropbox etc.
  • 1password password manager. technically also a web browser, but i don’t use this in anything but emergencies.
  • Photos because people always want to look at your photos, right?
  • Fantastical my calendar app of choice. quickly able to schedule events and check what is coming up.
  • puush easiest way to get photos off the phone when necessary (i don’t like to sync with a PC).
  • osu!stream mainly just for display or showing people when they ask about it. i considered removing this but it’d just feel wrong.
  • Clock alarms are handy.
  • Google Maps maps are also handy.
  • Naver en-jp Dictionary the simplest yet most comprehensive english-japanese dictionary (imho).
  • Reeder this is a weird one, since it’s a 100% procrastination tool. it is an exception to the rule because i only use it before sleeping to get my daily dose of news and comics.
  • Simplenote cloud-synced memo.
  • Mailbox i actually prefer Apple Mail for serious mail, which is why i chose to use mailbox. it limits what i can do, and has a very optimal workflow for scheduling mail for later perusal.
  • Clear the absolute best to-do / bucket list app out there.
  • Music i couldn’t live without music.

You may be wondering where system setting are. I actually have a few more apps hidden in an invisible folder, which are utility-class apps that are necessary for maintenance of the phone itself (Cydia / App Store / Settings) or useful in emergency situations (iTap Remote Desktop / Prompt SSH / Dropbox). Out of sight, out of mind, until the day they are necessary.

So far I have not looked back. It sure does limit what I can do with my phone, but I don’t see this being an issue. I have already noticed a reduction in the number of times I mindlessly reach for my phone when I don’t actually have a particular task I need to perform.

I urge others to give this a try. You don’t need to go the full way (like Jake in the original article) but why not try cutting back what you use your phone for, and spending more time paying attention to what is going on around you; interacting with those that are close to you; taking in more of life. We already spend so many hours in front of a PC screen, after all.

comments

This week (month) in osu!

published 02 Apr 2013

Been working so hard on new stuff that I’ve basically forgotten all the things I have done and need to post about. Let’s get started with the big one: the osu! UI and default skin graphics refresh!

Over the course of two long weeks, I spent basically every minute awake in photoshop going through all 421 osu! sprites and doubling the dimensions of each and allowing pixel perfect display at 2048x resolutions. Yep, osu! is now “HD”, if you will! Where I was no longer happy with the old design, or where vector data was not available, they were re-created from scratch. This ended up being over half of the resources.

lots of resources

Let me start by saying that I couldn’t have done this alone. A huge thanks to RBRat3 for helping out with a lot of the icons which did not have vector data, including (but not limited to) the main menu icons, the editor control icons and the mod icons.

I also took this opportunity to not only split the resources into two separate resource collections (UI and gameplay), but also decided to distribute in the compressed PNG format rather than raw bitmaps. This is a trade-off allowing for faster patching and less disk usage while adding a load-time performance overhead. This overhead worked out to around 5% and does not affect runtime performance – plus it is only relevant when using the default skin, so all the people using custom skins won’t even notice a difference – so I figured it was beneficial overall.

I will be distributing the elements of this skin for use as templates and/or modification in the near future. The only reason I haven’t yet is because there are a few parts left which are not finished, including Taiko / CtB / osu!mania modes. Keep a watch on the skinning forum for the template release in the near future.

BUT WAIT, this update didn’t just stop at sprite changes! While changing all these sprites, I found so many other things along the way that I was no longer happy with, and took the liberty of changing them too. Let’s try and make a list!

  • The follow points (those dots which display between hitobjects) are now directional. In the new default skin, they become arrows which – in my testing – really help with map readability.

    direction follow points

  • Hit explosions are visually much smaller, allowing increased readability and reducing the cases they overlap future hitobjects. They also have a really nice glowy effect and a true particle system!

    new hit explosions

    You might also notice that they will briefly appear top-most, but quickly fade to a new position underneath hitobjects, helping with readability on streams.

    new hit explosions #2

  • The ranking popup dialog which used to show your online ranking has been moved so it is no longer a popup. It is now accessible by scrolling downwards, or clicking the handy button at the bottom of the screen.

    online ranking button

  • Special mode selection screen is gone. You can now change modes from song select using the new “Mode” menu! This makes a lot more sense to me these days, where the special modes are basically treated the same as osu! mode.

    mode selection

  • Scrollable views can now be dragged. Also scrolling is a lot smoother, with improved acceleration/inertia handling (please don’t sue me for patent infringement apple <3).

  • The back button has been unified across the game (it used to behave slightly differently in some cases).

  • The background has been removed from osu!direct. It now uses the default menu-background or a user replacement if one exists.

I’ve probably forgotten some other stuff, but I have a few more posts up my sleeve, so will tag it on the end of one of those if I do happen to recall it. I’ve already received quite a lot of feedback on the new design, but feel free to leave anything you have to say in the comments here as well :).

comments

Just As An Interim Update I Have Been Super Busy

published 12 Mar 2013

just as an interim update, i have been super-busy making awsome and decided to continue making awesome rather than post about it. once things are done i will make a couple of posts to make up for it.

comments

This Week in osu!

published 23 Feb 2013

Catching up on bug-fixes, I have finally been able to work on a few new features which are visible to the end user. Let’s get right into it!

  • The user panels display now adjusts to display four columns on all widescreen modes. This solves the long time problem of having a gap to the right on some widescreen aspect ratios.

    widescreen display

    It also allows for over four columns on super-widescreen resolutions, should you be running across two monitors using something like eyefinity.

  • osu!mania now support vertical flip, making gameplay similar to that of DDR/o2jam. This should help people coming to osu! from said game systems adapt quite easily, instead of resorting to flipping their monitors upside-down. I was planning on adding this one, but woc beat me to it ;).

    upscroll

    The skin you see above is available here.

  • Skinning now supports double-resolution sprites, allowing for very high resolution gameplay. osu! now uses these sprites when the window width is greater than 1600 pixels.

    To add support to a skin, add images with double the width/height containing the “@2x” suffix before the file extension.

  • The editor now supports widescreen resolutions more elegantly. The biggest change is allowing selections outside the play-field grid. This works even when the current placement mode is not select, meaning you can start a selection while in circle or slider placement mode as long as you begin the selection outside the grid area.

  • I completely restructure Bancho to allow for usergroup authentication on a per channel basis. In the process, I was able to combine the osu!stream and developer channels into the main bancho server, reducing the overhead to get people into the chat rooms. In the future, this also means we can have chat rooms pop up for teams in the case of tourneys or similar. Much more flexibility :).

Got more awesomeness already in the works. Stay tuned!

p.s. Sorry for the slider issues in the editor. Hopefully all the bug fixes made up for it.

comments