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.
