5

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?

4
  • ENGINE=MyISAM is going to kill you. As an absolute basic first step, you need to start using InnoDB if you're going to use MySQL for this sort of thing.
    – ceejayoz
    Dec 3, 2013 at 20:41
  • 3
    @ceejayoz not really. MyISAM is quite fast, it just doesn't support transactions properly :) Dec 3, 2013 at 20:46
  • @DennisKaarsemaker dev.mysql.com/doc/refman/5.5/en/internal-locking.html "MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables, allowing only one session to update those tables at a time, making them more suitable for read-only, read-mostly, or single-user applications."
    – ceejayoz
    Dec 3, 2013 at 21:33
  • 2
    and 300+100 writes per minute is mostly read-only to me :) Dec 3, 2013 at 21:38

2 Answers 2

9

Ah, what is the problem?

a table with 54 million records

A small table. Nice. I have one here with 8.5 billion rows.

we have !300 inserts and !100 updates per minute

Yeah. Small. I know. I have one here with around 500 million inserts per day. That is 347222.2222222222 per minute.

All that running on stock hardware. Seriously. THough not totally as low end as what you suggest.

But your metric is totally off. 54 million was large 25 years ago. Today it is small, unless someone tries to run a database server on a mini virtual machine.

For example a non-server with 5gb memory - ouch.

Generally - if you need analysis and this is not document style data - stick to MySql, avoid NoSql databases and learn the relational theory so that you know what you do there.

SSD is great, but I am not sure 5gb memory is decent - come on, that is less than a decent workstation has.

For daily / weekly / mnothly aggregations I would make DAILY aggregates, then use those as basis for larger aggregations. A month is only max. 31 days - so most of hte work is done once per day (in an off time). I strongly would reconsider using ebay-level cheap used hardware, though - and that is what the specs look like.

3
  • Great! Thanks a lot for the details. So just out of curiosity - what type of hardware spec do you suggest as bare minimum?
    – Hasin Hayder
    Dec 3, 2013 at 20:49
  • 2
    If you don't mind, I have a few questions (built large MySQL servers, but not at this scale): Are you doing this on a MySQL server? Single server, or a cluster? Can you share a bit about your hardware specs?
    – Suman
    Dec 3, 2013 at 22:49
  • No, I am a SQL Server guy, but the hardware specs don't change. Single server - in fact the "main" virtual machine on what is now a 6 year old dobuble opteron with 64gb memory. (8 virtual processors, 48gb memory. Discs are 8x10k 450gb SAS, 4x15k 73gb SAS, some SSD as transparent caching - done by the raid controller). Heavy analysis done on that thing... user slice and dice 150 million rows datasets. CPU upgrade planned in 2014.
    – TomTom
    Dec 4, 2013 at 7:35
4

The raw data for that table is 1.4GB (54m * 28 bytes), the indexes will add some data but nowhere near a huge amount, let's say 5GB for the index.

So whatever you do: why only 4GB ram? Make it 32GB for room for growth.

MySQL on an SSD will be nicely fast. NoSQL is a buzzword that means nothing, so unless you have a specific product in mind, I will ignore that part of your question.

2
  • mm, it's 54 Million records. So isn't it 54m*28b ?
    – Hasin Hayder
    Dec 3, 2013 at 20:44
  • 1
    You're quick, I fixed that typo 30 seconds after posing :) Dec 3, 2013 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.