Bug #80191 Long running select count queries in state "optimizing" for minutes
Submitted: 28 Jan 2016 16:42 Modified: 25 May 2018 5:59
Reporter: Mark Callaghan Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[28 Jan 2016 16:42] Mark Callaghan
Description:
I see this in 5.7.10 but not in 5.6.26. The query with 5.7 is in state "optimizing" for 10+ minutes. The query with 5.6 is in state "Sending Data".

The docs for state "optimizing" state the following which can't still be true after 10 minutes. By this point the query is running.
    "The server is performing initial optimizations for a query."
https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

SHOW PROCESSLIST for 5.7.10
*************************** 3. row ***************************
     Id: 161207
   User: root
   Host: localhost
     db: linkdb
Command: Query
   Time: 599
  State: optimizing
   Info: select count(*) from counttable

SHOW PROCESSLIST for 5.6.26
*************************** 1. row ***************************
     Id: 161981
   User: root
   Host: localhost
     db: linkdb
Command: Query
   Time: 654
  State: Sending data
   Info: select count(*) from counttable

How to repeat:
Run a long running SELECT COUNT(*) statement and look at SHOW PROCESSLIST output. In this case I used a linkbench setup with maxid1=1B and billions of rows in the database.

Suggested fix:
I prefer you fix the SHOW PROCESSLIST output. If that isn't possible then improve the docs.
[29 Jan 2016 13:21] MySQL Verification Team
Hello Mark,

Thank you for the report.
Observed this with 5.7.10.

Thanks,
Umesh
[29 Jan 2016 13:26] MySQL Verification Team
// 5.7.10

bin/mysql_install_db --insecure --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.10 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.10/80191 -v
bin/mysqld --no-defaults --basedir=/export/umesh/server/binaries/mysql-advanced-5.7.10 --datadir=/export/umesh/server/binaries/mysql-advanced-5.7.10/80191 --core-file --socket=/tmp/mysql_ushastry.sock  --port=15000 --log-error=/export/umesh/server/binaries/mysql-advanced-5.7.10/80191/log.err 2>&1 &

1. Create the following table
create table t1 (
 id int primary key,
 value int,
 value2 varchar(255)
)engine=innodb;

2. Generate 2500000000 rows by the following perl script.

for(my $i=1; $i <= 2500000000; $i++) {
  my $txt = 'x' x 5;
  print "$i,$i,$txt\n";
}

3. Load the data into table
load data local infile '/export/umesh/server/binaries/mysql-advanced-5.7.10/bug.dmp' into table t1 fields terminated by ',';

-- dump file size
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: ls -lh bug.dmp
-rw-r--r-- 1 umshastr common 56G Jan 29 08:46 bug.dmp

-- Session 1

mysql> select count(*) from t1;
+------------+
| count(*)   |
+------------+
| 2000000000 |
+------------+
1 row in set (10 min 7.84 sec)

-- Session 2

mysql> show processlist;
+----+------+-----------+------+---------+------+------------+-------------------------+
| Id | User | Host      | db   | Command | Time | State      | Info                    |
+----+------+-----------+------+---------+------+------------+-------------------------+
|  2 | root | localhost | test | Query   |    2 | optimizing | select count(*) from t1 |
|  5 | root | localhost | NULL | Query   |    0 | starting   | show processlist        |
+----+------+-----------+------+---------+------+------------+-------------------------+
2 rows in set (0.00 sec)

.
.
.

mysql> show processlist;
+----+------+-----------+------+---------+------+------------+-------------------------+
| Id | User | Host      | db   | Command | Time | State      | Info                    |
+----+------+-----------+------+---------+------+------------+-------------------------+
|  2 | root | localhost | test | Query   |  567 | optimizing | select count(*) from t1 |
|  5 | root | localhost | NULL | Query   |    0 | starting   | show processlist        |
+----+------+-----------+------+---------+------+------------+-------------------------+
2 rows in set (0.00 sec)

-- table file size

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.10: ls -lh 80191/test/t1*
-rw-r----- 1 umshastr common 8.5K Jan 29 13:08 80191/test/t1.frm
-rw-r----- 1 umshastr common  84G Jan 29 13:30 80191/test/t1.ibd
[25 May 2018 2:00] Bin Hong
The same problem I observed with MySQL 5.7.16, is there any conclusion now?
[25 May 2018 5:58] Sreeharsha Ramanavarapu
Posted by developer:
 
This is a duplicate of "Bug #23046302: COUNT(*) MUCH SLOWER ON 5.7 THAN 5.6" and was fixed in 5.7.18.
[25 May 2018 5:59] Sreeharsha Ramanavarapu
This is a duplicate of Bug#80580 and is fixed in 5.7.18