0

I am trying to import a large file, it was really big and it did not seem to be working so I broke it into pieces. The first piece takes 2 minutes, and the second one takes almost an hour!!! How can that be???

bash-3.2$ wc -l allelePiecesaa 
2575063 allelePiecesaa
bash-3.2$ cp allelePiecesaa allele.txt
bash-3.2$ time /usr/bin/mysqlimport -h bluemoon-mgmt1a -u jbond --delete --local --fields_escaped_by=\\ MBSR -L allele.txt
MBSR.allele: Records: 2575064  Deleted: 0  Skipped: 0  Warnings: 6
real    1m50.285s
user    0m0.006s
sys     0m0.103s
bash-3.2$ cp allelePiecesab allele.txt 
bash-3.2$ wc -l allele.txt 
2545972 allele.txt
bash-3.2$ time /usr/bin/mysqlimport -h bluemoon-mgmt1a -u jbond --local --fields_escaped_by=\\ MBSR -L allele.txt
MBSR.allele: Records: 2545973  Deleted: 0  Skipped: 0  Warnings: 7
real    57m17.279s
user    0m0.030s
sys     0m0.097s

my configuration is:

[client]
user=X
host=Y
password=Z
port=3306

[mysqld]
init_connect='SET autocommit=0'
init_connect='SET foreign_key_checks = 0'
init_connect='SET unique_checks = 0'
log_slow_queries = 0

after I do SHOW CREATE TABLE allele; I get:

mysql> SHOW CREATE TABLE allele

CREATE TABLE `allele` (
    `allele_id` int(11) NOT NULL auto_increment,
    `variation_id` int(11) unsigned default NULL,
    `subsnp_id` int(11) unsigned default NULL,
    `allele_code_id` int(11) unsigned default NULL,
    `population_id` int(11) unsigned default NULL,
    `frequency` float unsigned default NULL,
    `count` int(11) unsigned default NULL,
    `frequency_submitter_handle` int(10) default NULL,
    PRIMARY KEY  (`allele_id`),
    KEY `variation_idx` (`variation_id`),
    KEY `subsnp_idx` (`subsnp_id`),
    KEY `population_idx` (`population_id`) 
) ENGINE=MyISAM
AUTO_INCREMENT=1082088650 DEFAULT CHARSET=latin1;
  • @JehadKeriaki SHOW CREATE TABLE allele\G is much better! :-) – Vérace Oct 26 '15 at 6:37
  • 1
    @ramiro Please don't hesitate to use the editing tools at the top of the window where you type in your question - code block, quote... &c. p.s. welcome to the forum. – Vérace Oct 26 '15 at 6:38
1

I am thinking of two things to try. If you get faster execution time, then the slowness would be related to the distribution of the data. If you still get slow execution time, it maybe then related to the server configs.

  1. Try to load allelePiecesab file before. If this is slow, then the data in this file has higher cardinality of key fields.
  2. Try to disable the indexes (as this is MyIsam), and load the files in whichever order, then re-enable the keys. If this is faster, then the slowness is related to the updating time of index files after certain growth:

ALTER TABLE allele DISABLE KEYS; // Do the load ALTER TABLE allele ENABLE KEYS;

|improve this answer|||||
  • Thank you very much. Disabling the indexes helped a lot, and now the data is going in. Thanks again for your help. – ramiro Sep 23 '15 at 13:29
  • If @Jehad's answer has helped you, please mark it as correct so that others with the same problem will be able to solve their issues. – Vérace Oct 26 '15 at 6:40
0

Set key_buffer_size to 20% of available memory. This may help significantly.

|improve this answer|||||
  • Hi Rick, maybe this should have been a comment? – Vérace Oct 26 '15 at 6:35
  • I'm presuming you are speaking from experience here about something that may have helped you in the past. The issue as I see it, though, is that without any kind of explanation of why or how this particular setting can be helpful a large part of the audience may end up either blindly accepting or blindly rejecting your suggestion. – Andriy M Oct 26 '15 at 16:50

Your Answer

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

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