InnoDB or MyISAM

[mdshare]

OK ... first impression is that it's interesting to switch a lot to InnoDB yet not so obvious as it's all dependend on the webapp also...

so please post your comments.... as InnoDB is new for me...

[Guest Anonymous]

Re: InnoDB or MyISAM

For safety - choose InnoDB - it's a much better engine and supports transactions plus some other nice tricks.

For speed - choose MyISAM - the original and just silly fast.

Mixing the two is possible ... as long as you remember never to attempt transaction wrappers around MyISAM tables.

There is also memory tables which are fantastic for (say) a single small table containing application settings. It can be preloaded on application startup, performs like a normal (MyISAM) table and provides speed like no other. But... (there's always a but...) as it's in memory - you will lose it's contents from time to time so you need a system to flush its contents out to disk now and again.

Or you could go the federated engine - this allows some nice new tricks, like linking databases and tables into a larger construct from different sources ... Great fun, but I'm still exploring this one so not sure if its something to be aware of.

For simple applications, my advice is MyISAM. For *any* financial transactions - I'd use InnoDB, that way I can wrap them in a transaction block and use PHP(5)'s try { } catch (Exception) { } blocks.

[oxidati0n]

Re: InnoDB or MyISAM

MyISAM - The commonly used one, I don't think anyone would use it for any financial reasons in this forum.

[Guest Anonymous]

Re: InnoDB or MyISAM

Actually I only use InnoDB these days -- Transaction safety is something sadly lacking these days, but InnoDB provides the necessary facilities.

[oxidati0n]

Re: InnoDB or MyISAM

But you sound as if you're doing something financial with your websites but you work with games, and in my opinion games have nothing to do with real funds or financial-related.

MyISAM seems to keep everyone fine and a lot of people seem comfortable with it. It's not as if any major PayPal type of site would use PHP never mind MySQL anyway, They have everything custom built.

[Guest Anonymous]

Re: InnoDB or MyISAM

Indeed I do, however there is still merit in using transactions:

For example - User a) selling an item via a market to user b). Were the web page to be terminated early (often by the user clicking at high speed), or the item not actually being available at the indicated price, a transaction safety block around the relevant statements makes this a simple and very secure operation.

I've seen far too many games that fail in this basic operation - some big, some small.

The trade of in speed is in general negligible, esp. if the queries are well optimized, the correct indicies are referenced and if possible stored procedures are used to limit the amount of data transfer backwards and forwards to the web-server.

[Floydian]

Re: InnoDB or MyISAM

I've only used myisam

Attempted once to enable innodb on my local web server but it didn't work out. I'm not sure where I went wrong, but I did something wrong during the mysql install process.

Since then, I've gone 100% with myisam and for me there is no need for anything else. I use a number of methods to ensure a user can only do one transaction at a time. This can involved setting a transaction id and then unsetting it after the transaction is done. No other transactions would be allowed until the transaction was completed.

In situations where one person buys something in a market and another person removes that same thing at the same time, I would use a different technique where I attempt to delete the item first, and if that's successful, carry on. It's simple, compatible with php 4, and can easily be turned into a db transaction like deal where you have a class that handles all transactions.

Any script that wanted to do some sort of cash or item transaction would use a set of code that secures all of this for you.

Something like:

$item = new Item();

$item->removeShopItem($item_id);

or

$item->buyShopItem($item_id);

You could even have

$item->removeShopItem($item_id, $shop_id); // for user owned shops, or multiple game run shops

Wish I could say something about innodb, but it doesn't seem like something that is neccessary in a gaming context.

I couldn't say what banks use as I've never been employed by wells fargo... nor do I know anyone else that has either...

 

On a side note, PHP is used in lots of top notch sites. Let's not forget that Yahoo employs Rasmus Lerdorf and has employed him since 2002. Of course he's the creator of PHP. I can't point to any yahoo pages that have a .php extension on them as they remove all that stuff, but I assure you that Yahoo is not letting Rasmus Lerdorf's PHP skills go to waste...

Pay Pal appears to be using ColdFusion which isn't any better or worse than PHP. Again, I can't say 100% for sure that they are because of the lack of extensions on web pages, but a few of them do have a cfm extension. The main pages do not have that though.

Google uses mysql for it's search engine and that's a fact. I can't think of any other example of usage of mysql that is more prominent than that. mysql dominates the database market for a reason. Sure there's lots of other db's out there, but I doubt any of them have more market share than mysql. If it's good enough for google, it ought to be good enough for the rest of us.

[mdshare]

Re: InnoDB or MyISAM

 

Google uses mysql for it's search engine and that's a fact. I can't think of any other example of usage of mysql that is more prominent than that.

Now that's new for me afaik google used Oracle till they had Bigtable developed, was once posted or still is at http://labs.google.com/

ah...

Bigtable clusters have been in production use since April 2005, and we spent roughly seven person-years on design and implementation

before that date. As of August 2006, more than sixty projects are using Bigtable.

writing to:

 

// Open the table
Table *T = OpenOrDie("/bigtable/web/webtable");
// Write a new anchor and delete an old anchor
RowMutation r1(T, "com.cnn.www");
r1.Set("anchor:www.c-span.org", "CNN");
r1.Delete("anchor:www.abc.com");
Operation op;
Apply(&op, &r1);

 

reading from:

 

Scanner scanner(T);
ScanStream *stream;
stream = scanner.FetchColumnFamily("anchor");
stream->SetReturnAllVersions();
scanner.Lookup("com.cnn.www");
for (; !stream->Done(); stream->Next()) {
printf("%s %s %lld %s\n",
scanner.RowName(),
stream->ColumnName(),
stream->MicroTimestamp(),
stream->Value());
}

 

anyway here is the link http://labs.google.com/papers/bigtable-osdi06.pdf

[Floydian]

Re: InnoDB or MyISAM

Read this:

http://www.mysql.com/customers/customer.php?id=75

And then a little bit of this one:

http://www.bytebot.net/blog/archives/2007/04/26/mysql-at-google

 

Google has a large MySQL deployment' date=' and they enhance it as needed.[/quote']

 

The second link was found on the page I linked to first.

Google does indeed use MySQL.

Note that I did not say Google only uses MySQL, or Google uses MySQL out of the box with no modifications, but just simply that Google uses MySQL.

;)

 

And if you want to see the vast customer list for MySQL, here is the link to that:

http://www.mysql.com/customers/

[Guest Anonymous]

Re: InnoDB or MyISAM

I don't get:

 

In situations where one person buys something in a market and another person removes that same thing at the same time, I would use a different technique where I attempt to delete the item first, and if that's successful, carry on. It's simple, compatible with php 4, and can easily be turned into a db transaction like deal where you have a class that handles all transactions.

[mdshare]

Re: InnoDB or MyISAM

Sure google uses mysql but not for their online apps aka search engine, maps, etc mysql can't handle nor in the near future multi clusters with petabyte sized databases. I'm sure their are ways but what a mess would that be.

And as far as I know all reasearch labs have multiple *nix OS, other OS and multiple RDBS , even allmighty microsoft uses *nix and google has their own version of ubuntu, HP labs has their own *nix also.

I also don't get it that 'blogs' are more trustworthy than info directly from google labs.

[Floydian]

Re: InnoDB or MyISAM

The info was linked to directly from the MySQL site. If the info is wrong, the egg is on their face, no?

I.e., claiming to have a customer you actually do not have could be considered a minor fraud... (but alas it has nothing to do with innodb lol so I'll leave it at that and respond to Nyna to pm)

[codestryke]

Re: InnoDB or MyISAM

 

The info was linked to directly from the MySQL site. If the info is wrong, the egg is on their face, no?

I.e., claiming to have a customer you actually do not have could be considered a minor fraud... (but alas it has nothing to do with innodb lol so I'll leave it at that and respond to Nyna to pm)

Sorry for the bump, but this has really chapped my hide..

No the egg is on your face for talking out your ass and not knowing the real context of the article you read and spewed to the general populous.

So because you couldn't install an engine on your server you claim it not worthy? Are you kidding me? You horrible sig graphic says your an expert, or alludes to it, with mySQL. Yet you can't get INNODB engine installed LOL? Is it just me or are you speaking out of turn when it comes to locking?

I don't give a rats butt if you have the most super duper locking myISAM transaction locking known to man. Locking the the table vs locking a record is slow when it comes to highly active websites, period. Have you even tested your "engine" with more then 3 ppl online? If not then you would defiantly see why INNODB is better then myISAM when it comes to concurrent read/writes.

If your engine is based on myISAM then I'll take a note from you....

Do NOT buy this engine, I repeat DO NOT BUY THIS ENGINE

I'm sure your a good coder, I've seen some instances when you are very good at what you say but you lack the knowledge of someone that has had to deal with a heavily trafficked site and the rules you must break to make a site work under HEAVY load. Security is one thing (as you claim to be an expert in) but it's no good if your site can only handle 10 people online at a time.

[Floydian]

Re: InnoDB or MyISAM

lol

[CtrlFreq]

Re: InnoDB or MyISAM

 

OK ... first impression is that it's interesting to switch a lot to InnoDB yet not so obvious as it's all dependend on the webapp also...

so please post your comments.... as InnoDB is new for me...

We use a combination of each, depending on the use of the table, all in the same database. MyISAM is best for read/write tables, as reads don't lock, and InnoDB is best for tables with a lot of updates, as it allows for row-level locking and is transaction-safe.

A scenario for each would be as follows:

User and Player tables: use InnoDB, since you're going to be updating these records regularly during game-play.

Audit/Log and Message tables: use MyISAM because you're going to be writing and reading from these tables, but rarely updating them (you may allow for messages to be deleted or moderated and such, but there are few updates expected)

[Karlos]

Re: InnoDB or MyISAM

@ CtrlFreq: If what your saying is accurate.. then

 

DROP TABLE IF EXISTS `members_attack_stats`;
CREATE TABLE `members_attack_stats` (
`AS_ID` INT(7) UNSIGNED NOT NULL DEFAULT '0',
`AS_Strength` DECIMAL(20, 2) NOT NULL DEFAULT '0.00',
`AS_Defence` DECIMAL(20, 2) NOT NULL DEFAULT '0.00',
`AS_Speed` DECIMAL(20, 2) NOT NULL DEFAULT '0.00',
`AS_Dexterity` DECIMAL(20, 2) NOT NULL DEFAULT '0.00'
) ENGINE = InnoDB;

 

Would be the best suited choice for the engine?

[CtrlFreq]

Re: InnoDB or MyISAM

 

@ CtrlFreq: If what your saying is accurate.. then ... Would be the best suited choice for the engine?

Assuming that table is regularly updated, instead of just being inserted into, that's correct, InnoDB is a much better choice for the engine.

[a_bertrand]

Re: InnoDB or MyISAM

Do some tests instead of assuming it would be better. I did, with 200 threads doing queries at the same time and saddly InnoDB was't as fast as MySQL even with updates. But again, this is for my own application, maybe yours is different (I don't use McCode for example). Also, I don't need transactions...

[CtrlFreq]

Re: InnoDB or MyISAM

 

Do some tests instead of assuming it would be better. I did, with 200 threads doing queries at the same time and saddly InnoDB was't as fast as MySQL even with updates.

Your test was wildly under the threshold required to observe the difference. As for testing, I can attest to the performance differences from more than a few years of production-level usage, during which my hand has been forced in an number of instances to move from MyISAM to InnoDB due entirely to performance issues. Simply put, when under load, MyISAM cannot compete with InnoDB on a table with a high number of updates because of it's locking mechanism.

Try your test again, only populate your table with about 50-100k records, and do random updates while simultaneously running queries. The MyISAM table will get crushed because any read will have to wait for any updates that came before it to finish because MyISAM only supports full table-level locking. InnoDB, on the other hand, will keep going without even breathing hard due to it's ability to only lock the specific rows affected by the query.

Also, grab a copy of MySQL Administrator, which lets you see specifically what is running against your database during your test - you'll be able to witness first hand the selects being blocked by the updates on the MyISAM table, but not on it's InnoDB counterpart.