MySQL Introduction

A look at the MySQL database—where it's been, where it is now, and where it's going.

MySQL's history goes back to 1979 when
TcX, the company that developed MySQL, started working with
database programs. This first version was a screen
builder/reporting tool written in BASIC. At that time,
state-of-the-art computers had 4MHz Z80 processors and 16KB of RAM.
This tool was moved to UNIX and further developed during the
following years. In the mid-1990s, we started having problems with
customers who liked the results the tool produced but wanted
something they had heard about before (a buzzword was needed). So
we started looking at making an SQL (an appropriate buzzword) front
end to our low-level libraries. We found mSQL, but it did not work
for our purposes. So we started to write an SQL engine from
scratch. However, since the mSQL API was useful, we used it as the
basis for our own API. This made it easy to port some applications
we needed that were available for the mSQL API.

Since this tool would be usable by others, we decided to
release it according to the business model pioneered by Peter
Deutsch at Aladdin Enterprises with Ghostscript. This copyright is
much more free than the mSQL copyright and allows commercial use as
long as you don't distribute the MySQL server commercially.

It is not perfectly clear where the name MySQL came from. We
have used the prefix “my” for libraries and path names since the
mid-1980s. The main MySQL developer's daughter is named My—a
fairly common name among Swedish-speaking Finns—so naming our
database MySQL was very natural.

In May 1996, MySQL version 1.0 was released to a limited
group of four people, and in October 1996, MySQL 3.11.1 was
released to the public as a binary distribution for Solaris. A
month later, a Linux binary and the source distribution were
released. The MySQL release included an ODBC driver in source form.
This also included many free MySQL clients ported to MySQL.

Platforms

The initial version of MySQL worked only on Linux and
Solaris. The biggest problem in porting to other platforms was that
MySQL needed a working POSIX thread library; in January 1997 a
modified version of MIT-pthreads was included in the
distribution.

To be able to use MySQL from your favorite language, you need
an API. The first MySQL version included only C and Perl APIs. Now
there are many (see Table 2). With the exception of the Java API,
all of these use the C API to communicate with the MySQL server.
So, as you can see, MySQL can be used from many popular
languages.

MySQL Benchmarks and crash-me

When we had gotten a nice working system, we wanted to test
it against old versions and against other databases, so we started
looking for good benchmarks. We found that most benchmarks (like
the TCP ones) represent an SQL server's performance as a single
number, often as transactions/second. We regard these to be almost
worthless, since comparatively few users run applications that do
the same thing as these benchmarks. There is usually no way to
determine your application's performance from the numbers given by
this type benchmark.

The MySQL benchmarks are designed to show how fast a SQL
server is for common operations, such as establishing a connection,
performing simple inserts or joining two tables using a key. This
also makes it possible to calculate loads on a web site when you
know the mix of operations. Of course, you need to actually
understand your own application to judge its performance with any
database.

Over time, we got many requests on the MySQL mailing lists
about MySQL's features and how it compares feature-wise with other
databases. As Michael (the main developer) didn't want to dig into
old inaccurate reference manuals to find this out, he thought of a
program that automatically detects what a SQL server has to offer.
He also thought it would be a nice test of how stable the MySQL
code is when you start to send it “abnormal” queries.

While working with the benchmarks, we needed a list of
capabilities for all supported databases. Since doing this by hand
was very tedious, we made a tool to do it
automatically. While trying early versions of this tool on some
different servers, bad things happened—the servers crashed. All
this crashing led us to name this tool
crash-me. In fact, the only SQL
server that has gone through this testing without crashing is
Oracle. Of course, all bugs found in MySQL were fixed
immediately.

crash-me (and the
benchmarks) are implemented as a Perl DBI/DBD program that sends
thousands of queries to a database to find out how things work in
real life. By doing this, it finds many limits in the server, such
as the supported column/query/variable/index lengths.

crash-me is also a nice tool
for helping you write portable SQL, since it can provide a list of
which functions, types and limits exist in the server you wish to
use. Currently, we have crash-me results from Adabas-D, Access,
DB2, Empress, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Solid
and Sybase. As the crash-me table is big and very detailed, we will
not include it here, but it is available at
http://www.mysql.com/crash-me-choose.htmy/.

What operations does the benchmark test? First and foremost,
the basic SQL operations are tested: INSERT,
UPDATE, DELETE and
SELECT. Other tests include a connect followed
by a select, and creation of tables and indexes.

The individual tests should give a good indication of how
fast an SQL server is for that specific operation. Do not use the
“total time” as an overall measure of the value of an SQL server.
This is because the tests are not weighted against each other. Some
tests are run more times with different options and numbers of
rows. An SQL server may be extremely bad at some “unimportant
things”, while it's very good at the things for which you actually
intend to use it.

We use the total time to compare things like the same
database engine on different operating systems. We also use it to
see how new versions of MySQL stack up against old ones.

Since all benchmark tables take even more space than the
crash-me results, we include only a few observations on how well
MySQL runs on different platforms.

Linux 2.2 is much faster than Linux 2.0 on a
multi-CPU machine. This is because the Perl and the MySQL server
run on different processors and the new SMP code is faster.

Linux is 7% faster than Windows 98 and 49% faster
than NT on the same machine.

Windows 98 is 27% faster than NT on the same
machine.

A Pentium II 400MHz machine running Linux 2.2 is
much faster than a Sun Ultrasparc 2/CPU 400MHz machine running
Solaris 2.7. The primary reason for this difference is that Linux
caches the file system much better than Solaris; this result might
be different under higher load. We will include a threaded test in
the next generation of benchmarks to test things like this.

If you do many inserts on Solaris, you will get
only a 22% speed increase by upgrading your processor speed by
100%.

The overhead of using MyODBC, and probably any ODBC
driver, is at least 19%.

Note that while benchmarking, it was still possible to work
on the Linux machine without any problems. However, NT became so
slow that it was impossible to do any other work, even simple
editing. It took about 30 times longer to start up a new DOS
window, and we had to wait 10 seconds or so before typed characters
showed up.

There are still many things to be done for both crash-me and
the benchmarks. For example, we would like crash-me to report if
there are identical functions that do the same thing (such as,
instead of CONCAT one can use
“||”). Also, many new tests should be added to test which
sub-select constructs an SQL server allows. Of course, the
documentation and presentation of the results could be much
improved.

Both these tools give invaluable information to any developer
who uses more than one SQL server. If they do not test the feature
you need, please contribute a new test. More test results can be
found at
www.mysql.com/benchmark.html.