Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.3.39, 10.4.31, 10.5.22, 10.6.15
-
None
-
None
Description
Hello,
I recently migrated a client environment from Mysql 5.6 to Mariad 10.6. Since then, almost every queries are slow on the database.
Here is an example of a query that is taking 22 minutes now to execute, when on Mysql 5.6 it took around 25 seconds or less.
+------+-------------+-------+--------+---------------------------------------+------------------------------+---------+--------------------------------------+------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+--------+---------------------------------------+------------------------------+---------+--------------------------------------+------+---------------------------------+
|
| 1 | SIMPLE | fd | ALL | PRIMARY | NULL | NULL | NULL | 95 | Using temporary; Using filesort |
|
| 1 | SIMPLE | a | ref | PRIMARY,ID_2 | ID_2 | 5 | X.fd.ID_2 | 19 | Using index |
|
| 1 | SIMPLE | cp | ref | ID_1,ID_2,ID_4 | ID_4 | 5 | X.a.4 | 453 | |
|
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | X.cp.2 | 1 | |
|
| 1 | SIMPLE | ptyp | eq_ref | PRIMARY | PRIMARY | 4 | X.p.ID_2 | 1 | Using where |
|
| 1 | SIMPLE | tbr | eq_ref | PRIMARY | PRIMARY | 4 | X.p.ID_TIMBRE_CPMA | 1 | Using where |
|
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | X.cp.ID_3 | 1 | Using where |
|
| 1 | SIMPLE | ptrad | ref | ID_PRODUIT | ID_PRODUIT | 4 | X.ptyp.ID_3 | 2 | Using where |
|
| 1 | SIMPLE | adh | eq_ref | PRIMARY | PRIMARY | 4 | X.c.ID_ADHERENT | 1 | Using where |
|
+------+-------------+-------+--------+---------------------------------------+------------------------------+---------+--------------------------------------+------+---------------------------------+
|
 |
9 rows in set (0,001 sec)
|
Note that i modified the the names of Keys, database and table to blur client's information. But the point is to show that there are no full table scan being done.
And still this query is taking 22 minutes to execute on mariadb 10.6.
My server has 32 Gb or RAM.
My Innodb_Buffer_Pool_Size is 20Gb
I tried adjusting by increasing the tmp_table_size , join_buffer_size (As the query does a lot of LEFT and INNER join).
I Compared the GLOBAL variables on both platform and everything seems to be pretty much the same.
I tried comparing the optimzer_switch and adjusted it accordingly.
The Query has the same Execution plan on both platform.
My final test to try debug was :
Desactivate : innodb_stats_persistent
Increase :innodb_stats_transient_sample_pages from 8 to 64.
Downgrade from Mariadb 10.6 > 10.5 > 10.4 > 10.3
All versions had the same problems.
Is there anything i can try?
