How to see progress of .csv upload in MySQL

Question

I have a very large .csv file, and I'm loading it into mysql with the LOAD DATA INFILE command. Because it takes so long, I'd like to see how far along the upload has progressed.

I've tried 2 methods so far- First I simply did a SELECT COUNT(*) command to see how many rows had been inserted as the upload was in progress, but that always returns a count of 0.
Second, I tried SHOW PROCESSLIST and saw simply how long the query has been running. sometimes the status says 'freeing data' or something to that effect.

Does anyone know a good way to track the progress of a LOAD DATA INFILE command? Also, does anyone know how to track the insertion rate?

Thanks

Chekiang

Answer 1

From http://www.stephenchu.com/2008/12/speed-up-your-mysql-data-load.html, you can use the SHOW INNODB STATUS IF your table is Innodb type, which you didn't happen to mention.

The page I linked to also has some good tuning suggestions to improve your overall performance with loading data in this manner.

Dextral

SHOW INNODB STATUS should now become SHOW ENGINE INNODB STATUS \G;, with the extra trailing \G, so that grep works – Acerate

Answer 2

On Linux you can print info about file descriptor (ls -l /proc//fd), and file position reader (cat /proc//fdinfo). So:

1. Find mysqld pid (in this example: 1234):

   $ ps -ef | grep mysqld

   mysql 1234 1 0 feb12 ? 00:00:55 /usr/sbin/mysqld

2. Find file descriptor number of your loaded file (in this example: 45):

   $ sudo ls -l /proc/1234/fd

   lr-x------ 1 root root 64 Feb 13 10:56 45 -> /var/lib/mysql/db/Loaded_file.txt

3. Print info about that file descriptor and check number of bytes already read (in this example: 494927872):

   $ cat /proc/1234/fdinfo/45

   pos: 494927872

   flags: 0100000

You can compare this progress indicator (in bytes) to the actual file size being loaded.

Instead of step 1 and 2, you can also use 'lsof' command:

$ lsof /var/lib/mysql/db/Loaded_file.txt | grep mysql

COMMAND   PID     USER   FD   TYPE DEVICE SIZE/OFF    NODE NAME

mysqld    1234 youknowwho    45r   REG  252,0   190312 5505353 /var/lib/mysql/db/Loaded_file.txt

Welcome

This combined with the time the query had been running gave me an incredibly accurate ETA. Hugely useful, thank you. – Celio

Answer 3

Couple of approaches here...

1. set session transaction isolation level read uncommitted; Then count(*) will work
2. select rows_read as 'Read', round((rows_read/{linecount})*100, 2) as 'Complete', round(time/60, 2) as 'Elapsed', round(time * 100 / round((rows_read/<line count>)*100, 2) / 60, 2) as 'ETA' from INFORMATION_SCHEMA.PROCESSLIST where id = <id>;

Eradicate