[MDEV-8178] Wrong progress report for operations on InnoDB tables - JIRA

Details

Type: Bug
Status: Confirmed
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.1
Fix Version/s: 10.1
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB, Storage Engine - XtraDB
Labels:
None

Description

Initially reported on the mailing list: https://lists.launchpad.net/maria-discuss/msg02495.html .

Stage: 1 of 72 'altering table' 350% of stage done.

Reproducible as described.

Dataset: salaries table in the employees sample database.

Statements that exhibit the incorrect behaviour:

ALTER TABLE salaries ENGINE = Innodb;
ALTER TABLE salaries ADD id INT NULL;
ALTER TABLE salaries DROP id;
ALTER TABLE salaries ROW_FORMAT = COMPRESSED;

I also saw the mentioned 350% and some other values, e.g.

MariaDB [employees]> ALTER TABLE salaries ADD id INT NULL;
Stage: 1 of 85 'altering table'    200% of stage done

MyISAM looks reasonable (2 stages, below 100% progress).

Not reproducible on 5.5.
10.0 has a different problem – progress for InnoDB tables is not reported at all.

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Jiri Kavalik added a comment - 2015-06-19 14:05 - edited

After ~~MDEV-8179~~ it showed on 10.0.20 today:

optimize table sazky;
Stage: 1 of 149 'altering table'   1.33% of stage done
Stage: 1 of 149 'altering table'   15.8% of stage done
Stage: 1 of 149 'altering table'    100% of stage done
 -- until here tmp file has stable size of 320K, and only after getting to 100% it started to grow by some MB per second
Stage: 1 of 149 'altering table'    350% of stage done
Stage: 1 of 149 'altering table'    800% of stage done
Stage: 1 of 14 'altering table'   14.3% of stage done 
Stage: 1 of 10 'altering table'     20% of stage done 
Stage: 1 of 14 'altering table'   14.3% of stage done 
Stage: 1 of 15 'altering table'   12.5% of stage done 
Stage: 1 of 11 'altering table'   16.7% of stage done 
Stage: 1 of 9 'altering table'     20% of stage done  
Stage: 1 of 12 'altering table'   16.7% of stage done 
Stage: 1 of 18 'altering table'   11.1% of stage done 
Stage: 1 of 8 'altering table'     25% of stage done  
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| vic_main.sazky | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| vic_main.sazky | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (3 min 1.22 sec)

show table status like 'sazky'\G
*************************** 1. row ***************************
           Name: sazky
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1138155
 Avg_row_length: 153
    Data_length: 174800896
Max_data_length: 0
   Index_length: 246022144
      Data_free: 0
...

Show

Jiri Kavalik added a comment - 2015-06-19 14:05 - edited After MDEV-8179 it showed on 10.0.20 today: optimize table sazky; Stage: 1 of 149 'altering table' 1.33% of stage done Stage: 1 of 149 'altering table' 15.8% of stage done Stage: 1 of 149 'altering table' 100% of stage done -- until here tmp file has stable size of 320K, and only after getting to 100% it started to grow by some MB per second Stage: 1 of 149 'altering table' 350% of stage done Stage: 1 of 149 'altering table' 800% of stage done Stage: 1 of 14 'altering table' 14.3% of stage done Stage: 1 of 10 'altering table' 20% of stage done Stage: 1 of 14 'altering table' 14.3% of stage done Stage: 1 of 15 'altering table' 12.5% of stage done Stage: 1 of 11 'altering table' 16.7% of stage done Stage: 1 of 9 'altering table' 20% of stage done Stage: 1 of 12 'altering table' 16.7% of stage done Stage: 1 of 18 'altering table' 11.1% of stage done Stage: 1 of 8 'altering table' 25% of stage done +----------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+----------+----------+-------------------------------------------------------------------+ | vic_main.sazky | optimize | note | Table does not support optimize, doing recreate + analyze instead | | vic_main.sazky | optimize | status | OK | +----------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (3 min 1.22 sec) show table status like 'sazky'\G *************************** 1. row *************************** Name: sazky Engine: InnoDB Version: 10 Row_format: Compact Rows: 1138155 Avg_row_length: 153 Data_length: 174800896 Max_data_length: 0 Index_length: 246022144 Data_free: 0 ...

Hide

Permalink

Michaël de groot added a comment - 2015-09-22 22:40

I experienced this (InnoDB, 71 stages, up to 900%) on 10.0.21 as well.

Show

Michaël de groot added a comment - 2015-09-22 22:40 I experienced this (InnoDB, 71 stages, up to 900%) on 10.0.21 as well.

Hide

Permalink

Jean Weisbuch added a comment - 2015-09-29 11:41

I am hitting this bug on 10.0.21 but with another additional issue : The progress on the CLI starts but after few seconds get stuck and the "STAGE", "MAX_STAGE" and "PROGRESS" columns of "I_S.PROCESSLIST" are all showing 0 for the alter query.

Happened with an INDEX creation on an InnoDB table and on a "ALTER TABLE xxx KEY_BLOCK_SIZE=0" (on a table that had "ROW_FORMAT=DYNAMIC" and "KEY_BLOCK_SIZE=8").

Show

Jean Weisbuch added a comment - 2015-09-29 11:41 I am hitting this bug on 10.0.21 but with another additional issue : The progress on the CLI starts but after few seconds get stuck and the " STAGE ", " MAX_STAGE " and " PROGRESS " columns of " I_S.PROCESSLIST " are all showing 0 for the alter query. Happened with an INDEX creation on an InnoDB table and on a " ALTER TABLE xxx KEY_BLOCK_SIZE=0 " (on a table that had " ROW_FORMAT=DYNAMIC " and " KEY_BLOCK_SIZE=8 ").

Wrong progress report for operations on InnoDB tables

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile