Switch Engine to InnoDB

[sizenine]

What are the reasons for using MyISAM engine? I'm not sure the pros about MyISAM but InnoDB supports foreign keys while MyISAM does not. If we are able to use foreign keys, queries for deleting/updating can be more reliable and efficient.

[xChiron]

MylSAM engine provides simplicity, use less system resources and is much more faster with huge amounts of data. 

MylSAM doesn't allow to perform more than one operation at same time (Table Transaction Global Mutex), poor data integrity and security.

 

InnoDB is the best choice where all data is correlated (PK/FK and FN) ,indexed and where have chance to perform one more operation at the same time.

This provides data integrity and security. 

InnoDB use more system resources and have performance penalty with large amounts of data.

 

Sry for bad english 

Edited October 27, 2013 by Chiron

[GmOcean]

Well, from my understanding the "MAIN" reason would actually be the way each engine handles DML sessions. I honestly can't say InnoDB lacks in the performance department in regards to large amounts of data. However, it does allow for multiple/parallel DML sessions, of which would be a negative effect on rA.

 

MyISAM will currently limit those DML sessions to run 1 by 1, meaning that the rA system, only has to process one of those at a time to bring forth an update, thus reducing the strain on the server and lag in total. Then when you compare how scripters use SQL to store information, you'll see most of it is called when player performs action X. And since we can have anywhere from 1 - 1000+ players at any given time, you have to take into consideration that each of those players could trigger action X at once. Or in the instance where action X is killing any monster and updating the data to log how many kills... Well, what happens when a 10 wizards in 10 different parties kill 100 monsters each with SG?

The answer is you'll have 1,000 indivisual queries to run, now if all 1,000 of those run at once, then rA systems will suffer until they are done and you'll experience lag on your server.

But if we were to use MyISAM it'd run all 1,000 1 by 1, meaning the lag experienced should only be that of Internet connection / cpu stats.

 

Again, this is all from my understanding. So I should say that, certain DBs should allow for InnoDB while most that perform at a more frequent rate, like that in my example or saving all the characters, should be handled by MyISAM.

[Digos]

I can share about my own experience with eAthena and InnoDB.

 

About a year ago I converted all databases from MyISAM to InnoDB. After configure the best settings for the InnoDB databases in the my.cnf  (with mysqltuner.pl help) I turned on the servers I noticed a very poor performance in the databases.

 

The game became unplayable freezing all the time.

 

Note that my hardware is good enough to run the servers

AMD FX-8120 Eight-Core

16GB RAM

4x1.5TB HDD In RAID 10 (via hardware)

 

The sum of all ragnarok databases has about 5GB and I configured enough RAM to mysql handle it.

 

So I gave up using the InnoDB and continued to use MyISAM that works very well.

[blushjenica]

Interesting point about MyISAM vs. InnoDB! While InnoDB's foreign key support is a huge advantage for data integrity, MyISAM's read speed can be faster in certain scenarios. Think high-traffic websites pulling static data, almost like zooming down a hill in Papa's pizzeria - speed is key! But yeah, for complex relationships, InnoDB's reliability wins. Is speed truly the only factor to use MyISAM over InnoDB?