MySQL Bugs: #101707: Slow Queries

Bug #101707	Slow Queries
Submitted:	21 Nov 2020 21:30	Modified:	23 Nov 2020 22:40
Reporter:	Bogdan K	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	8.0.22	OS:	Ubuntu (20.04)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
I'm in the process of upgrading from 5.7 to 8.0.22 -- and when executing this particular query -- there is a significant performance hit. 

5.7 -- ~80 milliseconds
8.0 -- ~12 seconds

Query
https://i.postimg.cc/jdpck9MK/Screen-Shot-2020-11-21-at-4-17-56-PM.png

How to repeat:
You can take a look at the query that was done here below

https://i.postimg.cc/jdpck9MK/Screen-Shot-2020-11-21-at-4-17-56-PM.png

Thank you

Thank you for the bug report. Could you please provide a SQL file script with create table, insert data and query. Thanks in advance.

Hi Bogdan,

Was the execution time measured on both servers when they are "cold" after (re)start or the time is measured when the both servers are "hot" running for few days so all the buffers and caches are populated, or you measured hot 5.7 and then cold 8.0 just after upgrade?

All best
Bogdan Kecman

Bogdan,

I run both 5.7 on docker, and 8.0 on docker locally (8.0 is not yet in production)

Both servers were in the cold state as you mention, I bright them up then execute these queries.

I also am attempting to diagnose the performance, so I am interacting with the 8.0 server fairly often and execute a variety of queries -- I get a range of query times, the longest I've been for that particular query was 42seconds and my CPU is like 120% with fans blasting

Also just for my own research, 
I tried Percona DB 8.0.21-12 - resulting in same 12second-ish query
MariaDB surprisingly performed the best at 60-80ms for that query

Hi Bogdan,

There is no expected reason for this amount of performance drop, quite contrary, so we'd really appreciate if you can get us the reproducible test case (as my colleague asked, a full SQL that will create tables, load them with data and the query to execute on top of it) so we can debug this locally.

Thanks
Bogdan

Well, I got to the bottom of it as trying to provide a dump to you guys -- of course I'm not sure why this is not the case in production or MariaDB 

But the `shift_id` column on `shift_requests` table was not indexed.

:cries-in-corner:

Hi Bogdan,

No worries, it happens to the best of us.

all best
Bogdan