[MDEV-5741] Different progress in "SHOW PROCESSLIST" and I_S.processlist table Created: 2014-02-26  Updated: 2014-02-26  Resolved: 2014-02-26

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.35
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Nilnandan Joshi Assignee: Unassigned
Resolution: Not a Bug Votes: 1
Labels: None
Environment:

CentOS (64-bit)


 Description   

While checking progress of ALTER TABLE, I found that "progress" value in SHOW PROCESSLIST and information_schema.processlist table are different. In fact, information_schema.processlist table is gives accurate progress for ALTER TABLE.

[root@centos 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@centos nil]# 
[root@centos 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  |    8 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |     1 |         2 |   14.304 |
 
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                       | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
|  2 | root | localhost | nil  | Query   |    9 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |    7.152 |
 
| 31 | root | localhost | NULL | Query   |    0 | NULL              | show processlist                           |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
[root@centos nil]# 
[root@centos nil]# 
[root@centos 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  |   14 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |     1 |         2 |   25.986 |
 
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                       | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
|  2 | root | localhost | nil  | Query   |   15 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |   12.993 |
 
| 32 | root | localhost | NULL | Query   |    0 | NULL              | show processlist                           |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
[root@centos 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  |   20 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |     1 |         2 |   37.117 |
 
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                       | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
|  2 | root | localhost | nil  | Query   |   21 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |   18.558 |
 
| 33 | root | localhost | NULL | Query   |    0 | NULL              | show processlist                           |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
[root@centos nil]# 
[root@centos nil]# 
[root@centos 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@centos nil]# 
[root@centos nil]# 
[root@centos 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  |   30 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |     1 |         2 |   54.117 |
 
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                       | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
|  2 | root | localhost | nil  | Query   |   30 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |   27.059 |
 
| 35 | root | localhost | NULL | Query   |    0 | NULL              | show processlist                           |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
[root@centos nil]# 
[root@centos nil]# 
[root@centos 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  |   35 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |     1 |         2 |   64.565 |
 
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                       | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
|  2 | root | localhost | nil  | Query   |   36 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |   32.284 |
 
| 36 | root | localhost | NULL | Query   |    0 | NULL              | show processlist                           |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
[root@centos nil]# 
[root@centos 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  |   41 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |     1 |         2 |   74.495 |
 
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                       | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
|  2 | root | localhost | nil  | Query   |   41 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |   37.248 |
 
| 37 | root | localhost | NULL | Query   |    0 | NULL              | show processlist                           |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
[root@centos nil]# 
[root@centos 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  |   51 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |     1 |         2 |   93.185 |
 
+----+------+-----------+------+------+-------------------+--------------------------------------------+-------+-----------+----------+
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
| Id | User | Host      | db   | Command | Time | State             | Info                                       | Progress |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
|  2 | root | localhost | nil  | Query   |   51 | copy to tmp table | ALTER TABLE nil_test ADD COLUMN phone1 int |   46.593 |
 
| 38 | root | localhost | NULL | Query   |    0 | NULL              | show processlist                           |    0.000 |
 
+----+------+-----------+------+---------+------+-------------------+--------------------------------------------+----------+
 
[root@centos nil]# 
[root@centos 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@centos nil]# 
[root@centos 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@centos nil]# 
[root@centos nil]#



 Comments   
Comment by Elena Stepanova [ 2014-02-26 ]

SHOW PROCESSLIST and INFORMATION_SCHEMA.PROCESSLIST are supposed to show different progress values if the operation has more than 1 stage.
SHOW PROCESSLIST shows the total progress for the operation. INFORMATION_SCHEMA.PROCESSLIST provides more detailed info – it shows the number of stage, the current stage, and current progress within the stage.

See more information in the documentation: https://mariadb.com/kb/en/progress-reporting/

If you find the explanation not satisfactory, please comment here to re-open the report.

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