Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Aug 26 '12 at 17:08

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks DTest. Trying to convert. Regarding the tuning part, can you direct me to the proper resource? –  Noam Aug 27 '12 at 8:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.