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:

• 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

• user11123: Please provide following: SHOW CREATE TABLE tbl_name\G SHOW TABLE STATUS LIKE 'tbl_name'\G MYSQL config file i.e. /etc/my.cnf How much RAM do you have on the server? Do you do single insert or batch insert (i.e. load data infile or insert into tbl_name values (...),(...)...
• Noam: Thanks. Added the info (except the config file, working on it)

Answer 1

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.

• Noam: Thanks, regarding your questions: 48GB RAM, the key_buffer size is 4GB. The indexes are on VarChars(255) and ints. What difference does that make? Can you elaborate on "appended to" indexes? If they are not auto-inc they aren't "appended to"?
• Rick James: "appended": An index on DATETIME or TIMESTAMP, where the rows are inserted in approximately chronological order -- these will be inserted into the 'end' of the index. This is much more efficient than jumping around (GUIDs, MD5, etc).
• Rick James: Don't use VARCHAR(255) too casually. There are cases where a full 255 bytes (or 767 for utf8) will be allocated. (Example: temp tables that use MEMORY.) If you are MyISAM only, and have a new enough version of MySQL (5.0.52 / 5.1.23 or later), key_buffer_size=8G. See mysql.rjweb.org/doc.php/memory .