CODEWITHSUNDEEP
mysqldatabaseperformancedatabase-designarchitecture
Hasin Hayder
Hasin Hayder

Reputation: 818

Architectural Help Needed - MySQL or NoSQL for a table with 54 million records

We have a table with 54 million records. Here is the table structure. 

CREATE TABLE `metaplay` (
  `track_id` int(11) NOT NULL DEFAULT '0',
  `user_id` int(11) DEFAULT NULL,
  `completed` int(11) DEFAULT NULL,
  `skipped` int(11) DEFAULT NULL,
  `created` int(11) DEFAULT NULL,
  `updated` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `created` (`created`),
  KEY `updated` (`updated`),
  KEY `skipped` (`skipped`),
  KEY `track_id` (`track_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

All these data are numeric. At this point, we have ~300 inserts and ~100 updates per minute. We also pull out daily, weekly and monthly track playing records from this table. Now my question is which of the followings will be better for architectural design and best performance. I would appreciate if you can highlight hardware details as well.

  1. MySQL in SSD Cached Storage with 4GB Ram
  2. MySQL in SSD with 4GB Ram
  3. Any NoSQL Solution
  4. Anything else?

Also do you suggest any mysql specific tuning tips for a table like this?

Upvotes: 0

Views: 118

Answers (1)

Sam
Sam

Reputation: 2761

MySQL in SSD Cached Storage with 4GB Ram. use mecached for the best performance. If you change your storage engine to innodb with buffer_pool_size=3GB you will see a huge performance gain. If possible upgrade to 5.6 innodb's performance in 5.6 is significantly better than before. Innodb also has native memcached support in 5.6. Read here for more info

Upvotes: 1

Related Questions