I'm using a fairly simple bash script to execute mysql load data infile to 11 different MEMORY engine tables.


echo "start $@ $(date)"
mysql -p"mypass" -e "use db; set bulk_insert_buffer_size = 1024 * 1024 * 256;
   alter table testLoadData$@ disable keys; 
   LOAD DATA INFILE '/var/db/mysql_secure/clearedhalf$@.log' INTO TABLE testLoadData$@ FIELDS TERMINATED BY '\t' lines terminated by '\n'; 
   alter table testLoadData$@ enable keys;"

echo "end $@ $(date)"

Now, multi.sh:

for i in {0..10}
    /usr/local/bin/bash /root/loadinfile/load.sh $i &

On a single table this runs about 12 seconds for a 200Mb file with around 1 million lines in it.

However when I run 10 of them in parallel it looks like they are locking at some point, mysql only uses one core and HDD is used about 6Mb/s (we do have raid10 ssd on this box), and it takes about 2 minutes for some reason.

My question is - do I need to change/tweak some settings to allow mysql use all of my resources and perform all of these loads simultaneously?

  • Try it with InnoDB instead of MEMORY. – Rick James May 21 '17 at 14:03
  • (That's 11 in parallel.) – Rick James May 21 '17 at 14:03
  • Hey @RickJames ! yes you are correct that's 11, and I've tried Innodb and it had worse performance even with all the needed tweaks. – Crashus May 21 '17 at 15:25
  • 1
    Check your table names and their sizes. Did the start/end times overlap as expected? – Rick James May 21 '17 at 16:07
  • All tables are filled in correctly and there are no problems, all tables are done almost simultaneously which is suggesting that they're all processed at the same time but mysql is not using all of the server resources – Crashus May 21 '17 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.