Nous travaillons actuellement avec JavaEE et MySQL 5 dans notre entreprise, mais nous avons certaines requêtes, en particulier les requêtes de suppression qui prennent > 10 min à exécuter. Nous envisageons de passer à PostgreSQL.
Quels sont les avantages de PostgreSQL par rapport à MySQL, s'il y en a ? Avez-vous de l'expérience avec les deux bases de données et pouvez-vous me dire si c'est une bonne idée ou si cela dépend entièrement des besoins de notre serveur ?
Nous utilisons MySQL dans mon entreprise et nous avons examiné PostgreSQL (qui fonctionne sur un petit système, à titre de test). Ils ont tous deux leurs avantages et leurs inconvénients. Notez que pour les besoins de cette discussion, je fais référence à MySQL 5.0.x avec InnoDB. Bien que la version 5.1 puisse corriger certains de ces problèmes, elle n'est pas encore stable.
MySQL - Les bons points
Le meilleur atout de MySQL est probablement qu'il est si commun. Comme il fait partie de LAMP, tout le monde et son frère le propose en option. Il fonctionne sous Windows, Linux et tout autre système. Il est impossible de frapper un hébergeur sans que MySQL soit disponible. Il y a des tonnes de personnes qui peuvent vous donner des conseils à ce sujet.
Je préfère les outils (en fait, le client en ligne de commande). Il me semble assez convivial. Vous voulez voir les tables d'une base de données ? SHOW TABLES. Vous voulez voir les bases de données ? SHOW DATABASES. Vous voulez voir l'état de votre réplication par rapport au maître ? SHOW SLAVE STATUS. PostgreSQL ressemble un peu à Oracle pour moi. Il n'y a pas de SHOW TABLES, c'est \dt (IIRC). Pour quitter, ce n'est pas QUIT ou EXIT, c'est \q.
La réplication dans MySQL est plutôt agréable. Elle est intégrée (PostgreSQL n'avait pas de réplication intégrée jusqu'à récemment). La dernière fois que nous avons passé beaucoup de temps à chercher (l'année dernière), la réplication PostgreSQL était basée sur des triggers, ce que nous avons trouvé un peu douteux (en théorie). La réplication MySQL a quelques limitations (elle doit utiliser InnoDB, elle est basée sur les déclarations et non sur les données (changements dans la version 5.1, je crois)), mais elle a bien fonctionné pour nous. Il peut faire plusieurs maîtres, plusieurs esclaves, des chaînes. Encore une fois, c'est une quantité connue, puisque MySQL est si commun.
MySQL - Points faibles
MySQL a quelques très grave limitations. Il est très important que vous comprenez ce dans quoi vous vous engagez.
La plus importante, celle qui nous tue, est l'impossibilité d'ajouter ou de supprimer des colonnes ou des index sans verrouiller la table. Nous avons des tables avec des dizaines de millions d'enregistrements. Nous ne pouvons pas les modifier. L'ajout d'une nouvelle colonne ou d'un nouvel index verrouille la table en lecture et en écriture, ce qui est fatal pour nous. Nous ne savons pas combien de temps cela prendrait, mais ce serait des heures, au minimum. Lorsque nous avons dû ajouter une nouvelle colonne l'année dernière, nous l'avons fait en créant une nouvelle table et en faisant toujours une jointure. C'était la seule façon de faire les choses sans retirer le serveur de la production (ou en passant par un grand désordre puisque nous utilisons la réplication).
MySQL peut parfois être très stupide avec les index. Il est important de lancer un DESCRIBE ou un EXPLAIN sur les index pour voir s'ils font quelque chose de sensé. Nous avons parfois dû utiliser FORCE INDEX pour obtenir de bonnes performances. Ceci est aggravé par le fait qu'il ne peut pas utiliser plusieurs index (pour la plupart). Si vous voulez qu'il utilise votre index sur la colonne date et votre index sur la colonne email, vous devez créer un index sur les deux colonnes. Ce problème est censé être corrigé/amélioré dans la version 5.1 (je pense, je n'ai pas testé la version 5.1).
Les sous-requêtes peuvent également poser un gros problème. Une requête peut fonctionner correctement. Une sous-requête peut fonctionner sans problème. Mais lorsque vous arrivez à trois niveaux de requêtes (ou plus), MySQL peut (et le fait généralement) abandonner. Alors, au lieu de faire les choses intelligemment (même si votre sous-requête est aussi simple qu'un "SELECT id FROM table WHERE id = '5'" constant), MySQL commencera à exécuter cette requête pour chaque ligne, tuant les performances. Encore une fois, vous devez utiliser EXPLAIN / DESCRIBE.
Les messages d'erreur de MySQL sont pires, ce qui n'est qu'un détail. Si vous essayez de créer une table avec une clé étrangère et que vous vous trompez, le système affiche simplement une erreur 150. Il ne vous dit pas quel est le problème (généralement des définitions de colonnes non concordantes), mais seulement l'erreur 150. Il faut aller chercher l'erreur pour voir que 150 est un problème de clé étrangère. D'autres messages d'erreur sont beaucoup plus utiles.
Ensuite, il y a le, eh bien, nous l'appellerons le bogue et la bizarrerie. Les performances de MySQL, du moins sous Windows et Linux, explosent avec plus de 4 (voire 8) processeurs. J'ai entendu dire que ce n'était pas un problème sur Solaris (grâce au travail de Sun et aux processeurs Niagara). C'est quelque chose dont vous devez être conscient. Je crois que les index doivent être conservés en mémoire pour InnoDB. Si vous voulez un index géant que vous n'utiliserez qu'une fois par semaine, tant pis. Cela peut avoir changé, ou être corrigé dans la version 5.1, je ne sais pas.
Vous rencontrez aussi des petites choses amusantes. Nous avons rencontré une condition impliquant des sous-requêtes et des jointures dans la version 5.0 qui fait que MySQL ne renvoie aucune donnée. Malgré ce que dit l'explication, ce que montrent les parties de la requête partielle, etc, vous obtenez zéro ligne. Vous modifiez un peu votre code et le bogue n'est pas atteint et vous obtenez vos données. Les TIMESTAMPs n'enregistrent pas les millisecondes, vous devez le faire à la main avec une autre colonne. Nous avons également rencontré une fois une situation où la commande de formatage de date pouvait faire planter MySQL (c'était à l'époque de la 4.0 ou 4.1). Vous devez simplement être conscient que de petites choses bizarres comme celles-ci peuvent survenir. Ai-je mentionné que j'ai entendu dire (par quelqu'un en qui j'ai vraiment confiance et qui a eu une expérience à ce sujet) que les procédures stockées / triggers pouvaient faire planter le serveur MySQL en 5.0 ? C'était plus tôt dans la branche 5.0 et c'est probablement corrigé maintenant... mais vous devez surveiller MySQL de près.
Cela devrait aller de soi, mais si vous optez pour MySQL, utilisez InnoDB. En fonction de votre configuration, ce n'est peut-être pas le moteur par défaut. Changez cela. Tout ce qui est bon sur MySQL est InnoDB. La réplication et les transactions ont toutes deux besoin d'InnoDB, MyISAM ne les a pas. Il existe d'autres moteurs de stockage, mais ils sont beaucoup plus spécialisés.
PostgreSQL - Bon et mauvais
Maintenant, je n'ai pas une tonne d'expérience avec PostgreSQL. Comme je l'ai dit, nous avons commencé à l'expérimenter. Le fait qu'il n'ait pas les limitations de MySQL est un gros avantage. La simple possibilité d'ajouter une colonne sur une grande table sans verrouiller la chose pendant un temps énorme serait formidable pour nous. PostgreSQL peut également utiliser des index multiples, ce qui, là encore, est un atout majeur. Les messages d'erreur que PostgreSQL renvoie peuvent être beaucoup plus informatifs que ceux de MySQL. Parfois, lorsque vous vous trompez dans une requête, au lieu de "ceci est impossible" ou "vous ne pouvez pas faire cela", vous obtenez quelque chose qui ressemble plus à "ceci est impossible à cause de X". Encore une fois, ce n'est que mon impression. En fait, PostgreSQL ressemble plus à un Oracle open source (une base de données adulte) que MySQL (qui ne ressemble pas vraiment à Oracle).
Les outils sont maintenant BEAUCOUP moins conviviaux, mais si vous venez d'un monde Oracle (ou probablement DB2), vous y serez habitué. Bien qu'ils nécessitent des commandes plus cryptiques (voir les outils MySQL, ci-dessus), ils fonctionnent très bien.
Il y a le problème de la réplication. Lorsque nous avons repris PostgreSQL cette année pour jouer avec un nouveau petit système, nous avons découvert qu'ils ont pris l'un des systèmes de réplication autrefois externes et qu'il a été placé dans l'arbre. C'est une très bonne chose, car la situation de la réplication était un gros problème pour nous. Auparavant, il y avait des petits trucs de tiers (basés sur des déclencheurs) avec lesquels on pouvait tenter sa chance, et il y avait des solutions de réplication payantes. Le fait d'avoir une solution intégrée est très appréciable.
Résumé
Mon conseil ? Bien que j'aie moins d'expérience avec ce système, si je devais commencer avec un nouveau système, je pense que j'opterais pour PostgreSQL. J'ai vu assez de bizarreries dans MySQL pour être prêt à l'essayer. Nous n'avons pas eu de problèmes avec lui jusqu'à présent, et je sais que beaucoup de gens l'utilisent. MySQL s'améliore rapidement. Il a gagné la réplication pour un moteur de stockage sur disque, des procédures stockées, un meilleur choix d'index, et il est devenu beaucoup plus strict pour ne pas autoriser des données manifestement mauvaises (comme la date '0000-00-00') ou des données qui violent une clé.
Le changement de fournisseur n'est pas une décision à prendre à la légère. Le simple fait de transférer les données posera un gros problème si vous n'avez pas été très strict en matière de validation dans le passé.
Mais le plus important, c'est d'essayer. Mettez en place un serveur de test et un rapide hack de la base de code pour faire fonctionner votre code. Introduisez quelques données et évaluez-les. Peut-être que ce ne sera pas beaucoup plus rapide que votre configuration actuelle et que cela ne vaudra pas la peine de s'y intéresser.
Pour votre mise à jour
En ce qui concerne la petite mise à jour que vous avez publiée, j'ai deux commentaires à faire. Pour les BLOBs, sont-ils stockés en dehors de la table ou en ligne ? Je sais qu'Oracle peut le faire, et je suppose que PostgreSQL aussi, mais je ne me souviens pas de MySQL. Cela pourrait être un avantage considérable.
Quant au stockage des PDF en BLOB dans les colonnes, d'après ce qu'on m'a appris, c'est un peu bizarre et cela pourrait causer votre problème de performance. Je n'ai pas une grande expérience dans ce domaine.
Avez-vous essayé de stocker les PDF dans une ou plusieurs autres tables et de ne stocker que les ID ? C'est-à-dire en les normalisant ? Cela pourrait résoudre vos problèmes de performance.
Ce ne sont que des coups de poignard dans le noir.
Je préfère Postgres . Cela s'explique principalement par le fait que Postgres est un peu plus performant avec les jointures et les sous-requêtes, qu'il dispose d'une excellente analyse explicative, qu'il présente moins de bizarreries, qu'il fournit des messages d'erreur plus jolis et qu'il dispose d'une meilleure ligne de commande que MySQL .
La base de données est très courante, notamment chez les développeurs web.
Une sous-requête avec une profondeur de 3 niveaux ne peut plus être optimisée par MySQL et est exécutée sur chaque ligne.
Il y a beaucoup de petites bizarreries qui sont gênantes, par exemple les champs de temps n'enregistrent pas les millisecondes, les sous-requêtes avec les jointures peuvent ne pas retourner de données, des crashs inexplicables peuvent se produire, etc.
C'est bien que vous puissiez moteurs de base de données d'échange sous ce qui pourrait être très pratique. D'autre part, presque tous les moteurs, à l'exception d'InnoDB, ne sont pas très bons car ils ne supportent pas les transactions et la réplication. En particulier, MyISAM ne fait pas du tout respecter les contraintes.
Les messages d'erreur sur MySQL ne sont souvent qu'un numéro, c'est comme ça qu'Oracle fait les choses et ça pue.
J'ai constaté que MySQL semble être un peu plus rapide pour les requêtes de table unique sur MyISAM. Cela pourrait être dû à une meilleure mise en cache.
La ligne de commande pour est facile à utiliser, mais MySQL a étendu SQL afin de fournir des fonctionnalités que d'autres clients de ligne de commande offrent avec \shortcuts Je trouve cela déplacé.
El EXPLIQUER/DÉCRIRE pour l'analyse, bien que cette commande ne soit pas aussi utile que celle de postgres.
Postgres fait de son mieux pour optimiser ce que vous lui proposez. La raison la plus courante des mauvaises performances des requêtes Postgres est l'absence ou l'inadéquation des index.
La commande "explain analyze ...". qui est très utile pour analyser les performances des requêtes.
Il n'est pas possible de changer de moteur, mais les transactions sont prises en charge par défaut et, depuis peu, la réplication est également possible.
Les messages d'erreur dans Postgres sont souvent descriptifs et vous expliquent même parfois de manière assez précise ce que vous devez faire.
La ligne de commande est facile à utiliser et chaque fois que vous vous demandez ce qu'il faut faire avec elle, vous pouvez taper \help
Si une suppression prend 10 minutes, c'est probablement que votre condition de suppression prend beaucoup de temps ou que vous effectuez une suppression en cascade qui nécessite de nombreuses jonctions.
Le type de données Blob dans MySQL est probablement plus complexe à stocker, à itérer et à récupérer que les champs de texte de longueur fixe. La taille des blobs est également limitée, et si un fichier dépasse cette limite, les octets de queue sont coupés. Vous pouvez essayer de stocker les fichiers en tant que fichiers sur le système de fichiers et de créer un champ texte de longueur fixe dans votre table pour stocker le nom du fichier. Si vous servez ces données à l'aide d'un serveur Web, cela accélérera également le processus, car il est beaucoup plus rapide de laisser Apache servir un fichier que de le récupérer dans une base de données et de le diffuser dans votre code.
J'ai une assez bonne expérience de MySQL et de PostgreSQL. Je trouve que PostgreSQL est plus fiable, plus rapide et meilleur en général, mais je ne le recommanderais pas aveuglément. Vous devez savoir ce que vous recherchez dans une base de données pour pouvoir choisir. J'ai eu l'expérience de mettre PostgreSQL pour être un bon remplacement de SQL Server dans des environnements où nous avons tout migré de SQL Server à Postgres, et fait beaucoup mieux avec le même matériel (en passant de Windows à Linux aussi).
PostgreSQL vous offre également beaucoup plus d'options pour optimiser vos performances et régler votre base de données en général, à condition que vous savez ce que vous faites vous pouvez modifier à votre guise ses fichiers de configuration. Une chose qui me tue dans MySQL est ce fichu fichier my.cnf. Vraiment, il me tue. C'est un fichier qui peut se trouver n'importe où, et nulle part. Vous pouvez même avoir un fichier my.cnf dans votre répertoire personnel - et oui, en fonction de votre configuration et de la façon dont vous invoquez votre serveur, il peut interférer de façon sauvage avec les travaux du db.
J'ai eu de sérieux problèmes avec des serveurs dont les fichiers my.cnf étaient placés de manière aléatoire, ce qui m'a donné de gros maux de tête pour les retrouver. où est-ce que MySQL a trouvé ses configurations ? . Il est beaucoup, beaucoup, beaucoup mieux de n'avoir qu'un ou deux fichiers .conf, comme PostgreSQL.
J'ai également constaté que PostgreSQL remplaçait convenablement Oracle dans certains environnements, où Oracle n'était pas vraiment nécessaire au départ, mais où quelqu'un s'est laissé convaincre (un homme d'affaires inondé d'arguments de vente insensés est probablement la personne qui a passé l'appel) et l'a acheté. C'était totalement inutile, et nous avons pu le remplacer sans problème majeur par PostgreSQL. C'était il y a un certain temps, la version 6.xxx de PostgreSQL, mais elle a tout de même bien remplacé Oracle. Il s'agissait d'une base de données assez importante (environ 80 Go), et PostgreSQL était à la hauteur de la tâche. PostgreSQL supporte nativement quelques fonctionnalités intéressantes, comme les procédures stockées (qui peuvent être en Pl/PgSQL, C, Java, ou tout autre langage que PostgreSQL supporte maintenant).
J'ai également une certaine expérience de MySQL, avec lequel je travaille depuis sa version 3.23 - donc depuis un certain temps. MySQL est généralement très bon si vous ne vous souciez pas du fonctionnement interne de la base de données, ou si vous avez besoin d'une base de données compétente pour faire quelque chose de cool et de simple.
Eh bien, ça peut être très rapide, très bon, et vous pouvez vraiment avoir des problèmes et vous faire coincer à l'avenir si vous ne pensez pas à ce que vous faites. Une chose que je déteste le plus est la possibilité de choisir ses moteurs de stockage. Bien que cela ait l'air génial (du genre "hé, je peux choisir ! je suis grand !"), ce n'est en réalité pas très agréable. Le principal défaut est que, quel que soit le moteur que vous choisissez, vous gagnerez et perdrez toujours des choses (en termes de fonctionnalité/capacité). Par exemple, si vous voulez l'intégrité référentielle (créer des clés étrangères et autres), vous ne pouvez pas utiliser MyISAM, et vous devez vous contenter des performances inférieures (attention : pas nécessairement mauvaises, juste pires que) d'InnoDB. InnoDB présente également de nombreuses limitations stupides : taille et quantité de colonnes. Vous ne pouvez pas avoir de tables avec plus de 1000 colonnes dans InnoDB. Ainsi, il tue littéralement MySQL lorsque nous parlons d'entrepôts de données.
Cela me donne l'impression que MySQL est un produit cousu entre de nombreux autres produits différents (InnoDB était une société dans le domaine des bases de données). Il suffit de télécharger le code source des deux pour voir la différence : alors que le code source de PostgreSQL est plus petit, plus organisé, le code source de MySQL est plus gros, son arbre des sources n'est pas aussi organisé que celui de Postgres. Et oh là là, cela fait une énorme différence lorsque vous compilez. Un autre fait qui soutient mon argument sur le code source est le temps de construction : La compilation de PostgreSQL est beaucoup, beaucoup, beaucoup plus rapide que celle de MySQL. Parfois, je pense que la reconstruction de mon noyau est plus rapide que la reconstruction de MySQL...
Un autre point négatif pour MySQL, si vous vendez des logiciels, comme une entreprise de logiciels ou un fournisseur de logiciels (SaS) ou quelque chose comme ça, est leur politique de licence merdique. Oui, je sais, c'est GNU/GPL, mais ils ont aussi un mode de licence d'entreprise, et si vous voulez empaqueter MySQL pour le distribuer avec votre application, vous ne pouvez pas le faire sans leur payer des royalties (frais de licence, etc.). PostgreSQL, d'un autre côté, est sous licence BSD, donc vous pouvez faire tout ce que vous voulez avec lui - même l'améliorer et vendre un Putyournamehere-PostgreSQL-with-killer-feature.
Il y a aussi un autre aspect que j'aimerais observer : MySQL est activement maintenu par MySQL AB, une société privée. Quant à PostgreSQL, il est totalement soutenu par la communauté opensource, sans aucun lien avec une quelconque entreprise.
Et le planificateur de requêtes. Oh mon dieu, ne me laisse pas commencer sur le planificateur de requête. Le planificateur de requête MySQL craint . Pas pour 'SELECT * FROM sometable'... mais si vous allez plus loin et commencez à mettre des requêtes avec des sous-requêtes et des sous-sous-sous-requêtes, le planificateur vous abandonne et commence à lire chaque ligne de la base de données, ce qui fait tomber votre application. Cela vous réveillera au milieu de la nuit.
Donc, pour en venir directement à l'essentiel, MySQL est idéal si vous voulez.. : - Avoir une base de données rapide et fiable, à utiliser pour des choses simples. La synchronisation facile de MySQL est utile lorsque nous devons construire un système de haute disponibilité, et elle est également excellente pour l'équilibrage de charge. - Utilisez n'importe quel type d'application LAMP. Wordpress, Joomla, PhpBB, tout ce que vous voulez. C'est tout simplement génial, et ça fonctionne parfaitement. Et il est facile à trouver, dans des lieux d'hébergement bon marché. Intranets d'entreprise, sites web, etc.
PostgreSQL est préférable si vous : - Vous avez besoin d'une solution plus fiable pour répondre aux besoins de votre entreprise. Vous avez besoin d'une solution plus fiable pour répondre aux besoins de votre entreprise, qui puisse remplacer SQL Server et Oracle. - Il est rapide, évolutif, ajustable, supporte des fonctionnalités avancées de base de données, comme les procédures stockées dans de nombreux langages différents, différents niveaux d'isolation des transactions. - La synchronisation n'est pas aussi triviale que dans MySQL, mais bon, elle fonctionne très bien.
D'autres ressources qui peuvent être utiles : http://en.wikipedia.org/wiki/InnoDB http://en.wikipedia.org/wiki/Comparison\_of\_relational\_database\_management\_systems
Cela ne répond pas vraiment à votre question, mais si vous avez des requêtes de suppression qui prennent plus de 10 minutes à exécuter, ce n'est probablement pas MySQL qui est votre problème mais la conception de la requête ou la disposition de la base de données. Vous obtiendrez probablement un meilleur retour sur investissement dans cet espace qu'en passant à une autre plateforme de base de données.
Ma réponse très simple à la question est la suivante : je vote pour Postgres, voici pourquoi :
Oracle ne peut pas acheter et contrôler l'avenir de Postgres (je sais que MySQL peut très probablement être bifurqué et être indépendant d'Oracle si nécessaire). En ce qui me concerne, je ne mettrais pas mon système sur une base de données qui est dans les limbes, ce que MySQL est dans une certaine mesure avec l'acquisition de Sun par Oracle.
L'assistance en ligne sur les forums Postgres est excellente.
Postgres possède toutes les fonctionnalités dont vous aurez très probablement besoin.
Postgres a fait ses preuves sur des systèmes critiques.
Postgres a tendance à être très standardisé. Les normes ne sont pas le fruit d'une réflexion des développeurs qui y travaillent.
Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.
