MyIsam or InnoDB or ?

[a_bertrand]

Hi,

Maybe some of you already thought about it, if not then you should ;-) To explain why you should, and at the same time to not make it a too long story just let tell you a bit of the background of my own game dev adventure.

So far I was renting a dedicated server in the US by Lunarpages, and I was really happy with their service, however the price is somehow... not so low. Also as the number of player grows (as should any game experience), we may soon face troubles with our current hardware. Finally having a server hosted on the US territory has some drawbacks, specially if your are, like me, not living in the US, you may have legal troubles which in that case could be under the US law (due to the fact your server is running there), but there is also simply timezones troubles which having your server nearby would be avoided.

Well, for all those reasons, we decided to buy our own server and host it here in Switzerland (yes I'm Swiss).

Now, it's just a good time to test things and see if some parts could be optimized, as we don't risk to have impact yet on the players while playing ourself with the new server. So we decided to test for example if we should use MyISAM tables or have at least some InnoDB tables. Why? well InnoDB offers a few advantages over MyISAM:

- Row locking instead of table locking (which means when you do an update it will not lock the whole table, but just the rows you are modifying)

- Transactions supported.

- Full backup consistency (without locking all the tables during the backup)

All this seams nice, but what about speed? Well, searching on the net, lot of people says InnoDB is actually faster than MyISAM... so why not jump on it right away? Well, let's say I'm a skeptic guy, and unless I test it myself, I tend to not believe it ;-)

So how did I tested it? Well using my full game setup, and extracting about 14 queries (10 select and 4 updates) I created a small C# program which connect to my DB Server, create 200 threads (concurrent connections) and runs in loop my 14 queries by changing the player ID at each loop, to be sure that the server doesn't cache too much my results ;-)

Results?

InnoDB 130 iterations per seconds

MyISAM 400 iterations per seconds

Those queries are not the lightest one (on purpose), and the updates should actually lock the tables which other thread should try to read... so we should really experience near real life experience.

Conclusion: I will not move to InnoDB as it's clearly too slow for my own experience. I may however double test if just changing one or two table I do gain something or not... but so far InnoDB didn't proved me it's superiority... specially as I don't really need the transactions. The only thing which I would miss is the possibility to make hot backups without locking my tables.

Just for info, my Server is a quad core with 4GB ram, and running Linux Cent-OS 5 in 64 bit. I did tweaked the my.cfg to use a lot more memory than default (without going to swap), as well setup correctly the number of thread the server uses.

If you had another experience about it, please share it ;-)

[Cronus]

Re: MyIsam or InnoDB or ?

I've looked into innodb a few times myself.

Every time I see the differences the flaws outweigh the good.

I have not tested it as you have, but I came up with the same conclusion.

[Lithium]

Re: MyIsam or InnoDB or ?

If you are looking for speed... definetly.. MyISAM, reliability... InnoDB.

Maybe you should consider reading around as opinions differ on the usage on one or another.

http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html

Just a small comparision on both.

[a_bertrand]

Re: MyIsam or InnoDB or ?

Cronus: thanks to share your experience. I wasn't searching somebody which tested like me... Just other tests was ok ;-)

Lithium: Thanks for the link, but actually this was the first link I read, then I checked also those

http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB

Oddly enough from those two links InnoDB seems faster than MyISAM, so I thought, great! I will switch right away... But wait... Maybe we should do some tests... and that's why I did my post, my results are not the same as those guys.

[codestryke]

Re: MyIsam or InnoDB or ?

The biggest problem I've found with using INNODB for running games is INNODB is extremely slow when doing SELECT COUNT when it comes to large tables. This is a known and well documented "feature" of the INNODB engine.

I still use the INNODB table, especially, for the player table as this is read and written to on each and every page hit for all games. With using myISAM the table locking produces a lot of lag, switching to INNODB and having record locking took care of the lag.

I also moved lasthit to a HEAP table, the player table now only updates when they last logged in, once they are in the game I update the HEAP table to keep track if they are still logged in or not (ie who's online, number of players playing). Yes if I reboot the server or if it crashes I lose the data in the HEAP table, but guess what? If the server is down there isn't anyone online anyways LOL.

If your game is experiencing lag try converting one of the more heavily written to tables to INNODB and see how it works for you (do a backup, of course, first). There isn't one cure all for everyone, with something like this (myISAM vs INNODB) it is very conditional.

[a_bertrand]

Re: MyIsam or InnoDB or ?

Thanks codestryke, that was also my planned approach, as I'm still somehow scared of the table lock approach of MyISAM. I do already use quiet a few HEAP tables (for things which doesn't need to be kept long and at the same time will be selected / modified a lot).

[codestryke]

Re: MyIsam or InnoDB or ?

 

Thanks codestryke, that was also my planned approach, as I'm still somehow scared of the table lock approach of MyISAM. I do already use quiet a few HEAP tables (for things which doesn't need to be kept long and at the same time will be selected / modified a lot).

I know I come off as a know it all prick on some posts and that's just because I've been there and done that ;) Hope it works out for ya, just 'member test, test and then when your tired and can't take anymore test it again. Its the only way we have to learn ;)

[a_bertrand]

Re: MyIsam or InnoDB or ?

To allow others do the test as well, I made a version of my test tool which can be configured by editing an XML file:

http://www.nowhere-else.org/temp/TestMySQL.zip

Edit the TestMySQL.exe.config (without breaking it) to reflect your config.

ConnectionSting: should reflects how to connect to your database

QueryID: should allows to retrieve a list of ID which will be then used to run the queries without too much caching.

NBThreads: tells how many concurrent threads should be launched.

Queries: is the list of queries you want to run... where @ID is replaced by one of the value returned by the QueryID select.

Now to run the test you need .NET 2.0 or above installed on your PC. It may run on Linux too, with Mono installed (never tested it). Just uncompress the zip, edit the config file and run the exe...

To understand what the output is, basically the top line shows how many iterations per second your system can answer, that means basically how many times per second the full list of queries has been run. Of course the higher number of queries you put the slower it will be. So make something similar as your game do, and you will be able to know how many "pages" your database will be able to server per second... Isn't that cool?

Now, to come back to my myIsam vs. InnoDB -> I do have definitively an higher number of iterations per second with myIsam than InnoDB. Also the disk activity is much more reduced with myIsam.

[CtrlFreq]

Re: MyIsam or InnoDB or ?

How many records were in the table you were working with, and how many updates were you applying per cycle vs. selects per cycle? It just sounds like your test was drastically under the threshold required to observe the performance differences under load.

[a_bertrand]

Re: MyIsam or InnoDB or ?

Was tested with

PLAYER 6000 records.

PLAYER_MESSAGES 346992 records.

INVENTORY 398298 records

QUESTVARIABLES 467392 recods

4 updates and 10 select per iteration... that was already specified BTW ;)

And I doubt we should not note performances differences with those numbers.

BTW If you unzip and check the config, you see exactly the queries used.

Again, this doesn't mean InnoDB is not good, it just mean that for me it is not the fastest DB for my own game right now. That's all.

[CtrlFreq]

Re: MyIsam or InnoDB or ?

 

4 updates and 10 select per iteration... that was already specified BTW ;)

The problem is your queries are all over the place and really don't give you an accurate indication of what you're looking for. Furthermore, you're only updating the table with 6k records using eqrefs, which means the delay from the InnoDB transaction log is going to slow you down more than the delay from MyISAM's full table lock.

To get an accurate idea of the true performance difference, you need to change your test a bit...

1) up the number of player records to something beyond 25k - anything over about 12k should be adequate to observe slowness from MyISAM, but you're looking for real results, and 25k should do it.

2) get rid of any queries not dealing with the player table - you're testing a specific engine on a specific table, so the other queries you're running aren't pertinent to the test. If anything they're giving the table you're testing a reprieve between calls, which is tainting your results.

3) mimic an actual game session - meaning that you'll most likely do as many, if not more, updates to the table than reads.

If you do these three things, you'll see the difference because the time to select will be exaggerated due to the wait for the MyISAM update locks to expire. Testing against InnoDB will not have said delay.

Also, get a copy of MySQL Administrator from their site, and connect to the server - that way you can watch the queries as they hit, and you'll be able to see which are getting blocked in real time.

[a_bertrand]

Re: MyIsam or InnoDB or ?

Sorry CtrlFreq, but I believe you didn't read carefully what I said. This reflect the normal usage of my database, with near real queries. Why should I increase the number of rows in one table? Why should I try only with the player table when my game doesn't only do that? If you want to test performances for your game, for your own code, you should run near real life queries and that what I did. I do not care at all if in other setups InnoDB are faster, that wasn't what I want to test / stress. What I wanted to see is how it react for my own personal needs. If you don't believe this is my own real usage of the database, well not my problem, and again I'm not using McCode, so.... my usage is not the same as you will get.

Also I don't care about seeing which query blocks which not, I do know very well what means a table lock vs. row lock, but that's not the unique thing. Even on the MySQL site they state an high load on the disks for the InnoDB engine.... which is also not something you really want, isn't it?

[CtrlFreq]

Re: MyIsam or InnoDB or ?

 

Why should I increase the number of rows in one table? Why should I try only with the player table when my game doesn't only do that?

Because 1) you're attempting to show a difference between engines, and 2) when your player table gets to be that size, it's only going to be the queries against that particular table that will be suffering, not to the periphery tables.

 

If you want to test performances for your game, for your own code, you should run near real life queries and that what I did.

I've done this over the course of three years, including two years worth of production usage, during which I've experienced first-hand what I'm describing to you. It's all well and fine that your particular table performs better under MyISAM, but I'm pointing out that it's only because your data set is miniscule, and thus your test cases are inadequate to demonstrate the difference between storage engines, and the benefits provided by InnoDB.

 

Also I don't care about seeing which query blocks which not, I do know very well what means a table lock vs. row lock, but that's not the unique thing.

Which queries block others is what this is all about - in fact, it's the very reason why InnoDB out-performs MyISAM so easily under load. The difference in an enterprise level environment is phenomenal, as it means the difference between being able to service a single write query (namely update and delete) to the exclusion of everything else and being able to service thousands reads and writes concurrently.

 

Even on the MySQL site they state an high load on the disks for the InnoDB engine.... which is also not something you really want, isn't it?

It is if it means the difference between your queries running and your server hanging at 100% CPU because you literally can't retrieve data from disk before the connection times out. I've watched a single MyISAM table with 10k records push an 8 CPU server with 16 gig of ram to 100% on multiple cores, every bit of which was due entirely to cpu wait (wa in top - waiting on I/O), simply because the queries on that table were blocking. In every case, a simple change of the table's storage engine from MyISAM to InnoDB has immediately alleviated the bottleneck and drastically improved performance.

[a_bertrand]

Re: MyIsam or InnoDB or ?

CtrlFreq: all good and well, but as yourself stated, on my case it's not the case, so why choose something slower RIGHT NOW? In case in a few years I will redo the experiment and see if it changes, up to then it would simply stupid switching and now have a lower performance. If my table is small or not, it's not the point, the point is my ACTUAL performances.

However thanks for your info, as I get it like "watch out, maybe the situation will change if your DB grows yet more". You could just have said that without pointing bad testing environment, wrong queries or whatever... That would have been more friendly and productive.

[CtrlFreq]

Re: MyIsam or InnoDB or ?

 

so why choose something slower RIGHT NOW?

Why? Because good design is good design, and shouldn't be put off until an actual catastrophic event forces your hand. I understand the "it's not broke, don't fix it" mentality, but the corollary to that is "a stitch in time saves nine", and as the administrator of a web-based game, that means the difference between being prepared for load, and spending hours trying to figure out why your game was crushed under load after the event has passed (a position I've found myself in a number of times, and am actively trying to help others avoid).

 

I get it like "watch out, maybe the situation will change if your DB grows yet more". You could just have said that without pointing bad testing environment, wrong queries or whatever...

The problem was your test wasn't valid for the hypothesis you were attempting to prove, and so your conclusion was based on faulty data. Sure, I could have just pointed out the size of the data-set, but that was only a third of the problem with your experiment, which is why I illustrated the others.

It may have come across as being "more friendly", but it wouldn't have been more productive by any measure, nor would anyone reading this thread have learned anything of use from doing so.

[codestryke]

Re: MyIsam or InnoDB or ?

While I don't give a lot of credence to the test I do admire the effort put in ;)

Like I said in the previous post it's all situational. INNODB is not the cure all, myISAM isn't the cure all. Sometimes running both is the best of both worlds (thank the gods we have that option with mySQL).

a_bertrand -- The test was cool but the best thing to look at is you mySQL, it tells all. What does your Table_locks_waited look like on your game? If you have a really high number then start looking at breaking some of your tables or switch to INNODB. It really is as simple as that. A test is a test but real data is real data.

 

Why? Because good design is good design, and shouldn't be put off until an actual catastrophic event forces your hand. I understand the "it's not broke, don't fix it" mentality, but the corollary to that is "a stitch in time saves nine", and as the administrator of a web-based game, that means the difference between being prepared for load, and spending hours trying to figure out why your game was crushed under load after the event has passed (a position I've found myself in a number of times, and am actively trying to help others avoid).

I get it like "watch out, maybe the situation will change if your DB grows yet more". You could just have said that without pointing bad testing environment, wrong queries or whatever...

Been there done that... If the problem is table vs row locking like a_bertrand is trying to decide on then what would it take to switch from ISAM to INNODB, 10 - 30 seconds? Easiest fix I've ever seen. INNODB is not the best in all situations. Pure speed of retrieving the data that isn't written to a lot then go ISAM if your doing A LOT of reading and writing then INNODB is sometimes the way to go. To be sure on all aspects just try it, covert a table and see how it goes for your situation.

[a_bertrand]

Re: MyIsam or InnoDB or ?

Table locks immediate 185260831

Table locks waited 716621

Factor 1:258... which seems still acceptable

And if you check articles like this one (there is tons of those kind):

http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-2/

You will soon see that indeed InnoDB is not the "best option" for all needs (better design or not). Also something else, why people developping a dabatase like MySQL would keep MyISAM if it would be so inferior in any case against InnoDB? Why don't they drop simply MyISAM then? Simply because InnoDB is not the solution for everything.

All my tests demonstrated what? (Live on the running game while converting a table or via my tool) Well simply that so far I don't see a benefit using InnoDB. Doesn't mean InnoDB is not good, or MyISAM is better, simply that for me, at the moment, myISAM still outperform InnoDB.

CtrlFreq:

All those talk about good design and teaching others, sorry but you can keep it for yourself, really I don't need that. I'm not a new player in the DB field, nor in the web development, and I really don't need a coach for those things. I just shared my experience (which is just mine), and asked if others got different results... also as somebody asked for the tool I developed, well I gave it for free too... Now if you had a bad day, you can simply avoid to answer and it will help to improve the overall mood of the community.

codestryke:

Thanks for your answer, indeed I don't usually do my choice only based on tests (as tests are always imperfect by nature), but simply as I wasn't able to see any improvement nor decrease in performance during real life runs, and I had the option to test it on the new server just before bringing the new server live, I though it would be a good timing for a test.

[CtrlFreq]

Re: MyIsam or InnoDB or ?

 

Also something else, why people developping a dabatase like MySQL would keep MyISAM if it would be so inferior in any case against InnoDB?

Noone ever said MyISAM is inferior to InnoDB - hell, my game's database is comprised of about a 50/50 mixture based on the table's usage pattern. MyISAM is perfect for inserts and reads (ie. message boards, audit tables, bans, whitelists, upgrades), but it fails miserably under load on tables with high update and insert counts (players, resources, etc).

 

All those talk about good design and teaching others, sorry but you can keep it for yourself, really I don't need that.

Of course not, which is why you devised such a pertinent set of test cases to prove your point. The real issue here isn't you, but rather the people who are reading, or are going to read, this thread thinking you've actually got a point because they don't have the experience necessary to spot the flaws in your test.

If you want to keep advocating the use rocks to hammer in nails, by all means, keep it up. Just don't be surprised when people point out that hammers are more effective because they've been designed for the particular job at hand.

[codestryke]

Re: MyIsam or InnoDB or ?

 

Of course not, which is why you devised such a pertinent set of test cases to prove your point. The real issue here isn't you, but rather the people who are reading, or are going to read, this thread thinking you've actually got a point because they don't have the experience necessary to spot the flaws in your test.

/quote]

Ding... ding.. ding.. we have a winner. This is the exact point you cannot teach experience, experience is learned. Thus far the thread has pointed out how you should proceed and things to look at. No game owner is going to know what the hell is going to go on until the the game is online, period.

Well 'cept you and your top ranked on CE Topsites, you of course know it all :)

[CtrlFreq]

Re: MyIsam or InnoDB or ?

 

Well 'cept you and your top ranked on CE Topsites, you of course know it all :)

I'm the first to admit I'm still learning, and am always open to new information (great bit on the MyISAM file handlers btw). I've just been through the pain of having a table or two crater my game under load too many times to wish it on anyone else, especially when the performance hit on the low end is negligible.