Why not MySQL?

Mysql
Note: This document was written in May 2000. Therefore, it does not explain the latest features of MySQL. But from this we can still understand some basic concepts and principles of RDBMS, so as to better apply the database in practice, but also to maintain the necessary vigilance to some false hype.

Why not MySQL?

Author: Ben Adida Translator: Mavida

Almost weekly, sometimes even more frequently, someone will ask why we don't use MySQL as a openacs RDBMS (relational database management system). ACS Classic Team (ArsDigita) has repeatedly encountered the same questions in their forums. If MySQL is good enough for Slashdot, it must be able to be used for Openacs, right?

Wrong. This short paper will try to explain why MySQL is not only the wrong choice for openacs, it should not be used for any system that deals with critical data.

The purpose of the RDBMS

The purpose of an RDBMS is to provide a reliable, permanent storage mechanism that specifically describes the very stringent nature of this mechanism in acid testing. I will refer directly to Philip Greenspun's brilliant explanation (represented by Oracle as an RDBMS):

Atomic Sex (atomicity)

The execution result of the transaction is either fully committed or rolled back (roll). Either all the changes will take effect, or no change will take effect. Assuming that a user is editing a comment, the web script tells the database to "copy the old annotation value to the Audit table and update the activity table with the new text." If the hard drive becomes full after the copy and before the update, the audit table insert is rolled back.

Consistency (consistency)

The database is converted from one valid state to another valid state. A transaction is legal only if it is subject to user-defined integrity constraints. Illegal transactions are not allowed, and if an integrity constraint is not met, the transaction is rolled back. For example, suppose you define a rule: a post in a forum table must be associated with a valid user ID. Then you hired Joe Novice to write the admin page. Joe wrote a delete user page that does not check whether the deletion will produce some unowned forum posts. An RDBMS like Oracle, however, will check and abort any transaction if it generates a forum post that is owned by a deleted user.

Isolation (Isolation)

The result of a transaction is not visible to other transactions until the transaction completes. For example, suppose you have a page that displays new users and their photos. According to the publisher's request, each user in the page has a face photograph, if the user does not have the picture, displays a picture which does not have the picture. While the new user, Jane, is registering at your site, old user bill is viewing the new user page. The script that handles Jane's registration inserts several tables: Users, mugshots, Users_demographics. If Jane's face photo is large, the insertion may take some time. If Bill's query started before Jane's transaction was submitted, Bill would never see Jane on his new user page, even in Jane's transaction, where the inserts for some tables have been completed.

Persistence (Durability)

Once committed (completed), the result of the transaction will be permanent and free from future system and media failures. Suppose your e-commerce system inserts an order from a consumer into the database table and instructs CyberCash to charge the consumer a 500 dollar fee. All of a sudden, someone tripped the machine's power cord before your server received a CyberCash response. In such a case, Oracle will not forget the order. Furthermore, it is possible for a programmer to spill coffee into a disk drive, install a new disk, and revert the transaction to the point where the coffee is spilled; The data will show you tried to charge someone 500 dollars, and it's not clear what happened at CyberCash.

If what you want is a fast, bare storage, use the file system. If you want to share between multiple machines, use NFS. If you want simple reliability to deal with too simple a fault, use mirroring. Want to add a SQL interface to them? To use MySQL.

Now, if all you want is a data store like this, it enables you to maintain a constant number of aspects of your dataset, to be able to perform complex operations on the data without violating those constraints, to isolate the local work performed by multiple users at the same time, and to be able to recover smoothly from any kind of failure, Then find yourself a real RDBMS. Yes, it will be slower than the MySQL file system, just as TCP is slower than UDP, but they provide better service guarantees.

The current situation and future of MySQL

Building a real RDBMS is a daunting task, perhaps more daunting than any other system problem. Most of the products on the market (Oracle, Sybase, PostgreSQL, InterBase) have been developed for years, some more than 10 or 15 years.

MySQL developers claim that they have sacrificed certain features to ensure better performance. While this may be an interesting way to track non-critical data, such as click-through tracking, it is not acceptable to sacrifice complete data integrity when dealing with critical data, even for speed.

When MySQL matures, the Openacs team is delighted to be closer to the expedition. However, the MySQL team does not seem to understand the concept and importance of true acid capacity: MySQL Todo mentions "transactions" in a long list, including issues such as "sleep process consuming CPU". In addition, the MySQL manual claims that MySQL will soon implement "atomic operations" through the use of table locks, but "no rollback". This is a flagrant misuse of the term "atom": "Atomic operation" means that either all operations are completed or no operation is completed. If there is no rollback capability, a hardware or power failure occurring in the middle of a set of statements destroys the atomic nature of the block.

Rollback is not just a convenient feature, it is a critical basis for reliable data storage.

There are many good reasons to use MySQL, but the need for reliable, compliant, acid-storage data is not one of them.

More details

L MySQL does not have subqueries.

For complex queries, MySQL users must perform two or more series of queries, each requiring interprocess communication or network communication between the application and the database. This significantly reduces the speed advantage of MySQL.

L MySQL does not have a stored procedure.

L MySQL has no triggers or foreign key constraints.

L MySQL has only table-level locking.

Conclusion

Enterprise-class systems do not sacrifice specific features for speed. The ACID properties of an RDBMS are an absolute necessity for any critical data. Critical sites running on non-acid compliant systems are asking for trouble.

The Openacs project refuses to break the important rules of acid testing. We're building an enterprise-class open source Web Toolkit. PostgreSQL, soon and InterBase will be the appropriate candidate RDBMS for this project. And MySQL is just a glorified file system with a SQL interface.