MySQL table locks solution -> InnoDb / Partitions

Question

I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows).

It's mainly caused due many heavy inserts. My current thoughts to optimize:

1. Changing to Innodb - to avoid table level lock. In the past I've had bad experience with Innodb, possibly due to bad configuration. If you would recommend this option, please advise regarding configuration.
2. Adding partitions - we're running on one server, and we don't have different mounts in the HDs, so we're just avoiding locks in such a solution. Currently the table isn't 'key-value', but it can be done (normalization to different table).

Which would you recommend / other options?

Thanks.

Answer 1

Definitely go with InnoDB, and especially upgrade to MySQL 5.5 (if you're not already running it and if possible). There are many improvements in InnoDB performance in 5.5.

Frankly, your use-case of a table that is 10GB and 15million rows can easily be handled by InnoDB. Proper tuning is the stuff of another question though!

As a side note though, even if you partitioned it and left it as MyISAM, you would still get table-locking issues.

For example, a SELECT from a partitioned MyISAM table causes a lock on the entire table.