Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.11.6
-
Debian 11 with docker container
Description
Hi,
I'm experiencing an issue with my database queries.
When I initialize the database and use it for a few weeks, the query responses are fast, around ~0.10 seconds. However, after a while, the queries suddenly become very slow, taking more than 30 seconds to execute.
By enabling the slow query log, I noticed that when the queries are fast, they scan approximately 50,000 rows. But when the queries become slow, the number of rows analyzed suddenly increases to 8 million.
To resolve this issue, I perform the following steps:
Create a dump of the database.
Move the /var/lib/mysql directory.
Create new instance.
Restore the database from the dump.
After doing this, the queries return to their original fast performance.
Thank you in advance for your help.
This the one of queries that have this problem :
SELECT m0_.id AS id_0 |
FROM materiel_tarification m1_ |
LEFT JOIN materiel m0_ ON m1_.materiel_id = m0_.id |
AND m0_.discr IN ('materiel') |
INNER JOIN materiel_base_categorie_base m3_ ON m0_.id = m3_.materiel_base_id |
INNER JOIN eav_categorie e2_ ON e2_.id = m3_.categorie_base_id |
AND e2_.discr IN ('categorie') |
LEFT JOIN materiel m4_ ON m0_.id = m4_.materiel_parent_variant_id |
AND m4_.discr IN ('materiel') |
LEFT JOIN eav_valeur_attribut_materiel e5_ ON m0_.id = e5_.materiel_id |
AND e5_.discr IN ('valeurAttribut') |
LEFT JOIN eav_attribut e6_ ON e5_.attribut_id = e6_.id |
AND e6_.discr IN ('attribut') |
LEFT JOIN configuration_taux_tva c7_ ON e2_.tva_courante_id = c7_.id |
AND c7_.discr IN ('tva') |
LEFT JOIN materiel_marque m8_ ON m0_.marque = m8_.id |
AND m8_.discr IN ('marque') |
LEFT JOIN canal_de_vente c9_ ON m1_.canal_de_vente_id = c9_.id |
AND c9_.discr IN ('canal_de_vente') |
LEFT JOIN materiel m10_ ON m0_.materiel_parent_variant_id = m10_.id |
AND m10_.discr IN ('materiel') |
LEFT JOIN campagne_materiel c12_ ON m0_.id = c12_.materiel_id |
LEFT JOIN campagne c11_ ON c11_.id = c12_.campagne_id |
AND c11_.discr IN ('campagne') |
LEFT JOIN modele_materiel m14_ ON m0_.id = m14_.materiel_id |
LEFT JOIN modele m13_ ON m13_.id = m14_.modele_id |
LEFT JOIN machine m15_ ON m13_.machine_id = m15_.id |
LEFT JOIN materiel_marque m16_ ON m13_.marque_id = m16_.id |
AND m16_.discr IN ('marque') |
WHERE (e2_.is_active = 1 |
AND m0_.flag_actif = 1 |
AND m0_.materiel_parent_variant_id IS NULL |
AND c9_.libelle LIKE 'Web' |
AND (e2_.id = 1 |
OR e2_.id = 4 |
OR e2_.id = 6 |
OR e2_.id = 7 |
OR e2_.id = 8 |
OR e2_.id = 9 |
OR e2_.id = 10 |
OR e2_.id = 11 |
OR e2_.id = 12 |
OR e2_.id = 117 |
OR e2_.id = 130) |
AND ( |
(SELECT MIN(m17_.prix_vente_ht) AS sclr_1 |
FROM materiel_tarification m17_ |
LEFT JOIN materiel m18_ ON m17_.materiel_id = m18_.id |
AND m18_.discr IN ('materiel') |
WHERE (m18_.materiel_parent_variant_id = m0_.id |
AND m17_.prix_vente_ht <> 0) |
AND m17_.discr IN ('tarification')) > 0 |
AND |
(SELECT MIN(m19_.prix_vente_ht) AS sclr_2 |
FROM materiel_tarification m19_ |
LEFT JOIN materiel m20_ ON m19_.materiel_id = m20_.id |
AND m20_.discr IN ('materiel') |
LEFT JOIN canal_de_vente c21_ ON m19_.canal_de_vente_id = c21_.id |
AND c21_.discr IN ('canal_de_vente') |
WHERE (m20_.materiel_parent_variant_id = m0_.id |
AND m19_.prix_vente_ht <> 0) |
AND m19_.discr IN ('tarification')) <= (1500 / (1 + c7_.tva)) |
OR (m1_.prix_vente_ht <= (1500 / (1 + c7_.tva)) |
AND m1_.prix_vente_ht <> 0 |
AND m1_.prix_vente_ht IS NOT NULL))) |
AND m1_.discr IN ('tarification') |
GROUP BY m0_.id |
ORDER BY m0_.flag_indisponible ASC, |
m0_.qteStock DESC |
LIMIT 12;
|
Best regards,
WareTeamFlo
Attachments
Activity
Yes, the point was to see if recreating a table helps. Because your process of dump everything and reload the whole datadir is rather expensive. OPTIMIZE recreates the table, as we can see. But does it help with your performance problem?
Hi
Recreate table has no effect and not resolve performance on few months.
After import again whole database, the query going fast > 0,1sec. But after a while, approx 1 month, the problem appear again.
Additionnal info:
While the query are slow, all another query are blocked until the current slowquerry finished. It seem that mariadb wait until the slow query finish.
W've see this problem because we have a front website and a backoffice.
Did you try to ANALYZE (or OPTIMIZE or ALTER...FORCE, etc) for all tables that are used in that slow query?
Are they all InnoDB tables?
Can you do ANALYZE FORMAT=JSON SELECT ... <your query>... both when the query is slow and fast and attach the output?
Hi,
The ALTER TABLE ... FORCE command doesn't seem to have any effect.
When I run OPTIMIZE TABLE, I get the following output:
| database_name.configuration_taux_tva | optimize | status | OK |How can I achieve the same result?
Would a simple export and import of the table work?
Thanks!