Quick question, When you select, insert or update into an myisam table, does it require the table to lock? I've never scaled large-large applications.
When should I start worrying about myisam->innodb?
1 Answer
Don't guess, estimate.
Time your queries on reasonable hardware. Let's say that your most common modifying query takes 15 milliseconds and the worst one that's not too uncommon takes 500 milliseconds.
That means that without concurrency, you could service 66 of the common queries per second, or one uncommon and 33 of the common ones.
Now, how many of these queries does a single user request do? How many users do you hope to have on your peak second? Do these numbers make you feel good or bad?
With MyIsam, you have to take in account the whole query time, because there's no concurrency on modifying a given table.
With InnoDB, on the other hand, your limit is the number of seeks your disks can give per second (usually 150-180 per second per spindle), a much better number, since that's the operations that hit the disk (with enough cache, it gets around one seek per modifying transaction), and also you can always rise it with more spindles or SSD's (even a single one can reach 10,000 operations per second).
