Faça uma estimativa de quanto tempo levará um ALTER do MySQL

#!/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[2Kr" +
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:])