MySQL Bugs: #74342: InnoDB disk reads at 1 thread much slower in 5.7.5

Bug #74342	InnoDB disk reads at 1 thread much slower in 5.7.5
Submitted:	12 Oct 2014 18:02	Modified:	23 Apr 2018 7:29
Reporter:	Mark Callaghan	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.5, 5.7.14	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
See http://smalldatum.blogspot.com/2014/10/page-read-performance-mysql-57-vs.html for more details. Peak QPS for an IO-bound database is 12% to 15% worse at 1 thread with 5.7.5 than with 5.6.21 assuming "fast storage" (OS filesystem cache).

Tests were done via SELECT and HANDLER statement. Regression was worse for HANDLER, so I assume the optimizer is less likely to be the problem. Both still go through the parser. But I also suspect InnoDB changes.

How to repeat:
see the blog post

Suggested fix:
Look at new code, run Linux perf utility

Hello Mark,

Thank you for the report and detailed information to reproduce this issue.

Regards,
Umesh

We tried to track down commits between 5.7.4 and 5.7.5 but we could not nail down
the offending commit.  We are able to get regression of around -4% between
5.7.4 and 5.7.5 and actual tps diff is around 400. Investigating this small 400 tps drop in single thread is difficult task. We used our 40 vcpu machine and
getting better tps difference than reported in the bug.
In the bug Marc reported the difference of around 1300 tps. If we get
similar difference in tps on our h/w then it will be easy to reproduce and
investigate the problem.
In nutshell, we need to know exact h/w details to be be able to
reproduce 1k tps difference, then only we can investigate the offending commits.

The Machine and OS details which we used are as follows

OS             LINUX
Memory         256GB
CPU            40 x intel(r) xeon(r) cpu e5-2690 v2 @ 3.00ghz
               2 Socket X 10 cores X 2 Threads
Arch           x86_64    

Version       oracle linux server release 7.3

CPU deatils
CPU(s):                40
On-line CPU(s) list:   0-39
Thread(s) per core:    2
Core(s) per socket:    10
Socket(s):             2
NUMA node(s):          2
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 62
Model name:            Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz
Stepping:              4
CPU MHz:               1200.000
BogoMIPS:              5991.69
Virtualization:        VT-x
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              25600K
NUMA node0 CPU(s):     0-9,20-29
NUMA node1 CPU(s):     10-19,30-39

This was a long time ago and I won't be able to answer. I have been repeating tests and will have results to share in the next month or two.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".