I have hundreds of millions of rows in a text/csv file (genomics database btw - each record is less than 255 characters long...).

Ideally I'd like to make them searchable since right now my best bet is spiting them (a little help from cygwin!) and reading them one by one as a text file ~500mb from notepad++ (yes...i know...) - so this is very inconvenient and caveman-like approach.

I'd like to use MySQL but maybe others, have budget of up to $500 for Amazon instances when needed - maybe 32gb ram some xeon gold and 200gb hard disk on Amazon can do it? No problem to use up to 10 instances each of which doing concurrent insert/loading.

I read someone had accomplished 300,000 rows/second using 'load data infile' on a local server with ssd and 32gb ram - if I make it to even 50,000 rows/second and then be able to query it with say phpmyadmin in normal time - I'd be happy. Thanks!

  • Is it one single huge table, or "database" is a database really, a lot of tables, indices, procedures, triggers, etc. ? – Akina Feb 4 '20 at 19:06
  • @Akina: Huge table with all those hundreds of millions of rows (could be up to one billion...given some future scalling). I just want to make it easily searchable so it will be just one table with likely 2 fields: one being indexed and the rest holding up to 250 characters (varchar??) maybe. – kevinjordan Feb 4 '20 at 19:21
  • I think that it is possible to load this data into the table within 3-4 hours, but indexing this table for searching (even common, even more fulltext) within this time - I think this is a fairy tale. – Akina Feb 4 '20 at 19:27
  • thanks, so how many days then on a single server would the indexing take? also if this is impossible then can say nosql solution do it? i suspect it's very hard to put all these records into mongodb... – kevinjordan Feb 4 '20 at 20:53
  • Please provide SHOW CREATE TABLE -- There are tricks to play with the schema to make the loading go faster. – Rick James Feb 8 '20 at 1:55

I read someone had accomplished 300,000 rows/second using 'load data infile' on a local server with ssd and 32gb ram

That sounds like taken from my blog posts (or at least those were my numbers and specs): https://jynus.com/dbahire/testing-the-fastest-way-to-import-a-table-into-mysql-and-some-interesting-5-7-performance-results/ and https://jynus.com/dbahire/testing-again-load-data-on-mysql-5-6-5-7-8-0-non-ga-and-mariadb-10-0-10-1-and-10-2-non-ga/

As you can see my experience is based on actual tests; but not only laboratory tests like the above, I do those because they help me be ready to make sure my database backups (and recoveries) are generated correctly, as well as they can be performed reliably and fastly, handling daily both logical dumps and snapshots for the half a petabyte of data we store on our MariaDB databases: https://www.slideshare.net/jynus/backing-up-wikipedia-databases

A 50 GB database on a 32GB memory server is a very generous ratio, where 60% of the data could fit in the buffer pool. In that case, thoughput can be optimized greatly, as long as you setup your vm, os and mysql configuration for it (disabling the binary log, increasing the buffer pool and transaction log files, loosening consistency parameters during the import, etc). You will also want the original format to be optimized for easy loading, so you dont waste cpu cycles on parsing or converting the format or other changes, as well as doing it in large transactions as well as on several threads in parallel if possible.

As an example, my production has 1-2TB databases with billions of rows, which I can recover logically in 6-12 hours on a 512GB memory machine, including the many indexes.

Under the above right circumstances, with a mostly in memory database, I would be able to load remotelly in parallel a 50 GB database in around 30 minutes. Less than 1h if the storage is slow. Be careful because the tests asume dedicated resources; a cpu, memory or io limitation can create a bottleneck, leading to higher load times.

  • If you mean ENGINE=MEMORY, the I question parallelism. That engine uses table locking. – Rick James Feb 8 '20 at 1:56
  • MySQL/InnoDB with dynamic row format. – jynus Feb 8 '20 at 10:04

Also, remember , you always want to store you data in chunks;

I personally prefer to cut it up into ~10GB (100 mill) give or take files which makes it easier to load;

in your case it would be 38 separate files;

they would all get uploaded into one folder on S3 and then it's just one COPY statement to send them to a single target table from the S3 (blob);

but should something happen during the upload time, you have a ref point where you left off;


if you use Redshift Managed Storage, you will pay around 1/2 of T of space ($12/month) and you can query it with queries coming back in less than 2 sec with no indexes (you really don't need any);

at the same time, you will incur the most pain in uploading the said data into S3 prior to import into Redshift but in any case - this is the way forward;

this releases you from any and all management activities once this is done and you just focus on analytics;

Your Answer

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

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