blocks|key|1558646|text|这是一个非常常见的请求--早在2005年就已经在bugs.mysql.com上被请求了。它已经存在于Oracle中，并且被列为有用的，但是"it+is+not+a+simple+thing+to+do,+so+don't+expect+it+to+be+implemented+soon."。尽管那是在2005年:)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1558647|尽管如此，提出原始问题的人后来发布了MySQL+5.0的patch，向后移植到4.1，这可能会对您有所帮助。|1558648|entityMap|0|LINK|mutability|MUTABLE|url|http://bugs.mysql.com/|1|http://bugs.mysql.com/bug.php?id=3926|2|http://jcole.us/blog/archives/2007/02/08/progress-in-mysql-process-list/^0|O|E|0|1X|24|1|0|S|5|2|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]|$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|F|3|-4|5|6|7|12|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]|P|$5|I|J|K|C|$L|Q]]]]

This is a pretty common request apparently - requested as far back as 2005 on <a href="http://bugs.mysql.com" rel="nofollow noreferrer">bugs.mysql.com</a>. 
It exists in Oracle already, and is listed as useful, but <a href="http://bugs.mysql.com/bug.php?id=3926" rel="nofollow noreferrer">"it is not a simple thing to do, so don't expect it to be
implemented soon."</a>. Although that was 2005 :)

That said, the chap who asked the original question later released a <a href="http://jcole.us/blog/archives/2007/02/08/progress-in-mysql-process-list/" rel="nofollow noreferrer">patch</a> for MySQL 5.0, backported to 4.1, which might help you out.

blocks|key|1773891|text|我能够执行这两个查询，并计算出还有多少行需要移动。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1773892|select+count(*)+from+`myoriginalrable`;
select+count(*)+from+`#sql-1e8_11ae5`;|code-block|syntax|javascript|1773893|这比比较磁盘上的文件大小更有帮助，因为从myisam更改为innodb等会改变行大小。|1773894|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I was able to perform these 2 queries and figure out how many rows remain to be moved.

<pre><code>select count(*) from `myoriginalrable`;
select count(*) from `#sql-1e8_11ae5`;
</code></pre>

this was WAY more helpful than comparing the file size on disk, because changing from myisam to innodb etc changes the row size.

blocks|key|1773945|text|对于InnoDB表，可以使用SHOW+ENGINE+INNODB+STATUS查找执行ALTER+TABLE的事务，并检查TX持有多少行锁。这是已处理的行数。详细解释如下：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1773946|http://gabrielcain.com/blog/2009/08/05/mysql-alter-table-and-how-to-observe-progress/|1773947|此外，MariaDB+5.3和更高版本还具有报告某些操作(包括ALTER+TABLE)的进度的功能。请参见：|1773948|http://kb.askmonty.org/en/progress-reporting/|1773949|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/show-engine.html|1|2^0|E|P|E|P|0|0|0|2D|1|0|0|0|19|2|0^^$0|@$1|2|3|4|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|F|3|G|5|6|7|11|8|@]|D|@$9|12|A|13|1|14]]|E|$]]|$1|H|3|I|5|6|7|15|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|16|8|@]|D|@$9|17|A|18|1|19]]|E|$]]|$1|L|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|S]]|T|$5|O|P|Q|E|$R|G]]|U|$5|O|P|Q|E|$R|K]]]]

In the case of InnoDB tables, one can use <a href="http://dev.mysql.com/doc/refman/5.0/en/show-engine.html"><code>SHOW ENGINE INNODB STATUS</code></a> to find the transaction doing the ALTER TABLE and check how many row locks the TX holds. This is the number of processed rows. Explained in detail here:

<a href="http://gabrielcain.com/blog/2009/08/05/mysql-alter-table-and-how-to-observe-progress/">http://gabrielcain.com/blog/2009/08/05/mysql-alter-table-and-how-to-observe-progress/</a>

Also MariaDB 5.3 and later has the feature to report progress for some operations (including ALTER TABLE). See:

<a href="http://kb.askmonty.org/en/progress-reporting/">http://kb.askmonty.org/en/progress-reporting/</a>

blocks|key|1558787|text|pt-online-schema-change+by+Percona显示剩余时间估计。默认情况下，它每30秒打印一次剩余时间估计和进度百分比。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1558788|与单独运行ALTER命令相比，它还具有其他功能。|1558789|http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html|offset|length|1558790|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|23|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@$F|R|G|S|1|T]]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|E]]]]

pt-online-schema-change by Percona shows remaining time estimate. By default it prints out the remaining time estimate and progress percentage every 30 seconds.

It also has additional functions compared to just running the ALTER command by itself.

<a href="http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html" rel="nofollow">http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html</a>

blocks|key|1558851|text|Percona+Server是MySQL的一个分支版本，具有一些增强功能，具有此功能。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1558852|您可以在ROWS_SENT和ROWS_EXAMINED的SHOW+PROCESSLIST中观察额外的列。例如，如果您的表有1000000行，您看到ROWS_EXAMINED为650000，那么它就完成了65%25。|1558853|请参阅http://www.percona.com/doc/percona-server/5.6/diagnostics/process_list.html|1558854|entityMap|0|LINK|mutability|MUTABLE|url|http://www.percona.com/software/percona-server|1|http://www.percona.com/doc/percona-server/5.6/diagnostics/process_list.html^0|0|E|0|0|0|3|23|1|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|H|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]]]

<a href="http://www.percona.com/software/percona-server" rel="nofollow">Percona Server</a>, which is a branched version of MySQL with some enhancements, has this feature.

You can observe extra columns in SHOW PROCESSLIST for ROWS_SENT and ROWS_EXAMINED. For example, if your table has 1000000 rows, and you see ROWS_EXAMINED of 650000, then it's 65% finished.

See <a href="http://www.percona.com/doc/percona-server/5.6/diagnostics/process_list.html" rel="nofollow">http://www.percona.com/doc/percona-server/5.6/diagnostics/process_list.html</a>

blocks|key|1774143|text|运行ls+-laShr+/var/lib/mysql+%7C+sort+-h，您将在mysql文件夹中看到类似以下内容的文件：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1774144|-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|code-block|syntax|javascript|1774145|您可以看到正在构造的原始表文件和临时目标表文件，其大小人类可读。通常它会随着时间线性增长，所以如果它的大小是原始表的一半，那么它就是一半大小。ls命令将按大小对文件进行排序，因此如果这是一个很大的表，并且您已经等待了一段时间，那么这两个文件都将位于文件列表的底部。|1774146|entityMap^0|2|Y|0|0|1Z|2|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|V|8|@]|D|@]|E|$]]]|N|$]]

Run <code>ls -laShr /var/lib/mysql | sort -h</code> and you'll see files in the mysql folder something like this:

<pre><code>-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
</code></pre>

You can see original table file and temporary target table file as it's being constructed, with human-readable sizes. Usually it will grow linearly with time, so if it's half the size of the original table, it's halfway through. The <code>ls</code> command will sort files by size, so both files will be near the bottom of the file list if this is a large table and you've been waiting a while.

blocks|key|1558973|text|如果有人想要bash解决方案：(+sql对我不起作用)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1558974|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+%25s+$TABLEFILE);

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

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

SIZE2=$(stat+-c+%25s+$TEMPFILE);+TIME2=$(stat+-c+%25Z+$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+\"%25d:%2502d:%252d\",+int($TIME_LEFT_MINUTES+/60),+int($TIME_LEFT_MINUTES+%25+60),+int($TIME_LEFT_SECONDS+%25+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|code-block|syntax|javascript|1558975|结论:这需要时间，很多时间。|1558976|只要确保有空闲的内存即可。和自由空间。比如50%25的内存没有被mysql使用。|1558977|低内存使得整个系统的工作非常低。|1558978|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

if anyone wants a bash solution: (the sql was not working for me)

<pre><code>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
</code></pre>

conclusions: 
it takes time, a lot of time.

just make sure there is ram free. and free space.
like 50% of memory is not used by mysql. 

low ram makes the whole system work very low

blocks|key|1559018|text|这是一个非常古老的问题，但至少MySQL5.7有一个正确的答案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1559019|https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html|offset|length|1559020|本质上..。|1559021|UPDATE+performance_schema.setup_instruments
+++++++SET+ENABLED+=+'YES'
+++++++WHERE+NAME+LIKE+'stage/innodb/alter%25';
UPDATE+performance_schema.setup_consumers
+++++++SET+ENABLED+=+'YES'
+++++++WHERE+NAME+LIKE+'%25stages%25';|code-block|syntax|javascript|1559022|..。运行alter+table|1559023|SELECT+EVENT_NAME,+WORK_COMPLETED,+WORK_ESTIMATED
+++++++FROM+performance_schema.events_stages_current;
%2B------------------------------------------------------%2B----------------%2B----------------%2B
%7C+EVENT_NAME+++++++++++++++++++++++++++++++++++++++++++%7C+WORK_COMPLETED+%7C+WORK_ESTIMATED+%7C
%2B------------------------------------------------------%2B----------------%2B----------------%2B
%7C+stage/innodb/alter+table+(read+PK+and+internal+sort)+%7C++++++++++++280+%7C+++++++++++1245+%7C
%2B------------------------------------------------------%2B----------------%2B----------------%2B
1+row+in+set+(0.01+sec)|1559024|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|2B|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@$D|Z|E|10|1|11]]|A|$]]|$1|F|3|G|5|6|7|12|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|13|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|14|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|15|8|@]|9|@]|A|$K|L]]|$1|Q|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|C]]]]

Very old question but at leas mysql 5.7 has a proper answer for this

<a href="https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html</a>

In essence...

<pre><code>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%';
</code></pre>

... run the alter table ...

<pre><code>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)
</code></pre>

For example, I issued an ALTER TABLE statement to create an index on a MEDIUMTEXT field in an InnoDB table that has 134k rows where the size of the index was 255 bytes and the average size of the data in the field is 30k. This command has been running for the last 15 minutes or so (and is the only thing running on the database). Is there any way for me to determine if it is going to finish in closer to 5 minutes, 5 hours, or 5 days?

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

数据库

MariaDB

MySQL

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

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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