Description

8.0.33 has about 18% performance regression for queries doing JOINs:
8.0.28

Query_ID

Duration

Query

22

0.73221925

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

23

0.64774975

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

24

0.67075575

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

25

0.67332025

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

26

0.66537300

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

27

0.67452900

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

28

0.72930675

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

29

0.71833350

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

30

0.65857525

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

31

0.65274575

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

32

0.69768975

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

33

0.69682700

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

34

0.72477825

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

35

0.64526125

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

 

 

Variable_name

Value

Handler_read_key

105218

Handler_read_next

999999

Handler_read_rnd_next

105217

 

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

1

SIMPLE

e

NULL

ALL

NULL

NULL

NULL

NULL

299069

100.00

NULL

1

SIMPLE

s

NULL

ref

PRIMARY

PRIMARY

4

employees.e.emp_no

9

100.00

NULL

 

8.0.33

 
 

Query_ID

Duration

Query

21

0.85898000

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

22

0.79197600

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

23

0.83340700

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

24

0.77812325

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

25

0.77719400

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

26

0.81970475

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

27

0.76522925

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

28

0.79419200

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

29

0.80916500

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

30

0.82678150

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

31

0.86895875

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

32

0.83041300

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

33

0.80798750

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

34

0.81469850

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

35

0.79329575

SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000

 
 

Variable_name

Value

Handler_read_key

105218

Handler_read_next

999999

Handler_read_rnd_next

105217

 
 

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

1

SIMPLE

e

NULL

ALL

NULL

NULL

NULL

NULL

292025

100.00

NULL

1

SIMPLE

s

NULL

ref

PRIMARY

PRIMARY

4

employees.e.emp_no

9

100.00

NULL

So, same explain, same number of rows read but 8.0.28 averaged 0.6839712833 while 8.0.33 average was 0.8113404167, so 18% more latency.

 

To repeat:

 

 

And then run this on each instance:

Environment

None

AFFECTED CS IDs

CS0036754

Attachments

11

Activity

Show:

Satya Bodapati November 27, 2023 at 8:31 PM

I see. Even if AHI is disabled, since the hashing algorithm for buffer pool pages is costly, it causes regression. We will wait and see the 8.0.36 fix. If non-AHI paths are not fixed, I will create a patch to bring the simple buf pool hashing algorithm back.

 

Mark Callaghan November 27, 2023 at 8:14 PM

Satya - I am unable to tag you. I have been disabling AHI on my tests for many years.

Satya Bodapati November 22, 2023 at 6:09 PM

I shared this information on the upstream bug report, and they replied.

 

I asked a follow-up question (if the fix fixes the non-AHI path) but didn't receive any response. We have to wait until 8.0.36 and verify if the fix solves the issue.

Satya Bodapati November 20, 2023 at 6:11 PM

BTW, on rec_init_offsets and the comp_ordinary variants, there might be some impact on overall time. I did see some improvement by inlining these functions in 8.0.29 but not so much on 8.0.33

I do believe after instant alters, the reads might take an impact to do translations of logical row to physical row. This has to be tested out.

Satya Bodapati November 20, 2023 at 5:58 PM

Reverting the hashing function commit was super painful. Although I reverted here to prove it is causing regression in single-threaded workloads, there might be a genuine reason why upstream implemented those changes. Is there data to prove that it improves multi-threaded workloads? I don't know.

We have to do regular sysbench testing and see and how this commit affects the performance in multi-threaded workloads

After all the hard work, I cannot conclude and say, hey "lets revert and get back to pre 8.0.30 behaviour". It was really really hard to revert and maintaining it will be nightmare.

We will present our data to upstream and wait for their response.

 

Pinned fields
Click on the next to a field label to start pinning.
Details

Assignee

Satya Bodapati

Reporter

Marcos Albe

Regression Issue

Yes

Upstream Bug URL

Needs QA

Yes

Affects versions

Priority

Smart Checklist
Created June 23, 2023 at 1:23 AM
Updated November 10, 2024 at 2:45 PM
Loading...