0

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.

Improve this question

1 Answer 1

Reset to default
0

Will you be having a mixture of MyISAM and InnoDB? I see a big buffer_pool (for InnoDB), but no setting for key_buffer_size, which is needed for MyISAM.

How much RAM do you have?

Advice on memory allocation between the engines: http://mysql.rjweb.org/doc.php/memory

Please elaborate on "failing to build functional indexes".

Improve this answer
1
  • 96 ECC RAM. These big buffers are only to successfully import the tables. key_buffer_size is now still the default. MyISAM and ARIA LOAD DATA LOCAL INFILE is running without error. Besides: stopping mariadb and repairing with myisamchk and aria_chk is runing OK, no error. To "failing to build functional indexes" I'm sorry to say, the description above is all I could elaborate. To InnoDB: it tries now since 8.5 days to import the table from the file. It's not important any more. I'm sadly trying this since more than an year.
    – rawi
    Mar 4 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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