Changeset 680

Show
Ignore:
Timestamp:
08/18/07 16:00:11 (1 year ago)
Author:
ioguix
Message:

§ Data Integrity/Intégrité des Données

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • materials/advocacy/trunk/Why_PostgreSQL_Instead_of_MySQL.txt

    r679 r680  
    137137 
    138138// Data Integrity 
    139 Intégrité des données 
     139Intégrité des Données 
    140140 
    141141// Before version 5.0, MySQL well deserved its reputation for allowing inconsistent data to be inserted into the 
    …… 
    144144// change its own SQL Mode to override this, with the result that these validation constraints are still not  
    145145// necessarily enforced by the server. 
     146Avant la version 5.0, MySQL méritait clairement sa réputation autorisant l'insertion de données inconsistantes dans la 
     147base de données. "Guaranteeing Data Integrity with MySQL 5.0" explique les problèmes avec les plus vieilles versions de 
     148MySQL, et comment ils pourraient être évité en utilisant le "Mode SQL" strict disponnible dans la version actuelle. 
     149Bien entendu, tout client MySQL à la possibilité de changer son mode SQL et contourner ainsi ce comportement, 
     150avec comme résultat que ces validations de contraintes ne soit plus forcément assurée coté serveur. 
    146151 
    147152// PostgreSQL has always been strict about making sure data is valid before allowing it into the database, and there is 
    148153// no way for a client to bypass those checks. 
     154PostgreSQL a toujours été strict sur la validation des données avant de les insérer dans la base de données, et il  
     155n'existe aucune alternative au client pour contourner ces vérifications. 
    149156 
    150157// Transactions and the Database Engine Core 
    151  
    152 The database core that gave MySQL its original reputation for speed is MyISAM. This engine has excellent read performance and its parser is very efficient for straightforward queries, which combine to make it very fast in read-intensive applications like web applications involving simple SELECTs. However, it is commonly known that MyISAM is more vulnerable to data corruption than most serious database applications would tolerate, and after a crash there may be a substantial delay while it rebuilds its indexes before the server can restart. Furthermore, it does not support foreign keys or transactions that would allow the database to have ACID properties. MyISAM also has issues dealing with concurrent reads and updates, since it only provides table level locking. 
    153  
    154 The integration of the InnoDB Storage Engine to MySQL greatly improved over MyISAM in terms of data integrity, adding a more robust log replaying mechanism for crash recovery and enabling ACID compliant transactions. However, this new approach comes with much more overhead, and InnoDB tables are not as fast as MyISAM ones for pure read loads. In addition, the internal MySQL metadata tables are still stored using MyISAM, which means they remain vulnerable to the traditional corruption issues associated with that storage engine. This issue is worked around using some complicated locking methods that have the potential to make a table alteration block for some time. 
    155  
    156 PostgreSQL has always focused on data integrity at the transaction level, keeping locking issues to a minimum, and barring hardware failure or grossly improper configuration it is difficult to corrupt a database. 
    157  
    158 It is worth observing that the database engine is part of the core of PostgreSQL, whereas InnoDB is a dual-licensed product presently licensed from Oracle Corporation. It is uncertain how Oracle may alter InnoDB in the future as they act in competition with MySQL AB, whereas PostgreSQL has no such conflict of interests. MySQL AB has been working on a new database engine core called Falcon in order to free themselves from this situation, but historically developing a database core engine that is both fast and reliable has required many years of work and testing before a mature product suitable for production use is available. Initial benchmarks suggest Falcon has plenty of rough edges that need to be addressed. 
    159 Foreign Keys 
     158Les Transactions et le Moteur Interne de la Base de Donnée 
     159 
     160/* en cours de trad par ioguix 
     161// The database core that gave MySQL its original reputation for speed is MyISAM. This engine has excellent read 
     162// performance and its parser is very efficient for straightforward queries, which combine to make it very fast in 
     163// read-intensive applications like web applications involving simple SELECTs. However, it is commonly known that 
     164// MyISAM is more vulnerable to data corruption than most serious database applications would tolerate, and after a 
     165// crash there may be a substantial delay while it rebuilds its indexes before the server can restart. Furthermore, 
     166// it does not support foreign keys or transactions that would allow the database to have ACID properties. MyISAM 
     167// also has issues dealing with concurrent reads and updates, since it only provides table level locking. 
     168 
     169 
     170// The integration of the InnoDB Storage Engine to MySQL greatly improved over MyISAM in terms of data integrity, 
     171// adding a more robust log replaying mechanism for crash recovery and enabling ACID compliant transactions. However, 
     172// this new approach comes with much more overhead, and InnoDB tables are not as fast as MyISAM ones for pure read 
     173// loads. In addition, the internal MySQL metadata tables are still stored using MyISAM, which means they remain 
     174// vulnerable to the traditional corruption issues associated with that storage engine. This issue is worked around 
     175// using some complicated locking methods that have the potential to make a table alteration block for some time. 
     176 
     177// PostgreSQL has always focused on data integrity at the transaction level, keeping locking issues to a minimum, and 
     178// barring hardware failure or grossly improper configuration it is difficult to corrupt a database. 
     179 
     180// It is worth observing that the database engine is part of the core of PostgreSQL, whereas InnoDB is a 
     181// dual-licensed product presently licensed from Oracle Corporation. It is uncertain how Oracle may alter InnoDB in the 
     182// future as they act in competition with MySQL AB, whereas PostgreSQL has no such conflict of interests. MySQL AB has 
     183// been working on a new database engine core called Falcon in order to free themselves from this situation, but 
     184// historically developing a database core engine that is both fast and reliable has required many years of work and 
     185// testing before a mature product suitable for production use is available. Initial benchmarks suggest Falcon has 
     186// plenty of rough edges that need to be addressed. 
     187*/ 
     188 
     189// Foreign Keys 
     190Clés Étrangère 
    160191 
    161192Proper implementation of design techniques like Database Normalization rely on the ability of the database to use Foreign keys to map relationships between tables. In MySQL, foreign keys are only supported with InnoDB. One problem with their implementation is that it is limited and will silently ignore some standard syntax. For example, when creating a table, even in the upcoming 5.1 release of MySQL "the CHECK clause is parsed but ignored by all storage engines". The basic design philosophy of PostgreSQL is to produce errors or warnings in similar situations where an operation is ambiguous or unsupported.