Forum Programmation.SQL Lenteur après migration mysql 5 vers 8

Posté par Le Pnume le 16 juin 2020 à 10:04. Licence CC By‑SA.

Étiquettes : aucune

juin

2020

Bonjour,

Je viens de migrer une base mysql 5 vers mysql8 et depuis cette migration j'ai un facteur 10 dans le temps de réponse de certaines requêtes.

par ex soit la table suivante :

CREATE TABLE `val_hor` (
  `date_time` datetime NOT NULL,
  `id_station` varchar(10) NOT NULL,
  `polluant` varchar(10) NOT NULL,
  `value` double DEFAULT NULL,
  PRIMARY KEY (`date_time`,`id_station`,`polluant`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Cette table contient plus de 19 millions de lignes

la requête suivante

SELECT COUNT(*) AS ct_date FROM `val_hor` 
       WHERE `id_station` = '14031'  
       AND date_time LIKE '2020-06-09%' 
       AND `polluant` = 'T' AND `value`!='-999';

prenait environs 4 secondes sur l'ancien serveur et maintenant plus de 40. En décomposant la requête, il semble que ce soit la partie date_time LIKE '2020-06-09%' qui entraine le ralentissement.

j'ai essayé de remplacer le like par un between mais c'est tjs aussi long.

Il y a 2 fois plus de RAM sur le nouveau serveur que sur l'ancien

le fichier my.cnf de l'ancienne base contenait les options suivantes que j'ai reporté sur la nouvelle

join_buffer_size = 512M
sort_buffer_size = 2M
read_rnd_buffer_size = 50M

Je suis preneur de toutes pistes permettant de résoudre cela

# Peut-être une piste

Posté par _kaos_ le 16 juin 2020 à 10:20. Évalué à 4.

Salut,

Voir ici. L'algo à changé. Extrait :

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. (Prior to MySQL 8.0.4, MySQL used Henry Spencer's implementation of regular expressions, which operates in byte-wise fashion and is not multibyte safe. For information about ways in which applications that use regular expressions may be affected by the implementation change, see Regular Expression Compatibility Considerations.)

Je ne sais pas si ça peut jouer, mais si ça peut être une piste…

Matricule 23415
- [^] # Re: Peut-être une piste
  
  Posté par _kaos_ le 16 juin 2020 à 10:41. Évalué à 3. Dernière modification le 16 juin 2020 à 10:41.
  
  Re salut,
  
  Désolé, je crois que j'ai foiré sur le lien (j'étais probablement en train de lire la suite).
  
  Donc, peut-être ici.
  
  Matricule 23415
# et pourquoi pas

Posté par Anonyme le 16 juin 2020 à 15:19. Évalué à 2.

doubler les valeurs de memoires dans my.conf ?

join_buffer_size = 1024M
sort_buffer_size = 4M
read_rnd_buffer_size = 100M

note : je ne suis pas spécialiste en sgbd, c'est juste une idée
# pas d'index?

Posté par wismerhill le 16 juin 2020 à 19:51. Évalué à 4.

https://use-the-index-luke.com/

S'il n'y a pas d'index, ce sera forcément lent.

Ensuite, il ne faut pas utiliser des expressions rationnelles pour comparer des dates, même s'il y avait un index sur la colonne date_time, il ne serait pas utilisé parce que tu lui demande de convertir le timestamp en chaine de caractère.
Il y a des fonctions pour extraire une partie d'une date, ou convertir un timestamp en date.
Mais pour profiter d'un éventuel index, le mieux est d'utiliser l'opérateur between (ou équivalent).
- [^] # Re: pas d'index?
  
  Posté par Le Pnume le 16 juin 2020 à 21:44. Évalué à 2.
  
  merci pour vos réponses.
  
  En l'occurrence, je dois gérer l'existant.j'ai fait des tests en ajoutant un index et je n'ai pas amélioré les performances.
  La table d'origine sous mysql 5, n'avait pas plus d'index mais la requete allait 10 fois plus vite, je ne suis pas sur d'avoir fait de tests avec index et between, je m'y colle demain.
  - [^] # Re: pas d'index?
    
    Posté par Le Pnume le 17 juin 2020 à 09:33. Évalué à 2.
    Pas d'avancée, j'ai ajouté un index simple sur le champs date_time, et fait mon select grâce à un CAST
```
SELECT COUNT(`date_time`) AS DATE FROM `val_hor` WHERE (`id_station` = '14031') AND DATE(date_time)= '2020-06-09'  AND `polluant` = 'T' AND `value`!='-999'
```
    Le temps de réponse de la requête est tjs de l'ordre de 40s
    - [^] # Re: pas d'index?
      
      Posté par claudex le 17 juin 2020 à 09:39. Évalué à 6.
      
      Que donne un EXPLAIN sur ta requête ? (et si tu as encore l'ancien serveur pour comparer, c'est encore mieux).
      
      « Rappelez-vous toujours que si la Gestapo avait les moyens de vous faire parler, les politiciens ont, eux, les moyens de vous faire taire. » Coluche
      - [^] # Re: pas d'index?
        
        Posté par Le Pnume le 17 juin 2020 à 10:21. Évalué à 2.
        
        Bonne idée, je n'y avais pas pensé. Comme indiqué plus bas, j'ai trouvé un contournement mais je ferais un test avec explain sur les 2 serveurs dés que j'aurais un peu de temps.
    - [^] # Re: pas d'index?
      
      Posté par wismerhill le 17 juin 2020 à 17:08. Évalué à 2.
      
      Forcément, si tu transforme la valeur de la colonne, l'index ne sera pas utilisé, c'est pour ça que j'indiquais d'utiliser between.
# Contournement

Posté par Le Pnume le 17 juin 2020 à 10:18. Évalué à 4.
Merci à tous ceux qui m'ont répondu.
j'ai trouvé une version de la requête qui semble donner satisfaction :
```
SELECT COUNT(*) AS DATE FROM `val_hor` WHERE (`id_station` = '14031') AND date_time >= '2020-06-09' AND date_time <  '2020-06-09' + INTERVAL 1 DAY  AND `polluant` = 'T' AND `value`!='-999'
```
avec cette requête la réponse est quasi immédiate.

ça n'explique pas les raisons de la perte de performance mais au moins ça permet d'avancer
- [^] # Re: Contournement
  
  Posté par _kaos_ le 17 juin 2020 à 10:24. Évalué à 2.
  
  Salut,
  
  Comme je suis pas DBA, je ne peux pas expliquer pourquoi du coup ça marche mieux, mais merci d'avoir partagé une solution de contournement.
  
  J'aime bien quand un contournement est trouvé, ça permet de se poser et réfléchir tranquillement ensuite pour du plus pérenne :)
  
  Matricule 23415
- [^] # Re: Contournement
  
  Posté par wismerhill le 17 juin 2020 à 17:13. Évalué à 4.
  
  Oui, parce que là l'index sur date_time peut être utilisé, c'est à peu près équivalent au between (je ne sais plus s'il correspond à des <= >= ou à des < >) que je recommandais plus haut.
  Tu devrais le voir dans un explain.
  
  Ce n'est pas étonnant que le query plan soit très différent quand on fait un changement de version majeure, surtout sur une requête qui n'était pas du tout optimisée (4s, c'était déjà horriblement lent pour une requête qui ne devrait ressortir que peu de résultats).
# Curiosité

Posté par DontShootMe le 17 juin 2020 à 10:49. Évalué à 1.
Je ne suis pas expert en la matière, mais te serait-il possible de faire un test avec cette requête :
```
SELECT COUNT(`date_time`) AS DATE FROM `val_hor` WHERE (`id_station` = '14031') AND DATE(date_time) = DATE('2020-06-09')  AND `polluant` = 'T' AND `value`!='-999'
```
Le but ici est de "caster" les deux parties de l'égalité en date et laisser MySQL comparer deux choses de même type :
```
DATE(date_time) = DATE('2020-06-09')

; au lieu de 

DATE(date_time) = '2020-06-09'
```
# Normalisation

Posté par champomy62 le 18 juin 2020 à 04:36. Évalué à 2.

Je connais pas MySQL mais disons:

Si tu as un type datetime, utilise les operateurs liés aux dates. La requête que tu montrais traité surement le prédicat comme une chaine de caractère!

Ensuite tu as bien un index sur ta clé primaire automatiquement mais pas sur value. Cela pourrait etre interessant d'en rajouter un et voir meme de faire une corrélation entre les valeurs ?

D'ailleurs, je trouve la clé primaire un peu mal définie. en fait, a en déduire la définition de la table, tu te retrouveras avec beaucoup de duplications.
Tu pourrais surement créer une table polluant et stations et faire des belles jointures :)

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.