Consultez la formation SEO spéciale Wordpress
par WebRankInfo / Ranking Metrics

Messages: 2765

Enregistré le: 14 Aoû 2002

Message le Mar Juin 16, 2015 11:00

Bonjour

Mon problème, est les requêtes en lecture avec critère(s) et index.

Depuis le début de mon site ( voir profil ), je suis fidèle à MySQL, format IsAM.

MySQL, semble traiter les index de cette façon :

Il prend ( suivant les statistiques d'utilisation ), l'index dont la cardinality est la plus faible.

Moi, j'en suis réduit à avoir deux index multiples séparés ( entre autres ) sur la table COURSES :

IX_NUMCRS(NUMCRS) NUMCRS est le numéro d'index des lignes d'une course ( plusieurs lignes par course. )

IX_NUMCH(NUMCH) NUMCH idem pour celui d'un cheval ( Table : CHEVAUX(NUMCH PRIMARY KEY, NOMCH) en gros ).

Mais... J'ai des requêtes MySQL faisant intervenir ces deux index, ou bien l'un ou l'autre. Là est le hic.

Si je met un index PRIMARY KEY(NUMCRS, NUMCH) ( c'est possible ), et supprime donc le deuxième index ( IX_NUMCH ) ci-dessus, la charge CPU explose à plus de 100% ( d'après la commande "top" de Debian ).

Il y a donc un problème récurrent d'utilisation des index par MySQL.

D'autre part :

Avec ces deux index ci-dessus ( séparés ), des requêtes en lecture avec ces deux critère ( WHERE et/ou GROUP BY ou ORDER BY ), utilisent un seul index ( caractéristique de MySQL ).

D'où mon besoin éventuel, de changer de SGBD ( autre que MySQL, qui puisse gérer plusieurs index en même temps ).

A la limite je laisse ces deux index ci-dessus, là le seul problème, est que mon site a plus que 1500 visites/jour ( en général ), et que MySQL fait un verrouillage ( locking ) de tout le contenu des tables à la lecture ( il me semble ).

Ceci parce que le moteur de mes tables est : IsAM, et non pas InnoDB, qui lui met des locks au niveau row.

Je ne fais aucune utilisation de glob et fulltext et autres cochonneries, donc je pourrais convertir toutes mes tables en mode InnoDB.

Mais... Si je fais celà, est-ce que ma charge CPU ( ou bien espace RAM ) ne va-t-elle pas exploser, pour cause de requêtes simultanées, alors qu'actuellement les requêtes ( à cause des locks ) se font plus ou moins en suivant ?

Mon MySQL est la version 5.5 sous Debian Wheezy ( j'ai un VPS OVH Classic 4, 8Go RAM, CPU 4 coeurs je crois ).

Donc.. Que faire : Convertir en InnoDB pour gérer les accès concurrents, ou choisir quel autre SGBD ?

J'ajoute... Que mes requêtes sont en lecture.

Merci beaucoup de vos réponses par rapport au choix :

1- IsAM ou InnoDB, ou

2- MySQL ou quel autre SGBD ?

Respectueusement.
Haut
7 Réponses
Messages: 3259

Enregistré le: 7 Sep 2003

Message le Mar Juin 16, 2015 11:25

ortolojf a écrit:D'où mon besoin éventuel, de changer de SGBD ( autre que MySQL, qui puisse gérer plusieurs index en même temps ).


J'ai lu en travers mais je trouve normal qu'on puisse avoir plusieurs indexes sur une même table, mais qu'ils soient gérés en même temps au sein d'un même requête me parait saugrenu. Il faudrait sans doute revoir les requêtes pour que le bon index soit pris
Haut
Messages: 507

Enregistré le: 23 Fév 2013

Message le Mar Juin 16, 2015 13:15

ortolojf a écrit: 1- IsAM ou InnoDB


IsAM c'est le passé, il faudrait toujours être en InnoDB, ne serait-ce que pour gérer les clés étrangères, depuis MySQL 5.5 le moteur par défaut est InnoDB. Ca ne répond pas à la question mais c'est une best practice.

2- MySQL ou quel autre SGBD ?


S'il faut changer, je pense que ProgreSQL a une bonne réputation (et c'est gratuit comme MySQL).
Haut
Messages: 8555

Enregistré le: 14 Mai 2003

Message le Mar Juin 16, 2015 13:31

C'est ce que je suis en train de faire: MySQL => PostgreSQL. Les datatypes supplémentaires (Hstore, Array, ...) sont super intéressants.
(et aussi PHP => Python, Apache => Nginx, MySQL Fulltext => ElasticSearch, Redis, Gunicorn, ...)
Haut
Messages: 2765

Enregistré le: 14 Aoû 2002

Message le Mar Juin 16, 2015 16:39

indigene a écrit:
ortolojf a écrit:D'où mon besoin éventuel, de changer de SGBD ( autre que MySQL, qui puisse gérer plusieurs index en même temps ).


J'ai lu en travers mais je trouve normal qu'on puisse avoir plusieurs indexes sur une même table, mais qu'ils soient gérés en même temps au sein d'un même requête me parait saugrenu. Il faudrait sans doute revoir les requêtes pour que le bon index soit pris


Bonjour Monsieur

J'ai un autre index : IX_DATECRS_REUNION_COURSE(DATECRS, REUNION, COURSE), pour trouver les enregs ( une seule valeur NUMCRS ), qui correspond à la Course ayant eu lieu le DATECRS, de Réunion REUNION et numéro de Course : COURSE. Cet index n'impacte pas le CPU ni la mémoire RAM semble-t-il.

Le problème, est que pour la table COURSES je peux avoir des requêtes ( SELECT ), aussi bien sur le critères NUMCRS seul, que NUMCH seul, ou bien NUMCRS et NUMCH en même temps.

Si je ne met que le PRIMARY KEY(NUMCRS, NUMCH) , cette dernière requête et est optimisé, mais les autres ne le sont pas ( accès séquentiel ).

C'est sans doute celà qui fait exploser le CPU dans cette configuration ( mais je ne suis pas sûr ).

Quand j'ai deux index séparés ( IX_NUMCRS et IX_NUMCH ), le CPU est ( très ) fiable, mais quand la requête a lieu sur les deux critères, aucun index ( je crois ) n'est pris, et la requête est séquentielle.

Dans la requête sur NUMCRS et NUMCH, je suis obligé d'avoir les deux critères, pour sélectionner les chevaux de la course. Pourquoi ? Parce qu'il y a une condition limite sur NUMCRS, et parce que le résultat doit être trié par NUMCRS ( ORDER BY NUMCRS ), c'est-à-dire en ordre chronologique.

Et... Si je met les trois index : IX_NUMCRS_NUMCH, IX_NUMCRS et IX_NUMCH, le mécanisme de choix de MySQL pour les index, fait que MySQL choisit le mauvais index ( à cause de la cardinality ).


Est-ce que postgreSQL, gère les sélection sur plus qu'un seul index à la fois ?

Celà me permettrait de n'avoir que IX_NUMCRS et IX_NUMCH ( pas l'index combiné ), et laisser PostgreSQL combiner les deux index ?

Si çà n'est pas possible, actuellement les temps de réponse sont en dessous de 2 secondes pour toutes les requêtes ( sans index combiné ), et d'autre part j'ai fait un cache "soft" avec des fichiers quotidiens texte temporaires, qui mémorisent les résultats des requêtes ( deux fichiers par course ).

Mais quand même, pour toute course ancienne accédée pour la première fois, le Speed Insight de Google me met dans les 4 secondes pour l'accès serveur... ;(

Et... Pour la même course déjà vue, celà tombe à de 0,25 à 0,45 seconde à peu près ou moins.

Le problème conceptuel, est qu'il peut y avoir jusqu'à 20 chevaux par course.

J'ai été obligé de tenir compte de cette problématique, au moment d'élaborer la structure de mes tables MySQL.

Je vais me renseigner à propos de postgreSQL. ;)

Je n'ai pas besoin de types géniaux de données, mais surtout d'accès concurrents et rapides.

Merci beaucoup de vos réponses.

Respectueusement.
Haut
Messages: 9031

Enregistré le: 22 Nov 2003

Message le Mar Juin 16, 2015 20:20

ortolojf a écrit: A la limite je laisse ces deux index ci-dessus, là le seul problème, est que mon site a plus que 1500 visites/jour ( en général ),

> on se rencontre sur WRI depuis plus de 10 ans mais honnêtement 1500 visiteurs par jours n'est pas forcément un monstre de visite.

Tu cherche les petites bêtes :wink: Au lieu de travailler sur des clés primaires, travaille sur les requêtes (en supprimant dans ="SELECT * FROM le * juste les champs à utiliser: crée des tables séparées ... avec des liaisons.


Et au final: ton site (et application) semble pas franchement lourd ... essaye un autre hébergement plus efficace.
Haut
Messages: 2765

Enregistré le: 14 Aoû 2002

Message le Mar Juin 16, 2015 21:44

Bonjour ybet ;)

En fait.. MySQL peut effectivement ( dans certains cas ), utiliser deux index séparés en même temps.

Je crois que le site MySQL 5.0 Manual appelle celà : select merge.

Moi, j'ai deux index séparés ( entre autres ), sur la table COURSE :

IX_NUMCRS(NUMCRS) , et IX_NUMCH(NUMCH).

Je sais maintenant, qu'on peut imposer l'utilisation d'un index ( ou plusieurs ) dans un SELECT, avec l'instruction : USE INDEX (nom_de_l_index).

Dans mon cas, je cherche à faire ceci :

Code: Tout sélectionner
  SELECT donnee1, donnee2, ... FROM COURSES WHERE NUMCH=$numch AND NUMCRS<$numcrs ORDER BY NUMCRS;



Mais... L'optimiseur foirait et me parcourait toutes les lignes de ma table COURSES.

Je sais qu'à partir de la version : MySQL 5.1.17, le ou les index spécifiés par USE INDEX, jouent aussi pour les ORDER BY ou GROUP BY.

Donc, je vais provoquer l'utilisation de ces deux index et faire :
Code: Tout sélectionner
  SELECT donnee1, donnee2, ... FROM COURSES USE INDEX (IX_NUMCH, IX_NUMCRS) WHERE NUMCH=$numch AND NUMCRS<$numcrs ORDER BY NUMCRS;


On va voir ce que celà va donner... après avoir updaté pour MYSQL >= 5.1.17.

Je vais investiguer. ;)

Merci beaucoup de ton aide.

Respectueusement.
Haut
Messages: 2765

Enregistré le: 14 Aoû 2002

Message le Mar Juin 16, 2015 23:00

Bon

Finalement, après vérification, il s'avère que l'optimiser utilise réellement l'index IX_NUMCH ( plus sélectif et de loin ), plus un petit filesort sans table temporaire de quelques dizaines de rows au maximum.

Il n'y a donc rien à changer dans ces requêtes.

Je n'ai plus qu'à fixer le critère ( trop imprécis pour l'instant ), sur la date DATECRS, de quelques scripts que j'ai repérés.

Le critère actuel est du type : SELECT MIN(NUMCRS) FROM COURSES WHERE DATECRS>valeur.

Je vais utiliser un critère d'égalité, au lieu de l'inégalité, ce qui va éviter d'avoir des timing > 2 secondes. ( rares ).

Normalement, problème résolu, j'ai éliminé tous les SELECT JOIN la semaine dernière.

Merci beaucoup de ton aide.

Respectueusement.
Haut

Formation recommandée sur ce thème :

Formation SEO spéciale Wordpress : apprenez à optimiser le référencement naturel d'un site fait avec Wordpress... Formation Ranking Metrics animée par un expert SEO / Wordpress.

Tous les détails sur le site Ranking Metrics : programme, prix, dates et lieux, inscription en ligne.