Discussion :

[PaladinFr]

Bonjour.

J'ai un petit souci avec une requete delete.
Je dois faire une mise à jour différentiel d'une base de donnée et pour cela on doit effacer un certain nombre d'enregistrement.

Seulement la on a une tres grosse mise à jour à faire et le delete est un vrai goulot d'etranglement.

J'essaye d'optimiser mon delete mais chaque essai à l'effet exactement inverse...

A l'origine chaque enregistrement à effacer avait "sa" ligne delete
de la forme "delete from *** where **=****" (à répéter 70.000 fois par exemple)
On a essayé de faire ca sous cette forme :
"delete from *** where **=**** or **=****" (avec 70.000 "or")

on a pas essayé le in mais a priori ca donne la meme chose que le or ?

On a essaye de gonfler les buffer de mysql, j'ai changer la valeur de optimizer_search_depth et à chaque fois... échec lamentable.

La j'avoue que je sais plus quoi faire...
Sur quel paramétre de mysql puis je jouer pour améliorer la vitesse de delete ?
est ce possible au moins ?

Pourquoi ma 2eme requete est plus lente que la 1er ? théoriquement envoyé une seule requete devrait etre plus efficace que d'en envoyer 70.000...

Je précise que l'on a beaucoup d'index mais "normalement" notre base est plus dédié à la lecture qu'à l'ecriture. Cette situation de mise à jour tres importante est completement exceptionnelle.
Si vous avez une piste je suis preneur.

[Waldar]

Dropper tous vos indexes SAUF celui de la clef sur lequel vous allez faire le delete.

Le mieux est de faire un seul delete ensembliste en insérant vos valeurs à supprimer dans une table dédiée (temporaire ou non, peu importe) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
DELETE FROM MaTable
 WHERE Clef IN (Select Clef FROM TEMP_DELETE);

Puis recréez vos indexes.

[PaladinFr]

je vais essayer de dropper mes index tout de suite pour voir ce que ca donne.

ca me parait en effet la solution la plus efficace.

[ced]

Bonjour,

Je ne sais pas trop comment tu évalues les 70 000 lignes, mais si c'est le résultat d'une requête et que tu souhaites faire des jointures dans le delete (plus efficace que OR ou IN), peut-être peux-tu t'inspirer de la syntaxe décrite dans la FAQ : http://mysql.developpez.com/faq/?pag..._data_sans_ref

Sinon, il nous faut plus d'infos pour pouvoir t'aider un peu plus.

ced

[PaladinFr]

Je vais essayer d'etre plus précis.
En fait j'ai une liste d'articles qui vont être modifiés.
par modifié ca peut être une "vraie" modification ou une suppression.

La suppression d'un article impacte plusieurs tables (comme une table d'utilisateur)

Les articles qui vont être modifiés sont enregistré dans une table.
Avec cette table on écrit via un script dans un fichier les requetes de suppressions.
Puis on execute ce fichier en ligne de commande.

Le script qui écrit la requete de suppression :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
use base_article;
select 'delete from article where ';
select concat(' num_serie= \'',num_serie,'\' OR ')
from zdiff_article;
select ' serie= \'AA\';';

l'ancienne version :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
use base_article;
select concat('delete from article where num_serie= \'',num_serie,'\';')
from zdiff_article;

L'ecriture du fichier contenant la requete de suppression est tres rapide.
On modifie 6 tables de cette maniere.
Ces tables ont pas mal d'index (qui nous sont nécessaires pour accélérer des extractions de données)

Quand on est dans des petits volumes (1000 articles 90% du temps) ca va tres vite. A partir de 40.000 articles ca va déjà moins bien et à 70.000, 150.000 la ca va plus ca devient beaucoup trop lent.

L'autre truc "étrange" c'est qu'on a remarqué que traiter 100.000 articles est plus de 2 fois plus long que de traiter 2 fois 50.000 articles.


edit : je vais attendre la fin du test en supprimant les index et je vais essayer avec le "left join"

[Waldar]

Faites de l'ensembliste :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
delete from article
 where num_serie in (select num_serie FROM zdiff_article);

Il y a peut-être un syntaxe avec une jointure, mais n'ayant jamais travaillé sur MySQL je ne la connais pas.

La suppression d'un article impacte plusieurs tables (comme une table d'utilisateur)

Bien entendu, vos références de ces tables sont également indexées ?

[PaladinFr]

Oui bien sur.
En fait le "problème" est qu'il y a beaucoup d'index.
Une table en particulier à 5 index (et malheureusement tous nécessaire pour accélérer certaines requêtes).
L'autre souci est que si j'ai 70.000 articles à supprimer si on compte une moyenne de 10 utilisateurs par articles cela fait 700.000 lignes à supprimer dans la table des utilisateurs.
(et bien sur le tout indexé vu que je dois pouvoir faire une requête qui puisse demander tous les articles pour un utilisateur).

La suppression des index n'a malheureusement pas donné de résultat probant (j'ai gagné 15 min mais il a fallu 15 min pour recréer les index, sans compter le temps de la suppression...)
je vais essayer une requête ensembliste et cumuler le tout.

J'ai également eu une autre idée mais je ne suis pas sur qu'elle soit judicieuse.
Je rajoute un champ "temoin" par défaut à 0, je l'indexe et je mets ce témoin à 1 pour toutes les lignes à supprimer via un update. Ensuite je dis de supprimer les lignes dont le témoin est à 1.

[PaladinFr]

Faites de l'ensembliste :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
delete from article
 where num_serie in (select num_serie FROM zdiff_article);

J'ai testé et j'ai immédiatement mis ca en production.
Ca fonctionne bien au dela de mes espérances, j'obtiens au minimun une division par 3 du temps d'execution de l'opération delete

J'ai même cru que la requête ne fonctionnait pas tellement ca m'a paru rapide par rapport à notre ancienne méthode avec les or.

Le truc qui ne surprend c'est que je croyais que le "in" n'était qu'un raccourci syntaxique pour des or en série mais apparement c'est bien plus que ca.
Après il faudra qu'on teste avec le left join il y a peut etre encore un gain à espérer.

Merci beaucoup pour votre aide

[Waldar]

Reste à combiner avec la suppression des indexes, je suis certain que vous pouvez encore gagner du temps.

J'ai regardé la FAQ mise en lien par ced, pour vous la syntaxe n'est pas avec le outer join :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
DELETE A
  FROM article as A
       INNER JOIN zdiff_article as Z
        ON Z.num_serie = A.num_serie