2

I'm currently uploading one of Wikipedia's dump files. It has 1GB compressed and ~7GB uncompressed. It has been importing it for 6 hours already. The size of the imported table in MySQL is at the moment 1.5GB(data+indexes). Will it continue to import until the size of the table will reach 7GB?

Note: The data directory of the database in on an external HDD. Considering this is only a development, so I'm not concerned by the real performance.

Improve this question

4 Answers 4

Reset to default
6

Can't say about the size, but you could try and check for the last entry or the amount of entries in the dump and then check for the current last entry in your database. This might help you determine the time the import will take till it finishes.

The size of the imported data might even get bigger than the uncompressed 7GB, since the indexes are usually not contained in dumps but get built on insert.

As a sidenote: This is also a way to speed up the import itself: drop the index during import and rebuild it later on, this helped me several times to speed things up.

Improve this answer
6
  • ok, it reached the point where one insert takes 60 seconds. How am I supposed to drop the index during the import in a 7gb large file? Do you know of any program that could open such a file?
    – Andrew
    Commented Jan 31, 2012 at 9:10
  • 2
    first you need to open a new connection, you also could use MySql Workbench (mysql.com/products/workbench) now: SHOW INDEXES FROM tbl_name; gives you a list of all indexes on a table DROP INDEX index_name ON tbl_name removes an index
    – Benjamin Seiller
    Commented Jan 31, 2012 at 10:46
  • 2
    concerning the file viewer: i asume you are using Windows? Have a look here superuser.com/questions/34749/… or here stackoverflow.com/questions/159521/…, otherwise (Linux) less will work like a charm.
    – Benjamin Seiller
    Commented Jan 31, 2012 at 10:50
  • +1 for offhanded comment about dropping indexes prior to insert.
    – Derek Downey
    Commented Jan 31, 2012 at 20:32
  • 1
    @Andrew: hehe, you're welcome - but wait thanking me any more till you recreate the index ;)
    – Benjamin Seiller
    Commented Feb 1, 2012 at 12:01
8

I assume you are connected by ssh or you are sitting right at the computer. Open a separate window and run this query in mysql:

SHOW PROCESSLIST;

I was just doing an import of a 3GB (uncompressed) db the other day and I believe that was the query that showed me exactly where the import was in its progress.

In my case, the tables were being imported in alphabetical order, so I knew exactly where it was in its progress.

Also, check out this stackoverflow question's answers for killing process vs killing query.

Improve this answer
2
  • if there is a process I'd like to kill? Is there a way to do it? There's a query I ran a while ago and is still not finished.
    – Andrew
    Commented Jan 31, 2012 at 8:56
  • 2
    Yes you can do that. I believe the id shown in SHOW PROCESSLIST corresponds to the PID. You can also check "top" to confirm. If you know the process id (pid), e.g. 407, you can use this simple command in the command line (just don't kill the wrong one): kill 407
    – Buttle Butkus
    Commented Jan 31, 2012 at 9:01
2

It may not be of very much help, but using pv and pipes in Linux, you can create a progress bar that will at least help you observe the quantity of data that has been loaded into the tables. It will also help you to assess the overall progress of the task.

As I am new on StackExchange, I need to pass a reputation score to use more StackOverflow. I request you to upvote, comment about improvements, or any other tips which can make this more helpful.

The command which worked for me is:

pv file-name.sql | mysql -u root -pPass <DataBaseName>

example:

pv steam_activity.sql | mysql -u root -pPass GAMEDATA

This will give a progress bar for SQL import which will look like this :

enter image description here

77.9GiB 9:20:33 [4.40MiB/s] [===========================================> ] 48% ETA 9:52:3

PLEASE NOTE THAT ALL THE METRICS ABOVE ARE CALCULATED WITH RESPECT TO DATA TRANSFERRED FROM SQL FILE TO MYSQL SERVER (i.e. from steam.sql to mysql process). THEREFORE THIS WON'T GIVE YOU PRECISE AMOUNT OF TIME LEFT FOR COMPLETION. BUT IT CAN PROVIDE A GOOD APPROXIMATION OF TIME REQUIRED.

It has nothing to do with the internal working of MySQL server engines so it won't be able to give a precise ETA for completion.

Let us discuss the output progress bar:

I am importing a 173 GB steam_activity.sql file into a newly created empty database named GAMEDATA.

Let us discuss the output progress bar:

77.9GiB 9:20:33 [4.40MiB/s] [===========================================> ] 48% ETA 9:52:3

  • 77.9GiB: Amount of Data transferred from that 173 data file:

  • 9:20:33 <Hours: Minutes: Seconds> : Time Elapsed since the process started

  • [4.40MiB/s] : Rate At which statements are read from steam_activity.sql

  • A progress bar: [===========================================> ]

  • 48% : Amount of transfer completed:

  • ETA 9:52:30: Time required for completing the import. THIS IS NOT THE CORRECT N PRECISE TIME AS SOME INSERT STATEMENT MIGHT TAKE LONGER EXECUTION TiME THAN OTHER. Also, the table checks process can also happen, which can delay the importing.

Original post: How can I monitor the progress of an import of a large .sql file?

If you want tips on speedup, please comment or post other questions ( I loaded that 173 GB .SQL file in 17 hours ) or follow this post: MySQL any way to import a huge (32 GB) SQL dump faster? Estimation is 24 Days

Improve this answer
0

You can take a note of the time you start to import the dump and after that finish, run a query to know the last update time of all tables from your database.

select max(update_time) from information_schema.tables where table_schema = 'your_database_name';

After that, just calculate it. (last_update_time - time_you_started) = time_spent.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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