/
osc_progress.sh
executable file
·93 lines (93 loc) · 3.44 KB
/
osc_progress.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
#!/bin/bash
#
# shows real progress of an online schema change session
# works when PK is a numeric autoinc column
# uses current user to log to the DB; you can specify the user running the OSC as an argument,
# if different than the user running this script (eg. if you want to run this on a slave)
#
# Version: 1.1
# Author: rick.pizzi@mariadb.com
#
#
#
AVG_BUF=120
#
[ "$1" != "" ] && OSC_USER=$1 || OSC_USER=$USER
#
echo -n "Getting OSC information... "
while true
do
row=$(echo "select * from information_schema.processlist where user = '"$OSC_USER"' and info like 'INSERT LOW%'" | mysql -AN -u $OSC_USER 2>/dev/null | sed -e "s/\`//g")
[ "$row" != "" ] && break
sleep 1
done
echo "done."
table=$(echo $row | grep -oP '(?<=FROM\s)\w+\.\w+' | uniq)
table_schema=$(echo $table | cut -d"." -f1)
table_name=$(echo $table | cut -d"." -f2)
pk=$(echo $row | grep -oP '(?<=WHERE\s\(\()\w+' | uniq)
pksize=$(echo "select count(*) from information_schema.columns where table_schema = '"$table_schema"' and table_name = '"$table_name"' and column_key = 'PRI'" | mysql -AN -u $OSC_USER 2>/dev/null)
coltype=$(echo "select data_type from information_schema.columns where table_schema = '"$table_schema"' and table_name = '"$table_name"' and column_name = '"$pk"'" | mysql -AN -u $OSC_USER 2>/dev/null)
case "$coltype" in
'int'|'bigint') ;;
*) echo "sorry, this script only works when PK is an integer"; exit 1;;
esac
pval=0
declare -a speed
while true
do
target=$(echo "select $pk from $table order by 1 desc limit 1" | mysql -AN -u $OSC_USER 2>/dev/null)
if [ $pksize -eq 1 ]
then
curr=$(echo "select substring_index(substring_index(right(info, 100), '<= \'', -1), '\')', 1) from information_schema.processlist where user = '"$OSC_USER"' and info like 'INSERT LOW%'" | mysql -AN -u $OSC_USER 2>/dev/null)
else
curr=$(echo "select substring_index(substring_index(right(info, 200), '= \'', -2), '\')', 1) from information_schema.processlist where user = '"$OSC_USER"' and info like 'INSERT LOW%'" | mysql -AN -u $OSC_USER 2>/dev/null | cut -d"'" -f 1)
fi
if [ "$curr" = "" ]
then
sleep 2
else
percd=$(echo "scale=6; $curr / $target " | bc)
perc=$(echo "scale=2; $percd * 100" | bc)
cval=$(expr $(echo $percd | tr -d ".") + 0)
if [ $pval -gt 0 ]
then
pmin=$((cval-pval))
if [ "${speed[$((AVG_BUF-1))]}" != "" ]
then
n=0
avg=$(for i in $(seq 0 1 $((AVG_BUF-1)))
do
[ $i -eq 0 ] && echo -n "scale=4; (0"
if [ "${speed[$i]}" != "" ]
then
echo -n "+${speed[$i]}"
n=$((n+1))
fi
[ $i -eq $((AVG_BUF-1)) ] && echo ")/$n"
done | bc)
fi
if [ "$avg" != "" ]
then
tr=$(echo "(1000000-$cval)/($avg*2)" | bc)
#echo "curr $pmin avg $avg tr $tr cval $cval"
ed=$((tr/1440))
eh=$(((tr-ed*1440)/60))
em=$((tr-eh*60-ed*1440))
/usr/bin/printf "%s: %s/%s (%.2f%%) ETA: %dd%02dh%02dm\n" $table $curr $target $perc $ed $eh $em
else
/usr/bin/printf "%s: %s/%s (%.2f%%) ETA: estimating...\n" $table $curr $target $perc $ed $eh $em
fi
for i in $(seq 0 1 $((AVG_BUF-2)))
do
speed[$i]=${speed[$((i+1))]}
done
speed[$((AVG_BUF-1))]=$pmin
else
/usr/bin/printf "%s: %s/%s (%.2f%%) ETA: estimating...\n" $table $curr $target $perc $ed $eh $em
fi
pval=$cval
sleep 30
fi
done
exit 0