myISAM Vs. Innodb in a database need some advice

[133794m3r]

ok what i'm doing with my dbs is, i've set up one for the accounts and characters of all the players also there are tables holding logs. The other db is theone holding all of the data that's not likely to change. Aka all the monsters names, skills etc. etc. It's all there and isn't likely to change. It might increase in size as time goes on but it's not likely to change anytime soon. Also when i do the backups it's not going to get backed up. Currently both as myISAM. What i'm going to do with my backup script which i know it'll be complex but basically i'm going to go through and backup the players who need to be backed up into the backup db.

 Basically i'm going to do a backup every 8 hours or so. And it'll only backup the players who have logged in during that time or have signed up in that time. If there is no change in them then i don't see a reason to back them up. I've already set up a couple bools that'll be set bsed upon logins and such other things. Now to my question. Which one would be better for my basically database that'll be backed up. And by best i mean decreasing time it'll take to be done and also reducing the "lag" that'll appear once it's happening as less time needed to do it = less lag.

Just looking for some advice from the seasoned vets of PHP and mysql.

Edit: I've already looked up the two databases on wikipedia and also on the mysql site. But still i've yet to figure out which one would be better for the database that'll be getting backed up. The other one will of course get backed up during the download time.

[Marek]

I would suspect that a plain database dump would be as fast, if not faster, compared to a complex backup script like you describe. When you dump a table, the database doesn't have to do any searching, it just dumps the data.

Themysqldump command is specifically made for this purpose and it runs from the command line. If you run it directly you don't even get the overhead of running a script. I think that's as fast as you can make this.

I found the following about table engine performance. Perhaps someone can back this up with actual experiences.

InnoDB is more efficient than MyISAM when you have a write-intensive environment. Pure read operations are slower than with MyISAM, but when you have combinations of reads and writes InnoDB performs well.

[133794m3r]

hmm... ok that's interesting, i thought having to access/dumping the data would cause "lag" on the server due to the amount of data that's being accessed.

I guess i'll just try to make it go a bit better as far as how well my tables go and try to optimize them as well as possible.

[JGadrow]

InnoDB is my engine of choice for gaming environments for a few reasons:

First, reads and writes are happening frequently. Row-level transactions are a great way to eliminate "race conditions" and also assist in creating speedy backups (becuase the database doesn't need to pause all write operations on the table until it's finished).

Couple this with the fact that InnoDB is the only MySQL storage engine that currently utilizes foreign key constraints and you have the best possible engine for data consistency.

[Mufasa]

Having recently spent 4 grand on a 2 day MySQL consult, I'd have to go with InnoDB as well. Simply switching to InnoDB sped up things drastically. The foreign key thing is great as well, though I won't be able to take advantage of it until I change my code.

The only downside I've seen is that you can't do fulltext searches and InnoDB takes up more space on disk.

[133794m3r]

hmm full text searches might be a tad bit annoying. As that was going to be my old way to find things but knowing that it's far better to do it in Innodb. Now all i have to do is make phpmyadmin convert my current ones to Innodb. Also this foreign key thing is confusing to me as i've yet to know about it. But i will be looking it up.  Thanks for the advice everyone. great responses from everyone here. This seems to be the best community forum i've been at yet.

[JGadrow]

as a quick explanation of foreign keys:

Say you have two tables:
Players
Units

In the players table you have the usual stuff:
player_id
password
...

In the units table you have something like:
player_id
unit_id
quantity

in the units table, player_id is the join column for the two tables so you can figure out which units a player has. If you mark that column as a FOREIGN KEY, you can enforce certain things. First of which, no value may be entered in the units.player_id column that is not a valid player_id from the players table. Secondly, if a change takes place to a row in the players table that should effect the units table (for example, if the account is deleted), you can CASCADE that effect to the units table:
ON DELETE CASCADE

This means, that if the player account is ever deleted, the database will automatically delete any unit entries owned by that player. You can also handle cases of UPDATE. There are several options, but foreign keys are what actually makes your database relational.

[133794m3r]

AH ok that's very intersting, i'll have to try that out a bit. since right now i'm making mine allow players to have up to 2 characters per account. And i of course have their account id on both the login table and also the character's table which is well referenced more often. So knowing that i can do that sort of thing. It should help me out alot. *poor attempt at a kool-aid man impression follows* OH YEAH!

[Chris]

InnoDB require more experience (like count(*), the requirement to setup proper buffers (which not necessarily means the more the better)), it will also consume more RAM.

MyISAM forgive mistakes to greater extend and works perfect with default configuration, also have lesser RAM consumption. It is faster on raw speed (which not necessarily means it will be faster in your case).

You can say MyISAM is a primitive barbarian that have sheer power and is hard to break. InnoDB is like a smart but not so tough wizard, it will obliberate everyone if you know which spell to cast on what monster. If you play the database RPG first time, the barbarian will most likely be the class that will allow you to level up quicker and die less often


Generaly, InnoDB could be a good choice, if you could utilise it. If you don't use transactions, foreign key or other fancy stuff you probably would be better using MyISAM.

By the way, selecting storage engine by backup performance is a kind of insanity to me Sure, backups are important, but there are many other things that are much, much more important

[JGadrow]

COUNT(*) is only slow if you do not also use a WHERE clause. Also, MyISAM eats space too if you forget to OPTIMIZE the table after performing deletes and etc. Really, you should learn both but, if you want data integrity you should go with InnoDB. Personally, I always prefer learning the difficult task first.

Same reason that I started HTML with a STRICT doctype instead of TRANSITIONAL: holding yourself to a higher standard only improves your abilities.

[codestryke]

If you want a relational database use INNODB, if you want a storage engine use myISAM 

It really all boils down to time (or call it laziness). Take for example you wanted to delete a player after 30 days of inactivity. With myISAM you would need to create a function that would execute a delete on any table that related to the player table, lets say tables player, player_attacklog, player_messages, player_inventory etc etc etc. Anytime you add another table that references the player table you'll have remember and go back to that function and add another delete.

Or with INNODB issue one delete command on the player table and the database knows how to delete all relation data to that table. If your at all interested in performance the delete will go faster based in C/C++ the INNODB is written in then continually going back to the PHP interpreter which then calls the myISAM library.

As for tuning INNODB there are a number of great utilities out there now that analyze your current setup and makes recommendations based on current usage data. myISAM is ridiculously fast at reads, if your writting a blog myISAM would work. Games however have an almost 50/50 with reading and writing. It'll hold up for a while but when your site starts getting some really good activity it quickly breaks down.

For those that run myISAM and you haven't suffered the dreaded table locking lag then you really don't have any activity (hardware doesn't matter either BTW a lock is a lock).

[133794m3r]

Hmm... so then the player db would be the best one to do with the user info and my database i have set up that's well written and wrote into. I think i'll change back the one that's purely read only to MyISAM then since it's "ridiculously fast at reads" and it won't have anything wrote to it unless i add something or change something. which'll not be every second of the day. Most of it's life will be just reading.

Also i have NEVER played the barbarian class in ANY rpg i've ever played. I always go for the glass canon ranged melee class. Knowing full well at any moment i'll die if i don't make the correct decision.

I'll go with MyISAM on the static tables/database. And use InnoDB on the one which'll be holding logs and such other things. As the InnoDB will be the only one that's backed up on a regular basis ie atleast once a day. The other one will just be backed up like maybe once a week or something. But even then it's not going be wrote on much. Thanks for all the help. Now time to get back to my random work i'm doing right now. Hopefully i'll have something tangible soon.

[Nox]

I think i'll change back the one that's purely read only to MyISAM then since it's "ridiculously fast at reads" and it won't have anything wrote to it unless i add something or change something.

This is probably at least to some degree a myth ...
http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
in these benchmarks (reading only) InnoDB actually beats MyISAM in speed in every test...

Of course there are aspects where MyISAM is better (if you read through the articles you most likely find several things), just wanted to point out that it's really not so black&white and that InnoDB can actually be faster in some cases

I posted about count(*) here: http://community.bbgamezone.net/index.php/topic,2204.msg15970.html#msg15970
there's also workaround included, when you can do the count(*) without where quite fast, although with reduced precision

[Chris]

Also, MyISAM eats space too if you forget to OPTIMIZE the table after performing deletes and etc.

Under *extreme* optimisation I was able to run 1000 players a day game with a memory spike usage below 64MB (not recommended, but doable), MyISAM of course. Codestryke, who uses mostly InnoDB, was saying that minimum for DB is like 1GB (I don't recall correctly, can't find that topic). Sure, it might be not only because of storage engine type, there is coding style, game type,  hardware type, system, etc, etc. But still...

Personally, I always prefer learning the difficult task first.

Same reason that I started HTML with a STRICT doctype instead of TRANSITIONAL: holding yourself to a higher standard only improves your abilities.

Well, for learning I would agree. It is a good general policy. But I prefer to respond in a style that assume the asker finished learning and want to make a professional game as soon as possible and as efficient as possible.

It really all boils down to time (or call it laziness). Take for example you wanted to delete a player after 30 days of inactivity. With myISAM you would need to create a function that would execute a delete on any table that related to the player table, lets say tables player, player_attacklog, player_messages, player_inventory etc etc etc. Anytime you add another table that references the player table you'll have remember and go back to that function and add another delete.

I wonder, is there any other reason for this than delete player? I heard this point a few times and when it comes to this functionality, everyone mention only delete player, I can't also find in my code any other place where I could use it. Do you use it for any other thing in your game? Can you find any place in your code that could use it? If there any place in your game code you regret you have not used it?
Please, practical usage in existing/upcoming games only, NOT in business software. I know it is a great thing, I can find several uses for it in business software. If I was doing anything else than a BBG I would go for it without hesitation. It's just that I want to know if I will be able to benefit from it when making BBG (other than delete player).

As for tuning INNODB there are a number of great utilities out there now that analyze your current setup and makes recommendations based on current usage data.

Could you drop a link? To something simple and easy to use if possible
Also, could you post some estimates? What buffers you set for what players volume? What you use in your games?

For those that run myISAM and you haven't suffered the dreaded table locking lag then you really don't have any activity (hardware doesn't matter either BTW a lock is a lock).

2700 daily players, average 100 clicks per player, 100% MyISAM. No lags (the first one to break down during last players' spike was... announcement system, definitely not related to locking )

I posted about count(*) here: http://community.bbgamezone.net/index.php/topic,2204.msg15970.html#msg15970
there's also workaround included, when you can do the count(*) without where quite fast, although with reduced precision

What!? You mean all this buzz on net over count(*) was for no WHERE clause that is used rarely anyway? And that count(*) + WHERE works equally fast on both storage engines? I'm outraged...

[Nox]

I wonder, is there any other reason for this than delete player? I heard this point a few times and when it comes to this functionality, everyone mention only delete player, I can't also find in my code any other place where I could use it. Do you use it for any other thing in your game? Can you find any place in your code that could use it? If there any place in your game code you regret you have not used it?
Please, practical usage in existing/upcoming games only, NOT in business software. I know it is a great thing, I can find several uses for it in business software. If I was doing anything else than a BBG I would go for it without hesitation. It's just that I want to know if I will be able to benefit from it when making BBG (other than delete player).

Deleting message, deleting item or whatever...but I guess it really depends on app's needs and might be a bit overrated

What!? You mean all this buzz on net over count(*) was for no WHERE clause that is used rarely anyway? And that count(*) + WHERE works equally fast on both storage engines? I'm outraged...

I base it on this: http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/

I guess it's actually not "equally fast" rather "equally slow" well...they say not equally, just that the count() process is the same (slower one) so it would be most likely similar, depending on other aspects

[JGadrow]

Also i have NEVER played the barbarian class in ANY rpg i've ever played. I always go for the glass canon ranged melee class. Knowing full well at any moment i'll die if i don't make the correct decision.

lol I like you already. +1!

As for the count(*) issue... yeah it's ONLY bad at giving a COMPLETE row count of the table. Which shouldn't be done terribly often anyways.

A foreign key relation is good for any situation where a key is related to another. If you decide to remove an item because it's too powerful, delete from item where id = $id and voila! they're all gone.

Maybe you allow a player to sell their items. A player has an helm equipped and you have a four table system:

player
item
player_item_head_slot;
player_inventory

Player sells items from inventory, but you also need to remove the same item from their equipment if it's equipped: ON DELETE SET NULL;

[133794m3r]

is it proper to have databases that are MyISAM and tables that are Innodb? b/c i've yet to find a way to actually atler my db to innodb. I'd rather not just delete the whole thing but if i have to i will.

[JGadrow]

Yes. You can define each table engine specifically when you create it. However, if you start using foreign keys be forewarned that both tables need to use InnoDB. Defining a database as using a particular engine only provides the default engine that tables in that database utilize.

[133794m3r]

hmm ok so is there some easy "no hassle" way to change that?  Since i'd much rather just change it once in there. Than to have to keep changing it when i realize that i have to add a table or two.

[JGadrow]

I'd use mysqldump (included with mysql) to dump your database into a .sql file. Then edit that file to change the create table declarations to use the desired table engine and then use mysql with the < operator to use that dumpfile as input:

Code: (text) [Select]

mysql -u root -p < dumpfile.sql
After this, you should have your database back but your tables should be changed to the new engine. If not, you can simply re-edit the file to change the table engines back and import the file to restore to your original structure and data.

[133794m3r]

well it seems that i'm unable to change them. And i'd like to know what makes Mysql not have the option to change my default db engine. in PHPmyadmin. I've yet to find an option in there. And it's literally forcing me to use MYISAM for all my new databases. And that command you gave me makes little to no sense really to me. As i've attempted to look it up to figure out where i should put in my database name.

Is there anyway to change it so i can have INNODB as my default?

[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.