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.

  • Nothing... someone must have an idea? – Alasdair Jan 11 '14 at 14:57
  • 1
    You still have your chances with TokuDB: although, as you suggest, it does not support splitting files across different direcotries, it does have excellent compression ratio (I met with x20 compression factor on ROW_FORMAT=TOKUDB_SMALL) which means it may yet fit in your SSD – Shlomi Noach Jan 11 '14 at 15:41
  • Are always the same records missing from the import if you retry or are the missing records random? – Raymond Nijland Jan 11 '14 at 16:09
  • 2
    Did it throw any errors/warning(skipped rows) while you where trying to LOAD DATA? If there was some warnings, what was that? – georgecj11 Jan 12 '14 at 16:13
  • 1
    Then with TokuDB you'll have to pick one file system to hold it all. – tmcallaghan Jan 13 '14 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.