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,
  PRIMARY KEY (`id`),
  KEY `created` (`created`),
  KEY `updated` (`updated`),
  KEY `skipped` (`skipped`),
  KEY `track_id` (`track_id`)

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?

  • 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 '13 at 20:41
  • 3
    @ceejayoz not really. MyISAM is quite fast, it just doesn't support transactions properly :) – Dennis Kaarsemaker Dec 3 '13 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 '13 at 21:33
  • 2
    and 300+100 writes per minute is mostly read-only to me :) – Dennis Kaarsemaker Dec 3 '13 at 21:38

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.

| improve this answer | |
  • 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 '13 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 '13 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 '13 at 7:35

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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