troubleshooting mysqlimport very large text file 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:

[mysql]
local-infile = 1

[mysqld]
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

[client]
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. 

-Update-

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`)
) ENGINE=MyISAM AUTO_INCREMENT=2324322623 DEFAULT CHARSET=latin1



RAM 128Gb
State: "Repair with keycache"


Any suggestions on how I can troubleshoot and import this file?
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

[mysqld]
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.