Re: HELP! making choises on Performance - Storage engine - Caching
Posted by:
Rick James
Date: November 10, 2009 09:29AM
The debate over relative speed of MyISAM vs InnoDB rages on. YMMV.
In InnoDB, the PRIMARY KEY is 'clustered' with the data, so is one less step to fetch a row by PK.
In an InnoDB secondary index, a leaf node has the PK of the row, so the lookup requires a second BTree drilldown.
MyISAM uses a simple byte or record offset to go from index to data.
Based on the above comments, when you have many indexes on a table, it is important (for performance) to have a short PK in InnoDB, but does not matter in MyISAM.
MyISAM locks only at the table level, but it is very simple and fast. InnoDB locks at the row level. A long-running UPDATE will blocks SELECTS in MyISAM; it will not in InnoDB. A short-running UPDATE -- I defy you to tell the difference.
InnoDB has lots of overhead in the data, so disk footprint is 2x-3x bigger than equivalent MyISAM table. For small tables (completely cached in RAM), this is not an issue; for large tables with highly random access, this can be an I/O difference.
MyISAM locking delays other operations; InnoDB locking either slows down other operations or causes deadlocks. Deadlocks need user code to recover from; usually by simply rerunning the TRANSACTION.
If your queries (in addition to your dbs) are "well optimized", then they may run fast enough to avoid most locking situations in either Engine.
If the server crashes, InnoDB automatically cleans up the tables based on the transaction log. This is more secure, more automated, but still not 100% protection from loss. The more you turn on flags to make InnoDB more crash-safe, the more disk hits it will do, hence the slower it can be.
Etc, etc.
Bottom line: Ignore the speed debate; go with what seems 'right' for other reasons. Then, if you still have performance, problems, optimize to the strengths of the Engine you picked.
Mixing InnoDB and MyISAM? No problem. There are advantages and disadvantages:
* FULLTEXT is available only in MyISAM.
* TRANSACTIONS (START...ROLLBACK/COMMIT), if they involve MyISAM tables, will work, but cannot ROLLBACK changes in MyISAM tables.
* There are slight differences in limitations (eg max size of index rows)
* MyISAM has a trick it can play with an AUTO_INCREMENT as the second field of a PRIMARY KEY.
* FOREIGN KEY CONSTRAINTS are honored in InnoDB, ignored in MyISAM. These provide protection against certain programming errors, but the cost for the checks may be non-trivial.
* Etc.