2

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
2
  • 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 (...),(...)...
    – user11123
    Commented Aug 26, 2012 at 11:15
  • Thanks. Added the info (except the config file, working on it)
    – Noam
    Commented Aug 26, 2012 at 11:47

1 Answer 1

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.

3
  • 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"?
    – Noam
    Commented Sep 2, 2012 at 7:22
  • 1
    "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
    Commented Sep 12, 2012 at 0:26
  • 1
    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 .
    – Rick James
    Commented Sep 12, 2012 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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