MySQL - InnoDB vs MyISAM vs Other for browser based games

[ST-Mike]

...

[Chris]

MyISAM does not support transactions. He is a primitive brute barbarian that has tons of raw speed and low memory consumption and forgive mistakes but lacks finesee. InnoBD is a sophisticated wizard that is slower and needs more care and affection (memory and server finetuning and configuration), but can cast awesome spells (like transactions or row level locking) of course only if you know how to use these properly

I love MyISAM more He is my type

[chrisjenkinson]

I can't imagine that you have more updates or inserts than selects. You priority, if it is a problem, should be to make selects faster. However, the integrity of your database updates is more important than speed of selection in most scenarios therefore you should use an engine which supports transactions.

[andrewjbaker]

He is a primitive brute barbarian...

Likin' the analogy.

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[Chris]

is row level locking important for browser games

It is useful only in situations when one user(query) is doing action on one row and another on another row only. But if you have "spend gold to buy a soldier" and "spend gold to buy building" and "steal gold from a player" then you get no advantage of row level locking (because you still need to wait for gold row being unlocked before proceeding, so no difference to table level locking in this particular case). It depends on a game, but I'm not sure if it is that powerful feature... Sure, it is nice to have, if it was for free I would not hesitate at all. Of coure it is not free

Anyway, agonizing over storage type choice is not worth it. Majority of performance will come from your game design and coding style.

[codestryke]

is row level locking important for browser games

It is useful only in situations when one user(query) is doing action on one row and another on another row only. But if you have "spend gold to buy a soldier" and "spend gold to buy building" and "steal gold from a player" then you get no advantage of row level locking (because you still need to wait for gold row being unlocked before proceeding, so no difference to table level locking in this particular case). It depends on a game, but I'm not sure if it is that powerful feature... Sure, it is nice to have, if it was for free I would not hesitate at all. Of coure it is not free

Anyway, agonizing over storage type choice is not worth it. Majority of performance will come from your game design and coding style.

I've been trying to stay out of this but I can't. The information above is wrong, completely wrong, and I would hate for people to take this to gospel. In the example above the row would be locked, which is true but if it is myISAM then the table is locked so if player A is making a bank deposit he locks the table, player B attacks C, B waits for the lock to free up from player A, then B locks the table, C is queued until B is done, with B done now C can write. Every write locks the table with table locking. Someone here pointed out that myISAM now and has supported concurrent writes, in my past experiences this hasn't worked, maybe it does now.

Back to the example and your question. As the write queue fills up your game will start to lag for the player, because the page is waiting the writes to process though the queue. Eventually the queue gets so large that connections start to timeout, eventually the server doesn't know what to do and the mySQL and/or HTTPD daemons crash or the server reboots.

There are a LOT of things that go with the choice of myISAM vs INNODB. Good programming will help (don't get me wrong on that), a decent server with fast hard drives and a good size of memory, all of that goes into the picture. What I can offer is if you start to experience lag (as explained above) switch one of your heavily used tables to INNODB and see what happens. From my experience it reduced the lag almost instantly with very little configuration. As I moved more tables to INNODB I had to start learning how to configure it more and more. At the same time I had huge traffic and realized INNODB was just one part of solution. Moving to a bigger server helped, moving to a dual SCSI hard drives was another huge improvement as well.

No one answer fits all but table locking isn't even close or in the same ball park as row level locking. The argument of INNODB costs is simple, it's because it's powerful. I wouldn't change my game design because of my storage engine choice. However like programming it takes time to tweak things to get them to run correctly

[codestryke]

Thinking about this more Chris is probably correct on game design. By his own admission he creates games that are played 10-20 minutes per day, we don't. We create games that can be soul sucking time sinks if the player wants that. We create games that do take a bit of time to play and with that we probably have a larger per user activity matrix then Chris does which would be why we see lag when using myISAM on heavily used tables like the main player table or chat table.

@Mike
We have a similar game to your Gangster Nation and we *had* to use INNODB because of the activity level on the player table as well as the in game messages. They really love to message on that style of game

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[Chris]

I've been trying to stay out of this but I can't.

You are part of our community too you know. Don't let the burden of being an admin take away all your fun We are not players, you don't need to bahave in front of us so professional

Oh, I get it, I mistaken it for field level locking (a single field of all rows being locked). Now it makes sense... Yes, this would be much more useful.

Thinking about this more Chris is probably correct on game design. By his own admission he creates games that are played 10-20 minutes per day, we don't. We create games that can be soul sucking time sinks if the player wants that. We create games that do take a bit of time to play and with that we probably have a larger per user activity matrix then Chris does which would be why we see lag when using myISAM on heavily used tables like the main player table or chat table.

Well, I was thinking more along the lines of "a huge map that is refreshed each second and hundreds groups of  units on the map per player", I don't think your games are so much more resource consuming than mine... Still, I get an impression that my games use at least 10 time less resources than other peoples games. Once I was talking with some dev whose game was 1/3 size of mine and he switched to the same server provider I did and... they immediately asked him to leave because of resource usage (on identical hardware I had still plenty of unused resources). I wonder why... are my games really that weird?

Anyway, this is not really important since nowadays the acquisition of players is becoming more and more difficult and hardware is becoming more and more powerful, so I just do optimization by habit and hope than maybe some day by some miracle the game will become laggy due to influx of players
I definitely liked the days when I was watching the growing players count and worrying about the server more

[Sunchaser]

There are a LOT of things that go with the choice of myISAM vs INNODB. Good programming will help (don't get me wrong on that), a decent server with fast hard drives and a good size of memory, all of that goes into the picture. What I can offer is if you start to experience lag (as explained above) switch one of your heavily used tables to INNODB and see what happens. From my experience it reduced the lag almost instantly with very little configuration. As I moved more tables to INNODB I had to start learning how to configure it more and more. At the same time I had huge traffic and realized INNODB was just one part of solution. Moving to a bigger server helped, moving to a dual SCSI hard drives was another huge improvement as well.

Do you have any useful links on how to configure InnoDb tables, and how can one monitor INNODB performance... with slow query configuration?

[ST-Mike]

Admins have ignored my deletion request - if you're not going to delete my account then don't have the option there please.

[codestryke]

So, am I likely to see some kind of crazy increase in hardware usage (I'll find this out for myself anyway) by switching to InnoDB? When you first switched codestryke did you do so with practically zero configuration then (only optimizing later)?

I first started with INNODB with mySQL 3.x and used the default configuration parameters. Like I said I saw an almost instant reduction in lag and none of the hardware went crazy! I started to optimize the configuration file to see how far I could take the hardware/INNODB engine. This was over 8 years ago now so my server was a single core with 2 gigs of RAM, paltry by todays standards.

Our server now has multiple cores and 4 gigs of RAM and since we switched to the new server I still haven't touched the default INNODB configurations because I haven't found the need to. Same goes for PHP and the Apache. With my old server I remember tweaking those constantly to eek out every little bit of performance I could. The hardware, for us, has actually caught up with our usage. One thing to remember is the default configuration for INNODB is conservative and doesn't even come close to reflecting modern day CPU's, memory amounts etc.

When I was optimizing the engine I used the status command a lot, plus the dump from phpMyAdmin which also highlights in red what parameters are questionable. That mixed with a lot of reading from all over the net I was able to tweak the engine best I could. I'm sure I could of done better but as I said the lag was gone and that's all I cared about.

I never had a problem with the INNODB engine as they are self repairing. However, I have had in the past problems with myISAM tables. Anyone else go to there site and find a horrible crash took out there tables to the point where they had to run repair from the command line I did, multiple times actually and if that doesn't scare the cr*p out of you nothing will LOL


@Chris,
I wasn't playing the omnipotent admin I stayed out as I've been very vocal in the past about INNODB so I didn't see a reason to rehash my views

[JGadrow]

I stayed out as I've been very vocal in the past about INNODB so I didn't see a reason to rehash my views

I have stayed out of the discussion mostly because I've been too busy. But a couple of the "crunch time" tasks that I had are finally winding down. lol On top of that, I've got a former co-worker who is going to pick up some of my work as well which frees up my time.

Anyways, InnoDB is awesome. Especially for games that have just as many write operations as there are read operations (heck, that may even be skewed in favor of writes). However, the reason I've always chosen to use InnoDB has nothing at all to do with performance: It is the only MySQL storage engine that utilizes foreign key constraints!

I'm a big fan of validation and integrity. Foreign key constraints help to maintain database integrity. I mean, seriously, why perform 15 queries to completely remove a user from your database upon account deletion? Delete the account, delete their character, delete their inventory item references, delete their messages, etc.. With foreign keys you could write one query:

Code: (sql) [Select]

DELETE FROM users WHERE id = %d;
And the database would make sure all the other crap gets deleted too! Pretty nifty, right?

[Chris]

I'm a big fan of validation and integrity. Foreign key constraints help to maintain database integrity. I mean, seriously, why perform 15 queries to completely remove a user from your database upon account deletion? Delete the account, delete their character, delete their inventory item references, delete their messages, etc.. With foreign keys you could write one query:

Code: (sql) [Select]

DELETE FROM users WHERE id = %d;
And the database would make sure all the other crap gets deleted too! Pretty nifty, right?

That's one of the reasons why I do NOT use it I find it extremely wasteful to force the storage engine to make all these additional checks each time it modify the table while you could just manually write these stupid 15 queries inside DeleteUser() function that is almost never called anyway

[codestryke]

That's one of the reasons why I do NOT use it I find it extremely wasteful to force the storage engine to make all these additional checks each time it modify the table while you could just manually write these stupid 15 queries inside DeleteUser() function that is almost never called anyway

Taken from the mySQL manual:
InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.

In your example above the database would NOT make a check on every change of a player record, it would only make the check if a delete was made to the table. The only difference is the database handles it and doesn't have to go back to the php interpreter to make yet another call to a mysql function which slow the program execution down.

[Chris]

I didn't mean that it would be terribly slow or require something as intense as database scan. I just meant the check has to be made, that the additional code (machine code) that handle it has to be there. It is not free. The cost it there. If you compare it with a convenient queries inside DeleteUser() function (and let's face, this + ChangeUsername() basicly covers all uses of this for a not overcomplex BBG) the choice to not use it obvious. You simply don't get any benefit for using it in a BBG...

I don't mean this is bad feature for business applications. If you are working with 20 people on a huge app that handles some tasks and the client is changing the requirements each month you would be insane not to use it. I just mean it is useless for BBG made by a single person.

[JGadrow]

You simply don't get any benefit for using it in a BBG...
[/snip]
I just mean it is useless for BBG made by a single person.

I disagree. It's a good thing to bake in the actual relationships between your data. People do leave your game. Or, perhaps, you have a perma-death system... in such a system, character deletion would be commonplace meaning the cascade effect would have significant value.

And, again, it's not about performance for me. It's more about readability of the resulting source code, ease of use, and the fact that it actually forces you to sit there and say, "Hmm... how does this piece of data actually relate to the rest of the data?" I mean... we're storing data in a relational database... why aren't we using those relations?

It's also good if you're developing a framework for use on multiple games. Using foreign keys, your framework is able to assume that deleting the user account record will also remove any extended data for that user which might (and likely is) specific to any individual game's data.

Also... what happens if you have a bug in your code and it erroneously performs an update or delete on a record. The foreign constraints can propagate this change throughout the system (kinda crappy in the case of an accidental delete but you could change from the "cascade" effect to the "prevent" effect instead...). So, if your code accidentally changes user.id from 5 to 15, that player's game will not be broken because all of their records' data will change to 15. Sure, your system shouldn't have done what it did... but at least while you're figuring it out, that user can enjoy the game uninterrupted.

I have never been a fan of the MyISAM engine... again, performance takes a backseat to ease of development and readability of code. Binary is sure some fast-executing code... but boy oh boy does it take forever to write a program that way.

[Chris]

So, if your code accidentally changes user.id from 5 to 15

... LOL. No, I can not imagine any programmer doing such huge basic mistake. Anyone who has such problems should worry about basic coding habits not about selection of storage engine. Come on, there are some limits how incompetent a programmer can be

Binary is sure some fast-executing code... but boy oh boy does it take forever to write a program that way.

It took me 2 weeks to write playable version of Lords and I was learning PHP and MySQL in the meantime. People, we are making here stupid simple websites, not real games! There is no 3D, no memory violation possibility, no anything. You can even divide by zero and get away with it without crashing the whole server. It is just a simple website with a bit of interactivity We don't need any of these awesome features that were designed to help producing multimillion huge business software developed by hordes of programmers in years or decaders. Oh boy...

[JGadrow]

No, I can not imagine any programmer doing such huge basic mistake.

There are some real beginners here... it's perfectly feasible to assume someone could make a huge error especially when they're relatively inexperienced. But, nobody's perfect and even pro-coders make stupid mistakes every now and again. We're only human after all. The key is to insulate your program from your humanity as much as is possible. And, yes, this is also an argument for test-driven development... something I wish a company would give me the opportunity to work more with! Stupid fear of reduced productivity...

It took me 2 weeks to write playable version of Lords and I was learning PHP and MySQL in the meantime.

"Playable" does not mean good. lol I looked and it appears you only had 47 users at first... not exactly crushing your hardware at that point. I had a game that was "playable" at one point, but I took a look at it and said to myself, "When I need to adjust this thing for balance, it's going to fall apart!" I created it when I was first learning PHP and MySQL. Granted, I had a longer background in programming in general so I knew it was impossible to maintain... but, yeah. I'm more concerned about producing a quality product than I am about getting some initial players. I want my first game to be one that people play, find fun, and think to themselves, "Wow! This is a great game!" I do not want them to be thinking, "This game is pretty good." The goal is to completely "wow" the gamer so that when you announce your next game, they're eager to play it not sitting there thinking, "I bet it's a rehash of the same old crap."

People, we are making here stupid simple websites, not real games! There is no 3D, no memory violation possibility, no anything.

You might be doing that, but that is not my goal! I actually find this statement somewhat insulting and derogatory to the entire community. Even with the technological limitations imposed by our medium, I've seen some pretty amazing-quality games out there. Most still suffer from the same sort of issues but I'm hoping to correct that in my game. I'm not content with a mediocre game... I want my game to come out and everyone to say, "Holy crap! This is how a browser-based game should work!"

We don't need any of these awesome features that were designed to help producing multimillion huge business software developed by hordes of programmers in years or decaders.

Actually, lots of this stuff was designed back when computers still measured hard-disk space in KB and even major label games were developed inside of a year with a staff maybe 20 large... Which qualifies as a small business not a multi-million dollar company. This is not new-age stuff.

[DV8]

Are we seriously debating foreign keys here? The time invested might not always seem worth it, but they are there to protect the integrity of your database, your data model and your data. What's next, debating unicity constraints? Debating non-auto incremented primary keys?

[Chris]

We do make high quality awesome games here, but these are still simple stupid websites that require minimum programming skills. The quality and fun of browser games does not come from code complexity or our coding prowess.

"Simple stupid easy to make wesite" is not opposite of "quality game that make everyone wow". On the contrary, too awesome code will make a game unplayable, confusing and boring, I'm almost sure of it.

And I have a few more players

[Harkins]

DV8: some folks don't even use version control or write tests. This is not community of hardcore coders, and that's OK. Games attract people to get involved in programming, so it's normal to have a lot of beginner topics.

[JGadrow]

The quality and fun of browser games does not come from code complexity or our coding prowess.

I agree on this point. However, it can (and does) affect your ease of maintaining that game. The great advantage of browser-based games (aside from instant cross-platform and no install procedure) is that you can make changes quickly. If you code yourself into a corner, you're still screwed.

"Simple stupid easy to make wesite" is not opposite of "quality game that make everyone wow". On the contrary, too awesome code will make a game unplayable, confusing and boring, I'm almost sure of it.

Actually, it kind of is. The more time you pour into a product, the more polished the end result. Sure, you can make a game in 2 weeks... or you can take your time and make a polished product. Compare Lords to the HoMM game. I enjoyed HoMM until I realized at a certain point you really just couldn't do anything aside from build armies and attack other players... and it was definitely designed so that the larger players will stay on top... giving newer players absolutely no ability to be competitive.

But, again, that's a common failing of browser-based games.

And I have a few more players

Yup, but I bet you got those players after investing more time than your initially quoted 2 weeks. The more time you've put into your game, the more players you have received. That's a function of increasing the quality of your game by putting in more time on it. Your statement made it sound like, "Guys! I only put in 2 weeks and I have over 300 active users!" Which is completely false! You put in 2 weeks and got ~50 users. Which is much more believable.

[Chris]

And I have a few more players

Yup, but I bet you got those players after investing more time than your initially quoted 2 weeks. The more time you've put into your game, the more players you have received. That's a function of increasing the quality of your game by putting in more time on it. Your statement made it sound like, "Guys! I only put in 2 weeks and I have over 300 active users!" Which is completely false! You put in 2 weeks and got ~50 users. Which is much more believable.

Well, yes... The initial 2 weeks, while very important and shaping the whole game, were not what made it successfull. It was all these things after. Actually, the most work was not related to coding at all...

Which makes me wonder, how we ended up with this on the MyISAM vs InnoDB topic?