Estimate how much longer a MySQL ALTER will take

progress.py

#!/usr/bin/env python

import os
import sys
import glob
import time
import argparse

"""
Track the progress of an ALTER.

This script will evaluate the current size of the table against the progress of
    the #sql-xxxxxx.ibd temporary file MySQL is writing out.

It will also track the rate at which the new table is being written to disk,
    and will use that average rate to determine how long is left.

Since the source and destination tables will not be exactly the same size, this
    is just an estimate.

Inspired by
    http://www.mysqlperformanceblog.com/2014/02/26/monitor-alter-table-progress-innodb_file_per_table/

"""


def main(argv):

    parser = argparse.ArgumentParser(description='Monitor an Alter')
    parser.add_argument('-t', '--table', required=True,
                        help='The table to monitor.')
    parser.add_argument('-i', '--interval', required=False,
                        default=10,
                        help='The table to monitor.')
    args = parser.parse_args()

    table = args.table
    interval = args.interval
    tmp_file_size_last = 1.0
    rates = {}

    while True:

        cur_file_size = os.path.getsize(table + '.ibd') / 1024 / 1024
        tmp_file = glob.glob("#sql*.ibd")

        if len(tmp_file) == 0:
            exit('No tmp file')

        tmp_file_size = os.path.getsize(tmp_file[0]) / 1024 / 1024

        cur_rate = float((tmp_file_size - tmp_file_size_last)) / interval

        if tmp_file_size_last != 1.0:
            if cur_rate in rates:
                rates[cur_rate] = rates[cur_rate] + 1
            else:
                rates[cur_rate] = 1

        avg_rate = get_avg_rate(rates)

        size_left = cur_file_size - tmp_file_size

        time_left_s = size_left / float(avg_rate)
        time_left_h = time_left_s / 60 / 60

        progress_pct = (tmp_file_size / float(cur_file_size)) * 100
        sys.stdout.write(
            "\x1B[2K\r" +
            str(tmp_file_size) +
            " M / " +
            str(cur_file_size) +
            " M written (" +
            str(progress_pct) +
            "%) " +
            str(time_left_h) +
            " Hours remain @ rate: " +
            str(avg_rate) +
            " M/s")
        sys.stdout.flush()

        tmp_file_size_last = tmp_file_size

        time.sleep(10)


def get_avg_rate(rates):

    if len(rates) == 0:
        return 0.4

    running_sum = 0
    running_count = 0

    for rate, count in rates.iteritems():
        running_sum = running_sum + (rate * count)
        running_count = running_count + count

    return running_sum / float(running_count)


if __name__ == "__main__":
    main(sys.argv[1:])