Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.20
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
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
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)
|
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?
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.
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)