MySQL table locks solution -> InnoDb / Partitions

configurationdatabase-designinnodbmyisamMySQL

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

Best Answer

key_buffer_size ? RAM size?

On huge tables, indexes, especially if they are not "appended to", cause disk hits -- whether MyISAM or InnoDB.

var0, being an AUTO_INCREMENT, will be "appended to" the end. Essentially no disk hits for it. I can't tell about the other 4 indexes. Let's say they are all very random. This implies that each row inserted will need 4 disk hits. On normal drives that means about 25 rows inserted per second.

Solutions...

RAID striping (0,5,6,10). That will give you a factor of improvement.

SSDs. $$$

PARTITIONing. This may help. It may also help with some SELECTs. To discuss further, please reveal some of the semantics of the indexed fields. (dates? md5s? names?) Also provide some of the SELECTs, so we can optimize them at the same time. The "partition key" must be part of any UNIQUE key. This is not a problem with the AUTO_INCREMENT, but it may be with the other UNIQUE key.

Nothing (yet) says whether the Engine will make any difference.

Related Question