When it is time for a table to change from MyISAM to InnoDb?

Question

This question is like a continuation of my previous question: Am I right that InnoDb is better for frequent concurrent updates and inserts than MyISAM?

But this time I have concrete questions.
We know that MyISAM is faster than InnoDb when we don't have many concurrent updates (inserts). When we have many concurrent updates MyISAM table gets locked and all other clients should wait.

1) But when it is time to change from MyISAM to InnoDb? 1 update every second? 10 updates every second? 100 updates every seconds?
2) For a concrete example, will it be better to change to InnoDb one of the tables on my website where I usually have a few updates per minute (from different sessions), but sometimes it can be ~2-3 updates per second?

Answer 1

I think your questions were pretty much answered in the previous question you referred to.

1) When you create the table. InnoDB provides more advantages than merely row-level locking. Better speed when you have multiple sql clients doing updates, less risk of data loss/corruption, referential integrity etc... With only a few transactions per second (TPS) you're not likely to tell the difference in the performance, but InnoDB is more reliable and scales better.

2) With only 2-3 TPS you will not see any significant difference between InnoDB and MyISAM. Even on semi-ancient hardware.

FYI, a modern disk drive should be able to handle at least ~200 update transactions per second.

I recommend that you read up on InnoDB, MVCC and ACID.

Answer 2

You should probably not change one-table-at-a-time if you can.

Change the entire server. That way you can tune your server for an innodb-only workload rather than a myisam-workload. The two are mutually incompatible (memory buffers are allocated for specific engines; they cannot share memory).

Say you have 16G of ram, you would probably want to use about 12G for an innodb buffer pool, provided you have no MyISAM tables.

Likewise, if you have only MyISAM tables, you probably want to turn innodb off completely, and give a bit less than half (say 6G) to your myisam key cache.

A mixed myisam-innodb server needs to trade off memory tuning.

Also you only really want to do your performance testing once, not for every time you change a table.

It is a massive over simplification in some cases, and plain wrong in others to say "We know that MyISAM is faster than InnoDb".

If you've tuned InnoDB properly for your server and are running proper server-grade hardware, InnoDB should be able to compete with MyISAM on most queries (Excluding full table scans, but you're not doing many of those, right).

That is, unless your data are "trivial joke" size.

If you find that innodb is much slower for inserts / updates, bear in mind that it offers a much better level of durability - if you tune it for approximately the same durability as MyISAM, then you'll see good performance hopefully.