Mysql – LOAD DATA INFILE blocks on concurrent use against MyISAM tables

innodbmyisamMySQL

I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks – a system lock, in MySQL profiling information – until the previous is finished. This happens independent of loading into the same/different table, or even a different database. InnoDB does not have the same problems, however, MyISAM seems better suited for a pure logging-table.

Can concurrent LOAD DATA INFILE operations be done with MyISAM engine on MySQL? Am I missing some crucial settings (DISABLE KEYS is automatic against an empty database)? Tested on MySQL 5.1.63 and 5.5.14.

I expected loading using LOAD DATA INFILE to different tables concurrently to be performant, however, performance is instead completely destroyed.


To replicate my scenario:

SQL

CREATE DATABASE test;
CREATE TABLE `log_isam` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `num` int(11) DEFAULT NULL,
  `surname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE log_isam ADD INDEX num(num);

CREATE TABLE `log_isam2` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `num` int(11) DEFAULT NULL,
  `surname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE log_isam2 ADD INDEX num(num);

LOAD DATA log.txt file contents

https://gist.github.com/4245602

Shell script

MYSQL_USER=root
MYSQL_PASS=PASS
time mysql -u$MYSQL_USER -p$MYSQL_PASS -e \
    "LOAD DATA INFILE '/tmp/log.txt' \
     INTO TABLE log_isam FIELDS TERMINATED BY ','" test & \
time mysql -u$MYSQL_USER -p$MYSQL_PASS -e \
    "LOAD DATA INFILE '/tmp/log.txt' \
     INTO TABLE log_isam2 FIELDS TERMINATED BY ','" test

Best Answer

Concurrent LOAD DATA INFILE on MyISAM tables are limited by mutex congestion due a global Key Cache. To alleviate lock congestion, MyISAM tables can be set to have their own Key Cache.

SET GLOBAL isam_cache.key_buffer_size=8*1024;
SET GLOBAL isam_cache_two.key_buffer_size=8*1024;
CACHE INDEX log_isam IN isam_cache;
CACHE INDEX log_isam2 IN isam_cache_two;