Gros Pb de perf sur une procédure stockée [Résolu]

[El Riiico]

Bonjour,

J'ai un problème avec une procédure stockée, qui au final me semble être lente lente qu'un traitement en php + mysql...

Je dois lancer l'import d'un gros fichier d'annonces immobilières (environ 80 000 annonces), avec quelques opérations sur chaque annonce (genre : si c'est une nouvelle annonce, j'insère juste l'annonce dans la base, par contre si c'est une mise à jour, je sauvegarde les modifs dans une table d'historique).

Pour améliorer les performances, je commence par stocker toutes les annonces à scanner dans une table temporaire. Après, ma procédure stockée parcourt la table temporaire et traite chaque enregistrement.

Mon code :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
DROP PROCEDURE IF EXISTS proapart.traite_annonces_from_fichier $$
CREATE PROCEDURE proapart.traite_annonces_from_fichier(
    IN des_param_de_config TYPEPARAM
)
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE d_autres_variables_necesaires_au_traitement TYPEVARIABLE;
        DECLARE cursor_annonces CURSOR
        FOR SELECT
            table_temp.mes_champs_a_recuperer
        FROM table_temp;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN cursor_annonces;
    REPEAT
        FETCH cursor_annonces INTO mes_var_utiles_au_traitement;
        -- Mes tests et mes traitements
    UNTIL done END REPEAT;
    CLOSE cursor_annonces;
END $$
DELIMITER ;

Mon traitement dure presque 2 heures quand je le lance. Quand il ne fait pas planter le serveur mysql (sous windows) au bout de 5000 enregistrements.
C'est dû à quoi d'après vous ?
Est-ce c'est que Mysql n'apprécie pas que je lui envoie 80 000 enregistrements dans le cu(rseur) ?

[El Riiico]

Désolé de faire du Up, mais personne n'a une idée ?

[Maximilian]

Salut,

Ta table temporaire est-elle indexée ? Elle fait quelle taille ?
Regarde peut-être du côté du moteur HEAP.

Plutôt que de passer par une table temporaire, tu pourrais aussi réaliser les insertions/modifications d'un bloc et utiliser un trigger pour mettre à jour la table d'historique...

[El Riiico]

Envoyé par Maximilian

Ta table temporaire est-elle indexée ? Elle fait quelle taille ?

Non, c'est vrai qu'elle n'est pas indéxée. Et c'est vrai qu'elle est en InnoDb, je vais voir à changer ça. Au niveau de la taille, elle a autant d'enregistrements qu'il y a d'annonces, soit 80 000. Ca fait dans les 50/100 Mo.

En fait jusqu'ici j'y accordais pas énormément d'importance, je pensais que c'était pas si important.

Mais parce que j'ai un doute, là sur les curseurs.
Une fois que la sélection est faite et chargée dans le curseur (au début, lors de la déclaration), est-ce que la liste des résultats est chargée dans une grosse variable en mémoire ?
C'est ce que je pensais jusqu'ici, c'est pour ça que je m'embêtais pas trop avec l'optimisation de la table temporaire.

Mais si c'est juste un index qui pointe vers les bons enregistrements, du coup là oui, faut que je l'optimise beaucoup plus ma table.
En même temps je suis con parfois, ça s'appelle un curseur, donc logiquement ça doit être ça.

Je vais ajouter des indexs et tester le format HEAP. Mais j'ai juste une crainte : mes erreures de plantage, c'est déjà du genre "Out of memory Needed 1633644136 bytes" (sur mon serveur de dev qui a 1Go de RAM, celui de prod a bien plus). Alors une table temporaire directement stockée dans la RAM, ça risque pas d'empirer mes affaires ?

Je vais voir ce que ça donne.

J'essaierai les triggers éventuelement après, ça me demande des modifications de code plus importantes.


En tous cas merci. Parce que là, livrer un produit qui rame autant (quand il ne plante pas) ça ne me plait pas trop ; on peut pas dire que ça me laisse confiant face aux éventuelles montées en charge futures.

Là j'ai déjà, plus de pistes à suivre.

[Maximilian]

Je ne connais pas du tout l'implémentation des curseurs mais j'imagine que tout le résultat n'est pas mis en mémoire dès le début, ou alors bonjour l'engorgement...

Si j'ai bien compris ta problématique, utiliser un trigger devrait permettre de se passer de SP et de table temporaire et de se reposer uniquement sur les tables de base et leurs index.
Autant d'éléments intermédiaires lourds zappés pour plus de performance.

[El Riiico]

Après quelque tests, finalement j'ai remonté mes manches et tout passé en triggers.

Adieu tables temporaires et grosses procédures stockées, maintenant j'ai un simple fichier CSV que je traite ligne à ligne, avec un trigger sur l'update.

Les allers-retours Php / Mysql pour chaque enregistrement me faisaient peur pour les performances, mais au final c'est plus rapide qu'avant (25 % plus rapide).
Et surtout je suis plus tranquille, ça résoud mes problèmes de mémoire et de stabilité : alors qu'avant ça plantait au bout de 10 minutes, là j'arrive à en lancer plusieurs, de mes gros traitements, ça passe à l'aise.

Bon, je continue à trouver que 1h30 c'est un peu long... mais comme de toutes façons ca se passera la nuit et que le site n'est pas bloqué, je me remettrai à l'optimisation un peu plus tard.

Merci !