Any examination of open-source databases would be incomplete without a discussion of MySQL, a light and lean database that's a favorite of many Webmasters for its speed and small size.
In particular, MySQL's fast read performance (especially when used with a small number of users), host name, subnet- or user name-based security layer, and tight integration with Perl and PHP make it a perfect fit for Web use. For example, Slashdot.org is run off a MySQL database.
However, PC Week Labs cautions that MySQL shouldn't be compared with higher-end databases, such as InterBase or PostgreSQL (see Tech Analysis). It's a fundamentally different product with different design goals.
MySQL is a database built for blasting such simple static data as user account information onto Web pages as fast as disk and CPU speeds will allow. It's not designed to do—nor should it be used for—financial transactions, inventory management or other business-critical tasks. The trade-off that MySQL, along with similar Indexed Sequential Access Method databases, including Microsoft Corp.'s Visual FoxPro and Corel Corp.'s Paradox, makes is to give up the defining characteristic of a high-end SQL database—transactions and a transaction log—to gain simplicity and speed.
Transaction support ensures that database operations are atomic, consistent, independent and durable (ACID for short), thus guaranteeing that a group database's changes happen correctly and that the database can properly survive disk or power failures. No business should put critical internal or customer data into any database that doesn't provide ACID operations.
|
MySQL offers feast, famine
MySQL pros:
Very fast operation for read-heavy or low-user-load applications
Flexible security system
Strong connectivity to dynamic Web publishing systems
MySQL cons:
No transaction support
Table locking system behaves poorly when there are many competing users
Not SQL-92 compliant
|
|
MySQL's multiuser support is also quite poor—it can only issue table-level locks. Any database writer locks the entire table, making all readers wait until the write is finished. This can be a performance killer, and MySQL developers will need to write SQL very carefully to avoid this situation. (In a special case, MySQL can allow simultaneous inserts along with reads, as long as inserts happen only at the end of the file—that is, only as long as there are no empty slots from deleted records.)
Just using table locks when there is no lock contention is a real speed win—the fewer locks there are, the less processing the lock manager has to do. In situations such as Web publishing, where there is very little lock contention, this approach really pays off. Generally, however, we believe that absolute performance matters less than predictable performance, which more granular locking—say, page or row-level—provides.
MySQL lacks some SQL language features, such as subselects and views, as well as server-side cursors and stored procedures. As a result, we couldn't run the same benchmark code we used with InterBase and PostgreSQL with MySQL.
We did run just an online transaction processing single-read test with a single user. MySQL really smoked, clocking 649 transactions per second, a result that's about four times faster than InterBase's throughput and more than 10 times faster than PostgreSQL's throughput at the same user load and workload. We plan to rework the benchmark code to avoid MySQL's areas of SQL incompatibility to do more comprehensive multiuser benchmarking of MySQL later this year.
—T.D.
