I have a long suffering with this issue after moving forward from MySQL 5.6 and then MariaDB 10.0.
Working now on Debian 11 (Bullseye) with MariaDB "Ver 15.1 Distrib 10.9.5-MariaDB, for debian-linux-gnu (x86_64) using readline EditLine wrapper".
The hardware: Workstation with two Xeons and 96 ECC RAM
I'm trying to load the database from ftp://ftp.ensembl.org/pub/release-109/mysql/homo_sapiens_variation_109_38/ but I'm failing to build functional indexes on the big tables.
One of them is:
DROP TABLE IF EXISTS `variation_feature`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `variation_feature` (
`variation_feature_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`seq_region_id` int(10) unsigned NOT NULL,
`seq_region_start` int(11) NOT NULL,
`seq_region_end` int(11) NOT NULL,
`seq_region_strand` tinyint(4) NOT NULL,
`variation_id` int(10) unsigned NOT NULL,
`allele_string` varchar(50000) DEFAULT NULL,
`ancestral_allele` varchar(50) DEFAULT NULL,
`variation_name` varchar(255) DEFAULT NULL,
`map_weight` int(11) NOT NULL,
`flags` set('genotyped') DEFAULT NULL,
`source_id` int(10) unsigned NOT NULL,
`consequence_types` set('intergenic_variant','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','TFBS_ablation','TFBS_fusion','TFBS_amplification','TFBS_translocation','regulatory_region_ablation','regulatory_region_fusion','regulatory_region_amplification','regulatory_region_translocation','feature_elongation','feature_truncation','regulatory_region_variant','TF_binding_site_variant','protein_altering_variant','start_retained_variant','splice_donor_5th_base_variant','splice_donor_region_variant','splice_polypyrimidine_tract_variant') NOT NULL DEFAULT 'intergenic_variant',
`variation_set_id` set('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64') NOT NULL DEFAULT '',
`class_attrib_id` int(10) unsigned DEFAULT '0',
`somatic` tinyint(1) NOT NULL DEFAULT '0',
`minor_allele` varchar(50) DEFAULT NULL,
`minor_allele_freq` float DEFAULT NULL,
`minor_allele_count` int(10) unsigned DEFAULT NULL,
`alignment_quality` double DEFAULT NULL,
`evidence_attribs` set('367','368','369','370','371','372','418','421','573','585') DEFAULT NULL,
`clinical_significance` set('uncertain significance','not provided','benign','likely benign','likely pathogenic','pathogenic','drug response','histocompatibility','other','confers sensitivity','risk factor','association','protective','affects') DEFAULT NULL,
`display` int(1) DEFAULT '1',
PRIMARY KEY (`variation_feature_id`),
KEY `pos_idx` (`seq_region_id`,`seq_region_start`,`seq_region_end`),
KEY `variation_idx` (`variation_id`),
KEY `variation_set_idx` (`variation_set_id`),
KEY `consequence_type_idx` (`consequence_types`),
KEY `source_idx` (`source_id`)
) ENGINE=MyISAM AUTO_INCREMENT=813302185 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
/*!40101 SET character_set_client = @saved_cs_client */;
The file to be imported (variation_feature.txt, TAB separated) is 103Gb and contains 747758162 lines/records.
My configuration changes for MariaDB:
## this was some time ago - because some bug mysql or mariadb didn't took this from the system
default-time-zone = Europe/Berlin
## ensembl uses mysql version 5.6 and MariaDB 10.0
## because of table creation scripts I need compatibility:
show_compatibility_56 = ON
performance_schema
## ensembl writes in DATETIME fields: "0000-00-00 00:00:00"
## the new sql_mode doesn't allow it any more
## SHOW VARIABLES LIKE 'sql_mode' ;
## ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
## so change sql_mode deleting NO_ZERO_IN_DATE,NO_ZERO_DATE
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
## raid6
datadir = /mnt/SRVDATA/var/lib/mysql
## raid0
tmpdir = /mnt/WORK/tmp
## big values for importing ensembl homo_sapiens
##
## MyISAM
lower_case_table_names=1
bulk_insert_buffer_size = 1G
myisam_sort_buffer_size = 64G
## ARIA
aria_sort_buffer_size = 64G
## INNODB
innodb_file_per_table = ON
innodb_buffer_pool_size = 76G
innodb_log_buffer_size = 1G
innodb_log_file_size = 2G
innodb_write_io_threads = 16
Despite not getting any errors importing the variation_feature.txt some essential indexes are not working. In this case, the PRIMARY KEY and variation_idx. Importing has been done with both mysqlimport and "LOAD DATA LOCAL INFILE".
Trying a SELECT by a known primary key (variation_feature_id) or a known variation_id returns nothing.
Doing the same SELECT with IGNORE INDEX returns after long time the known record.
Doing a mysqlchk marks the table as bad with KEY in wrong position at xxxxx.
Repairing with myisamchk runs without error, allegedly recovering all indexes.
But after that I get the same situation like described above. I cannot query the table with conditions based on the said indexes.
I tried the same by rebuilding the table with the ARIA engine, but I get the same result with non functional indexes and errorless run of aria_chk.
The ensembl MariaDB 10.0.38 is working well at:
mysql --host=useastdb.ensembl.org --port=3306 --database=homo_sapiens_variation_109_38 --user=anonymous
But not on my above described system.
Given that most people now are recommending only InnoDB, I started 7 days ago a "LOAD DATA LOCAL INFILE". Even with my huge innodb config values - it is still running...
Thanks. Any hint will be highly appreciated.