3

I am in the process of moving my servers from stand alone hosting to AWS using RDS (MySQL) for database usage.

One of the first things RDS states is that in order to use their backup, and snapshot features of RDS the tables must be using InnoDB.

My site has always run on MyIsam, probably mainly because 10+ years ago, it seemed like the right choice.

Because of this, my database structure is quite simple, and quite separate, there are no join queries on the site, so every select is only getting data from one table.

some of the tables have quite a few indexed fields, The site requires, frequent inserts, regular updates and many reads,

I don't use full text searching, the majority of queries revolve around either the UserID or the tables autoincrement ID

So, my question is, what to expect if i was to move to Innodb? I am not that worried about the transaction side of it. and for this reason MyISAM is ok. I am more concerned about backup, previously I had a cron job that would automate the backup , but of course this came at a price of lockouts while the backup was in process.

MY understanding of InnoDB is that because it does row level locking, this would not be an issue.

But what about performance? I have read so many reviews and bench tests, but i dont know how they relate to my situation.

Can I move over to InnoDB without worrying about setting up foreign keys etc, can i keep things as simple as they are now, or would that negatively affect the system if using InnoDB.

I realize that InnoDB has become the default option in MySQL these days, which makes me think that all dbs should be running on it anyway.

What I dont want to do is have my database fall over because I didnt set up innodb properly.

Is it even worth moving my tables over to it, given its been running just fine on MyISAM for years.

Or is it a case of, this is good cause to change?

Improve this question

2 Answers 2

Reset to default
3

Without knowing your dataset size, or your usage (read or write heavy? queries per second?), here are some generalities:

  • InnoDB has improved drastically in even the last few years performance-wise. This, along with the (mostly) ACID compliance and row-level locking, is why it's now the default in 5.5+

  • If you're not using full text indexes in your MyISAM table, you should be fine switching to InnoDB

  • Make sure you know the changes to the mysql version you're going to on RDS. Performance might not be the only 'gotcha' on moving to InnoDB at RDS. If RDS is newer than your current application, make sure there's not any features that changed. You do this by:

  • Test it!! Don't make the move on your application until you've tested it out.

One method to test it is to turn on the general query log for a week or so (if the performance hit won't be an issue) and then run it against the RDS instance to see how it runs.

Disclaimer: I'm not an expert on RDS, so I don't know the best way to test against it.

Improve this answer
4
  • Would it be good to to test the transition in 2 phases? (first the change to InnoDB in a local test server, solve any issues that might appear, and then the change to RDS)
    – ypercubeᵀᴹ
    Mar 4, 2013 at 21:12
  • @ypercube Personally I don't think so, unless he was going to convert to InnoDB regardless of if RDS worked out. If RDS migration is a certainty, I'd do it all at once.
    – Derek Downey
    Mar 4, 2013 at 21:36
  • Thanks for your help guys, The site I am running is only a small one, it gets about 20 queries/second majority of calls are reads but there is still a high % of calls that are inserts or updates. Its a gaming site, so each time a new game is created there is an insert, each turn there is an update, but between the Game list and opening a game, there is more reads.
    – Lawrence Cooke
    Mar 5, 2013 at 6:26
  • my main concern is that I dont want a performance degredation due to this change, Is it as simple as changing the "type" frm myisam to innodb? and the rest is taken care of by mysql? I will test any changes on a small section of the site just to see what it does. Main thing is, will the current queries work in inno or myisam, the querie work fine on the RDS I have running with myisam, so the server itself shouldnt be an issue, its just myisam vs innodb that might cause issue
    – Lawrence Cooke
    Mar 5, 2013 at 6:31
0

Also keep in mind InnoDB tables are backed up automatically daily, and recovery for InnoDB is painless, but MYISAM tables will be unrecoverable.

Improve this answer

Your Answer

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

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