Pb lenteur dans mes fonctions stockées [Résolu]

[TeepH]

Bonjour à tous,

Je vous écris à cause d’un problème de lenteur qui me fait tourner en bourrique.
Je vous plante le décor. Je dispose de deux serveurs, un de dév et un de prod. Ces deux serveurs tournent tous les deux sur des linux ubuntu 7.04 (noyau 2.6.20 d'un coté et 2.6.18 de l'autre). Sur ces machines sont installés les mêmes mysql (5.0.38-Ubuntu_0ubuntu1-log). Ces mysql ont des configurations strictement identiques (excepté le langage french d’un coté et english de l’autre). J’ai fait un dump de la machine de dév vers la machine de prod et tout semblait fonctionner pour le mieux. Seulement je me suis rendu compte que certaines requêtes étaient étrangement longues sur la machine de prod par rapport à la machine de dév (qui est légèrement moins puissante). Après analyse je me suis rendu compte que ces lenteurs étaient dues à l’utilisation des mes fonctions stockées. Je les ai donc réanalysée. J’ai repris chacun des curseurs présents à l’intérieur et ai contrôlé que tous les index étaient bien présents pour que les SQL tournent au plus vite.
Tout semble ok mais malgré tout mes fonctions continuent de plomber mes perfs. L’impact est flagrant. C’est de l’ordre de qq centièmes d’un coté à 10 secondes de l’autre.

Est-ce que qqun aurait une idée ? Je suis preneur de toutes informations me permettant d’avancer.

Merci d’avance,

T.

[pop_up]

es tu sur que les indexs sont bien utilisés ? en faisant un explain des procedures penalisantes, tu peux verifier.

[TeepH]

Je ne suis pas sûr qu’on puisse faire un explain sur une fonction.
Par contre ce que je peux te garantir c’est que les index existent bien.

Un exemple flagrant, cette fonction :

DROP FUNCTION IF EXISTS `fct_nomcli` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `fct_nomcli`(p_sigtie varchar(16)) RETURNS varchar(50) CHARSET latin1
DETERMINISTIC
BEGIN
declare v_nomcli varchar(50);
select nomtie into v_nomcli from w_tie force index (PRIMARY) where sigtie=p_sigtie;
return v_nomcli;
END $$

DELIMITER ;

Nous l’avons compilé avec et sans le « force index (PRIMARY) » (trouvé sur une page web).

Si nous appelons cette requête qui ramène une 40ene de lignes, nous arrivons à obtenir le résultat en qq centièmes sur la machine de dév alors qu’il faut 19s pour la machine de prod.

select fct_nomcli(sigtie) from w_tie where sigtie like '505910%';

Nous utilisons pourtant la clé primaire de la table !
Si maintenant nous exécutons la requête suivante (qui ramène le même résultat sans utiliser de fonction) :
select nomtie from w_tie where sigtie like '505910%';
nous obtenons le résultat en qq millième quelque soit la machine.

Tout me laisse penser que le mysql de la machine de prod n'utilise pas les indexs dans les fonctions.

:°-(

[pop_up]

essaye de faire ceci sur ton serveur de prod et de dev:

Code :

1
2
 
EXPLAIN  SELECT nomtie INTO v_nomcli FROM w_tie WHERE sigtie=p_sigtie;

verifie bien que tu as un index sur la colonne sigtie et regarde le delta entre les deux explain.

[seb_59]

Bonjour !

J'interviens pour remplacer mon pote qui vient de nous quitter pour rentrer à la maison !

j'ai lancé ça sur les 2 serveurs :

EXPLAIN SELECT nomtie FROM w_tie WHERE sigtie='50591010';

Des 2 cotés, j'obtiens la même réponse, à savoir :

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | w_tie | const | PRIMARY | PRIMARY | 14 | const | 1 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

C'est vraiment désespérant...

[pop_up]

essaye avec le like '505910%' mais je vois pas trop pourquoi ça met plus de temps sur le serveur de prod.

normalement les index sont declenchés avec un like qui n'a pas le caractere joker au debut donc dans ce cas ça doit declencher.

Si les explain sont exactement les memes , que tu utilises les memes scripts de creation de table, d'index et de contrainte alors les temps devraient etre identique.

Si c'est pas le cas ça ne vient peut etre pas de mysql mais de l'utilisation memoire ou autre chose. mais la encore si la config est identique c'est bizarre

si quelqu'un a des suggestion, je serai curieux de comprendre tout ça.

courage



PS: en y pensant ça vient peut etre du cache.

[seb_59]

re !

Très étrange :
quand j'examine le fichier mysql-slow.log, je vois :
Query_time: 22 Lock_time: 0 Rows_sent: 41 Rows_examined: 10317240

j'ai 251639 clients dans ma base.

or 251639 *41 = 10317240

ça signifie que à chaque client selectionné, il fait un full scan de la table...

du coup, j'ai fait un nouveau test :

SELECT fct_nomcli(sigtie) FROM w_tie WHERE sigtie in (select sigtie from w_tie where sigtie like '505910%');

C'est pas beau, hein ! Mais ça marche beacoup mieux, ce qui confirme l'hypothèse que MYSQL évalue la valeur de la fonction avant même de lire le WHERE...
Super....

[TeepH]

Je viens de trouver.
C'était une histoire de character-set différents dans la conf du serveur de prod. Je pense qu'à un moment mysql savait que le curseur comparait des valeurs encodées différemment. Du coup l'utilisation de l'index n'était certainement plus possible.

Voici les lignes de conf commentées
#character-set-server=utf8
#skip-character-set-client-handshake

Ensuite j'ai droppé ma base et j'ai relancé un import à partir d'un dump de ma machine de dev.

Et ça marche !