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:
- 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.
- 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?
This is the SHOW CREATE TABLE
CREATE TABLE `tbl_name` (
`var0` int(10) unsigned NOT NULL AUTO_INCREMENT,
`var1` int(11) NOT NULL,
`var2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`var3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`var4` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`var5` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`var6` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`var7` tinyint(1) NOT NULL,
`var8` int(11) NOT NULL,
`var9` int(11) NOT NULL,
`var10` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`var11` int(11) NOT NULL,
...
`var22` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`var0`),
UNIQUE KEY `var1` (`var1`),
KEY `var2` (`var2`,`var3`),
KEY `var4` (`var4`),
KEY `var5` (`var5`)
) ENGINE=MyISAM AUTO_INCREMENT=13932545 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
This is the SHOW TABLE STATUS
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment tbl_name MyISAM 10 Dynamic 13931652 2605 36293518012 281474976710655 1318508544 0 13933203 2012-07-31 13:25:26 2012-08-26 14:44:44 2012-08-20 23:15:31 utf8_unicode_ci NULL