Impact of exchange - MyISAM by InnoDB

6

In a medium database (~ 2GB) MySQL that is all with the MyISAM engine. Realizing the conversion to InnoDB, what will be the impact on my system? Can I simply convert by phpMyAdmin, for example?

Has anyone ever had this experience?

    
asked by anonymous 17.02.2014 / 18:44

2 answers

3

Like everything in almost everything "Depends" Myisam is faster for linear queries and has very few checks on inserts and updates, making it quicker for writing. Innodb is much more robust and guaranteed in terms of data consistency , such as foreign keys that work for real

Rate

1st: needs very high speed and does not have difficult references to retrieve between tables? (yes: myisam +1, no: innodb +1)

2nd: Is the partition where the base is on a RAID volume? (yes: negative impact of innodb writing decreases, innodb +1)

3rd: Many concurrent writings on the same table or row? (yes: innodb + 1, no: myisam +1)

There are many more factors to compute ... but these I think are the most shocking.

As for the migration, it's handy, no use ... it may (will!) have problems with lack of consistency going from myisam to innodb, and will have to correct at hand to make sure everything was imported as it should. ..

obs: myisam is faster when the size of the tables is fixed .. otherwise it may be lower .. (example use varchar always)

    
17.02.2014 / 19:29
1

It has two types of impact, both during migration and after during use.

Impact during use

Virtually everything MyISAM does, InnoDB does, especially if you use newer versions of MySQL or MariaDB. You should not notice a significant difference, and if your number of writes is significant, there should be a noticeable improvement.

However, if you do it on the same server that has MyISAM, you will probably experience performance issues because your server may be optimized for MyISAM and not InnoDB. Be sure to check if the performance has not been penalized and, if it was, take server configuration seriously

Another point is that you will probably need more disk space, especially if you have many primary keys and indexes.

Impact during migration

This will depend on your data structure, but we are talking about minutes of downtime, or even hours. It all depends on how your table structure is and the power of your server. Test out of your server to see if downtime is acceptable, and if not, read on how to optimize InnoDB for initial conversion, some tips can shorten time from 30 hours to 30 minutes in huge databases and not optimized to use all available memory.

    
17.02.2014 / 19:21