[MDEV-8471] No progress report for online DDL of InnoDB tables Created: 2015-07-15  Updated: 2015-08-08  Resolved: 2015-08-08

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.0.20
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 1
Labels: innodb

Issue Links:
Relates
relates to MDEV-8179 Absent progress report for operations... Closed

 Description   

Progress reports for online DDL on InnoDB tables does not appear to be working, even after the fix for MDEV-8179.

For example, if I have this table:

CREATE TABLE db1.test_table (
 
   id INT AUTO_INCREMENT PRIMARY KEY,
 
   file BLOB
 
);

And then I execute this:

ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL;

The progress printed to the terminal is stuck at 25% of stage 1, and SHOW PROCESSLIST is stuck at 0%:

MariaDB [(none)]> SHOW PROCESSLIST;
 
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State          | Info                                                           | Progress |
 
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
 
|  6 | root | localhost | NULL | Query   |   22 | altering table | ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL |    0.000 |
 
|  7 | root | localhost | NULL | Query   |    0 | init           | SHOW PROCESSLIST                                               |    0.000 |
 
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW PROCESSLIST;
 
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State          | Info                                                           | Progress |
 
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
 
|  6 | root | localhost | NULL | Query   |   23 | altering table | ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL |    0.000 |
 
|  7 | root | localhost | NULL | Query   |    0 | init           | SHOW PROCESSLIST                                               |    0.000 |
 
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW PROCESSLIST;
 
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State          | Info                                                           | Progress |
 
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
 
|  6 | root | localhost | NULL | Query   |   25 | altering table | ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL |    0.000 |
 
|  7 | root | localhost | NULL | Query   |    0 | init           | SHOW PROCESSLIST                                               |    0.000 |
 
+----+------+-----------+------+---------+------+----------------+----------------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)

However, if I perform an operation that forces a table copy, such as changing a column's data type, I get a progress report:

ALTER TABLE db1.test_table MODIFY COLUMN file longblob;

The progress report:

MariaDB [(none)]> SHOW PROCESSLIST;
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                                   | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
|  6 | root | localhost | NULL | Query   |   36 | copy to tmp table | ALTER TABLE db1.test_table MODIFY COLUMN file longblob |   48.527 |
 
|  7 | root | localhost | NULL | Query   |    0 | init              | SHOW PROCESSLIST                                       |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW PROCESSLIST;
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                                   | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
|  6 | root | localhost | NULL | Query   |   37 | copy to tmp table | ALTER TABLE db1.test_table MODIFY COLUMN file longblob |   49.667 |
 
|  7 | root | localhost | NULL | Query   |    0 | init              | SHOW PROCESSLIST                                       |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW PROCESSLIST;
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                                   | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
|  6 | root | localhost | NULL | Query   |   38 | copy to tmp table | ALTER TABLE db1.test_table MODIFY COLUMN file longblob |   50.787 |
 
|  7 | root | localhost | NULL | Query   |    0 | init              | SHOW PROCESSLIST                                       |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW PROCESSLIST;
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                                   | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
|  6 | root | localhost | NULL | Query   |   39 | copy to tmp table | ALTER TABLE db1.test_table MODIFY COLUMN file longblob |   52.329 |
 
|  7 | root | localhost | NULL | Query   |    0 | init              | SHOW PROCESSLIST                                       |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------+----------+
 
2 rows in set (0.01 sec)

Should progress reporting work for online ALTER TABLE in 10.0? I see no mention that it shouldn't in the KB:

https://mariadb.com/kb/en/mariadb/progress-reporting/

https://mariadb.com/kb/en/mariadb/alter-table/#progress-reports

 Comments   
Comment by Geoff Montee (Inactive) [ 2015-07-15 ]

If I force the ADD COLUMN operation to use ALGORITHM=COPY, progress is reported:

ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL
 
, ALGORITHM=COPY;

Then the progress:

MariaDB [(none)]> SHOW PROCESSLIST;
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                                                           | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------+----------+
 
|  6 | root | localhost | NULL | Query   |   44 | copy to tmp table | ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL, ALGORITHM=COPY |   50.903 |
 
|  7 | root | localhost | NULL | Query   |    0 | init              | SHOW PROCESSLIST                                                               |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [(none)]> SHOW PROCESSLIST;
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                                                           | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------+----------+
 
|  6 | root | localhost | NULL | Query   |   45 | copy to tmp table | ALTER TABLE db1.test_table ADD COLUMN new_int_col INT NOT NULL, ALGORITHM=COPY |   52.044 |
 
|  7 | root | localhost | NULL | Query   |    0 | init              | SHOW PROCESSLIST                                                               |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)

Comment by Jan Lindström (Inactive) [ 2015-07-21 ]

Hi, I tested this and I do see progress reports on both mysql command line tool as well as show processlist. Note that InnoDB will send progress report only when doing merge sort phase while reconstructing clustered index for the table (basically all tables are stored as clustered index that contain all columns in the table).

MariaDB [(none)]> show processlist;
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State          | Info                                                       | Progress |
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
|  2 | root | localhost | NULL | Query   |    0 | init           | show processlist                                           |    0.000 |
 
|  3 | root | localhost | test | Query   |    9 | altering table | ALTER TABLE test_table ADD COLUMN new_int_col INT NOT NULL |    0.065 |
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
 
 
MariaDB [(none)]> show processlist;
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State          | Info                                                       | Progress |
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
|  2 | root | localhost | NULL | Query   |    0 | init           | show processlist                                           |    0.000 |
 
|  3 | root | localhost | test | Query   |   15 | altering table | ALTER TABLE test_table ADD COLUMN new_int_col INT NOT NULL |    0.260 |
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
 
 
MariaDB [(none)]> show processlist;
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State          | Info                                                       | Progress |
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
|  2 | root | localhost | NULL | Query   |    0 | init           | show processlist                                           |    0.000 |
 
|  3 | root | localhost | test | Query   |   20 | altering table | ALTER TABLE test_table ADD COLUMN new_int_col INT NOT NULL |    0.779 |
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
 
 
MariaDB [(none)]> show processlist;
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State          | Info                                                       | Progress |
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
|  2 | root | localhost | NULL | Query   |    0 | init           | show processlist                                           |    0.000 |
 
|  3 | root | localhost | test | Query   |   26 | altering table | ALTER TABLE test_table ADD COLUMN new_int_col INT NOT NULL |    1.818 |
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)
 
 
 
 
 
MariaDB [(none)]> show processlist;
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State          | Info                                                       | Progress |
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
|  2 | root | localhost | NULL | Query   |    0 | init           | show processlist                                           |    0.000 |
 
|  3 | root | localhost | test | Query   |   29 | altering table | ALTER TABLE test_table ADD COLUMN new_int_col INT NOT NULL |    4.545 |
 
+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------+----------+
 
2 rows in set (0.00 sec)

Comment by Geoff Montee (Inactive) [ 2015-07-21 ]

Hi jplindst,

Note that InnoDB will send progress report only when doing merge sort phase while reconstructing clustered index for the table (basically all tables are stored as clustered index that contain all columns in the table).

In practical terms, what does that mean for online DDL in InnoDB?

Are there any alter table operations that wouldn't support progress reporting if performed online? For example, if progress reporting is only done when reconstructing the clustered index in InnoDB, is the clustered index reconstructed at all when adding an index to a table with ALGORITHM=INPLACE?

Comment by Jan Lindström (Inactive) [ 2015-07-27 ]

Hi,

My reply was little bit incorrect. InnoDB will send progress report only when doing merge sort phase while reconstructing indexes i.e. either clustered index for the table (basically all tables are stored as clustered index that contain all columns in the table) or secondary indexes.

Generated at Thu Feb 08 07:27:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.