I've been attempting to import a very large (390 GiB) text file using the mysqlimport client. I've been successful at importing a 14 GiB text file as well as numerous smaller files using this method. Server version is 5.7.28

The specific command used is:

mysqlimport -u root --fields-terminated-by='\t' --fields_escaped_by=\\ homo_sapiens_variation_98_38 -L *.txt

The my.cnf file I'm using:

local-infile = 1

secure-file-priv = ""
local-infile = 1
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 8G
innodb_write_io_threads = 64
innodb_log_buffer_size = 256M
innodb_read_io_threads = 64
innodb_buffer_pool_size = 32G
innodb_doublewrite = 0
innodb_flush_method = O_DIRECT

loose-local-infile = 1

It always hangs (repeated 3 times) when it reaches an auto increment value of 2 324 322 623 (i've spaced for clarity).

The number of lines in the file is 2 324 037 635 (determined by sed -n '$=' transcript_variation.txt)

This suggests the file has been read but the process of writing the data did not take place.


Table structure

CREATE TABLE `transcript_variation` (
  `transcript_variation_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `variation_feature_id` int(11) unsigned NOT NULL,
  `feature_stable_id` varchar(128) DEFAULT NULL,
  `allele_string` text,
  `somatic` tinyint(1) NOT NULL DEFAULT '0',
  `consequence_types` set('splice_acceptor_variant','splice_donor_variant','stop_lost','coding_sequence_variant','missense_variant','stop_gained','synonymous_variant','frameshift_variant','non_coding_transcript_variant','non_coding_transcript_exon_variant','mature_miRNA_variant','NMD_transcript_variant','5_prime_UTR_variant','3_prime_UTR_variant','incomplete_terminal_codon_variant','intron_variant','splice_region_variant','downstream_gene_variant','upstream_gene_variant','start_lost','stop_retained_variant','inframe_insertion','inframe_deletion','transcript_ablation','transcript_fusion','transcript_amplification','transcript_translocation','feature_elongation','feature_truncation','protein_altering_variant','start_retained_variant') DEFAULT NULL,
  `cds_start` int(11) unsigned DEFAULT NULL,
  `cds_end` int(11) unsigned DEFAULT NULL,
  `cdna_start` int(11) unsigned DEFAULT NULL,
  `cdna_end` int(11) unsigned DEFAULT NULL,
  `translation_start` int(11) unsigned DEFAULT NULL,
  `translation_end` int(11) unsigned DEFAULT NULL,
  `distance_to_transcript` int(11) unsigned DEFAULT NULL,
  `codon_allele_string` text,
  `pep_allele_string` text,
  `hgvs_genomic` text,
  `hgvs_transcript` text,
  `hgvs_protein` text,
  `polyphen_prediction` enum('unknown','benign','possibly damaging','probably damaging') DEFAULT NULL,
  `polyphen_score` float DEFAULT NULL,
  `sift_prediction` enum('tolerated','deleterious','tolerated - low confidence','deleterious - low confidence') DEFAULT NULL,
  `sift_score` float DEFAULT NULL,
  `display` int(1) DEFAULT '1',
  PRIMARY KEY (`transcript_variation_id`),
  KEY `variation_feature_idx` (`variation_feature_id`),
  KEY `consequence_type_idx` (`consequence_types`),
  KEY `somatic_feature_idx` (`feature_stable_id`,`somatic`)
  1. RAM 128Gb

  2. State: "Repair with keycache"

Any suggestions on how I can troubleshoot and import this file?

  • 1
    Try splitting the file into (say) 4 files and work from there? You can get utilities to do this or use bash (e.g. here). p.s. welcome to the forum! :-) – Vérace Dec 28 '19 at 12:27
  • 1
    It hangs there while it is busy building the indexes. Let's see SHOW CREATE TABLE. Also, how much RAM do you have? – Rick James Dec 29 '19 at 6:48
  • I renamed files to correspond to table name (table_name.part1, table_name_part2, etc) using mmv and used wildcard "table_name.*" with mysqlimport – haz Jan 4 at 0:03
  • ** BUT does repair by sorting act on the entire table (as it is appended to) and will it fail and convert to repair by keycache once it reaches a certain size? – haz Jan 4 at 0:39
  • after importing 1 x 10G segment successfully in about 30 mins (inc 20 mins repair by sort) the 2nd 10G segment import slowed down to 60 mins but did not undergo a repair by sort – haz Jan 4 at 1:52

The problem with this starts with the my.cnf

The table is using the MyISAM Storage Engine but you have no MyISAM specific options in my.cnf

You are getting the "repair by keycache" because the MyISAM Key Cache is way too small. The default key_buffer_size in MySQL 5.7 is 8MB. Also note that you could have set up the MyISAM bulk insert buffer, whose default is also 8MB.

If you had added this to the my.cnf

key_buffer_size = 1G (or 2G)
bulk_insert_buffer_size = 256M (or 512M)

and restarted mysqld, you could have bulk loaded without changing the data files all that much.

| improve this answer | |
  • Thanks, implemented the my.cnf changes, was able to upload all data with indexes disabled but when enabled indexes processlist reverted to repair by keycache after a few hours – haz Jan 6 at 0:27
  • the implementation that worked was to stop mysql during the repair by keycache resulting in a corrupted table. With mysql stopped, I located table and ran myisamchk utility. For 2.3 b rows with 4 indexes it took approx 8 hours to repair. – haz Jan 8 at 5:09

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.