Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer 1

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;
share|improve this answer
    
Since my answer did not help, I can remove it. +1 for you !!! –  RolandoMySQLDBA Dec 10 '12 at 22:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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