Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5741

Different progress in "SHOW PROCESSLIST" and I_S.processlist table

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Not a Bug
        • Affects Version/s: 5.5.35
        • Fix Version/s: None
        • Component/s: None
        • 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]#

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                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.

                Show
                elenst Elena Stepanova added a comment - 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.

                  People

                  • Assignee:
                    Unassigned
                    Reporter:
                    nilnandan Nilnandan Joshi
                  • Votes:
                    1 Vote for this issue
                    Watchers:
                    4 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: