| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.10 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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
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.