<p>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?</p>

[{'answer_id': 43444, 'author': 'ConroyP', 'author_id': 2287, 'author_profile': 'https://Stackoverflow.com/users/2287', 'pm_score': 3, 'selected': True, 'text': '<p>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>. \nIt 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\nimplemented soon."</a>. Although that was 2005 :)</p>\n\n<p>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.</p>\n'}, {'answer_id': 7713657, 'author': 'Jay Binks', 'author_id': 987797, 'author_profile': 'https://Stackoverflow.com/users/987797', 'pm_score': 4, 'selected': False, 'text': '<p>I was able to perform these 2 queries and figure out how many rows remain to be moved.</p>\n\n<pre><code>select count(*) from `myoriginalrable`;\nselect count(*) from `#sql-1e8_11ae5`;\n</code></pre>\n\n<p>this was WAY more helpful than comparing the file size on disk, because changing from myisam to innodb etc changes the row size.</p>\n'}, {'answer_id': 12055272, 'author': 'Axel', 'author_id': 1611966, 'author_profile': 'https://Stackoverflow.com/users/1611966', 'pm_score': 4, 'selected': False, 'text': '<p>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:</p>\n\n<p><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></p>\n\n<p>Also MariaDB 5.3 and later has the feature to report progress for some operations (including ALTER TABLE). See:</p>\n\n<p><a href="http://kb.askmonty.org/en/progress-reporting/">http://kb.askmonty.org/en/progress-reporting/</a></p>\n'}, {'answer_id': 13112738, 'author': 'Haluk', 'author_id': 174559, 'author_profile': 'https://Stackoverflow.com/users/174559', 'pm_score': 1, 'selected': False, 'text': '<p>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.</p>\n\n<p>It also has additional functions compared to just running the ALTER command by itself.</p>\n\n<p><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></p>\n'}, {'answer_id': 17755072, 'author': 'aalku', 'author_id': 270349, 'author_profile': 'https://Stackoverflow.com/users/270349', 'pm_score': 2, 'selected': False, 'text': "<p>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.\nDon't forget to change <em>&lt;tableName&gt;</em> 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.</p>\n\n<pre><code>select \nbeginsd, now(), qRuns, qTime, tName, trxStarted, trxTime, `rows`, modified, locked, hoursLeftL, estimatedEndL, modifiedPerSecL, avgRows, estimatedEndG, modifiedPerSecG, hoursLeftG\nfrom (\nselect \n    (@tname:='&lt;table&gt;') tName,\n    @beginsd:=sysdate() beginsd,\n    @trxStarted:=(select trx_started from information_schema.innodb_trx where trx_query like concat('alter table %', @tname, '%')) trxStarted, \n    @trxTime:=timediff(@beginsd, @trxStarted) trxTime,\n    @rows:=(select table_rows from information_schema.tables where table_name like @tname) `rows`,\n    @runs:=(ifnull(@runs, 0)+1) qRuns,\n    @rowsSum:=(ifnull(@rowsSum, 0)+@rows),\n    round(@avgRows:=(@rowsSum / @runs)) avgRows,\n    @modified:=(select trx_rows_modified from information_schema.innodb_trx where trx_query like concat('alter table %', @tname, '%')) modified, \n    @rowsLeftL:=(cast(@rows as signed) - cast(@modified as signed)) rowsLeftL,\n    round(@rowsLeftG:=(cast(@avgRows as signed) - cast(@modified as signed)), 2) rowsLeftG,\n    @locked:=(select trx_rows_locked from information_schema.innodb_trx where trx_query like concat('alter table %', @tname, '%')) locked,\n    @endsd:=sysdate() endsd,\n    --\n    time_to_sec(timediff(@endsd, @beginsd)) qTime,\n    @modifiedInc:=(cast(@modified as signed) - cast(@p_modified as signed)) modifiedInc,\n    @timeInc:=time_to_sec(timediff(@beginsd, @p_beginsd)) timeInc,\n    round(@modifiedPerSecL:=(@modifiedInc/@timeInc)) modifiedPerSecL,\n    round(@modifiedPerSecG:=(@modified/time_to_sec(@trxTime))) modifiedPerSecG,\n    round(@minutesLeftL := (@rowsLeftL / @modifiedPerSecL / 60)) minutesLeftL,\n    round(@minutesLeftG := (@rowsLeftG / @modifiedPerSecG / 60)) minutesLeftG,\n    round(@hoursLeftL := (@minutesLeftL / 60), 2) hoursLeftL,\n    round(@hoursLeftG := (@minutesLeftG / 60), 2) hoursLeftG,\n    (@beginsd + INTERVAL @minutesLeftL MINUTE) estimatedEndL,\n    (@beginsd + INTERVAL @minutesLeftG MINUTE) estimatedEndG,\n    --\n    @p_rows:=@rows,\n    @p_modified:=@modified,\n    @p_beginsd:=@beginsd\n) sq;\n</code></pre>\n"}, {'answer_id': 17755769, 'author': 'Bill Karwin', 'author_id': 20860, 'author_profile': 'https://Stackoverflow.com/users/20860', 'pm_score': 0, 'selected': False, 'text': '<p><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.</p>\n\n<p>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.</p>\n\n<p>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></p>\n'}, {'answer_id': 54616646, 'author': 'mahemoff', 'author_id': 18706, 'author_profile': 'https://Stackoverflow.com/users/18706', 'pm_score': 2, 'selected': False, 'text': "<p>Run <code>ls -laShr /var/lib/mysql | sort -h</code> and you'll see files in the mysql folder something like this:</p>\n\n<pre><code>-rw-r----- 1 mysql mysql 3.3G Feb  9 13:21 sql-#2088_10fa.ibd\n-rw-r----- 1 mysql mysql 10.2G Feb  9 13:21 posts.ibd\n</code></pre>\n\n<p>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.</p>\n"}, {'answer_id': 54816616, 'author': 'Shimon Doodkin', 'author_id': 466363, 'author_profile': 'https://Stackoverflow.com/users/466363', 'pm_score': 1, 'selected': False, 'text': '<p>if anyone wants a bash solution:  (the sql was not working for me)</p>\n\n<pre><code>cd /var/lib/mysql/mydb\nTABLEFILE="MYTABLE.ibd"\nTEMPFILE="\\#*ibd"\n\nls -lah $TABLEFILE;\nls -lah $TEMPFILE; # make sure you have only one temp file or modify the above TEMPFILE\n\nSIZE_TOTAL=$(stat -c %s $TABLEFILE);\n\n# other ways to get 1st size and time\n#SIZE1=1550781106; TIME1=1550781106;\n#SIZE1=$(stat -c %s $TEMPFILE); TIME1=$(stat -c %Z $TEMPFILE);  sleep 10;\nSIZE1=0; TIME1=$(stat -c %X $TEMPFILE); # use file create time\n\necho "SIZE1=$TIME1; TIME1=$TIME1";\n\nSIZE2=$(stat -c %s $TEMPFILE); TIME2=$(stat -c %Z $TEMPFILE);\n\nDELTA_SIZE=$(( $SIZE2 - $SIZE1 ))\nDELTA_TIME=$(( $TIME2 - $TIME1 ))\n\n# debug last numbers should not be zero:\n\necho $SIZE1  $SIZE2  $SIZE_TOTAL  $DELTA_SIZE;\necho $TIME1  $TIME2  $DELTA_TIME;\n\nSIZE_PER_SECOND=$( awk "BEGIN {print $DELTA_SIZE  / $DELTA_TIME }" );\nSIZE_LEFT=$(($SIZE_TOTAL - $SIZE2));\nTIME_LEFT_SECONDS=$( awk "BEGIN { print  ( $SIZE_LEFT  / $SIZE_PER_SECOND) }" );\nTIME_LEFT_MINUTES=$( awk "BEGIN { print  $TIME_LEFT_SECONDS /60 }" );\nTIME_LEFT=$( awk "BEGIN { printf \\"%d:%02d:%2d\\", int($TIME_LEFT_MINUTES /60), int($TIME_LEFT_MINUTES % 60), int($TIME_LEFT_SECONDS % 60 )  }" );\n\necho "TIME_LEFT = $TIME_LEFT";\necho "SIZE_LEFT = $SIZE_LEFT" "MB=" $(( $SIZE_LEFT/1024/1024 )) ;\nawk "BEGIN { if( $SIZE_TOTAL == $SIZE2 ) print \\"mysql finished\\" }" ;\n\nfree -h # check free memory, sometimes it is full and it makes it slow\n</code></pre>\n\n<p>conclusions: \nit takes time, a lot of time.</p>\n\n<p>just make sure there is ram free. and free space.\nlike 50% of memory is not used by mysql. </p>\n\n<p>low ram makes the whole system work very low</p>\n'}, {'answer_id': 62174308, 'author': 'Gonzalo Cao', 'author_id': 1222923, 'author_profile': 'https://Stackoverflow.com/users/1222923', 'pm_score': 2, 'selected': False, 'text': '<p>Very old question but at leas mysql 5.7 has a proper answer for this</p>\n\n<p><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></p>\n\n<p>In essence...</p>\n\n<pre><code>UPDATE performance_schema.setup_instruments\n       SET ENABLED = \'YES\'\n       WHERE NAME LIKE \'stage/innodb/alter%\';\nUPDATE performance_schema.setup_consumers\n       SET ENABLED = \'YES\'\n       WHERE NAME LIKE \'%stages%\';\n</code></pre>\n\n<p>... run the alter table ...</p>\n\n<pre><code>SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED\n       FROM performance_schema.events_stages_current;\n+------------------------------------------------------+----------------+----------------+\n| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |\n+------------------------------------------------------+----------------+----------------+\n| stage/innodb/alter table (read PK and internal sort) |            280 |           1245 |\n+------------------------------------------------------+----------------+----------------+\n1 row in set (0.01 sec)\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/43422', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2168/']