So I have a workload on 2 key tables in my application with the following characteristics:
- READ-ONLY DATA
The data is bulk-loaded in a fully consistent manner from a single process on a schedule, there are no deletes nor updates. I am using
INSERT IGNORE ...
- Very Large Tables
I am using temporal partitioning, but even with that my expected worse case on a single paritition is 10M-100M records per paritition (older partitions are eventually deleted).
- No need for FK, transactions, consistent reads
Theses table are used to seed very targeted time-sensitive queries and reporting views. There is no need for enforced FK constraints (there are no explicit FKs) and certainly no transactions are needed. There is also no need for clients issuing selects to have a fully consistent view of the data -- so if I am in the middle of a bulkload and they only see 1/2 of the in-progress updates, that's totally cool.
I initially was leaning towards InnoDB and then went back to MyISAM after reading about concurrent inserts, which sounds like a perfect description of my use case (high number of inserts with no intervening updates) I am thinking of going back to MyISAM esp given how many other features that I just don't care about (e.g. Foreign Keys, transactions).
Could someone with real-world experience using MySQL for similar workloads offer a suggestion on what storage engine is the right choice here. Plan on doing some serious testing early next week but just would like to get a rough idea of what to expect/look out for here.