Sign in to follow this  
sizenine

Switch Engine to InnoDB

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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  /gawi
Edited by Chiron
  • Upvote 3

Share this post


Link to post
Share on other sites

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.

  • Upvote 1

Share this post


Link to post
Share on other sites
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.

  • Upvote 2

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this