Ryan Thiessen has been using MySQL as a developer and administrator since 1998.
He is currently DBA Lead and MySQL guru at Big Fish Games in Seattle WA working with Operations and Engineering groups. Right now he is focusing on High Availability and data consistency across multiple data centres, and always maintains a passionate interest in performance optimization and tuning. In his spare time he is an avid gamer, and has recently started a weblog at http://hideandsql.com
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.
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.
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.
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
Read and post comments on this article in the MySQL Forums. There are currently -1 comments.