Description
Environment
AFFECTED CS IDs
Attachments
Activity
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.
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: