logo标签列表

有没有办法在MySQL中查看ALTER TABLE语句的进度？

mysql
26
例如，我发布了一条ALTER TABLE语句，在一个包含 134k 行的InnoDB表中为一个MEDIUMTEXT字段创建索引，索引大小为255字节，字段中数据的平均大小为30k。这个命令已经运行了大约15分钟（并且是数据库上唯一正在运行的事情）。有没有办法让我确定它是否会在更接近5分钟、5小时或5天内完成？
9个回答
22
我能够执行这两个查询并计算出还有多少行需要移动。
select count(*) from `myoriginalrable`;
select count(*) from `#sql-1e8_11ae5`;

这比比较磁盘上的文件大小要有用得多，因为从MyISAM到InnoDB等的变化会改变行大小。

7
你是如何获取 ALTER 命令创建的临时表的名称的？ - fixxxer
4
临时表的名称可以在文件系统中与其它表一起找到。 - carillonator
1
无法在文件系统中找到MySQL 5.6和InnoDB的临时表。 - Robycool
15
MariaDB 5.3+ 和 Percona Server - Go4It
2
请注意，该博客文章指出“SHOW ENGINE INNODB STATUS”技巧仅适用于MySQL 5.0。 - cbmanica
5

这是一个非常老的问题，但至少在mysql 5.7中有了一个恰当的答案。

https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html

简而言之...

UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES'
       WHERE NAME LIKE 'stage/innodb/alter%';
UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%stages%';

...运行alter table命令...

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
       FROM performance_schema.events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) |            280 |           1245 |
+------------------------------------------------------+----------------+----------------+
1 row in set (0.01 sec)
1
设置setup_consumers是否必须为YES？我有一个AWS RDS，events_stages_current的set_consumers为NO，但数据仍被收集到events_stages_current表中。 - Jacob
3
我制作了一个查询，用于估算在innodb表上完成alter命令的时间。您必须在同一会话中至少运行两次该命令，因为它会比较连续运行的统计信息以进行估算。 不要忘记在第四行更改<tableName>为正确的表名。它会给出两个估算值。本地估算仅使用两次运行之间的数据，而全局估算则使用整个事务时间。
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;
3
2

运行 ls -laShr /var/lib/mysql | sort -h 命令，您将看到 mysql 文件夹中的文件如下：

最初的回答：

-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命令将按大小对文件进行排序，因此如果这是一个大表格并且你等待了一段时间，那么这两个文件将位于文件列表的底部附近。"最初的回答"
1
如果有人想要一个Bash解决方案：（SQL对我来说不起作用）
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

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

只需确保有可用的内存和空闲空间。 例如，MySQL未使用50%的内存。

低内存会使整个系统运行非常缓慢。

1
0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接