Description:
Hi Team,
Bug #111538 is not completely fixed yet.
We are still seeing performance regression on the latest version of MySQL 8.0.40
Please find the complete profile below:
MySQL 8.0.28:
mysql [localhost:8028] {msandbox} (employees) > SHOW PROFILES;
+----------+------------+--------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------+
| 2 | 0.68563850 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 3 | 0.68456600 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 4 | 0.67942350 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 5 | 0.67427150 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 6 | 0.68712175 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 7 | 0.68856650 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 8 | 0.68886150 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 9 | 0.69101400 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 10 | 0.70157675 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 11 | 0.68774325 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 12 | 0.69398750 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 13 | 0.69617350 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 14 | 0.69295575 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 15 | 0.69256725 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 16 | 0.69788525 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
+----------+------------+--------------------------------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)
MySQL 8.0.37:
mysql [localhost:8037] {msandbox} (employees) > SHOW PROFILES;
+----------+------------+--------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------+
| 2 | 0.83333100 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 3 | 0.83146400 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 4 | 0.83129750 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 5 | 0.83372525 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 6 | 0.83278850 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 7 | 0.83186800 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 8 | 0.82902700 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 9 | 0.83302525 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 10 | 0.82331625 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 11 | 0.82405275 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 12 | 0.81407650 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 13 | 0.83072500 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 14 | 0.82239225 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 15 | 0.82235925 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 16 | 0.83509675 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
+----------+------------+--------------------------------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)
MySQL 8.0.40:
mysql [localhost:8040] {msandbox} (employees) > SHOW PROFILES;
+----------+------------+--------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------------------------------------+
| 5 | 0.83390675 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 6 | 0.81574000 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 7 | 0.81755625 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 8 | 0.83904800 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 9 | 0.81995825 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 10 | 0.81681475 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 11 | 0.81051700 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 12 | 0.81284600 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 13 | 0.81515150 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 14 | 0.82281475 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 15 | 0.83038800 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 16 | 0.81575225 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 17 | 0.82343350 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 18 | 0.82049550 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
| 19 | 0.81554050 | SELECT * FROM employees AS e LEFT JOIN salaries AS s ON e.emp_no = s.emp_no LIMIT 1000000 |
+----------+------------+--------------------------------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)
How to repeat:
Please check Bug #111538 to repeat the issue.
