MySQL (MySQL & mSQL)

MySQL may be one of the hottest grass-roots software projects since Linux. While mSQL certainly deserves credit for getting the ball rolling, MySQL has built upon that momentum. It is now nothing less than a serious competitor for the major database engines in the field of small-to-medium scale database development. In its beginnings, MySQL was simply a replacement for the aging mSQL 1 database engine. As we noted in Chapter 1, "Introduction to Relational Databases", mSQL began showing signs of its age in the form of stability issues and an inability to meet the growing demands thrust upon it by its success. MySQL built upon the basic design goals of mSQL and now exceeds mSQL in its feature set while also managing to beat mSQL in performance.

4.1. Design

Working from the legacy of mSQL, TcX decided MySQL had to be at least as fast as mSQL in spite of its expanded feature base. At that time, mSQL defined database performance, so TcX's goal was no small task. MySQL's specific design goals are speed, robustness and ease of use. To get this sort of performance, TcX decided to make MySQL a multithreaded database engine. A multithreaded application performs many tasks at the same time just as if multiple instances of that application were running simultaneously.

By making MySQL multithreaded, TcX has given us many benefits. A separate thread handles each incoming connection with an extra thread always running in order to manage the connections. Clients therefore do not have to wait for queries from other clients to run. Any number of simultaneous queries can run. While any thread is writing to a table, all other threads requesting access to that table simply wait until the table is free. Your client can perform any allowed operation without any concern for other concurrent connections. The connection managing thread prevents two threads from writing to the same table at the same time.

This design is certainly more complex than mSQL's single-threaded design. The speed advantages of performing multiple simultaneous queries, however, far outweighed the speed penalties of the increased complexity.

Another advantage to multithreaded processing is inherent to all multi-threaded applications. Even though the threads share the same process space, they execute individually. Because of this separation, multiprocessor machines can spread the load of each of the threads across the many CPUs. Figure 4-1 illustrates the multi-threaded nature of a MySQL database server.

Figure 4-1. The client/sever design of MySQL

In addition to the performance gains introduced by multithreading, MySQL has a richer subset of SQL than mSQL. MySQL supports over a dozen datatypes and additionally supports SQL functions. Your application can access these functions through ANSI SQL statements.

MySQL actually extends ANSI SQL with a few features. These features include new functions (ENCRYPT, WEEKDAY, IF, and others), the ability to increment fields (AUTO_INCREMENT and LAST_INSERT_ID), and case sensitivity.

TcX did intentionally omit some SQL features found in the major database engines. Transactions and stored procedures are the two most notable omissions. Like David Hughes with mSQL, TcX decided that including these features would incur too much of a performance hit to be worth their addition. TcX is working on adding these features, however, in such a way that only users who really need these features are penalized by them.

Since 1996, TcX has been using MySQL in an environment with more than 40 databases containing 10,000 tables. Of these 10,000 tables, more than 500 have more than seven million records -- about 100 GB of data.

Chapter 4. MySQL

Contents:

4.1. Design

Figure 4-1. The client/sever design of MySQL