This is sort of a continuation of these question: http://stackoverflow.com/questions/1722155/am-i-right-that-innodb-is-better-for-frequent-concurrent-updates-and-inserts-than/
But this time around I've concrete questions.
We all know that MyISAM is faster than InnoDb whenever we do not have many concurrent updates (card inserts). Whenever we have numerous concurrent updates MyISAM table will get locked and all sorts of other clients should wait.
1) However when it's time to vary from MyISAM to InnoDb? 1 update every second? 10 updates every second? 100 updates every seconds?
2) For any concrete example, could it be easier to switch to InnoDb among the tables on my small website where It's my job to possess a couple of updates each minute (from different periods), but sometimes it may be ~2-3 updates per second?
I believe the questions you have were virtually clarified in the earlier question you known to.
1) Whenever you produce the table. InnoDB provides more advantages than basically row-level securing. Better speed if you have multiple sql clients doing updates, less chance of loss of dataOrproblem, referential integrity etc... With merely a couple of transactions per second (TPS) you are unlikely to differentiate within the performance, but InnoDB is much more reliable and scales better.
2) With only 2-3 TPS you won't use whatever factor between InnoDB and MyISAM. Even on semi-ancient hardware.
FYI, a contemporary disk drive should have the ability to handle a minimum of ~200 update transactions per second.
You need to most likely not change one-table-at-a-time if you're able to.
Alter the entire server. That method for you to tune your server to have an innodb-only workload as opposed to a myisam-workload. The 2 are mutually incompatible (memory buffers are allotted for specific engines they can't share memory).
If you have 16G of ram, you'd most likely desire to use about 12G to have an innodb buffer pool, provided you've got no MyISAM tables.
Likewise, for those who have only MyISAM tables, you most likely wish to turn innodb off completely, and provide a little under half (say 6G) for your myisam key cache.
An assorted myisam-innodb server must downside memory tuning.
You also only actually want to do your speed and agility testing once, not for any time you change a table.
It's a massive over simplification in some instances, and plain wrong in other people to express "We all know that MyISAM is faster than InnoDb".
If you have updated InnoDB correctly for the server and therefore are running proper server-grade hardware, InnoDB should have the ability to contend with MyISAM of all queries (Excluding full table scans, but you are not doing a lot of individuals, right).
That's, unless of course your computer data are "trivial joke" size.
In the event that innodb is a lot reduced for card inserts / updates, keep in mind it offers a far greater degree of sturdiness - should you tune it for roughly exactly the same sturdiness as MyISAM, then you will see good performance hopefully.