Any problem will all InnoDB and one MyISAM table?

Question

I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM table just because this one table doesn't need transactions.

• Is there a maintenance or tuning headache here?
• Any benefit to it?

Thanks

Answer 1

Is there a maintenance or tuning headache here? ABSOLUTELY !!!

Any benefit to it? I'll pretend I didn't read that.

The MyISAM storage engine always performs a full table lock with each DML (INSERT, UPDATE, DELETE). SELECTs block the daylights out of DML Statements.

Any transactions that mix MyISAM and InnoDB tables will cause even InnoDB tables to degenerate to full table locks because of the presence of even just one MyISAM table. I wrote about that in StackOverflow back in March 2011.

If the only MyISAM table is just the log table you mentioned, that is still very bad. The reason? If 50 DB Connections want to write a single row into the log table, there will 50 full tables locks, 50 inserts. Here is what is worse:

• 50 DB Connections, 1 performs table lock, does INSERT, 49 DB Connections have to wait
• 49 DB Connections, 1 performs table lock, does INSERT, 48 DB Connections have to wait
• 48 DB Connections, 1 performs table lock, does INSERT, 47 DB Connections have to wait
• ...
• 1 DB Connection, 1 performs table lock, does INSERT

Get the idea ??? This is what awaits you by making the log table MyISAM !!!

If the same log table was InnoDB, 50 DB Connections would perform 50 one line transactions that do not block each other.

Please read these questions for your own benefit on MyISAM and InnoDB

• What are the main differences between InnoDB and MyISAM?
• Benefits of table level locking
• Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
• Is it common practice to mix InnoDB and MyISAM tables on same server?

CAVEAT : To be fair, if a MyISAM has a FULLTEXT, then you have no choice but to live with it. MySQL 5.6 will change that problem as InnoDB will support FULLTEXT indexes.

Answer 2

Aside from the aforementioned issues with MyISAM, one of the biggest things is tuning. With mixed tables, you have to tune 2 different storage engines. On top of that, you have to split resources on the server to allocate some memory to the MyISAM key cache and such. With a homogenous storage engine, you can fully remove the resources for one of the engines and focus on the other.