Changeset 676

Show
Ignore:
Timestamp:
08/17/07 15:51:38 (1 year ago)
Author:
kryskool
Message:

traduction (fin du document)

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • materials/advocacy/trunk/Why_PostgreSQL_Instead_of_MySQL.txt

    r675 r676  
    3838// performance against reliability, both these topics need to be considered together in order to get an accurate view  
    3939// of the landscape. 
    40 Alors que l'innovation sur ces deux bases a progressé, chacune des communautés de développement ont travaillé  
     40Alors que l'innovation sur ces deux bases a progressé, chacune des communautés de développement ont travaillés  
    4141activement à réduire leur liste de désavantages perçus. Le résultat est qu'il est devenu plus difficile de déterminer  
    42 objectivement quel base de données est susceptible de convenir à une application donnée. Ce document vise à clarifier  
     42objectivement quelle base de données est susceptible de convenir à une application donnée. Ce document vise à clarifier  
    4343dans quelles situations PostgreSQL est plus approprié que MySQL, en essayant de comparer équitablement les versions de 
    44 production courante de chacune et d'en discuter les forces et faiblesses. Les domaines principaux étudiés ici  
    45 concernent les questions fondamentales de l'intégrité des données et la vitesse du noyau logiciel de la base de donnée. 
     44production courante de chacune et d'en discuter les forces et faiblesses. Les domaines principalement étudiés ici  
     45concernent les questions fondamentales de l'intégrité des données et la rapidité du noyau logiciel de la base de donnée. 
    4646Puisqu'il est souvent question de faire un choix entre la performance ou la fiabilité, ces deux sujets doivent être  
    47 considérés ensemble afin d'avoir une vision précise de l'ensemble. 
     47considérés conjointement afin d'avoir une vision précise de l'ensemble. 
    4848 
    4949// The position of this paper is that when the two are compared using the high level of data integrity demanded by a  
    …… 
    154154It is worth observing that it is only this precise form of aggregate that must be so pessimistic; if augmented with a "WHERE" clause like 
    155155 
    156 SELECT COUNT(*) FROM table WHERE status = 'something' 
    157  
    158 PostgreSQL, MySQL, and most other database implementations will take advantage of available indexes against the restricted field(s) to limit how many records must be counted, which can greatly accelerate such queries. PostgreSQL will still need to read the resulting rows to verify that they exist; other database systems many only need to reference the index in this situation. 
    159  
    160 One popular approach for applications that need a row count but can tolerate it not including transactions that are in the middle of being committed is to use a trigger-based mechanism to count the rows in the table. In PostgreSQL, another alternative when only an approximate count is needed is to use the reltuples field from the pg_class catalog table. 
    161 Join Complexity 
    162 PostgreSQL uses cost-based query optimization methods in order to get good performance for many different types of joins. Query costs are assessed based on planner statistics collected when tables are analyzed combined with adjustable planner costs, and advanced features such as the Genetic Query Optimizer allow optimizing even very complicated joins efficiently. 
    163  
    164 MySQL doesn't have this level of sophistication in its planner, and the tunables for Controlling Query Optimizer Performance are crude. Developers instead do things like explicitly provide index hints to make sure joins are executed correctly. To make this task easier, MySQL provides a Query Profiler that is easier to work with than typical EXPLAIN data. Regardless of hinting, subselect optimization is a known weak spot in MySQL. There is also a fairly serious subquery null handling bug in MySQL 5.0 (which at this time appears to be still present in 5.1). 
    165  
    166 Finding order in execution provides several comparisons of how the two databases handle queries differently. Because of its more robust automatic optimization, PostgreSQL usually does a better job of handling complicated joins than MySQL--but only if the planner is properly configured (setting the effective_cache_size tunable too small is one common mistake) and statistics about the tables are kept up to date (typically via auto-vacuum). The fact that you must give the PostgreSQL optimizer correct information to work with, and can't explicitly control which join it uses, is a somewhat controversial design decision. The core PostgreSQL developers feel that it's more important to focus on improving the optimizer so it works correctly in all cases instead of just allowing queries to hint at a plan as a workaround for problems. 
    167  
    168 There are some add-on tools some find useful for exploring the PostgreSQL planner. pgAdmin includes an explain plan viewer (sample). Another option is Visual Explain, originally a RedHat component that has been kept current and improved by Enterprise DB. It comes bundled with the EnterpriseDB Advanced Server package and can be built to run against other PostgreSQL installations using the source code to their Developer Studio package. 
    169 Credits and Feedback 
    170  
    171 This document was written by Greg Smith with substantial contributions by Christopher Browne, Lukas Kahwe Smith, and other members of the PostgreSQL Advocacy mailing list. Some of the references linked to by this document point to articles also written by these authors. 
    172  
    173 Corrections, suggestions, flames, and similar feedback should be addressed to Greg, an independent consultant whose only affiliation with The PostgreSQL Global Development Group consists of submitting patches to improve the upcoming 8.3 release. He feels that PostgreSQL stands on its own merits and comparisons with MySQL should be as factual as possible, and feedback will be treated accordingly. 
     156// SELECT COUNT(*) FROM table WHERE status = 'something' 
     157SELECT COUNT(*) FROM table WHERE status = 'quelque chose' 
     158 
     159//PostgreSQL, MySQL, and most other database implementations will take advantage of available indexes against  
     160//the restricted field(s) to limit how many records must be counted, which can greatly accelerate such queries.  
     161//PostgreSQL will still need to read the resulting rows to verify that they exist; other database systems  
     162//many only need to reference the index in this situation. 
     163PostgreSQL, MySQL, et beaucoup d'autres implémentations de base de données tireront profil de la disponibilités des 
     164indexes pour le champs restreint afin de limiter le nombre d'enregistrement devant être comptés, ce qui accelère 
     165grandement de telles requêtes. 
     166 
     167//One popular approach for applications that need a row count but can tolerate it not including transactions that are  
     168//in the middle of being committed is to use a trigger-based mechanism to count the rows in the table. In PostgreSQL,  
     169//another alternative when only an approximate count is needed is to use the reltuples field from the pg_class catalog table. 
     170Une approche apprécié pour les applications qui ont besoin de compter les lignes mais ... 
     171 
     172//Join Complexity 
     173Jointure complèxe 
     174 
     175//PostgreSQL uses cost-based query optimization methods in order to get good performance for many different types of  
     176//joins. Query costs are assessed based on planner statistics collected when tables are analyzed combined  
     177//with adjustable planner costs, and advanced features such as the Genetic Query Optimizer allow optimizing  
     178//even very complicated joins efficiently. 
     179PostgreSQL utilise une méthode économique d'optimisation des requêtes afin d'obtenir de bonne performances pour les  
     180différents types de jointures.  
     181Les requêtes coûteuses sont évalués et basé sur des statistiques du planificateur recueillies quand les tables 
     182sont analysées et combiner avec l'ajustements des coûts du planificateur, et les fonctionnalités avancées tel que le 
     183"Genetix Query Optimizer" permettant l'optimisation efficace de jointûres très compliqués. 
     184 
     185//MySQL doesn't have this level of sophistication in its planner, and the tunables for Controlling Query Optimizer  
     186//Performance are crude. Developers instead do things like explicitly provide index hints to make sure joins  
     187//are executed correctly. To make this task easier, MySQL provides a Query Profiler that is easier to work with than  
     188//typical EXPLAIN data. Regardless of hinting, subselect optimization is a known weak spot in MySQL.  
     189//There is also a fairly serious subquery null handling bug in MySQL 5.0  
     190//(which at this time appears to be still present in 5.1). 
     191 
     192 
     193 
     194//Finding order in execution provides several comparisons of how the two databases handle queries differently.  
     195//Because of its more robust automatic optimization, PostgreSQL usually does a better job of handling complicated  
     196//joins than MySQL--but only if the planner is properly configured (setting the effective_cache_size tunable too small  
     197//is one common mistake) and statistics about the tables are kept up to date (typically via auto-vacuum).  
     198//The fact that you must give the PostgreSQL optimizer correct information to work with, and can't explicitly control  
     199//which join it uses, is a somewhat controversial design decision. The core PostgreSQL developers feel that it's more  
     200//important to focus on improving the optimizer so it works correctly in all cases instead of just allowing queries  
     201//to hint at a plan as a workaround for problems. 
     202 
     203 
     204 
     205// There are some add-on tools some find useful for exploring the PostgreSQL planner. pgAdmin includes an explain plan  
     206// viewer (sample). Another option is Visual Explain, originally a RedHat component that has been kept current and  
     207// improved by Enterprise DB. It comes bundled with the EnterpriseDB Advanced Server package and can be built to run  
     208// against other PostgreSQL installations using the source code to their Developer Studio package. 
     209Il y'a quelque outils complémentaires pour explorer le planificateur de PostgreSQL. pgAdmin inclus une visionneuse  
     210de plan d'exécution. Une autre option est "Visual Explain", à l'origine un composant de RedHat qui est maintenant 
     211maintenu par Enterprise DB. Il est inclus dans l'offre EnterpriseDB Advanced Server et peut être compiler avec une  
     212installation de PostgreSQL en utilisant le code source du paquet "Developer Studio". 
     213 
     214 
     215//Credits and Feedback 
     216Remerciements et Remarques 
     217 
     218// This document was written by Greg Smith with substantial contributions by Christopher Browne, Lukas Kahwe Smith,  
     219// and other members of the PostgreSQL Advocacy mailing list. Some of the references linked to by this document  
     220// point to articles also written by these authors. 
     221Ce document à été écrit par Greg Smith incluant de pertinante contribution de Christopher Browne, Lukas Kahwe Smith, 
     222et beaucoup d'autres membres de la liste de diffusion Advocacy. Quelques unes de ces références citées dans ce document 
     223proviennent d'articles qui ont été écrit par ces mêmes auteur. 
     224 
     225// Corrections, suggestions, flames, and similar feedback should be addressed to Greg, an independent consultant  
     226// whose only affiliation with The PostgreSQL Global Development Group consists of submitting patches to improve  
     227// the upcoming 8.3 release. He feels that PostgreSQL stands on its own merits and comparisons with MySQL should be  
     228// as factual as possible, and feedback will be treated accordingly. 
     229Corrections, suggestions, coup de gueule, et autres remarques peuvent être adresser à Greg, un consultant indépendant 
     230dont le seul lien avec l'équipe de développement mondial consiste à soumettre des patchs pour améliorer la future  
     231version 8.3