Mysql n'utilise pas ses index !!!

[benjiman]

Bonjour !

je vous présente rapidement mon probleme:
J'ai ouvert dimanche dernier un jeu virtuel. Je pensais avoir 100 visites dans un premier temps ... Donc j'ai pris une DEDIBOX.
Sauf que vendreid, j'ai eu 1200 visites, et hier 1400.
En d'autres termes, la dedibox ne suffit plus et bourre.
Le site est inutilisable tellement il est lent.
J'ai un commandé un nouveau serveur OVH (C2D 2x1.8 ) mais, il faut attendre au moins 1 semaine pour la livraison, et je suis en train de perdre tous mes visiteurs :cry: :cry:

En regardant le probleme, c'est MYSQL qui bouffe tout et notamment sur les requetes de MISES à JOUR.
En fait, Mysql explore toutes les tables pour une simple requete, alors que je lui ai mis des INDEX sur les champs qui sont tout le temps utilisé dans la clause WHERE.

Dans mes logs j'ai : "Query_time: 23 Lock_time: 6 Rows_sent: 12 Rows_examined: 191881"
(la table a plus de 300 000 entrées à l'heure actuelle)


Comment faire pour régler ce probleme ?
J'ai l'impression que mes 3 index ne servent à rien !
(Et pourtant, toutes mes requetes ne se basent que sur ces index).


Merci beaucoup pour votre attention.

 

[david96]

Optimises tes requêtes, utilises la fonction explain pour l'analyse de celles-ci :
http://dev.mysql.com/doc/refman/5.0/fr/explain.html :wink:

 

[benjiman]

Optimiser, optimiser ...
j'entend que ce mot et je ne fais que ca !

Dans PHPmyadmin je vois :
"Handler_read_rnd_next 497 G Le nombre de requêtes de lecture du prochaine enregistrement dans le fichier. Élevé si vous faites plusieurs parcours de tables. Ceci suggère que vos tables ne sont pas correctement indexées ou que vos requêtes ne sont pas écrites de façon à tirer parti des index que vous avez définis."

Vraiment, je ne sais plus quoi faire ...
J'ai mis comme index le champs A et le champs B, et dans mes requetes, je n'utilise que le champs A et B ... Et pourtant, ca n'utilise pas mes index !


PS: Lorsque je fais le EXPLAIN sur des longues requetes (avec beaucoup de jointures), je trouve, pour la table principale :
select_type=SIMPLE, type= ALL, possible_keys= NULL,key= NULL,key_len= NULL,ref= NULL, rows=300936, Extra=Using Where

POURQUOI il ne prend pas en compte mes index ??? :cry: :cry: :cry: :cry:
(sur les tables de jointures, il prend bien en compte les index)

 

[FloBaoti]

Donnes-nous un exemple clair (structure de table et requete type)...

 

[MarvinLeRouge]

Salut,

J'ignore si le moteur joue beaucoup sur ce genre de trucs, mais en InnoDb, tu pourris déclarer explicitement les jointures entre les tables, ce qui pourrait améliorer tes performances.

 

[benjiman]

En fait ma question est celle-ci :

Comment gerer une base qui fait plus de 50 mo et qui enregistre plus de 10 mo/ jour de données ?

De plus, il faut manier des tables de plus de 1 millions d'entrées avec des requetes de selection très récurrente (avec jointures).

A part utiliser la methode de pages par cache (car il ne servira a rien vu que les données sont mises a jour toutes les minutes presque), que pourriez-vous me proposer ?

Je suis vraiment perdu ...
J'attend la recette miracle

InnoDB ne me convient pas vu que je fait beaucoup de séléctions ...

A savoir: le serveur Mysql sera hébergé sur un Core 2 Duo 2x1.8 Ghz avec 1 go de ram sans apache. (apache fonctionnera sur un autre serveur à part).

(D'ailleurs, Mysql n'utilise qu'a peine 10% de ma mémoire de ram... je ne comprend pas pourquoi ! Alors que j'ai mis une limite de cache à plusieurs centaines de Mo)

Je vous remercie pour votre attention.

 

[medium69]

A part utiliser la methode de pages par cache (car il ne servira a rien vu que les données sont mises a jour toutes les minutes presque), que pourriez-vous me proposer ?

Je ne sais pas trop si cela peut t'aider, mais si tu as vraiment beaucoup de visites, une mise en cache ne serais-ce qu'avec un laps de temps d'une minutes peut aider pas mal non ?

 

[Julia41]

A savoir: le serveur Mysql sera hébergé sur un Core 2 Duo 2x1.8 Ghz avec 1 go de ram sans apache. (apache fonctionnera sur un autre serveur à part).

(D'ailleurs, Mysql n'utilise qu'a peine 10% de ma mémoire de ram... je ne comprend pas pourquoi ! Alors que j'ai mis une limite de cache à plusieurs centaines de Mo)

Futur client OVH ?
Hum si tu dis que ton MySQL utilise seulement 10% de la ram, soit ça vient du processeurs, soit de ce qui prends le reste de la ram... AS-tu fouiné dans les réglages de MySQL ? Par hasard, tes logs MySQL sont-il activé (je ne pense pas mais bon).
Et je pense que tu peux tout mettre sur ton superplan (si futur client OVH).
As-tu regardé du coté SQL Plan de chez OVH d'ailleurs (non non je leur fait pas de la pub). Je n'ai jamais trop regardé, mais normalement ces plans sont fait pour ça.

 

[benjiman]

Bonjour !

Concernant le cache, le probleme, c'est qu'il faut générer toutes les pages de tous les comptes (et vu que l'on fait en ce moment presque du 2 nouvelle inscriptions/minute ...)

Pour OVH, oui, c'est bien le cas, je suis un client OVH (pour un Kimsufi et un nom de domaine) et un futur client (pour le SP).

Le probleme c'est que j'ai bien fouiné dans my.cnf :
"key_buffer = 400M
max_allowed_packet = 160M
thread_stack = 1M
join_buffer_size=100M
sort_buffer_size=100M
table_cache=1500
tmp_table_size=100M
key_buffer_size=400M
#
# * Query Cache Configuration
#
query_cache_limit = 20M
query_cache_size = 204M
query_cache_type = 1" ...
Avec 1Go de Ram, msql n'tilise que 3.4% de sa mémoire ! (c'est absurde quand j'y pense mais c'est ce que je vois sur mon TOP)
"2954 mysql 15 0 736m 88m 4196 S 83.2 8.9 625:39.41 mysqld"

Mes logs sont désactivés sauf mes logs de long-queries (pou voir si ce qui bug ...)

Je n'ai pas trouvé le "SQL Plan" de chez OVH ... ca me serait surement utile !


Merci beaucoup pour votre attention !
PS: Mysql4 utilise t-il les 2 coeurs ?

 

[Julia41]

Pour le SQL Plan, ils ne doivent plus le faire...
Tu dis "PS: Mysql4 utilise t-il les 2 coeurs ?"
J'en conclus que tu es en version 4, mais as-tu tenté une version 5 ? Ca pourrait peut-être améliorer les performances, je n'ai pas étudié de Bench concernant ces 2 versions de MySQL.

Sinon je trouve tes valeurs ENORME...
J'ai des valeurs nettement plus basse, vraiment plus basse mais bon, moi mon serveur SQL, n'a qu'un enregistrement énorme toutes les 3 heures...

key_buffer              = 32M
max_allowed_packet      = 4M
thread_stack            = 128K
thread_cache_size       = 8
max_connections         = 100
table_cache             = 64
thread_concurrency      = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 8M
query_cache_size        = 64M
[mysqldump]
quick
quote-names
max_allowed_packet      = 32M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 8M

Copié un peu à l'arrache, mais je trouve tes valeurs, énorme de chez énorme...

Edit: Pour MySQL V4 et Double Coeur, aucune idée

 

[benjiman]

Mysql5 et PHP4 ne font pas très souvent bon ménage ...

Concernant les données de my.cnf ...
C'était ma seule solution pour qu'il utilise un max le cache ! (je vous dis il utilise moins de 10% de la mémoire de RAM, meme le swap total du serveur n'est pas utilisé !)
Et j'ai entré ces données par rapport à la demande qu'il y a écrit sur Phpmyadmin...

Et encore, je pense que ces valeurs sont trop basses...

En tout cas, vraiment, merci pour vos réponses .

 

[julienr]

salut,
deux choses :
si vraiment tu as l'impression que mysql n'utilise pas tes indexes alors tu peux les lui spécifier avec 'use index' voir 'force index'
Ensuite si tu lis et tu écris dans les mêmes tables, c'est normal que cela coince car pour écrire mysql vérouille toute la table
en conclusion c'est pas sur que ton nouveau serveur améliore les choses, il faut revoir l'organisation de ta base, monter une réplication par exemple, le master pour les ecritures et les slaves pour les lectures...

 

[benjiman]

Ahhhh !!
Je ne savais pas en effet que Mysql bloquait l'ensemble de la table pour mettre à jour ou pour inserer ...
je pensais que c'était le cas que pour quelques lignes...

Sinon, pour force index, je vais voir ...

De toutes les manieres, il faut que je sépare apache et Mysql pour laisser + de processeurs à Apache...
Et je pense également qu'un serveur mysql bien plus rapide diminuera le temps de blocage des tables

En tout cas, je te remercie pour ces indications ...
je vais essayé de voir ce que je peux faire avec un systeme de réplication ...

 

[Vld44]

fait voir une requête stp (qui rame)

Et fait voir ton modèle aussi (twè twè impoltant)