Get the Status of a Currently Running Alter

Nick Lamb -

To check the status of an ALTER you can simply query the system.alter_progress table with:

sql> select * from system.alter_progress;

This will show the following information about any alters in progress which will give an estimate on the time the alter will complete based off of the amount of time and work done up until that point. Please note that system load and writes to the table will affect the alter time and can change the estimation.

+-----------------------+--------------------+
| Field | Type |
+-----------------------+--------------------+
| session_id | int(0) unsigned |
| src_relation_oid | oid |
| src_relation_name | varchar(65535) |
| start | datetime(6) |
| copy_xid | oid |
| rows_read | bigint(0) unsigned |
| src_relation_est_rows | bigint(0) unsigned |
| progress_pct | double |
| completion_est | datetime(6) |
+-----------------------+--------------------+

---------------------------------------------------------------------------------------------

To get an indication as to how far along an alter is in earlier versions of ClustrixDB (before 9.0), you can do the following:

First find the xid for the transaction in the system.transactions table. You'll be looking for SERIALIZABLE (DDL) here. For example:

mysql> select * from system.transactions where isolation='SERIALIZABLE ' order by 2,1;

Screen_Shot_2012-05-24_at_6.32.06_PM.pngThat SERIALIZABLE transaction was started by the alter table machinery. You can then track the progress of the alter statement by getting an estimate for the number of rows in the table using explain:

mysql> explain select * from databasename.tablename;

Screen_Shot_2012-05-24_at_6.32.11_PM.png

And then plug in that number into the following query:

mysql> select sum(rows_read)/122838050 from system.transactions where xid = 5745092185389604867;

Screen_Shot_2012-05-24_at_6.32.14_PM.png

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk