1

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.

1
  • No need for key 'code'... It's already indexed with PRIMARY KEY.
    – Vérace
    Jul 16, 2015 at 16:08

1 Answer 1

1

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

  1. it would slow down inserts
  2. needlessly bloat the table to about triple the amount on index info
  3. 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

4
  • You're right Rolando, thanks! Just updated it. How do I find how much memory and disk for my dataset? I did 255+12 (bytes) / 35 million ~ 9.3GB but I'm not sure how to calculate this form memory. Jul 16, 2015 at 16:29
  • Go to my Oct 22, 2012 post and run the first query in that post. Jul 16, 2015 at 16:33
  • Thanks! So the memory consumed by it would be the RIBPS plus 10%? I don't have the records yet on a database but as soon as I do will run it. Jul 16, 2015 at 16:44
  • The 10% is to accommodate future growth. You can use whatever fudge factor you like. You just need to to run the query to forecast what the amount of memory should for the InnoDB Buffer Pool. For the actual amount of RAM, I would use double RIBPS as the amount of RAM for the VM. Jul 16, 2015 at 16:46

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.