MySQL Design (Managing and Using MySQL, 2nd Edition)

1.3. MySQL Design

Working from the legacy of mSQL, TcX decided MySQL had to be at least as fast as mSQL with a much greater feature set. At that time, mSQL defined good database performance, so TcX's goal was no small task. MySQL's specific design goals were 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 as if multiple instances of that application were running simultaneously. Fortunately, multithreaded applications do not pay the very expensive cost of starting up new processes.

In being multithreaded, MySQL has many advantages. A separate thread handles each incoming connection with an extra thread that is always running to manage the connections. Multiple clients can perform read operations simultaneously without impacting one another. But write operations, to a degree that depends on the type of table in use, only hold up other clients that need access to the data being updated. 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 concern for other concurrent connections. The connection-managing thread prevents other threads from reading or writing to a table in the middle of an update.

Another advantage of this architecture is inherent to all multithreaded applications: even though the threads share the same process space, they execute individually. Because of this separation, multiprocessor machines can spread the threads across many CPUs as long as the host operating system supports multiple CPUs.

In addition to the performance gains introduced by multithreading, MySQL has a richer subset of SQL than mSQL. MySQL supports over a dozen data types and additionally supports SQL functions. Your application can access these functions through the American National Standards Institute (ANSI) SQL statements.

The Great Transaction Debate

Transactions are important for applications that support complex rules for concurrent updating of data. They prevent concurrent updates from putting the database in an inconsistent state at any point in the database's life.

Transactions are a relatively new feature of the MySQL database engine. In fact, the transaction features are not present unless you set up your tables to support them. Many people wondered what use MySQL was without transactions and why someone would set up a table without supporting them. The answer is one word: performance.

The minute you introduce transactions into the picture, the database takes a performance hit. Transactions add the overhead of complex locking and transaction logging. The complex locking includes support for transaction isolation levels, discussed in Chapter 8. Basically, however, increasing transaction isolation levels requires an increasing amount of work by the database to support the same functionality. The more work the database has to do for a task, the slower it performs that task.

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.

Some SQL features found in the major database engines were omitted intentionally from MySQL. For the longest time, transaction support was the most notable omission. The latest releases of MySQL, however, provide support for transactions. Stored procedures, another notable omission, are scheduled for the 4.1 release that should be available at the same time as this book. Finally, MySQL does not support most additions to the SQL standard as of SQL3. The most important SQL3 feature missing from MySQL is support for object-oriented data types.

Since 1996, MySQL AB has been using MySQL internally in an environment with more than 40 databases containing 10,000 tables. Of these 10,000 tables, more than 500 contain over 7 million records—about 100 GB of data.