Hardware specs for MySQL database with millions of records

Question

For 35 million records I have this table

CREATE TABLE IF NOT EXISTS `records` (
  `code` varchar(12) NOT NULL,
  `type` varchar(255) NOT NULL,
  `used` set('y','n') NOT NULL default 'n',
  PRIMARY KEY  (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The code is alphanumerical and unique. I just have to check if a code exists and whether or not it has been used.

What server specs would you recommend? Any other suggestions on arquitechture? We will probably be using Rackspace so if you have any experience with them that would be great.

score 1 · Answer 1 · edited Apr 13 '17 at 12:42

Given the above table description, my first order of business would be to get rid of the duplicate index for three reasons:

it would slow down inserts
needlessly bloat the table to about triple the amount on index info
would render either the PRIMARY KEY or the code index useless in EXPLAIN plans

NOTE : A duplicate index is an index that has the same leading columns as another index. You would remove the index if the leading columns has no additional columns needed by certain queries.

Please run this to discard the duplicate index

ALTER TABLE records DROP INDEX code;

You need to find out how much memory and disk will be need for a given dataset.

Please see my other posts on memory, threading, and InnoDB configurations

Oct 22, 2012 : How large should be mysql innodb_buffer_pool_size?
Sep 12, 2011 : Possible to make MySQL use more than one core?

Hardware specs for MySQL database with millions of records

1 Answers1