Bug #74283 Non-covering secondary index scans degrade more in 5.7 than 5.6

• Submitted: 8 Oct 2014 17:00
• Modified: 29 May 2017 17:25
• Reporter: Mark Callaghan
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S5 (Performance)
• Version: 5.7.5
• OS: Any
• CPU Architecture: Any

[8 Oct 2014 17:00] Mark Callaghan

Description:
At high concurrency non-covering secondary index scan response times degrade much more in 5.7 than in 5.6 for InnoDB. High concurrency is 32 clients via mysqlslap on a server with 40 hyperthread cores. All clients scan the same 1000 rows.

Test results are at http://smalldatum.blogspot.com/2014/10/details-on-range-scan-performance.html

How to repeat:
See http://bugs.mysql.com/bug.php?id=74280 for the mysqlslap reproduction case. That also has the 3 query types (PK index scan, non-covering secondary index scan, covering secondary index scan). Run this for 5.6.21 and 5.7.5 at 1, 4 and 32 threads with mysqlslap and mysqld on the same host. Compare the 32 and 1 thread results and note that the non-covering secondary index scan degrades more in 5.7 at 32 threads.

Suggested fix:
Wish I knew

[10 Oct 2014 12:14] MySQL Verification Team

Hello Mark,

Thank you for the bug report and test case.
Confirmed that the non-covering secondary index scan degrades more in 5.7.5 at 32 threads than 5.6.21.

I ran mysqlslap like this and stats are in following table:

X=1, 4, and 32

# non-covering secondary index scan
mysqlslap --concurrency=$X --create-schema=test1 --number-of-queries=100000 --create=iddl.sql --query="select k from foo FORCE INDEX(`xj`) where j >= 100 order by j limit 1000"

###################################
         MySQL Version 5.7.5
###################################

-------------------------------------------------
Concurrency | non-covering secondary index scan |
-------------------------------------------------
1           | 123.684  sec                      |
4           | 70.811   sec                      | 
32          | 104.539  sec                      | 
-------------------------------------------------

###################################
         MySQL Version 5.6.21
###################################

-------------------------------------------------
Concurrency | non-covering secondary index scan |
-------------------------------------------------
1           | 100.413  sec                      |
4           | 70.666   sec                      | 
32          | 63.710   sec                      |
-------------------------------------------------
Average number of seconds to run all queries
Minimum number of seconds to run all queries
Maximum number of seconds to run all queries
-------------------------------------------------

Thanks,
Umesh

[29 May 2017 17:25] Mark Callaghan

Is there an update for this?