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
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.