LOAD DATA INFILE + progress tracking

Question

I made a PHP console script that downloads big CSV file (> 2.5m rows) and insterts it to the database. I'm using LOAD DATA INFILE query here and it works perfectly, taking ~20 seconds to complete.

I'd like to track progress of this query. I've read about several approaches here, but I don't know how to run LOAD DATA INFILE query and then run progress tracking loop. The script waits until LOAD DATA INFILE is done.

My database is MySQL and the engine is InnoDB. I'm using Laravel framework.

check this post http://stackoverflow.com/questions/5748565/how-to-see-progress-of-csv-upload-in-mysql and http://dotmanila.com/blog/2013/04/check-rough-progress-of-your-csv-import-to-mysql/ and also this one http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql — tzafar, Dec 30 '14 at 09:41
@tzafar I checked all of them out before asking this question. — Robo Robok, Dec 30 '14 at 09:46
@RoboRobok Then it would be helpful to explain why it is not address your question. — RandomSeed, Jan 06 '15 at 11:40

score 0 · Answer 1 · answered Jan 25 '15 at 05:29

You can "split" the dump file with pt-fifo-split and print progress message after each chunk.

For example, in shell:

f=dump.sql
nlines=`cat $f | wc -l`
let chunk=$nlines/100

pt-fifo-split --lines $chunk $f
i=0
while [ -e /tmp/pt-fifo-split ] 
do 
    echo "$i% is done"
    mysql -e "LOAD DATA INFILE '/tmp/pt-fifo-split' INTO ..."
    let i=$i+1
done

LOAD DATA INFILE + progress tracking

1 Answers1