Discussion :

[Bluelane]

Bonjour à tous,

Je rencontre un problème sur un serveur dédié : une base MySQL pesant 50Mb, pourtant optimisée avec beaucoup de valeurs numériques, fait monter la consommation de MySQL à 50% de CPU mais seulement 4% de mémoire. Parfois, sous certaines requêtes légèrement complexes (par exemple une SELECT faisant appel à trois tables) la consommation monte à 98% et le serveur se bloque entièrement, je suis obligé d'attendre plusieurs minutes pour que la requête se termine et que la consommation chute. Si je coupe MySQL et que je le redémarre la consommation monte tout de suite à 40% en quelques secondes.

Mes tables ont toutes une clef primaire mais ne sont pas indexées correctement, est ce que cela pourrait en être la raison ?

Merci beaucoup !!!


[Message édité par Maximilian]
[cf http://club.developpez.com/regles/#L4.9]

[ren0]

Bonjour,

est-ce un vrai serveur dédié ou un serveur virtuel dédié ?

[Bluelane]

Il s'agit d'une MPS (pour ceux qui connaissent) : Manage Private Server. En fait je suis seul sur la machine mais elle est administrée par des administrateurs réseau. Le serveur tourne sous FreeBSD 4.7

A mon avis ça vient de la mauvaise indexation des tables... Quoi que..

[ren0]

et pour info, quelle est ta version de MySQL ?

Code :

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

mysql> SELECT VERSION();

[Maximil ian]

Mes tables ont toutes une clef primaire mais ne sont pas indexées correctement, est ce que cela pourrait en être la raison ?

Ca vaut le coup d'essayer

Voir http://mysql.developpez.com/faq/?pag...miser_requetes

[Bluelane]

MySQL 4.0.24

[ren0]

Comme te l'indique Maximilian, je t'invite à faire le tour de l'indexation de tes tables.
Mais le simple fait que tu consomme 40% des ressources au démarrage de MySQL, donc j'imagine, à un niveau de charge nul (sans requete qui tourne), laisse présager un problème plus profond qu'un simple probleme d'indexation.

Tiens nous au courant,

[Bluelane]

En fait ce n'est pas la première fois que je rencontre ce problème. Je l'ai rencontré l'été dernier également et depuis il ne m'a plus vraiment quitté. Voici ce qui m'était arrivé :

Je bossais sur mes scripts et mes tables, tout d'un coup alors que je faisais des changements importants le serveur s'est mis à répondre difficilement.

Par exemple, une requête prenant une donnée dans une table afin de mettre à jour une seconde table. Sur environ 40 000 lignes ce genre de script prenait environ 10 secondes à tout casser. Et bien là la montée en charge allait jusqu'à 98% et le serveur avait besoin de 36H (véridique !!) pour l'éxécuter !!

Depuis certaines requêtes trop "massives" me sont interdites. Alors est ce que l'indexation sera la solution ? A vrai dire je n'espère plus trop !

[ren0]

Donne nous un exemple de requete qui mets ta machine à genoux avec son explain plan et un desc des tables.
et on verra si on peut t'aiguiller...

[Bluelane]

Voici un exemple de requête qui a entrainer un blocage (depuis un fichier PHP sur le serveur, avec une boucle WHILE) :

SELECT usr_idt.usr, usr_idt.login, usr_inf.city, usr_inf.age, usr_inf.sex, usr_inf.artistic, usr_inf.activity, usr_inf.level, usr_inf.votes, usr_inf.picture, usr_inf.picture_check
FROM usr_idt, usr_inf
WHERE usr_idt.validity = '1'
AND usr_idt.type = '1'
AND usr_idt.usr = usr_inf.usr
AND usr_inf.age
BETWEEN '0'
AND '50'
AND usr_inf.artistic = '1'
OR usr_inf.artistic = '6'
ORDER BY usr_idt.vip DESC , usr_idt.lastconnex DESC
LIMIT 0 , 20;

Le contenu du EXPLAIN :

table type possible_keys key key_len ref rows Extra
usr_idt ALL PRIMARY NULL NULL NULL 53381 Using temporary; Using filesort
usr_inf ALL PRIMARY NULL NULL NULL 53380 Using where

[Maximil ian]

Donne nous un exemple de requete qui mets ta machine à genoux avec son explain plan et un desc des tables.
et on verra si on peut t'aiguiller...

Peux-tu également nous donner le résultat d'un SHOW STATUS ainsi que le contenu de ton fichier de config (my.ini / my.cnf) ?

[ren0]

c'est bon, ton probleme est là !

tu fetch 53381 * 53380 soit 2849477780 lignes !
Donc en plus MySQL est obligé de gérer des tables temporaires (using temporary) et de swapper sur le disque dur pour faire les tris (using filesort) !

donc ta machine en prends plein la tronche...

[ren0]

pour info le between est à proscrire, si tu peux le remplacer par des comparaisons...

where ((usr_inf.age >= 0) and (usr_inf.age <= 50))

rajoute un index sur usr_inf.age et un sur usr_inf.artistic

ça devrait aller un peu mieux...

[Bluelane]

Déjà : merci à vous tous pour s'être intéressé à mon problème, ça fait plaisir de voir une entraide comme celle ci entre développeurs !

Malheureusement je m'y connais plus en PHP qu'en MySQL et quand vous me parlez de tables temporaires, de swappage sur le disque, etc.. Je m'y perds royalement (et c'est pas que j'ai pas envie) !!

Déjà pour le problème de la fonction BETWEEN c'est règlé, depuis ce matin sur le site je ne fais plus appel à cette fonction, je l'ai remplacé par des comparateurs.

Après si j'ai bien compris pour règler le reste du problème il faudrait faire des index, c'est bien ça ?

Alors j'ai lu des pages et des pages d'aide sur le net à propos de l'indexation des tables seulement on nous apprend comment les crée (avec PHPmyAdmin c'est assez facile), mais pas exactement comment choisir les clés.

Sur l'exemple présent des deux tables (plus haut sur la discussion), quelles colonnes je dois placer en PRIMARY KEY, INDEX, UNIQUE, etc... ? Et quand on change une colonne en PRIMARY KEY faut il absoluement modifier sa similaire dans une table différente ?

[Bluelane]

Configuration en MP.

[ren0]

Re,

ne change rien à tes tables, elles sont trés bien, il suffit juste de rajouter deux index...

pour créer les deux index, il te suffit de faire :

Code :

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

1
2
mysql>CREATE INDEX idx_usr_inf_age ON usr_inf (age);
mysql>CREATE INDEX idx_usr_inf_artistic ON usr_inf (artistic);

[Bluelane]

Si j'ai bien compris en fait ça n'a rien de compliquer, il faut créer des index sur les colonnes les plus utilisées ?

Quelques questions :

Quelle est l'utilité de nommer les index ? Est ce que techniquement c'est important pour lui ?

Comment bien choisir ses colonnes à indexer ? Faut il le faire sur toutes les colonnes numériques entrant dans un SELECT récurent ?

A partir de quelle donnée PHPmyAdmin affiche la cardinalité d'un index. Par exemple j'ai indexé la même colonne sur deux tables différentes, pour l'un il m'indique une cardinalité de 92 et l'autre de 0 alors que des données y sont enregistrées (mais beaucoup moins).

Faut il mettre à jour ces index ? Si oui, comment ?

[Maximil ian]

Sur l'exemple présent des deux tables (plus haut sur la discussion), quelles colonnes je dois placer en PRIMARY KEY, INDEX, UNIQUE, etc... ? Et quand on change une colonne en PRIMARY KEY faut il absoluement modifier sa similaire dans une table différente ?

Comme indiqué dans le lien que je t'ai donné plus haut, les colonnes à indexer en priorité sont celles utilisées dans les clauses WHERE, les pivots des jointures (clause ON) et accessoirement les critères de tri (clause ORDER BY).

La clé primaire est la colonne qui permet d'identifier une ligne de la table de manière unique. Il n'y en a qu'une par table, et le mot-clé PRIMARY KEY implique qu'elle est automatiquement unique, indexée et NOT NULL. Dans ton cas donc, rien à modifier de ce côté-là.

Une colonne avec un index UNIQUE doit avoir des valeurs toutes distinctes. C'est le type d'index le plus efficace (on accède directement à la ligne recherchée, il n'y a besoin de considérer X lignes pour une valeur d'index) mais peu de colonnes s'y prêtent de par leur nature.

Voir aussi http://dev.mysql.com/doc/refman/5.0/fr/mysql-indexes.html

De plus, selon l'utilisation de ta base (typiquement, beaucoup d'accès en lecture et peu en écriture) et la configuration matérielle de ton serveur, il peut être intéressant d'augmenter le cache d'index (key_buffer_size) et d'activer le cache de requêtes (query_cache_size).

[Bluelane]

Merci beaucoup...

Pour le cache de requête comment peut on le monter en commande MySQL et à combien dois je le fixer ? En clair ça va changer quoi ?

[Maximil ian]

Pour le cache de requête comment peut on le monter en commande MySQL et à combien dois je le fixer ? En clair ça va changer quoi ?

Pour paramétrer le serveur il suffit d'ajouter variable = valeur dans le fichier de configuration my.cnf, section [mysqld].

http://maximilian.developpez.com/mysql/queryCache/
http://florian.developpez.com/mysql/page2.php

bonne lecture

Quelle est l'utilité de nommer les index ? Est ce que techniquement c'est important pour lui ?

Les index doivent avoir un nom ne serait-ce que pour pouvoir les supprimer. Si on n'en précise pas MySQL en crée un par défaut.

A partir de quelle donnée PHPmyAdmin affiche la cardinalité d'un index. Par exemple j'ai indexé la même colonne sur deux tables différentes, pour l'un il m'indique une cardinalité de 92 et l'autre de 0 alors que des données y sont enregistrées (mais beaucoup moins).

Faut il mettre à jour ces index ? Si oui, comment ?

Cardinality

An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.