(I've seen this question but it's not specific enough for what I want to ask.)
I'm setting up a large-ish (a hundred GB or so) log table with an average record size of 100-200 bytes and several indexes (indices?). Insertion rate will be about 100-200 records per second. I will run analytical queries on this table, probably not all of them will hit a suitable index so they might run for a long time and look up a lot of data.
What storage engine would you suggest? (Basically MyISAM vs InnoDB.)
If using MyISAM, will long queries block inserts?
Table size is a concern (not a big one, but still). In this respect, is one engine more efficient than the other?
Performance-wise, how do they compare?
Is there anything else I must be aware of in this situation?
解决方案
if you use INSERT and UPDATE high performance uses InnoDB over MyISAM is better.
and if you use more SELECT statements before the INSERT / UPDATE uses MyISAM.
The InnoDB has support with ACID (Atomicity, Consistency, Isolation and Durability) therefore the more SELECT and JOIN is slower but is faster for INSERT.
EYE: If you need to transact such a payment gateway, you should use InnoDB has transaction support