Discussion :

[Sennad]

Salut à tous !!

J'ai développé une application en JAVA qui parcourt une arborescence (plusieurs en réalité) de fichiers, et qui fait le hashMD5 de chaque fichier.
Ces hash sont stockés dans une table de ma base de donnée MySQL, ainsi que l'URL (plutôt chemin d'accès) du fichier.
Mon programme a pour objectif de vérifier l'intégrité et l'intégralité des données dans le temps.
Pour ce faire il est exécuté périodiquement (l'objectif étant toutes les semaines).
Le programme va donc pour chaque fichier, comparer l'ancien hash et le nouveau, pour savoir si le fichier a était modifié.
Je vous rassure je ne fais pas de SELECT pour chaque fichiers, ça prendrai beaucoup trop de temps, je récupère donc tous les hash au début de l’exécution de mon programme.
Dans tous les cas, pour chaque fichiers, modifié ou non, je fais une requête UPDATE sur la colonne Checked pour la setter à 1.
Pour pouvoir à la fin du programme trouvé les fichiers qui on était supprimé (Checked = 0).
Encore une fois, je ne fais pas cette requête à chaque fichier, mais seulement au bout de 500fichiers.
Mais c'est là que les lenteurs interviennent.
En effet au plus ma base de donnée, ou plutôt la table, grossit (> 2 000 000 d’enregistrements), au plus les requêtes sont longues. Normal je suppose, mais là c'est vraiment HYPER long !
J'ai fait des index sur la plupart de mes colonnes, a l’exception de la colonne URL car elle a une longueur de 500, et MySQL Workbench m'envoi péter.
Cependant cette colonne est présente dans toutes mes requêtes..
Voici mes requêtes principales :

Code :

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

1
2
3
INSERT INTO ProjectsFiles (Name, URL, Signature, Checked, IdProject) VALUES (?,?,?,?,?);
UPDATE ProjectsFiles SET Signature=?, Checked=1 WHERE URL=?;
UPDATE ProjectsFiles SET Checked=1 WHERE URL=?;

La dernière étant appelée des millions de fois.
Tout ce que je dis est valable pour les deux tables : ProjectsFiles et ReferencesFiles.
Au début de mon post j'ai parlé de plusieurs arborescences, en effet je lance 50 fois mon programme en parallèle sur 50 arborescences différentes.
Seulement pour l'instant mes tests se font sur une seule arborescence, et les lenteurs sont déjà là.

Voici un schéma de ma DB :


Voici les indexs créé sur la table ReferencesFiles (également valable pour la table ProjectsFiles).


Ma question est "simple" :
Que puis-je faire pour résoudre ce problème de lenteurs ?
Le seul moyen est de réduire la longueur de la colonne URL à 255 et de faire un index dessus ?

Merci d'avance !

[CinePhil]

Bonjour,

Ça ne changera peut-être pas grand chose mais vous pouvez déjà supprimer l'index UNIQUE sur l'ID puisque la clé primaire est déjà un index unique.

Si j'en crois la doc MySQL, une colonne VARCHAR est comprise entre 1 et 255 caractères. Il est curieux alors que MySQL permette la création d'une colonne VARCHAR(500) mais ce ne serait pas la première curiosité de MySQL. Peut-être qu'il considère alors qu'il s'agit d'une colonne de type TEXT ? Dans ce cas, il n'est possible d'indexer une colonne TEXT qu'en fournissant la longueur de l'index, ce qui est également possible sur une colonne VARCHAR :

Pour les colonnes CHAR et VARCHAR, les index peut être créés sur uniquement une partie de la colonne, avec la syntaxe col_name(length). Pour les colonnes BLOB et TEXT la longueur d'index est obligatoire. La requête suivante crée un index en utilisant les 10 premiers caractères de la colonne name :

mysql> CREATE INDEX part_of_name ON customer (name(10));

Comme la plupart des noms ont en général des différences dans les 10 premiers caractères, l'index ne devrait pas être plus lent qu'un index créé à partir de la colonne name en entier. Ainsi, en n'utilisant qu'une partie de la colonne pour les index, on peut réduire la taille du fichier d'index, ce qui peut permettre d'économiser beaucoup d'espace disque, et peut aussi accélérer les opérations INSERT!

Vous pouvez donc indexer votre colonne URL sur une partie seulement de la longueur de la colonne.
Avec une colonne indexée, cela devrait beaucoup accélérer les choses.

Si votre programme est lancé périodiquement et en dehors des heures d'utilisation normale de la BDD (batch de nuit), il peut être utile de désactiver les index pour lancer les UPDATE en masse puis de réactiver les index car sur les grosses tables, la réorganisation d'index prend du temps.

Si tous les fichiers sont stockés dans une arborescence standardisée, par exemple dans /home/production/projets/projetNNN/, il peut être inutile de stocker la totalité de l'URL mais seulement sa partie significative, à partir de projetNNN. Ça réduit le volume de stockage, la taille de la colonne et améliore la pertinence de l'index.
Il peut aussi être intéressant de rechercher prioritairement le nom du fichier (qui, si j'ai bien compris), est de toute façon lu sur le disque par le parcours de l'arborescence), plutôt que de passer par l'URL.

Voilà des pistes, à vous de les explorer.

Bon courage !

[SQLpro]

Vus n'avez pas d'index sur la colonne URL. Donc chacun de vos updates nécessite de parcourir l'intégralité de la table.
De plus MySQL n'étant pas capable de faire du parallélisme, les accès aux donnés sont séquentiel.
Par exemple dans SQL Server, dès que le cout de requête est important, le plan de requête est récrit pour effectuer la requête en parallèle sur autant de threads que nécessaire, ce qui diminue sensiblement le temps de traitement !

Il y a aussi une grosse merde dans MySQL, c'est que lorsque vous mettez de le chaine de caractères, MySql travaille en pseudo UTF-8 et utilise donc 3 octets par caractères (c'est grotesque... !!!) Or la limite de longueur de clef d'index dans MySQL étant voisine de 1000 octets, avec l'encodage UTF-8 vous êtes coincé avec une longueur de 340 caractères...

De toute façon MySQL n'est pas réputé ni pour sa fiabilité ni pour ses performances et encore moins pour ses fonctionnalités. A me lire : http://blog.developpez.com/sqlpro/p9...oudre_aux_yeux

A +

[Sennad]

Salut !

D'abord merci beaucoup pour vos réponses très enrichissantes

Ça ne changera peut-être pas grand chose mais vous pouvez déjà supprimer l'index UNIQUE sur l'ID puisque la clé primaire est déjà un index unique.

C'est noté

Si j'en crois la doc MySQL, une colonne VARCHAR est comprise entre 1 et 255 caractères. Il est curieux alors que MySQL permette la création d'une colonne VARCHAR(500) mais ce ne serait pas la première curiosité de MySQL.

Effectivement j'avais vu la même chose, j'avais donc ma colonne en 255, sauf que j'ai eu beaucoup d'erreur car des fois le chemin d'accès était trop grand, j'ai donc essayé de passer à 500, il m’a rien dit, ça a marchait donc j'étais content, jusqu'à ce que j'essaye de faire un index ^^

Peut-être qu'il considère alors qu'il s'agit d'une colonne de type TEXT ? Dans ce cas, il n'est possible d'indexer une colonne TEXT qu'en fournissant la longueur de l'index, ce qui est également possible sur une colonne VARCHAR

Ça c'est super ! Je vais faire ça tout dessuite Sur les 255 premiers caractères, ça sera déjà ca Il n'y a pas énormément d'URL qui dépassent les 255, il y en a, d'où le problème, mais pour le peu qu'il y a, il cherchera un peu plus longtemps, mais ça sera négligeable
Merci beaucoup !

Si votre programme est lancé périodiquement et en dehors des heures d'utilisation normale de la BDD (batch de nuit), Il peut être utile de désactiver les index pour lancer les UPDATE en masse puis de réactiver les index car sur les grosses tables, la réorganisation d'index prend du temps.

Euh je comprends pas trop :/
Alors ma base de donnée ne sert que pour ce programme, donc l'utilisation normale c'est tout le temps ^^ Mon programme tourne tout le temps, il est sensé durer moins d'une semaine, et se lancer tous les Lundi.
Puis mes UPDATE sont long car il cherche l'URL, il n'y a pas d'index, si je désactive les index pour les UPDATE ça sera long, pourquoi faire ?

Si tous les fichiers sont stockés dans une arborescence standardisée, par exemple dans /home/production/projets/projetNNN/, il peut être inutile de stocker la totalité de l'URL mais seulement sa partie significative, à partir de projetNNN. Ça réduit le volume de stockage, la taille de la colonne et améliore la pertinence de l'index.

Le problème est là, chaque programme commence bien d'une arborescence du style /XXX/XXX/XXX/*
Où seulement ce qu'il y a après l'* changera, seulement chaque programme a une arborescence différente.
En gros on aura :
/XXX/XXX/XXX/* => Process1
/YYY/YYY/* => Process2
/zzzz/zzzz/zz/zzz/* => Process3
Etc..
Dommage mais tempi ^^

Il peut aussi être intéressant de rechercher prioritairement le nom du fichier (qui, si j'ai bien compris), est de toute façon lu sur le disque par le parcours de l'arborescence), plutôt que de passer par l'URL.

Ça va être difficile, puisque il peut y avoir beaucoup de noms identique, mais peut être qu'une requête de ce style :
SELECT Signature FROM ReferencesFiles WHERE Name = ? AND URL = ?;
Serrait plus rapide que celle-ci :
SELECT Signature FROM ReferencesFiles WHERE URL = ?;
Grace à l'index sur la colonne Name ?

Merci énormément pour votre aide précieuse !

__________________________________________________

Vus n'avez pas d'index sur la colonne URL. Donc chacun de vos updates nécessite de parcourir l'intégralité de la table.
De plus MySQL n'étant pas capable de faire du parallélisme, les accès aux donnés sont séquentiel.
Par exemple dans SQL Server, dès que le cout de requête est important, le plan de requête est récrit pour effectuer la requête en parallèle sur autant de threads que nécessaire, ce qui diminue sensiblement le temps de traitement !

Il y a aussi une grosse merde dans MySQL, c'est que lorsque vous mettez de le chaine de caractères, MySql travaille en pseudo UTF-8 et utilise donc 3 octets par caractères (c'est grotesque... !!!) Or la limite de longueur de clef d'index dans MySQL étant voisine de 1000 octets, avec l'encodage UTF-8 vous êtes coincé avec une longueur de 340 caractères...

De toute façon MySQL n'est pas réputé ni pour sa fiabilité ni pour ses performances et encore moins pour ses fonctionnalités. A me lire : http://blog.developpez.com/sqlpro/p9...oudre_aux_yeux

C'est bien dommage, j'ai fini ma mission, il est trop tard pour basculer sur du SQL Server.. Si j'avais su avant... :'(
En tout cas merci pour votre réponse!
Mais du coup, qu'el intérêt a MySQL ? Il a bien une utilité particulière par rapport a du SQL ou autre nan ?
Je vais aller lire votre lien pour me renseigner un peu sur la question


Merci à vous pour vos réponses !
Je vais faire quelques tests avec l'index et je reviens vers vous !
Merci

[aieeeuuuuu]

Bonjour,

Je vous rassure je ne fais pas de SELECT pour chaque fichiers, ça prendrai beaucoup trop de temps, je récupère donc tous les hash au début de l’exécution de mon programme.

ça c'est plutôt une bonne idée, mais pourquoi ne pas récupérer l'ID des fichiers dans le même temps ?
Vos requêtes d'UPDATE pourraient s'appuyer dessus, ce qui serait beaucoup mieux qu'avec l'URL.

Par ailleurs, même si cela reste à vérifier, je pense que vous pouvez supprimer votre index sur la colonne checked. il a peu de chance d'être efficace pour les sélections, par contre il doit être maintenu à chacune de vos requêtes d'UPDATE...

Vérifier aussi si l'index sur signature est utile !

[CinePhil]

Si votre programme est lancé périodiquement et en dehors des heures d'utilisation normale de la BDD (batch de nuit), Il peut être utile de désactiver les index pour lancer les UPDATE en masse puis de réactiver les index car sur les grosses tables, la réorganisation d'index prend du temps.

Euh je comprends pas trop :/

Si vous insérez une donnée dans votre table, MySQL va reconstruire l'index et plus la table est grosse, plus cela prend du temps.
Lorsque c'est possible, il vaut mieux donc :
1) Désactiver les index
En fait, si je me souviens bien, chez MySQL on ne peut pas simplement les désactiver mais il faut les supprimer.
2) Faire des insertions en masse
3) Reconstruire les index

Ça prend moins de temps si on a beaucoup d'insertions à faire en même temps.

Pour les UPDATE, c'est pareil, sauf que dans votre cas, si j'ai bien compris, les seules choses modifiées sont la colonne "Signature" et la colonne "Checked".
Pour la seule modification de la colonne "Checked", ce n'est, à la réflexion, peut-être pas une bonne suggestion.
D'ailleurs, il est inutile d'indexer cette colonne puisqu'elle ne prend que deux valeurs. L'index ne serait jamais utilisé car il n'est pas discriminant.
Mais pourquoi avoir indexé la colonne "Signature" ? L'utilisez-vous pour les recherches de données dans la table ?

Maintenant, je ne sais pas si le fait d'UPDATEr seulement une ou des colonnes non indexées entraîne quand même la reconstruction de l'index par MySQL. SQLPro saura peut-être nous le dire.

Alors ma base de donnée ne sert que pour ce programme, donc l'utilisation normale c'est tout le temps ^^ Mon programme tourne tout le temps, il est sensé durer moins d'une semaine, et se lancer tous les Lundi.
Puis mes UPDATE sont long car il cherche l'URL, il n'y a pas d'index, si je désactive les index pour les UPDATE ça sera long, pourquoi faire ?

Je vous ai donné plus haut le principe en vigueur dans MySQL, sauf erreur de ma part, ainsi que l'astuce pour éviter la reconstruction de l'index à chaque UPDATE d'une seule ligne.

Le problème est là, chaque programme commence bien d'une arborescence du style /XXX/XXX/XXX/*
Où seulement ce qu'il y a après l'* changera, seulement chaque programme a une arborescence différente.
En gros on aura :
/XXX/XXX/XXX/* => Process1
/YYY/YYY/* => Process2
/zzzz/zzzz/zz/zzz/* => Process3
Etc..
Dommage mais tempi ^^

Il peut alors être intéressant de stocker l'URL à l'envers si la partie finale de l'URL est ce qu'il y a de plus variable.
Au lieu de stocker "/chemin/vers/projet1/process1/sous-rep_1" où la partie qui se répète le plus est "/chemin/vers/projet1/process1/", vous pouvez par programme inverser vos URL et les enregistrer à l'envers, soit : "sous-rep_1/process1/projet1/vers/chemin"
D'un process à l'autre dans un même projet, il y a peut-être de fortes chances que les sous-répertoires aient des noms très différents, puis en avançant dans l'arborescence inversée, que les répertoires des projets aient des noms très différents...

Rien que par mon petit exemple, vous pourriez avoir cette arborescence :
/chemin/vers/projet1/process1/sous-rep_1
/chemin/vers/projet1/process1/sous-rep_2
/chemin/vers/projet1/process1/sous-rep_3
/chemin/vers/projet1/process2/sous-rep_A
/chemin/vers/projet1/process2/sous-rep_B
/chemin/vers/projet1/process3/sous-rep_truc
/chemin/vers/projet1/process3/sous-rep_bidule
/chemin/vers/projet2/processA/sous-rep_0824
/chemin/vers/projet2/processB/sous-rep_XYZ
/chemin/vers/projet3/processA/sous-rep_1
/chemin/vers/projet3/processA/sous-rep_2

On voit bien que pour chercher une URL, l'index ne serait discriminant qu'à partir du 20ème caractère alors que si on inverse l'URL, elles sont différentes à partir du 10ème caractère.

[Sennad]

Merci encore pour vos réponses !!

ça c'est plutôt une bonne idée, mais pourquoi ne pas récupérer l'ID des fichiers dans le même temps ?
Vos requêtes d'UPDATE pourraient s'appuyer dessus, ce qui serait beaucoup mieux qu'avec l'URL.

Je n'y avais pas pensé, le probleme est que je stocke URL et Signature dans une collection (clé / valeur) donc je peux pas rajouter une troisieme colonne. En cherchant un peu je devrais pouvoir faire autrement, je vais y réfléchir !

Par ailleurs, même si cela reste à vérifier, je pense que vous pouvez supprimer votre index sur la colonne checked. il a peu de chance d'être efficace pour les sélections, par contre il doit être maintenu à chacune de vos requêtes d'UPDATE...
Vérifier aussi si l'index sur signature est utile !

Effectivement, j'ai viré ces deux indexs, merci

Si vous insérez une donnée dans votre table, MySQL va reconstruire l'index et plus la table est grosse, plus cela prend du temps.
Lorsque c'est possible, il vaut mieux donc :
1) Désactiver les index
En fait, si je me souviens bien, chez MySQL on ne peut pas simplement les désactiver mais il faut les supprimer.
2) Faire des insertions en masse
3) Reconstruire les index
Ça prend moins de temps si on a beaucoup d'insertions à faire en même temps.

Okey ! Je comprend !

Pour les UPDATE, c'est pareil, sauf que dans votre cas, si j'ai bien compris, les seules choses modifiées sont la colonne "Signature" et la colonne "Checked".
Pour la seule modification de la colonne "Checked", ce n'est, à la réflexion, peut-être pas une bonne suggestion.
D'ailleurs, il est inutile d'indexer cette colonne puisqu'elle ne prend que deux valeurs. L'index ne serait jamais utilisé car il n'est pas discriminant.
Mais pourquoi avoir indexé la colonne "Signature" ? L'utilisez-vous pour les recherches de données dans la table ?

C'est fait, j'ai bien viré ces deux indexs inutiles.

Il peut alors être intéressant de stocker l'URL à l'envers si la partie finale de l'URL est ce qu'il y a de plus variable.
Au lieu de stocker "/chemin/vers/projet1/process1/sous-rep_1" où la partie qui se répète le plus est "/chemin/vers/projet1/process1/", vous pouvez par programme inverser vos URL et les enregistrer à l'envers, soit : "sous-rep_1/process1/projet1/vers/chemin"
D'un process à l'autre dans un même projet, il y a peut-être de fortes chances que les sous-répertoires aient des noms très différents, puis en avançant dans l'arborescence inversée, que les répertoires des projets aient des noms très différents...

Ah oui effectivement ! Je n'avais pas pensé à ca.. Ca me ferais changer pas mal de chose dans le code, mais pourquoi pas ! Je vais surement mettre ca en place !

En tout cas le seul fait d'avoir mis l'index sur les 255 premiers caractères de la colonne URL a permis de débloquer les lenteurs !
C'est super !
Mon programme est suffisamment rapide (j'ai l'impression), donc ça à l'air d'être bon
Je vais lancer un gros gros test pour voir si ça rentre dans la semaine, et je reviens vers vous

Je vous remercie énormément !!


A bientôt

[Sennad]

Salut à tous !

Je vais lancer un gros gros test pour voir si ça rentre dans la semaine, et je reviens vers vous

Me revoilà
Mon programme a parcourut 2To de données <=> 3.000.000 de fichiers, en moins de 4jours.
Je pense que les performances sont satisfaisantes.
Encore merci à vous !

A bientôt !

[CinePhil]

Mon programme a parcourut 2To de données <=> 3.000.000 de fichiers, en moins de 4jours.

Soit à peu près 8 fichiers par seconde... ça semble correct, en effet.

[Sennad]

Soit à peu près 8 fichiers par seconde... ça semble correct, en effet.

Avec 8 process qui tournent en même temps, j'atteint les 2000 requettes / secondes !
Avec 50 process on verra bien si ça tourne

Une dernière petite question, à l'heure d'aujourd'hui j’exécute le batch tout les 1000fichiers, donc j'envoie une presque 1000 requettes d'un coup.
Vous pensez que c'est trop ? Ou je peux monter encore du style tous les 3000fichiers ?
Merci !

A bientôt,

[CinePhil]

Je ne sais pas.
À tester.