0
  • 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?

Dennis
  • 7,907
  • 11
  • 65
  • 115
aadidasu
  • 1,037
  • 4
  • 15
  • 26
  • I don't have direct experience with this type of high-performant MyISAM scenario, but ... I think MyISAM will be okay *provided* you set up your tables and server appropriately. I'm talking about using static fields, proper indices, and you can even use a dedicated server for the task that needs this performance and I think MyISAM will do just fine. You can also set up your database on an SSD rather than a hard drive. – Dennis Jul 21 '17 at 18:16
  • 2
    The only reason for MyISAM here is to get the increment. So what you gain is the time to not look up a method to do it with InnoDB (status table/column or simply using e.g. a timestamp instead of an increment). What you lose: it won't work in MySQL 8 (no MyISAM anymore). And you will have invalid version entries whenever an InnoDB-transaction rolls back (as it can't roll back in MyISAM). The performance on the other hand should not be a problem, and if it is, you can shard it, e.g. put versions for odd/even ids in table/server 1/2. Anyway, my point is: MyISAM is dead. Let it rest in peace. – Solarflare Jul 21 '17 at 18:30
  • The MyISAM Storage Engine will perform a full table lock. Read and write cannot be done at the same time to a table, all other sessions that want to access this particular table must wait until the update is done. InnoDB will definitely solve this. For production system with lots of writes, you will have to try turning query_cache off. Too large query cache size leads to significant performance degradation. Because of cache overhead and locking. Cacheable queries take out an exclusive lock on MySQL’s query cache. –  Aug 30 '17 at 07:45

1 Answers1

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".

Rick James
  • 135,179
  • 13
  • 127
  • 222