CODEWITHSUNDEEP
mysqlperformanceinnodbmyisam
Ivan
Ivan

Reputation: 327

MySQL storage engine for a large log table

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

  1. What storage engine would you suggest? (Basically MyISAM vs InnoDB.)
  2. If using MyISAM, will long queries block inserts?
  3. Table size is a concern (not a big one, but still). In this respect, is one engine more efficient than the other?
  4. Performance-wise, how do they compare?
  5. Is there anything else I must be aware of in this situation?

Upvotes: 3

Views: 3072

Answers (1)

Edy Aguirre
Edy Aguirre

Reputation: 2143

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

Upvotes: 7

Related Questions