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

Question

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?

Answer 1

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

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

In essence...

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%';

... run the 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)

Answer 2

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

-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

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 ls 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.

Answer 3

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

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

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

Answer 4

I made a query that estimates the time to finish an alter command on an innodb table. You have to run it at least twice on the same session since it compares stats from consecutive runs to make the estimatation. Don't forget to change <tableName> to the correct table name on the fourth line. It gives you two estimations. Local estimation uses only data between runs while Global estimation uses the entire transaction time.

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;

Answer 5

Percona Server, 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 http://www.percona.com/doc/percona-server/5.6/diagnostics/process_list.html

Answer 6

In the case of InnoDB tables, one can use SHOW ENGINE INNODB STATUS 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:

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

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

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

Answer 7

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.

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

Answer 8

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

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

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

Answer 9

This is a pretty common request apparently - requested as far back as 2005 on bugs.mysql.com. It exists in Oracle already, and is listed as useful, but "it is not a simple thing to do, so don't expect it to be implemented soon.". Although that was 2005 :)

That said, the chap who asked the original question later released a patch for MySQL 5.0, backported to 4.1, which might help you out.