Bonjour !
 
Tout d'abord, je suis nouveau sur ce forum, et je tiens à préciser que je le trouve très sympa : apparemment plein de personnes compétentes et conviviales !!
 
 
Je vous expose mon problème :
 
Je dispose d'une table qui comporte environ 9.000.000 d'entrées.
Elle est de format fixe, et comporte tous les index nécessaires pour effectuer rapidement les requetes.
 
D'ailleurs, les requetes SELECT s'effectuent toutes très rapidement.
 
En revanche, les requetes du type : " UPDATE table SET lu='oui' WHERE id=123456 " sont par période très très lentes (plusieurs dizaines de secondes).
 
Or le champ "id" est pourtant la clé primaire, et la table n'est jamais vérouillée au moment où la requete démarre.
 
J'ai pensé à un problème de mise à jour des index. En effet, ma table fait le poids suivant :
 
Données : 828 522 Ko
Index : 862 989 Ko
Total : 1 652 Mo
 
Mais lorsque j'effectue des INSERT, je n'observe pas ce pb.
 
Auriez-vous une idée de l'origine de telles lenteurs ? Peut-être la config du serveur Mysql, ou bien un problème dans les Index (mais un OPTIMIZE a été effectué très récemment ....)
 
Merci pour votre aide

Salut.
 
Plein de gens : ça, oui
Compétent : parfois
Conviviaux : ça c'est moins souvent
 

 
Bon, sinon, là, comme ça, c'est pas trop évident de répondre.
 
Dans un premier temps, tu peux vérifier les éléments ci-dessous :
L'index est-il bien en CLUSTER ? (données ordonnée pas seulement dans l'arbre, mais aussi au niveau physique sur le disque). Ca change carrément les performances. Sous SQL Server, c'est la valeur par défaut d'un index sur une PK, mais je ne sais pas comment se comporte MySQL par défaut.
Tu peux essayer de recalculer l'index. Je n'ai pas le code en tête, car différent d'un SGBD à l'autre. Reporte-toi à la doc de MySQL pour savoir comment le faire manuellement. Normalement, c'est automatique, sauf indications contraires. A froid, je dirais qu'un simple "OPTIMIZE" ne suffit pas. Si tu ne trouves pas, tu peux toujours détruire l'index et le recréer dans la foulée, il est forcément populé à la création (sauf indication explicite contraire)
 
Ensuite, lors des updates, as-tu des processes succeptibles de faire des modifications en même temps, ou même des select ? Les seclect sont-ils "one shot", ou restent-ils actifs tant que tout le curseur n'est pas lu ? Dans ce cas, sont-ils dynamiques ou statiques ? En effet, si ce sont des curseurs statiques, alors il se produit un LOCK sur la table jusqu'à la fin du traîtement, afin de garantir une vue statique des données (qui ne changent pas d'une lecture à l'autre d'un passage à l'autre dans le curseur).
 
Sinon, le coup du "le insert est pourtant rapide", c'est normal. Je présume que ton ID est autoincrément. MySQL gère ce type de champ d'une façon différente de la plupart des SGBD : la nouvelle valeur crée est forcément inexistante dans la table, par conséquent, il n'y a pas de CHECK dans l'index pour vérifier son existance lors de l'insertion. Donc en cas de problème d'index, ça n'influe en rien sur les perfs.
 
Courage !

si tu est pret a gerer toi meme l'integrite referentielle passe ta table en MyISAM au lieu de InnoDB , les performances sont bien meilleures

Merci beaucoup pour vos réponses
 
- Le fait de récreer des index : j'ai déjà essayé : le problème reste le même.
 
- Là je viens de reordonner physiquement mes tables ( ALTER TABLE tbl ORDER BY id ). Je verrai bien ce que ça va donner. Ce problème se pose essentiellement en période de fort trafic.  
 
Mais ce qui reste surprenant, c'est que c'est par périodes : la requete pourra durer un dixieme de seconde, comme elle peut durer une minute.
 
Ma table est bien en MyISAM, et bien sûr lorsqu'une requete UPDATE est en cours, la table est vérouillée jusqu'à la fin de l'exécution de la requete. Et les commandes SELECT sont donc en attente.

Pour le lock, ok, mais le souci, c'est que comme je dis, les select aussi sont succeptibles de faire des LOCK.
 
Ainsi, même en faisant un lock avant ton update, il doit attendre que les précédents locks soient releasés par les select. Deplus, lors d'un lock, les requêtes sont passées les unes après les autres, dans le sens FIFO (First In First Out). Donc si un SELECT fait un LOCK de 10 secondes, et que t'as 10 selects qui arrivent à la queue leue-leue, alors un UPDATE, landé 60 secondes plus tard, devra quand même attendre les 40 secondes restantes pour s'éxécuter.

Voilà un exemple de ce qui se produit peut-être chez toi avec les select (ici, je force SQL Server à locker la table, non pas seulement durant le select, mais toute la transaction - que je ne termine volontairement pas tout de suite afin de simuler un traîtement long durant le select -).
 
Connection 1 :

 
Résultat :

 
Connection 2 :

 
Résultat :

On voit que la seconde requête est bloquée
 
Connection 1 :

 
Rsultat (cnx1)

 
Rsultat (cnx2)

 
 
Voilà, ça donne une idée du système de LOCK qu'on trouve avec les bases de données. Evidement, c'est plus complexe que ça, mais disons que ce résultat est le cas le plus commun.

Apparemment, cela ne viendrait pas d'une concurrence avec des SELECT.
 
Depuis que j'ai re-ordonné physiquement ma table selon la clé primaire "id", il semblerait que je n'ai plus ce problème.
 
A surveiller ...
 
------------------------------------------------------------
 
Mais je ne comprends pas quelque chose :
Lorsqu'on effectue une requete de type " UPDATE tbl SET blabla.. WHERE id=123456 ",
étant donné que "id" est la clé primaire, pourquoi doit il chercher la ligne correspondante ?
 
Même si la table n'est pas triée physiquement sur id, le fait que ce soit la clé primaire ne devrait-il pas le ramener directement sur la bonne position dans la table ?
 
Car sur un document lié à MySQL je peux lire ça :
" La création d’un index associé à un attribut ou à un ensemble ordonné d’attributs va créer une liste ordonnée des valeurs de ces attributs et de l’adresse de la ligne associée. "
 
Je trouve donc étrange que retrier physiquement les données supprime ces lenteurs.

Je peux pas entrer dans le détail, mais non, clairement, savoir où se situe la ligne ne suffit pas toujours à obtenir les meilleures performances, même quand on n'en cherche qu'une seule.