MyISAM or InnoDB for a mostly-write table

2
votes

I've got a table that receives around 50 inserts per second. Right now there are 700k records, using 160 MiB. My little VPS with 1 gig of ram is keeping up, but just barely.

I chose for InnoDB, as people say "it scales better", gets corrupt less frequently and implements row locking instead of table locking.

But is it the right choice for this?

I've also read MyISAM supports delayed inserts, which could be very interesting. (I wonder what the trade-off is with table locking)

1
mysqlinnodbmyisamstorage-engines
What you should get is a hard drive that's dedicated only to you and not to X users. You'll be able to push more than 50 inserts / second (around 150 if I'm not mistaken with 7200RPM drive) without problems if you use InnoDB. Other than that, what Johan said (+1 from me). – Michael J.V.

1 Answers

5
votes

InnoDB, no question.

  • Row level locking
  • transactions
  • better integritry

InnoDB allows delayed inserts as well.

The only issue I can think off is that running MySQL without InnoDB support (special compile of the source) can use 100MB less memory.
Don't do that though, it's not worth the headaches.