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.