首页 / 知识

有什么方法可以查看MySQL中ALTER TABLE语句的进度吗？

2023-04-14 04:25:00

Is there any way to see the progress of an ALTER TABLE statement in MySQL?

例如，我发布了一条ALTER TABLE语句在InnoDB表的MEDIUMTEXT字段上创建索引，该表具有134k行，其中索引的大小为255个字节，并且该字段中数据的平均大小为30k。该命令已经运行了大约15分钟(这是数据库上唯一运行的命令)。我有什么办法可以确定它是否会在将近5分钟，5小时或5天内完成？

我能够执行这两个查询，并找出还有多少行要移动。

1 2	select count() from `myoriginalrable`; select count() from `#sql-1e8_11ae5`;

这比比较磁盘上的文件大小更有帮助，因为从myisam更改为innodb等会更改行大小。

对于InnoDB表，可以使用SHOW ENGINE INNODB STATUS查找执行ALTER TABLE的事务，并检查TX持有多少行锁。这是已处理的行数。这里详细解释：

http://gabrielcain.com/blog/2009/08/05/mysql-alter-table-and-how-to-observe-progress/

MariaDB 5.3和更高版本还具有报告某些操作(包括ALTER TABLE)的进度的功能。看到：

http://kb.askmonty.org/en/progress-reporting/

我进行了一个查询，该查询估计了完成innodb表上的alter命令的时间。您必须在同一会话上至少运行两次，因为它会比较连续运行的统计信息以进行估算。
不要忘记在第四行将更改为正确的表名。它给您两个估计。本地估算仅使用两次运行之间的数据，而全局估算则使用整个交易时间。

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

select
beginsd, now(), qRuns, qTime, tName, trxStarted, trxTime, `rows`, modified, locked, hoursLeftL, estimatedEndL, modifiedPerSecL, avgRows, estimatedEndG, modifiedPerSecG, hoursLeftG
from (
select
(@tname:='<table>') tName,
@beginsd:=sysdate() beginsd,
@trxStarted:=(select trx_started from information_schema.innodb_trx where trx_query like concat('alter table %', @tname, '%')) trxStarted,
@trxTime:=timediff(@beginsd, @trxStarted) trxTime,
@rows:=(select table_rows from information_schema.tables where table_name like @tname) `rows`,
@runs:=(ifnull(@runs, 0)+1) qRuns,
@rowsSum:=(ifnull(@rowsSum, 0)+@rows),
round(@avgRows:=(@rowsSum / @runs)) avgRows,
@modified:=(select trx_rows_modified from information_schema.innodb_trx where trx_query like concat('alter table %', @tname, '%')) modified,
@rowsLeftL:=(cast(@rows as signed) - cast(@modified as signed)) rowsLeftL,
round(@rowsLeftG:=(cast(@avgRows as signed) - cast(@modified as signed)), 2) rowsLeftG,
@locked:=(select trx_rows_locked from information_schema.innodb_trx where trx_query like concat('alter table %', @tname, '%')) locked,
@endsd:=sysdate() endsd,
--
time_to_sec(timediff(@endsd, @beginsd)) qTime,
@modifiedInc:=(cast(@modified as signed) - cast(@p_modified as signed)) modifiedInc,
@timeInc:=time_to_sec(timediff(@beginsd, @p_beginsd)) timeInc,
round(@modifiedPerSecL:=(@modifiedInc/@timeInc)) modifiedPerSecL,
round(@modifiedPerSecG:=(@modified/time_to_sec(@trxTime))) modifiedPerSecG,
round(@minutesLeftL := (@rowsLeftL / @modifiedPerSecL / 60)) minutesLeftL,
round(@minutesLeftG := (@rowsLeftG / @modifiedPerSecG / 60)) minutesLeftG,
round(@hoursLeftL := (@minutesLeftL / 60), 2) hoursLeftL,
round(@hoursLeftG := (@minutesLeftG / 60), 2) hoursLeftG,
(@beginsd + INTERVAL @minutesLeftL MINUTE) estimatedEndL,
(@beginsd + INTERVAL @minutesLeftG MINUTE) estimatedEndG,
--
@p_rows:=@rows,
@p_modified:=@modified,
@p_beginsd:=@beginsd
) sq;

显然，这是一个非常常见的请求-最早可以追溯到2005年，是bugs.mysql.com上的请求。
它已经存在于Oracle中，并被列为有用，但是"这不是一件容易的事，所以不要期望它会
即将实施。"尽管那是2005年：)

就是说，后来提出原始问题的家伙后来发布了MySQL 5.0的补丁程序，该补丁程序反向移植到4.1，这可能会帮到您。

运行ls -laShr /var/lib/mysql | sort -h，您将在mysql文件夹中看到类似以下的文件：

1 2	-rw-r----- 1 mysql mysql 3.3G Feb 9 13:21 sql-#2088_10fa.ibd -rw-r----- 1 mysql mysql 10.2G Feb 9 13:21 posts.ibd

您可以看到正在构造的原始表文件和临时目标表文件，并且具有易于理解的大小。通常，它会随着时间线性增长，因此，如果它是原始表的大小的一半，则它是一半。 ls命令将按大小对文件进行排序，因此，如果这是一个大表，并且您已经等待了一段时间，则这两个文件都将位于文件列表的底部附近。

Percona的pt-online-schema-change显示剩余时间估计。默认情况下，它每30秒打印一次剩余时间估算和进度百分比。

与仅运行ALTER命令相比，它还具有其他功能。

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

如果有人想要一个bash解决方案：(SQL对我不起作用)

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

cd /var/lib/mysql/mydb
TABLEFILE="MYTABLE.ibd"
TEMPFILE="\#*ibd"

ls -lah $TABLEFILE;
ls -lah $TEMPFILE; # make sure you have only one temp file or modify the above TEMPFILE

SIZE_TOTAL=$(stat -c %s $TABLEFILE);

# other ways to get 1st size and time
#SIZE1=1550781106; TIME1=1550781106;
#SIZE1=$(stat -c %s $TEMPFILE); TIME1=$(stat -c %Z $TEMPFILE); sleep 10;
SIZE1=0; TIME1=$(stat -c %X $TEMPFILE); # use file create time

echo"SIZE1=$TIME1; TIME1=$TIME1";

SIZE2=$(stat -c %s $TEMPFILE); TIME2=$(stat -c %Z $TEMPFILE);

DELTA_SIZE=$(( $SIZE2 - $SIZE1 ))
DELTA_TIME=$(( $TIME2 - $TIME1 ))

# debug last numbers should not be zero:

echo $SIZE1 $SIZE2 $SIZE_TOTAL $DELTA_SIZE;
echo $TIME1 $TIME2 $DELTA_TIME;

SIZE_PER_SECOND=$( awk"BEGIN {print $DELTA_SIZE / $DELTA_TIME }" );
SIZE_LEFT=$(($SIZE_TOTAL - $SIZE2));
TIME_LEFT_SECONDS=$( awk"BEGIN { print ( $SIZE_LEFT / $SIZE_PER_SECOND) }" );
TIME_LEFT_MINUTES=$( awk"BEGIN { print $TIME_LEFT_SECONDS /60 }" );
TIME_LEFT=$( awk"BEGIN { printf "%d:%02d:%2d", int($TIME_LEFT_MINUTES /60), int($TIME_LEFT_MINUTES % 60), int($TIME_LEFT_SECONDS % 60 ) }" );

echo"TIME_LEFT = $TIME_LEFT";
echo"SIZE_LEFT = $SIZE_LEFT""MB=" $(( $SIZE_LEFT/1024/1024 )) ;
awk"BEGIN { if( $SIZE_TOTAL == $SIZE2 ) print "mysql finished" }" ;

free -h # check free memory, sometimes it is full and it makes it slow

结论：
需要时间，很多时间。

只要确保有免费的ram。和自由空间。
像50％的内存没有被mysql使用。

低ram使整个系统工作非常低

Percona Server是MySQL的分支版本，具有一些增强功能，具有此功能。

您可以在SHOW PROCESSLIST中观察ROWS_SENT和ROWS_EXAMINED的其他列。例如，如果您的表有1000000行，而您看到的ROWS_EXAMINED为650000，则表示已完成65％。

参见http://www.percona.com/doc/percona-server/5.6/diagnostics/process_list.html

语句方法索引字段