Planning to optimize access to a really large InnodDB table

I'm a developer of a social game where we have almost 2 millions players(and this number is growing).

The master MySQL DB server has 24 Gb RAM and the database could fit into the memory if it wasn't for one table which has really large size. Currently it has almost a billion of records and its size is 33Gb. It has the following schema:

CREATE TABLE `plant` (
  `player_id` int(10) unsigned NOT NULL DEFAULT '0',
  `id` medi开发者_如何转开发umint(8) unsigned NOT NULL DEFAULT '0',
  `x` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `y` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `distort_step` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `grow_step` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `proto_id` int(10) unsigned DEFAULT '0',
  `yflip` tinyint(4) NOT NULL DEFAULT '0',
  `grow_start` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`player_id`,`id`)
) ENGINE=InnoDB

I'm thinking about the following plan on how to optimize it:

1. Add a similar table with "archive_" prefix

2. Partition this new table by hash

3. Figure out inactive players who haven't played the game, say, for a month.

4. Copy their records from the big table to the archive table

5. Mark the player being archived and use the archive table instead the original one whenever he/she logs in

6. Optionally partition the original table by hash as well(optionally, because it may cause lots of downtime)

7. If nothing helps think about sharding

What do you think about it? Does it sound like a good plan?

I think your suggestion is a very good one, it's simple and likely very effective. You steer away from 'scary' stuff like partitioning. Of course if you are expecting to have 2 million players playing all at once you will need to rethink your approach.

You could even go all the way and only keep track of which 'plants' are actually actively playing the game RIGHT NOW. I'm assuming you wont ever update the table for players that aren't playing right now.

You could even partition the archive tables as you see fit, for instance by hash on player_id or something similar.

How about sharding the table? Then you can scale without any problem. If you're worried about the heavy lifting associated with Sharding, try out ScaleBase for a transparent sharding solution