4

I have a txt file with about 100 million records (numbers). I am reading this file in Python and inserting it to MySQL database using simple insert statement from python. But its taking very long and looks like the script wouldn't ever finish. What would be the optimal way to carry out this process ? The script is using less than 1% of memory and 10 to 15% of CPU.

Any suggestions to handle such large data and insert it efficiently into database, would be greatly appreciated.

Thanks.

Community
  • 1
  • 1
jcoder12
  • 167
  • 1
  • 4
  • 15
  • What do your records look like? – Karpov Jun 19 '15 at 01:58
  • 3
    Your best bet is to reformat the file into a CSV, if necessary, and then batch load it from the mysql command line instead of writing a Python script. See [MYSQL import data from csv using LOAD DATA INFILE](http://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile) – Warren Dew Jun 19 '15 at 02:02

3 Answers3

6

The fastest way to insert rows into a table is with LOAD DATA INFILE statement.

Reference: https://dev.mysql.com/doc/refman/5.6/en/load-data.html

Doing individual INSERT statements to insert one row at a time, RBAR (row by agonizing row) is tediously slow, because of all the work that the database has to go through to execute a statement... parsing for syntax, for semantics, preparing execution plan, obtaining and releasing locks, writing to the binary log, ...

If you have to do INSERT statements, then you could make use of MySQL multi-row insert, that would be faster. 

  INSERT INTO mytable (fee, fi, fo, fum) VALUES 
   (1,2,3,'shoe')
  ,(4,5,6,'sock')
  ,(7,8,9,'boot')

If you insert four rows at a time, that's a 75% reduction in the number of statements that need to be executed.

spencer7593
  • 106,611
  • 15
  • 112
  • 140
2

Sticking with python, you may want to try creating a list of tuples from your input and using the execute many statement from the python mysql connector.

If the file is too big, you can use a generator to chunk it into something more digestible.

http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html

emican
  • 256
  • 1
  • 6
  • 1
    The `executemany` with an `INSERT` statement invokes the multi-row syntax... a single insert statement inserting multiple rows. And this will be faster than performing individual insert statements for each row. Since there's a limit on the size of SQL statement... with a 100 million rows to be inserted, if this is done 100 rows per insert, that's still one million statement executions. – spencer7593 Jun 19 '15 at 13:33
1

Having tried to do this recently, I found a fast method, but this may be because I'm using an AWS Windows server to run python from that has a fast connection to the database. However, instead of 1 million rows in one file, it was multiple files that added up to 1 million rows. It's faster than other direct DB methods I tested anyway.

With this approach, I was able to read files sequentially and then run the MySQL Infile command. I then used threading with this process too. Timing the process it took 20 seconds to import 1 million rows into MySQL.

Disclaimer: I'm new to Python, so I was trying to see how far I could push this process, but it caused my DEV AWS-RDS DB to become unresponsive (I had to restart it), so taking an approach that doesn't overwhelm the process is probably best!

Coffee and Code
  • 885
  • 14
  • 16