Getting an ETA on a MySQL import

In the first of what will hopefully be a series of regular technical blog posts, I’ll discuss a little tool which has helped remove the mystery of when a MySQL import will finish. If, like me, mysqldump forms part of your core day-to-day tools, you’ve probably found yourself staring at a blinking cursor waiting for a DB dump to import.pv removes that mystery by monitoring the throughput of pipes. If you can pipe data into a process, then you can get a progress bar. For example, rather than typing:

mysql < dump.sql

Use:

pv dump.sql | mysql

You will be presented with a nice progress bar, with ETA and throughput as shown below:

192MB 0:02:58 [2.23MB/s] [====================> ] 80% ETA 0:01:09

It’s worth pointing out that this ETA won’t always be 100% accurate for the likes of MySQL. PV only knows how much data has been pushed through the pipe, not how long it will take to process it. Fortunately, the contents of mysqldump should be fairly straight forward to process and there shouldn’t really be any sub-select festooned UPDATE statements.

Outside of MySQL, There are many applications for this little tool assuming you can squeeze it into processes that can have their input or output piped; gzip for instance. Of course, sometimes one might be tempted to use pv for the sake of it. Don’t try and pv something through scp, just use rsync -e ssh --progress.

We’re always interested to hear how people use novel tools, so please let us know how you’ve used pv.