Journal Comparatif performances PHP : MySQL / PostgreSQL / SQLite

Posté par  .
Étiquettes :
0
27
nov.
2005
La dépêche parue il y a quelque temps concernant PostgreSQL m'a vivement intéressé (cf. http://linuxfr.org/2005/11/09/19872.html ). Une partie de la discussion a porté sur la qualité de ce SGBDR au sein d'un environnement web. C'est pourquoi j'ai voulu - à ma petite échelle - tenter un comparatif de performance avec PHP comme langage. Ayant sous la main une base utilisée en production sous MySQL 5.0.16, j'en ai extrait trois tables faisant sens entre elles et je les ai injectées dans PostgreSQL et SQLite. J'ai bien entendu recréé les index présents sur chaque table de la base d'origine. J'ai ensuite créé un script php testant les unes après les autres différentes requêtes par ordre croissant de 'complexité' avec à chaque fois l'envoi sur chaque moteur de la même requête. J'ai relancé plusieurs fois la page à coup de Ctrl + R répétés à intervalles variables pour m'assurer que les résultats étaient constants.

Informations matérielles et logicielles :

Plateforme matérielle :
- Pentium 4 3.2Ghz
- 1 Go RAM

Plateforme logicielle :
- Linux 2.6.12-12mdksmp #1 SMP Fri Sep 9 17:43:23 CEST 2005 i686 Intel(R) Pentium(R) 4 CPU 3.20GHz GNU/Linux
- PHP 5.0.5 compilé
- Apache 2.0.55 compilé
- MySQL 5.0.16 compilé
- Postgres 8.1 compilé

Volumétrie de la base (en nombre d'enregistrements) :

Table personne : 50946
Table structure : 3330
Table structure_type : 10

Vous trouverez le schéma de la base, les requêtes exécutées et les résultats à la page :
http://docteur.cosmos.free.fr/tests.html


Notes :
- utilisation de connexions persistantes
- tables en MyIsam pour MySQL
- les temps d'exécution sont obtenus avec le code suivant (repiqué de
phpmyadmin) :
list($usec, $sec) = explode(' ',microtime());
$querytime_before = ((float)$usec + (float)$sec);
// appel de pg_query ou mysql_query ou sqlite_query selon
list($usec, $sec) = explode(' ',microtime());
$querytime_after = ((float)$usec + (float)$sec);
$duree = sprintf ('%01.4f sec.', ($querytime_after - $querytime_before));

Quelques commentaires

Les résultats sont assez surprenants.
On aurait pu penser de prime abord qu'une API était plus aboutie qu'une autre mais les performances sont vraiment disparates... Voir par exemple la requête 6 en faveur de MySQL) ou encore la requête 1 (en faveur de PostgreSQL) avec des différences notables dans le temps d'exécution.

Noter par ailleurs la requête 7 que PostgreSQL ne peut pas exécuter (restriction sur les colonnes ramenées et le Group By).

Enfin, tout ça c'est bien sûr du mono utilisateur...
  • # Hmm

    Posté par  . Évalué à -2.


    - PHP 5.0.5 compilé
    - Apache 2.0.55 compilé
    - MySQL 5.0.16 compilé
    - Postgres 8.1 compilé


    Parce que t'arrive à les faire tourner en interprété ? :)
    • [^] # Re: Hmm

      Posté par  . Évalué à 3.

      je suppose qu'il voulait dire qu'il les as compilés lui meme et n'as pas utilisé les binaires precompilés fournis par sa distriution (visiblement mandrake).
      • [^] # Re: Hmm

        Posté par  . Évalué à 3.

        Exact.
        C'est pour donner l'égalité des chances aux binaires.
  • # SQLite

    Posté par  . Évalué à 2.

    Il y a un petit moment j'ai fait un truc en PHP qui avait besoin d'une base de données, j'ai choisi SQLite mais en fait il a carrément fallu que je passe à MySQL parce que sur UN truc, SQLite était vraiment très lent : si je me rappelle bien c'était pour faire à la fois un order by et un limit sur beaucoup de résultats.

    Par contre, ça allait assez vite avec sqlite3 chez moi (PHP en est toujours à la version 2, c'était peut-être le problème).

    Je suis plus vraiment sûr que c'était à cause de l'order by + limit, je vais revoir ça, mais c'était quelque chose dans le genre.
  • # umount

    Posté par  (site web personnel) . Évalué à 2.

    J'ai relancé plusieurs fois la page à coup de Ctrl + R répétés à intervalles variables pour m'assurer que les résultats étaient constants.


    L'idéal étant de monter et démonter la partition contenant les tables, ou faire ça d'une autre station.
    Pour information, as-tu activé le cache pour les requêtes dans MySQL ?
    • [^] # Re: umount

      Posté par  . Évalué à 1.


      L'idéal étant de monter et démonter la partition contenant les tables, ou faire ça d'une autre station.


      Peux-tu m'expliquer pourquoi ?
      J'ai également relancé la page après avoir éteint puis relancé les serveurs.


      Pour information, as-tu activé le cache pour les requêtes dans MySQL ?


      Oui, les caches sont actifs, mais ça l'est par défaut non ?
  • # sympa mais ...

    Posté par  . Évalué à 1.

    un petit test bien sympatique mais faire autre chose que des select ca aurait été mieux je trouve ( ceci dit c'est deja bien d'avoir fait ca )
    P.S: n'ignoré plus INSERT et UPDATE ou meme CREATE TABLE ...



    ;)
    • [^] # Re: sympa mais ...

      Posté par  . Évalué à 1.

      Les requêtes SELECT sont les plus simples à écrire... et sont exécutables à volonté !

      Mais si tu veux me proposer des scripts SQL contenant des ordres INSERT ou UPDATE sur ce schéma de base, je suis preneur (n'oublie pas les ordres inverses pour remettre tout en état ! ;-)).
    • [^] # Re: sympa mais ...

      Posté par  (site web personnel) . Évalué à 4.

      [...] a porté sur la qualité de ce SGBDR au sein d'un environnement web.


      En environnement WEB les SELECT représente tout de même la majorité des types de requêtes effectuées !
      • [^] # certe

        Posté par  . Évalué à 1.

        je dit pas le contraire mais si on veut avoir un test complet ( ce n'est peut etre pas le but mais bon) faut inclure les autre requete

        bon apres c'est surtout une question de ce que l'on veut tester.
    • [^] # Re: sympa mais ...

      Posté par  (site web personnel) . Évalué à 1.

      En plus, une petite remarque sur la syntaxe SQL.

      Il est bien ( recommandé ) de faire les jonture avec la clause JOIN et non dans la clause WHERE, d'ailleurs PostGreSQL peut s'avérer plus performant avec la syntaxe JOIN.
  • # Un bench sur le meilleur et pas sur le moindre

    Posté par  . Évalué à 4.

    Pourquoi ne pas plutôt faire des benchs en prennant en compte des situations plus réalistes et qui tirent partie des atouts des sgbd ?

    Par ex on prend un table entête reliée à une 50aine de tables, lorsqu'on supprime un enregistrement de l'entête il faut d'une part que tous les enregistrements liés soit supprimés, et d'autre part que soit tous soient supprimés soit aucun pour garantir l'intégrité des données.
    Avec mysql il va falloir faire autant de requetes que de tables liés et faire toute une gymnastique pour faire l'équivalent d'une transaction. Alors qu'avec postgresql une seule requete suffira, toute la machinerie sera exécuté côté serveur sans pour autant bloquer d'autres utilisateurs (contrairement à sqlite) ni surcharger le réseau (la bdd étant bien entendu sur une autre machine)...
    • [^] # Re: Un bench sur le meilleur et pas sur le moindre

      Posté par  (site web personnel) . Évalué à 1.

      Je trouve son test très pertinent.

      Une appli web n'utilie généralement que très peu de tables. Il suffit pour s'en convaincre de regarder des applis tel que dotclear, spip, b2evolution, *wiki, etc.

      C'est ton cas qui n'est pas réaliste avec une cinquantaine de tables à mettre à jour a chaque opérations. Je soupçonne meme ta base de données d'être un vrai foutoir !
      On s'en branle des transactions puisque le but et de tester ces bases de données dans le cadre d'une appli web justement ! Faudrait voir à cesser de jouer les master of the universe of database, en alignant des mots savants et hors contexte juste pour essayer de briller.


      Ce que je retire de ce test ce sont les excellente performance générale de sqllite et que sur certaines requetes postgresql est 10 fois plus lent que les 2 autres ! MySQL semble conserver son titre de base de données la plus rapide.
      Bien sur cela n'empechera pas les esprits chagrins de pinailler, voire troller, et bien qu'il fasse comme doctorcosmos, et qu'ils alignent les chiffres de leur propres tests.
      • [^] # Re: Un bench sur le meilleur et pas sur le moindre

        Posté par  . Évalué à 7.

        On s'en branle des transactions puisque le but et de tester ces bases de données dans le cadre d'une appli web justement !

        Hébé.
        Y'a pas besoin de faire des grosses appli pour avoir besoin de transactions. Et faire des mises à jour sur plusieurs tables en utilisant une transaction pour garantir l'atomicité du tout, c'est pas etre le master of the universe of database, c'est juste etre un developpeur.
      • [^] # Re: Un bench sur le meilleur et pas sur le moindre

        Posté par  . Évalué à 4.

        Si tu as très peu de tables, uniquement en lecture seule et que tu recherche la perf à tout prix ce qui est irréaliste c'est d'utiliser un sgbd, et encore moins des requêtes à base de like %... Pis j'ose espérer que les applis dont tu parles utilisent un système de cache sinon c'est pas sympa de maltraiter des sgbd comme ça qui n'ont fait de mal à personne.

        Et dans ce cas postgresql n'a vraiment rien à foutre là, pourquoi pas comparer mysql avec fseek tant qu'on y est ;-)

        Je brille bien là ou faut que je frotte encore ?
    • [^] # Re: Un bench sur le meilleur et pas sur le moindre

      Posté par  . Évalué à 5.

      La fonctionnalité de suppression en cascade fonctionne avec MySQL, la seule contrainte étant de passer les tables en InnoDB.

      Il suffit d'avoir le même index dans les deux tables et de les lier avec une requête du style : ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte FOREIGN KEY (nom_index) REFERENCES table(nom_index) ON DELETE CASCADE;

      je suis peut-être hors sujet...

      Bonne journée.

      Je trolle dès quand ça parle business, sécurité et sciences sociales

  • # Un test complémentaires?

    Posté par  (site web personnel) . Évalué à 1.

    Il serait intéréssant de voir un test sur la facon donc les différents SGBD surpporte une charge importante...Plusieurs connection, plusieurs requètes "en même temps"(je ne suis peux être pas clair...)

    Bon je n'ai aucune idée de comment réaliser ce genre de test, mais bon ca pourrait être sympa :p
  • # il manque certaines informations ...

    Posté par  (site web personnel) . Évalué à 5.

    il aurait été sympa d'executer plusieurs fois ( ~1000 ) dans une boucle 'for'.

    car si tu ne fais qu'une requete tu risque d'avoir le cout ouverture fermeture comme preponderant.

    donc, un "for/while" avec un chti print "." pour maintenir la connexion ouverte ( ou alors un timeout > 3600 sur ton apache ), pourrait te founir des resultats plus interessant.

    dans ses resultats, il te faudrait :
    - min temps
    - max temps
    - moyenne temps
    - median temps ( temps à la position 500/501 apres tri des 1000 temps obtenu )
    - 1ier et 3ieme quartiel ( temps à la position 250/251 et 750/751 apres tri des 1000 premiers )

    de plus, pense à bencher les temps de connection ( donc connection dans la boucle avec un select 1; comme requete.

    puisque tu fais cela en PHP, tu pourras paralleliser tes tests avec 2, 3, 4, 5, 10 wget en parallele ( /!\ par contre attention a l'analyse des resultats. )
    • [^] # Re: il manque certaines informations ...

      Posté par  . Évalué à 1.

      Je n'ai jamais dit que j'étais exhaustif (loin de là !).


      car si tu ne fais qu'une requete tu risque d'avoir le cout ouverture fermeture comme preponderant.


      Je ne vois pas le problème puisque je teste la performance du SGBDR au travers de PHP et non le moteur seul.
      De plus j'utilise des connexions persistantes.

      Cela dit, l'idée de la boucle est intéressante. Je vais tâcher de vous proposer un second benchmark basé là-dessus.
      J'utiliserai un processus php en mode console.


      puisque tu fais cela en PHP, tu pourras paralleliser tes tests avec 2, 3, 4, 5, 10 wget en parallele ( /!\ par contre attention a l'analyse des resultats. )


      Je vois pas trop, tu m'expliques ?
      • [^] # Re: il manque certaines informations ...

        Posté par  (site web personnel) . Évalué à 3.

        il n'y a aucun reproche ... je te proposais juste de completer ton etude.

        en retournant regarder sur ton site, si il y avait un lien vers le source PHP ... je viens de voir que pour un bench BDD il manquait aussi certaines requetes à comparer si tu veux etre plus exhaustif.

        si cela t'interesse, ces requetes sont de la serie " select 1", "select *", "select * where id=1", "select * where id like '%'", "insert", "delete", "update", ...

        pour ce qui est du probleme de parallelisation :
        par exemple, si tu travailles sur 10 tests de 10 lots en paralleles de 1000 requetes, tu peux considerer aussi bien cela comme un ensemble de 100000 requetes, ou comme 100 ensembles de 1000 requetes ( et la c plus couillu comme stats ). cela depend de ce que tu recherches comme resultats.
        • [^] # Re: il manque certaines informations ...

          Posté par  . Évalué à 1.


          " select 1", "select *", "select * where id=1", "select * where id like '%'"


          Je ne vois pas bien ce que ces requêtes apportent...


          par exemple, si tu travailles sur 10 tests de 10 lots en paralleles de 1000 requetes, tu peux considerer aussi bien cela comme un ensemble de 100000 requetes, ou comme 100 ensembles de 1000 requetes ( et la c plus couillu comme stats ). cela depend de ce que tu recherches comme resultats.


          Je suis tout à fait d'accord, mais comment puis-je lancer ces traitements 'parallèles' ?
  • # Syntaxe de la requête SQL

    Posté par  . Évalué à 1.

    Noter par ailleurs la requête 7 que PostgreSQL ne peut pas exécuter (restriction sur les colonnes ramenées et le Group By)


    Il me semblait que seules les colonnes présentes dans une clause GROUP BY pouvaient apparaître dans le SELECT associé (ou alors il faut utiliser des fonctions d'aggrégation).

    En effet dans ta requête :

    SELECT denomination,count(*) AS nbre
    FROM personne A, structure B
    WHERE A.id_structure=B.id_structure
    GROUP BY B.id_structure
    ORDER BY nbre DESC,denomination ASC


    que renvoient les autres SGBD pour la colonne dénomination (puisqu'elle n'est pas groupée, il y a plusieurs valeurs possibles par ligne) ?
    • [^] # Re: Syntaxe de la requête SQL

      Posté par  . Évalué à 1.

      C'est une limitation - à mon sens - des SGBDR proprio et de PostgreSQL (je l'ai découvert à l'occasion de ce test), car même si le champ 'denomination' n'est pas dans le Group By, le champ 'id_structure' étant une clef primaire, le libellé associé sera lui-aussi pertinent et te permettra d'identifier le comptage effectué.

      Le résultat pourrait être effectivement difficile à interpréter si tu avais dans la table 'structure' des tuples du genre :

      id_structure | denomination

      1 | Boite bidulle
      2 | Boite machin
      3 | Boite bidulle
      etc. ...

      Mais là c'est un problème de 'signification' des données.
      Je ne vois donc pas pourquoi les autres SGBDR ne permettent pas cette interrogation.

      Mais peut-être y a-t-il des spécialistes de cette question dans l'assistance ?
      • [^] # Re: Syntaxe de la requête SQL

        Posté par  . Évalué à 3.

        Dans mon bouquin de SQL, il est également écrit que tous les champs du select doivent se retrouver dans le group by à moins d'être aussi dans une fonction d'aggrégation

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.