Logging Game Downloads With Partitioning

Overview

Big Fish Games, Inc. develops, publishes, and distributes casual games through its website at http://bigfishgames.com. It offers downloadable games for PC/Mac; and online games, including puzzle, card, and word games. The company was founded in 2002 and is based in Seattle, Washington.

At Big Fish Games, our Technology Group has used partitioned tables for our download logging application in production since May 2007 (yes, really!). We're now on MySQL 5.1.28 and our first production release using partitioning was with MySQL 5.1.17.

Purpose of our application

To log the game downloads served from Big Fish's many download servers, and then update when the downloads have completed.

Big Fish Games serves over 1 million game downloads a day, and thus our log tables quickly became very large. Partitioned tables allow us to log and update records with low latency under high concurrency. As well, we can prune older stale partitions instantly without locking or causing severe load on the server. Further, it aids the speed of reporting/analytical queries by having fewer records to examine.

Our environment

We develop and deploy on 64bit Ubuntu Linux. Our application is written in Java and PHP. The size of our InnoDB tables for this application is currently over 40GB, with more than 300 million records at any point in time. The data is hosted on a Sun Fire x4140 with 32GB of RAM.

How partitioning meets our needs

We have found partitioning by range to be very user friendly. It is easy to define, add, and drop new partitions using the simple CREATE/ALTER syntax in the documentation. The fact that most of these operations are non-blocking is invaluable for administration purposes to keep our downloads serving 24x7.

Performance has been very good. Dropping old partitions takes only seconds without affecting load, and we can sustain high levels of inserts while doing concurrent selects and updates in a table with over 300M rows. For reporting/analytical queries, we can use EXPLAIN PARTITIONS to see that only certain partitions need to be examined, which can dramatically improve query times in large tables such as ours.

We have found 5.1.22+ to be very stable even prior to GA release. The one bug we did find was Bug#37531, found during Unit Testing - and is now fixed in 5.1.28