MyISAM performance

Question

• In Mysql (5.7 onwards) for Change tracking of a table, this approach is very simple to implement.
  
• But it needs the versions table to be of MyISAM, which does table level locking.
  
• Would this approach work well for production systems where multiple inserts/updates are happening every second?
  
• Does any one have any real production systems experience about this approach?

Each table in the DB(InnoDB) has Versions table(MyISAM) My system has the following load.
* Approx 500 reads/sec on each table due to various joins.
* And 50 writes/sec to various tables which have triggers to the versions table.
Would the versions table (MyISAM) become a bottleneck for performance?

Answer 1

When a MyISAM table has AUTO_INCREMENT (and a certain mode set), and no other UNIQUE keys, it will append to the table "without a lock". So, I don't think the 50 writes/sec will be an issue.

MariaDB will probably continue to include MyISAM long after Oracle jettisons it. Oracle's intent is to make InnoDB so good that there will be no need for MyISAM, and they are likely to succeed.

Secondary indexes on the versions tables may become a bottleneck. In this area, I think InnoDB's "change buffer" does a better job than MyISAM's "do it now".