How to monitor ALTER TABLE progress in MySQL
While working on a recent support issue as a Percona Support Engineer, I got one question from a customer asking how to monitor ALTER TABLE progress. Actually, for MySQL 5.5 and prior versions, it’s quite difficult to ALTER the table in a running production environment especially for large tables (with millions records). Because it will rebuild and lock the table affecting the performance as well as our users. Therefore even if we start ALTER it’s really important to know when it will finish. Even while creating the index, ALTER TABLE will not rebuild the table if fast_index_creation is ON but still it might lock the table.
fast_index_creation feature was introduced in MySQL 5.5 and higher versions. Also available in MySQL 5.1 with the InnoDB Plugin
From MySQL 5.6 and later, “Online DDL” feature has been introduced, which is enhancing many other types of ALTER TABLE operations to avoid “copying the table” and “locking.” It also allows SELECT, INSERT, UPDATE, and DELETE statements to run while the table is being altered. So in latest version, we can control the copy of file and locking by using ALGORITHM and LOCK options. But even in MySQL 5.6, there are some ALTER operations which still required table rebuild i.e ADD/DROP column, change data type, ADD/DROP primary key etc. You can check table here for more details.
“Summary of Online Status for DDL Operations” http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
So in any case, if required, we can check the ALTER TABLE progress with below solutions.
One of the solutions is to use the Percona Toolkit utility, pt-online-schema-change which will ALTER the table without locking it for long time and show the progress. i.e
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
nilnandan@Dell-XPS:~$ pt-online-schema-change --alter "ADD COLUMN phone INT" u=root,p=root,D=nil,t=nil_test --execute No slaves found. See --recursion-method if host Dell-XPS has slaves. Not checking slave lag because no slaves were found and --check-slave-lag was not specified. Operation, tries, wait: copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `nil`.`nil_test`... Creating new table... Created new table nil._nil_test_new OK. Altering new table... Altered `nil`.`_nil_test_new` OK. 2014-02-07T12:20:54 Creating triggers... 2014-02-07T12:20:54 Created triggers OK. 2014-02-07T12:20:54 Copying approximately 12583349 rows... Copying `nil`.`nil_test`: 29% 01:12 remain Copying `nil`.`nil_test`: 60% 00:38 remain Copying `nil`.`nil_test`: 91% 00:08 remain 2014-02-07T12:22:33 Copied rows OK. 2014-02-07T12:22:33 Swapping tables... 2014-02-07T12:22:33 Swapped original and new tables OK. 2014-02-07T12:22:33 Dropping old table... 2014-02-07T12:22:33 Dropped old table `nil`.`_nil_test_old` OK. 2014-02-07T12:22:33 Dropping triggers... 2014-02-07T12:22:33 Dropped triggers OK. Successfully altered `nil`.`nil_test`. nilnandan@Dell-XPS:~$ |
While using pt-online-schema-change, many times I get questions such as: “What will happen to those data changes (INSERT/UPDATE/DELETE) which are performing during the ALTER TABLE?”
Here, I would like to explain briefly about it. While running pt-online-schema-change, if we will check data dir,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
root@Dell-XPS:/var/lib/mysql/nil# ll total 830524 drwx------ 2 mysql mysql 4096 Feb 7 12:20 ./ drwxr-xr-x 5 mysql mysql 4096 Feb 7 12:05 ../ -rw-rw---- 1 mysql mysql 65 Jan 31 12:12 db.opt -rw-rw---- 1 mysql mysql 8616 Feb 7 12:06 nil_test.frm -rw-rw---- 1 mysql mysql 822083584 Feb 7 12:18 nil_test.ibd -rw-rw---- 1 mysql mysql 8648 Feb 7 12:20 _nil_test_new.frm -rw-rw---- 1 mysql mysql 28311552 Feb 7 12:20 _nil_test_new.ibd -rw-rw---- 1 mysql mysql 944 Feb 7 12:20 nil_test.TRG -rw-rw---- 1 mysql mysql 40 Feb 7 12:20 pt_osc_nil_nil_test_del.TRN -rw-rw---- 1 mysql mysql 40 Feb 7 12:20 pt_osc_nil_nil_test_ins.TRN -rw-rw---- 1 mysql mysql 40 Feb 7 12:20 pt_osc_nil_nil_test_upd.TRN |
We can see that it will create triggers (separate trigger for INSERT, UPDATE and DELETE) on the original table to update corresponding rows to the new table( _nil_test_new). So any modifications happened to the data in original tables during the copy, will be reflected in the new table.
NOTE: This tool will not work if any triggers are already defined on the table.
But what if we don’t want to use pt-online-schema-change and run regular ALTER TABLE on mysql prompt? After some research I found many ways to calculate the progress of ALTER TABLE specially with innodb_file_per_table is ON. (innodb_file_per_table is ON by default in MySQL 5.6.)
- Calculate the progress by checking the size of temporary tablespace.
With innodb_file_per_table=1,when we are running ALTER TABLE, innodb creates a temporary tablespace within the same data directory with random name starting from #sql ending with .ibd like #sql-1c80_27.ibd. i.e
|
1 2 3 4 5 6 |
root@Dell-XPS:/var/lib/mysql/nil# ll ... -rw-rw---- 1 mysql mysql 8682 Feb 7 13:33 nil_test.frm -rw-rw---- 1 mysql mysql 335544320 Feb 7 13:34 nil_test.ibd -rw-rw---- 1 mysql mysql 8716 Feb 7 13:35 #sql-1c80_27.frm -rw-rw---- 1 mysql mysql 23068672 Feb 7 13:35 #sql-1c80_27.ibd |
While altering table, innodb reads original ibd file like nil_test.ibd and writes new pages to #sql-1c80_27.ibd. So with file size of nil_test.ibd and temporary #sql-1c80_27.ibd , we can check the ALTER TABLE progress with something like,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
#!/bin/bash while true do A=`du -m '#sql'*'.ibd' 2>/dev/null|cut -f 1`; # if $A -lt 0 ; if [[ -z "$A" ]] ; then echo "Done"; exit 0 ; fi TABLENAME='nil_test'; TT=$TABLENAME.ibd; B=`du -m $TT |cut -f 1`; echo "ALTER TABLE $TABLENAME...$A MB written to tmp tablespace out of $B MB"; sleep 10 done |
When we run ALTER on mysql, we can simply run this script in data dir and check the progress like,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> ALTER TABLE nil_test ADD COLUMN phone int; Query OK, 7582912 rows affected (58.54 sec) Records: 7582912 Duplicates: 0 Warnings: 0 root@Dell-XPS:/var/lib/mysql/nil# ./alter_table_monitor.sh ALTER TABLE nil_test...23 MB written in tmp file out of 485 MB ALTER TABLE nil_test...73 MB written in tmp file out of 485 MB ALTER TABLE nil_test...121 MB written in tmp file out of 485 MB ALTER TABLE nil_test...173 MB written in tmp file out of 485 MB ALTER TABLE nil_test...225 MB written in tmp file out of 485 MB ALTER TABLE nil_test...277 MB written in tmp file out of 485 MB ALTER TABLE nil_test...333 MB written in tmp file out of 485 MB ALTER TABLE nil_test...381 MB written in tmp file out of 485 MB ALTER TABLE nil_test...433 MB written in tmp file out of 485 MB ALTER TABLE nil_test...481 MB written in tmp file out of 485 MB Done root@Dell-XPS:/var/lib/mysql/nil# |
There are couple of things which we need to consider while using this script.
- We have to change script with $TABLENAME as per your requirement. It will work for only one ALTER TABLE.
- Script should run from database dir (i.e /var/lib/mysql/nil)
- This calculations are an approximation, because new table can be a bit different in size. For example, deleted rows or fragmentation can cause accuracy problems.
I would like to mention here that we cannot check the progress of every ALTER TABLE with the above formula. For example, with fast_index_creation, It will create the table without any secondary indexes, then adding the secondary indexes after the data is loaded so it will not create temp tablespace (.ibd file) while creating secondary index. This process is very interesting, let me explain here.
When we add secondary index with ‘fast_index_creation‘, it will only create #sql***.frm file to update table structure but not temporary .ibd file. It will do some file sorts and then add directly index to original table.ibd file. so when you monitors those files, initially you didn’t see that file size increasing, but after some time (after files sorts) file size starts increasing till the end of ALTER TABLE. i.e
Initially, you’ll see
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
root@Dell-XPS:/var/lib/mysql/nil# ll total 409644 drwx------ 2 mysql mysql 4096 Feb 12 10:50 ./ drwxr-xr-x 5 mysql mysql 4096 Feb 12 10:10 ../ -rwxr-xr-x 1 root root 306 Feb 7 13:35 alter_table_monitor.sh* -rw-rw---- 1 mysql mysql 65 Jan 31 12:12 db.opt -rw-rw---- 1 mysql mysql 8750 Feb 12 10:26 nil_test.frm -rw-rw---- 1 mysql mysql 419430400 Feb 12 10:50 nil_test.ibd -rw-rw---- 1 mysql mysql 8750 Feb 12 10:50 #sql-671_25.frm ... root@Dell-XPS:/var/lib/mysql/nil# ll total 409644 drwx------ 2 mysql mysql 4096 Feb 12 10:50 ./ drwxr-xr-x 5 mysql mysql 4096 Feb 12 10:10 ../ -rwxr-xr-x 1 root root 306 Feb 7 13:35 alter_table_monitor.sh* -rw-rw---- 1 mysql mysql 65 Jan 31 12:12 db.opt -rw-rw---- 1 mysql mysql 8750 Feb 12 10:26 nil_test.frm -rw-rw---- 1 mysql mysql 419430400 Feb 12 10:50 nil_test.ibd -rw-rw---- 1 mysql mysql 8750 Feb 12 10:50 #sql-671_25.frm |
Then suddenly .ibd file size starts increasing…till end.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
root@Dell-XPS:/var/lib/mysql/nil# ll total 417836 drwx------ 2 mysql mysql 4096 Feb 12 10:50 ./ drwxr-xr-x 5 mysql mysql 4096 Feb 12 10:10 ../ -rwxr-xr-x 1 root root 306 Feb 7 13:35 alter_table_monitor.sh* -rw-rw---- 1 mysql mysql 65 Jan 31 12:12 db.opt -rw-rw---- 1 mysql mysql 8750 Feb 12 10:26 nil_test.frm -rw-rw---- 1 mysql mysql 427819008 Feb 12 10:50 nil_test.ibd -rw-rw---- 1 mysql mysql 8750 Feb 12 10:50 #sql-671_25.frm .... root@Dell-XPS:/var/lib/mysql/nil# ll total 487456 drwx------ 2 mysql mysql 4096 Feb 12 10:51 ./ drwxr-xr-x 5 mysql mysql 4096 Feb 12 10:10 ../ -rwxr-xr-x 1 root root 306 Feb 7 13:35 alter_table_monitor.sh* -rw-rw---- 1 mysql mysql 65 Jan 31 12:12 db.opt -rw-rw---- 1 mysql mysql 8750 Feb 12 10:50 nil_test.frm -rw-rw---- 1 mysql mysql 499122176 Feb 12 10:51 nil_test.ibd root@Nil-Dell-XPS:/var/lib/mysql/nil# |
This will happen if there is only one secondary index. If there are multiple secondary indexes then for each index, process will pause, do file sorts and then add index so the number of pauses in file size increases, will be equal to number of secondary indexes.
- Calculate the progress by checking the records in information_schema.GLOBAL_TEMPORARY_TABLES
When the ALTER TABLE is running, we can also check GLOBAL_TEMPORARY_TABLES in information_schema and it will give you records count which are inserted in temporary table. i.e
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
mysql> select * from GLOBAL_TEMPORARY_TABLES G; *************************** 1. row *************************** SESSION_ID: 38 TABLE_SCHEMA: nil TABLE_NAME: #sql-1c80_27 ENGINE: InnoDB NAME: #sql-696_26 TABLE_ROWS: 623711 AVG_ROW_LENGTH: 42 DATA_LENGTH: 26787840 INDEX_LENGTH: 0 CREATE_TIME: 2014-02-11 10:37:34 UPDATE_TIME: NULL 1 row in set (0.00 sec) ....... mysql> select * from GLOBAL_TEMPORARY_TABLES G; *************************** 1. row *************************** SESSION_ID: 38 TABLE_SCHEMA: nil TABLE_NAME: #sql-1c80_27 ENGINE: InnoDB NAME: #sql-696_26 TABLE_ROWS: 7017408 AVG_ROW_LENGTH: 42 DATA_LENGTH: 299663360 INDEX_LENGTH: 0 CREATE_TIME: 2014-02-11 10:37:34 UPDATE_TIME: NULL 1 row in set (0.01 sec) |
- Calculate the progress by checking the Handler_read_rnd_next status variable (global counters).
While running ALTER TABLE, we can also check the handler_read_rnd_next status variable by “SHOW GLOBAL STATUS LIKE ‘Handler_read_rnd%’ OR mysqladmin extended. Check this 2008 post titled “How to estimate query completion time in MySQL” by Baron Schwartz. He describes it very well.
NOTE: This won’t need innodb_file_per_table = ON but we can use this option only when there are no other parallel transactions running.
So normally in any case, it will become really difficult to find the ALTER TABLE progress unless MySQL itself will provide some feature like MariaDB is providing progress in “SHOW PROCESSLIST” https://mariadb.com/kb/en/show-processlist/
I’ve installed MariaDB 5.5 locally and tried to check, along with “SHOW PROCESSLIST”, you can also check progress by running query against information_schema.processlist table. But I observed that progress time is different in both the output. It looks like information_schema.processlist table gives accurate progress for ALTER TABLE.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
[root@percona-pxc3 nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist" +----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+ | ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS | +----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+ | 2 | root | localhost | nil | 3 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 4.279 | +----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+ +----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+ | 2 | root | localhost | nil | Query | 3 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 2.140 | | 29 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | +----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+ [root@percona-pxc3 nil]# ............... ........... [root@percona-pxc3 nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist" +----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+ | ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS | +----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+ | 2 | root | localhost | nil | 25 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 45.613 | +----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+ +----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+ | 2 | root | localhost | nil | Query | 25 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 22.807 | | 34 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | +----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+ [root@percona-pxc3 nil]# .............. ......... [root@percona-pxc3 nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist" +----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+ | ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS | +----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+ | 2 | root | localhost | nil | 54 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 1 | 2 | 98.300 | +----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+ +----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+ | 2 | root | localhost | nil | Query | 54 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int | 49.157 | | 39 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | +----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+ [root@percona-pxc3 nil]# [root@percona-pxc3 nil]# mysql -uroot -proot -e "select ID, USER, HOST, DB, TIME, STATE, INFO, STAGE, MAX_STAGE, PROGRESS from information_schema.processlist where ID = 2; show processlist" +----+------+-----------+------+------+-------+------+-------+-----------+----------+ | ID | USER | HOST | DB | TIME | STATE | INFO | STAGE | MAX_STAGE | PROGRESS | +----+------+-----------+------+------+-------+------+-------+-----------+----------+ | 2 | root | localhost | nil | 0 | | NULL | 0 | 0 | 0.000 | +----+------+-----------+------+------+-------+------+-------+-----------+----------+ +----+------+-----------+------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+------+-----------+------+---------+------+-------+------------------+----------+ | 2 | root | localhost | nil | Sleep | 1 | | NULL | 0.000 | | 40 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | +----+------+-----------+------+---------+------+-------+------------------+----------+ [root@percona-pxc3 nil]# |
Conclusion: As we know, some ALTER TABLE can cause problems like server hang or crash. So whenever we are planning to run it on productions servers, it has to be well planned. As MySQL doesn’t provide a “progress bar,” we can use the above explained methods to check progress. If anyone knows of any other method aside from the above then please share in the comments and I would glad to add it in this post.
Comments (8)
I’ve been working on this now for a little while, but is there any solution like the shell script for partitioned tables?
I’ve been working on one in python but the tmp table ends up being far larger than the table I’m altering when using partitions.
Hi Kevin,
I think, same solution you can use for partitioned tables as it will create temp tablespace for each partition. But yes, as I said in the post “This calculations are an approximation, because new table can be a bit different in size.” so it can end up with large temp table size but it should not become very large. Have you checked if there are any bugs related to this? Please let us know which MySQL/PS version, you are using.
Nilnandan,
It seems I spoke too soon. The shell script doesn’t work for partitioned tables, but the python script I wrote does work… it just seems I had an alter die that left behind the temp table partitions of a different table. Sorry for the confusion.
Any view on myisam table’s ALTER progress bar ?
Is’t possible calculate required tmp (tmpdir) disk space for online DDL queries?
As well in the case of online DDL with inplace operation (5.6 and higher) is used tmpdir too. But imho not within same size as whole table.
This is great. I used the temp table size trick to help me figure out if I need to start freaking out or not. And I didn’t! So thanks for sharing!
I wrote this query to monitor progress via row count estimates using InnoDB locked rows as a proxy for progress, as indicated above. Useful on systems where you don’t have shell access.
select info stmt, id process, time run_time, state,
trx_rows_locked, table_rows,
round(trx_rows_locked/table_rows*100,2) pct_done,
round(trx_rows_locked/time) rows_per_sec,
round(table_rows/(trx_rows_locked/time)/60/60,2) hours_remaining,
round(table_rows/(trx_rows_locked/time)/60,2) minutes_remaining
from information_schema.tables t
join information_schema.processlist p
on p.info like concat(‘alter table ‘,t.table_name,’%’)
and p.db = t.table_schema
join information_schema.innodb_trx x
on x.trx_mysql_thread_id = p.id
;
adjust the join criteria if your statements are more complex or have line breaks up front, of course.
argh, the remaining time calculations need to subtract the locked rows from the total rows before dividing:
round((table_rows-trx_rows_locked)/(trx_rows_locked/time)/60/60,2) hours_remaining,
round((table_rows-trx_rows_locked)/(trx_rows_locked/time)/60,2) minutes_remaining