7
In a medium database (~2GB) Mysql that is all with the Myisam engine. Performing the conversion to Innodb, what will be the impact on my system? I can simply convert by phpMyAdmin, for example?
Someone’s been through this experience?
2 answers
4
Like everything in almost everything "Depends" Myisam is faster to linear consultations and has very few checks on Inserts and updates, making more quick to write. Innodb is much more robust and guaranteed in terms of data consistency, like real-life foreign keys
Evaluate
1º: need very high speed and no hard to recover references between tables? (yes: myisam +1, no: innodb +1)
2º: the partition where the base is in a volume in RAID? (yes: negative impact of writing innodb decreases, innodb +1)
3º: Many competing entries in the same table or row? (yes: innodb +1,no: myisam +1)
there are many other factors to compute... but these I believe are the most impactful.
As for migration, it is manual, no use... it can(will!) have problems with lack of consistency passing from myisam to innodb, and will have to correct in hand to have assurance that everything was imported as it should...
Bs: myisam is faster when the size of the tables is fixed.. otherwise it may be lower.. (example use of varchar always)
-
Perfect Breno, could you give me an example of the first item?
-
1
the example a guy from SUN gave me when mysql was still theirs.. phone operators using Mysql for telephone number and central query that belonged to, is a kilometric list and with much more query than written... if it were not the number portability perhaps an ldap was the ideal solution.. but not these days. so many carriers use myisam, in which they have customer registration to rebuild the table so trivial if necessary, corrupted innodb Obs is goodbye to base, backup more logbinary Restore.. myisam has the myisamcheck
-
Got it... And in the case of +500 tables, for example? Would it be advisable to be part of Myisam and part of Innodb? I am making a survey for a company of this issue.
-
2
The way it’s written, it looks like Innodb has bad performance for READ. That’s not true. He might be a little slower, but definitely this does not prevent it from being used. No coincidence that the standard engine for years is Innodb instead of Myisam. Unless you are sure of why to use Myisam, in 2014 it is better to use Innobd.
-
rsrsrs... All great answers... It would be possible to score a few more items to establish +1 or -1 and firm, in my case, whether it is recommended or not to use?
-
1
in your case is innodb the solution... many Inserts in a single table that has constant reading..
1
It has two types of impact, the during migration and the after during use.
Impact during use
Pretty much everything Myisam does, Innodb does, especially if you use newer versions of Mysql or Mariadb. Should not notice significant difference, and if your Writes quantity is significant, it should have a noticeable improvement.
However, if you do it on the same server as Myisam, you will probably experience performance problems because your server may be optimized for Myisam and not Innodb. Make sure to check that the performance has not suffered penalty and, if so, take the server setup seriously
Another point is that you will probably need more disk space, especially if you have many primary keys and indices.
Impact during the migration
This will depend on your data structure, but we are talking about downtime minutes, or even hours. It all depends on your table structure and the power of your server. Test off your server to see if downtime is acceptable, and if not, read about how to optimize Innodb for initial conversion, some tips can decrease time from 30 hours to 30 minutes in huge databases and not optimized to use all available memory.
Browser other questions tagged mysql innodb
You are not signed in. Login or sign up in order to post.
For small bases like this, there won’t be a big performance loss, probably the base or at least the indexes fit all in memory... I really believe that only has to gain going to myisam... unless it has a single gigantic table and accessed constantly with little writing... in this case better leave in myisam
– BrenoZan
Got it... My biggest problem is time consuming select cases in tables where simultaneously an update is made. In this case, the update is waiting and the whole table hangs (locked). From what I understand, Innodb hangs only the line. Right?
– Marcony Felipe
yes.. goes to Inno is the solution... myisam makes table lock even.. momentarily I suggest you do a delayed Insert if chronological consistency is not important
– BrenoZan