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?

What

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.

Starinsky

I understand advantages of InnoDb, but I think that on a shared web hosting - performance and "lightness" is the number priority. Otherwise I would definitely always use InnoDb. – What

@nightcoder: Then you should always use InnoDB. In the big picture, it will be "lighter" than MyISAM. – Starinsky

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.

Veator

Well, on our main server we use InnoDb only. But I am asking about our website on a shared hosting, so I think your ideas don't really apply, because as I understand MySql server on a shared hosting has common settings for all databases of all hosting users. – What

If you're on shared hosting you cannot expect performance. I hope your database is tiny, otherwise you will have a performance disaster. Shared hosting does not allow busy sites or large databases to be hosted. On such sites, performance generally doesn't matter very much. – Veator

Well, we have 500Mb database and hundreds of visitors every day. Queries can be complex enough. But the site runs ok, maybe we are lucky with our hoster. – What

@nightcoder: 500Mb is relatively small. If you've tuned your queries, and added all the proper indexes, the database will barely notice that you are there. – Starinsky

Yes it is definitely luck rather than judgement; you have no idea who you're sharing with or what MySQL atrocities their broken applications are committing. 500M is small enough that it fits in ram on a very modestly specced server; if you don't have enough performance, just buy one of those it's much cheaper than the developer time to evaluate alternatives. – Veator