I've got 2 tables with 10-20 billion records each.
Currently these are in MyISAM (MySQL).
They only require to be built and indexed once, after this only SELECT is used.
They are currently partitioned into 100 partions each.
MyISAM is currently used because it has lower storage, has fast SELECT and supports
LOAD DATA INFILE,
REPAIR WITH X THREADS (for index building) and supports having the data & index files in a different directory from default (which is required because the file sizes are too large for the partition that the rest of MySQL runs on, which is an SSD.)
This is on a single server with single quad-core i7 processor and 64GB RAM, running CentOS 6.
The table structure is:
id bigint(20) UNSIGNED word1 mediumint(8) UNSIGNED word2 mediumint(8) UNSIGNED origyear smallint(5) UNSIGNED cat tinyint(3) UNSIGNED pibn int(10) UNSIGNED page smallint(5) UNSIGNED pos smallint(5) UNSIGNED num tinyint(3) UNSIGNED
The indexes are: (Note that the primary key is over id & word1 as the partitioning is done over word1 and it is required to be part of the primary key to be used as the partitioning value.)
PRIMARY id, word1 INDEX word2, word1, pibn INDEX origyear, word1 INDEX cat, origyear INDEX pibn, word1 INDEX pibn, page, word2 INDEX word1, word2, num, pibn, page
The problem is that half of the rows are missing after importing. I can confirm that the rows exist and have unique keys as I have checked this in the flat files that the tables are built from (when they were first imported with
LOAD DATA INFILE.) Luckily I have a backup of these flat files so it would not be difficult to reimport the entire tables (except that it takes about a month to import and index it all.)
I have no idea why these rows are missing, I guess it's just because MyISAM can't keep up with the amount of data.
I need advise on how to fix this, or a better storage engine that can be easily migrated to without having to change all the code in the scripts that use this data (all the SELECT statements.)
These tables are used in combination with other MyISAM & InnoDB tables in some SELECT statements, so it is necessary that they are part of an SQL database supporting that.
I've been looking at TokuDB but I'm not sure if it will be better. Plus is not does appear to support having the data in a different directory to default, which is required since I have only 300GB of SSD and these 2 tables are obviously several TB in size.