1

I have a dataset of around 200GB.

The sample is shown below:

Posted a new song: "Ensaio Don`t back anger & imagine- Stone Rocks " http://t.co/i9TqakT38t #music  460523675293196289  1308622848  0   0   Sun Apr 27 20:59:34 +0000 2014  nc  f   nr
Posted a new song: "Led Zeppelin- Black Dog Feat. RockSauro" http://t.co/Scdo6ncIc1 #music  460454112694263809  13086228298 0   0   Sun Apr 27 16:23:09 +0000 2014  nc  f   nr 

This data is in a text file. I have created a table and importing this data into mysql by using the command:

mysqlimport --local -u root -pxxx twitter_data '/home/abc/alltweet.txt' 

How can I check how much data has been loaded in mysql table. Actually this command is running since 10 hours and count(*) is giving me zero count.

0

I recommend you to import the text file in batches, that will allow you easier monitoring of the progress by committing from time to time. This can easily be achieved with tools like pt-fifo-split from Percona. The first example you can see on that page is what you should do, changing the cat for the mysqlimport.

The reasons you are seeing 0 rows is because you are importing to an InnoDB table and that means that its insert won't be seen until the process is complete. There are two way in which you can monitor the progress:

  • If you are using innodb_file_per_table (default on since MySQL 5.5), you should see an increasing in size table called something like alltweet.ibd inside the twitter_data directory on your datadir. You can check the location of your datadir by executing:

    SHOW GLOBAL VARIABLES like 'datadir';
    

    By default it is usually on /var/lib/mysql.

    The size of that file will be roughly the size of the .txt file, maybe a bit larger (depending on the structure). That, and the progress in which it is growing can be an indicator of the time left.

  • Monitor the Handler_write status variable:

    SHOW GLOBAL STATUS like 'Handler_write';
    

    This will give you the number of rows inserted on your database. Assuming there is no other inserting activity, this can give you the number of inserts/second, which you can compare with the number of lines of the file.

As a reminder, both MariaDB and Percona Server have better defaults to show you the pending work remaining.

If you want to speed up the mass insertion using a .txt file, sometime ago I wrote a guide on how to speed it up. In summary:

  • Increase your transaction log size
  • Increase your buffer pool
  • Disable your double-write buffer
  • Use a recent version of MySQL
  • Split the file in several pieces and insert it in parallel
  • Thanks for your help. Actually I am working on a server machine on which I m not super user. Hence, can't access var/lib/mysql. Is there any other way to track the progress. Please let me know. – Saurabh Nov 28 '14 at 9:37
  • @Mona Did you read the SHOW GLOBAL STATUS like 'Handler_write'; option? – jynus Nov 28 '14 at 9:51

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.