InnoDB vs. MyISAM

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

InnoDB vs. MyISAM

Post by icesolid »

I was told recently that using InnoDB in MySQL vs. MyISAM is a better solution if your tables are consistently being updated/deleted.

Is this true? If I just change the table type to InnoDB, is that all I have to do? Any changes to the database tables or my PHP code?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

InnoDB storage engine uses row-level locking instead of table-level locking - so, in general it should be true.

There are many advantages and disadvantages of using InnoDB vs. MyISAM: http://www.google.com/search?q=myISAM+v ... __206__206
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

The reason they claim that is because INNODB uses automatic row locking and MYISAM doesn't. You cannot lock an individual row with MYISAM but you can lock the entire table using MYISAM.

Under MYISAM if you lock a table then nothing can access the table except for the process that locked the table. This means all MYSQL database accesses to that table are PAUSED until the table is unlocked. This can cause a backup of MYSQL processes if your database server is slow and/or overloaded. When that happens you will usually start seeing error messages. Most applications do not use table locking because it really isn't needed.

INNODB uses automatic ROW LOCKING. When a database process accesses a row in a table to update, delete or insert data the row is locked and no other database process can access that row data. Unlike MYISAM table locking other database processes can STILL access other rows in the table. Row locking will prevent another process from trying to update the same row at the same time as another process. The processes accessing that row will be processed in a first come first served order.

For most applications row locking or table locking isn't needed because even on massive tables the data is updated fast enough that it is finished before another process can perform its operation. We run many e-commerce sites and NEVER use any form of row or table locking and there has never been a conflict any time in over 10 years of operation.

The one major drawback to INNODB are the memory requirements. INNODB loads entire tables into memory for processing. MYISAM loads only the MYISAM index into memory for processing table data. If you have tables with massive amounts of information then you will need massive amounts of memory to allocate to the database server. INNODB should only be used on servers that have a lot of memory and are dedicated only as a database server.

INNODB can also be more secure as it has automatic recovery processes built-in if something like a server crash happens and the database has been corrupted.

The other thing to remember is the data for INNODB databases is NOT stored in separate directories like MYISAM. It does create separate directories but they only contain support files. The actual database data is stored in a massive file called ibdata? where the question mark is a number. The ib_log?? files are used for database recovery of INNODB databases that get corrupted.

INNODB has both pluses and minuses and should only be used for specific applications because of the high memory overhead.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

We run many e-commerce sites and NEVER use any form of row or table locking
MySQL automatically use row/table locking (for every query it processes) depending on the db engine in use. For MyISAM tables it means entire table is unavailable while processing statement. The following situation is not that uncommon: table is locked during a select for a long time (due to the massive amount of data), and subsequent inserts are queued until the select is finished. Subsequent select would need to wait for both first select and *all* of the inserts to complete. In such case InnoDB is much more efficient.

InnoDB has some useful features unsupported by MyISAM APJ forgot to mention: transactions and foreign key constraints.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Weirdan wrote:
We run many e-commerce sites and NEVER use any form of row or table locking
MySQL automatically use row/table locking (for every query it processes) depending on the db engine in use. For MyISAM tables it means entire table is unavailable while processing statement. The following situation is not that uncommon: table is locked during a select for a long time (due to the massive amount of data), and subsequent inserts are queued until the select is finished. Subsequent select would need to wait for both first select and *all* of the inserts to complete. In such case InnoDB is much more efficient.
You are right it does use a more intelligent form of internal table locking.

http://dev.mysql.com/doc/refman/5.0/en/ ... cking.html

It can apparently intelligently table lock for read and write access based upon the actions being taken by the current query. It can allow reading of data from a table when data is being inserted under certain circumstances. About the only reason I can see for using the LOCK TABLE statement is if you are inserting and/or updating massive amounts of data and want to prevent access until finished.
Weirdan wrote:InnoDB has some useful features unsupported by MyISAM APJ forgot to mention: transactions and foreign key constraints.
Actually you can use transactions using MYISAM tables but they are less secure that using INNODB. Plus you can lose the ability to rollback a transaction if a non-transaction table is updated while performing a transaction when using MYISAM. INNODB doesn't have this limitation.

Honestly in my opinion there isn't a need to use INNODB unless you require Safe Transaction support using transaction syntax.

http://dev.mysql.com/doc/refman/5.1/en/commit.html
User avatar
ReverendDexter
Forum Contributor
Posts: 193
Joined: Tue May 29, 2007 1:26 pm
Location: Chico, CA

Post by ReverendDexter »

AKA Panama Jack wrote:Honestly in my opinion there isn't a need to use INNODB unless you require Safe Transaction support using transaction syntax.
Unless you're big on data integrity, and insist on having foriegn keys that are enforced by the database instead of trying to ensure them with your code. I personally would much rather have the dbms doing that. But, I like triggers, too, which seem to be frowned upon for lack of portablility.
User avatar
The Phoenix
Forum Contributor
Posts: 294
Joined: Fri Oct 06, 2006 8:12 pm

Post by The Phoenix »

AKA Panama Jack wrote:\Actually you can use transactions using MYISAM tables but they are less secure that using INNODB. Plus you can lose the ability to rollback a transaction if a non-transaction table is updated while performing a transaction when using MYISAM. INNODB doesn't have this limitation.

Honestly in my opinion there isn't a need to use INNODB unless you require Safe Transaction support using transaction syntax.
You cannot use transactions using MyISAM tables. It isn't supported. When you issue the commands, it simply silently ignores the command. Rollback, start trans, commit, all of the transaction commands simply silently fail to do anything.

You can create make-shift transactions with a combination of locks and triggers, but it doesn't ensure the transaction (as a whole) completes, and it doesn't ensure integrity at the db level.

Transactions are meant for things like purchases. I give you the money, and you give me the item I purchased. Having one succeed and not the other isn't a transaction - its a robbery. :) Thats why its critical to have proper transaction support in a database for some systems.

Performance on Innodb has been substantially increased in mysql-5.1. Thats not to say that its always better (its not), but it is competitive and offers additional functionality that myisam tables don't offer.
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post by Charles256 »

Transactions are meant for things like purchases. I give you the money, and you give me the item I purchased. Having one succeed and not the other isn't a transaction - its a robbery. Smile Thats why its critical to have proper transaction support in a database for some systems.
Now that's funny :-D[/quote]
Post Reply