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?