Databases
StackOverflow2013
Postsdbo
POPlanning to optimize access to a really large InnodDB table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPlanning to optimize access to a really large InnodDB table
    primarykey
    data
    text
    <p>I'm a developer of a social game where we have almost 2 millions players(and this number is growing). </p> <p>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:</p> <pre><code>CREATE TABLE `plant` ( `player_id` int(10) unsigned NOT NULL DEFAULT '0', `id` mediumint(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 </code></pre> <p>I'm thinking about the following plan on how to optimize it:</p> <ol> <li><p>Add a similar table with "archive_" prefix</p></li> <li><p>Partition this new table by hash</p></li> <li><p>Figure out inactive players who haven't played the game, say, for a month.</p></li> <li><p>Copy their records from the big table to the archive table</p></li> <li><p>Mark the player being archived and use the archive table instead the original one whenever he/she logs in</p></li> <li><p>Optionally partition the original table by hash as well(optionally, because it may cause lots of downtime)</p></li> <li><p>If nothing helps think about sharding</p></li> </ol> <p>What do you think about it? Does it sound like a good plan?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USpachanga
    1. USpachanga
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPlanning to optimize access to a really large InnodDB table
      1. USpachanga
      1. VTFavorite
    2. VO
      singulars
      1. POPlanning to optimize access to a really large InnodDB table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy are you having to optimise - what is the issue ?? Does the following answer help ? http://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601
      singulars
      1. POPlanning to optimize access to a really large InnodDB table
      1. USJon Black
    2. COUpdate/Insert times for items in this table are gradually becoming slower and slower. Index for this table doesn't fit into RAM...
      singulars
      1. POPlanning to optimize access to a really large InnodDB table
      1. USpachanga
    3. COSo read times are good then - can you post your table schema pls
      singulars
      1. POPlanning to optimize access to a really large InnodDB table
      1. USJon Black
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload