Quelle base de données/format tables pour ce type d'application ?

Discussion dans 'Développement d'un site Web ou d'une appli mobile' créé par ortolojf, 16 Juin 2015.

  1. ortolojf

    ortolojf WRInaute accro

    Inscrit:
    14 Août 2002
    Messages:
    2 757
    J'aime reçus:
    0
    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.
     
  2. indigene

    indigene WRInaute accro

    Inscrit:
    7 Septembre 2003
    Messages:
    3 247
    J'aime reçus:
    0
    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
     
  3. rick38

    rick38 WRInaute impliqué

    Inscrit:
    23 Février 2013
    Messages:
    536
    J'aime reçus:
    0
    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.

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

    spout WRInaute accro

    Inscrit:
    14 Mai 2003
    Messages:
    8 663
    J'aime reçus:
    2
    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, ...)
     
  5. ortolojf

    ortolojf WRInaute accro

    Inscrit:
    14 Août 2002
    Messages:
    2 757
    J'aime reçus:
    0
    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.
     
  6. ybet

    ybet WRInaute accro

    Inscrit:
    22 Novembre 2003
    Messages:
    9 059
    J'aime reçus:
    0
    > 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.
     
  7. ortolojf

    ortolojf WRInaute accro

    Inscrit:
    14 Août 2002
    Messages:
    2 757
    J'aime reçus:
    0
    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:
      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:
      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.
     
  8. ortolojf

    ortolojf WRInaute accro

    Inscrit:
    14 Août 2002
    Messages:
    2 757
    J'aime reçus:
    0
    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.