MySQL System Architecture
In this article, we're going to take a look at MySQL internals. It will be a fun, informative examination of how all the different pieces and parts of the MySQL server operate together. MySQL's implementation is a fascinating mix of technology that is truly a remarkable achievement—an achievement born from the sweat and inspiration of numerous developers over many years.
One of the joys of open-source technology is just that: it's open source! On a system as large as MySQL,1 taking a look at the source code gives you a true understanding of the dilemmas faced by the developers over the course of the software evolution. In this article, we'll investigate the source code of the server itself, so put on your hard hat. We encourage you to take a look at the source code, even if you have no intention of making any changes. You will gain an appreciation of the enormity of the tasks undertaken by the development team at MySQL AB, as well as gain a much deeper understanding of how the software works, and thus how you can optimize your programs to best utilize MySQL's strengths and avoid its weaknesses.
The information presented in this article comes from an analysis of both the internal system documentation and the actual source code for the MySQL database server system. Because MySQL is an evolving code repository, since press time, some of the design features explained here have likely changed and will continue to do so over time. If you look at the source code from one version to the next, you'll notice variations in the implementations of subsystems and how they interrelate; however, much of the way in which the system generally functions has persisted over the evolution of the software.
Even if you're not a C programming guru, you should be able to follow most of what we'll cover here. The focus will be less on the code itself and more on the structure and flow of operations within the server, and how the different code libraries interact with each other. Our intention is to provide a basic road map from which you can navigate the source code and documentation yourself. However, there are a few sections of this article that require a significant knowledge of C and C++ programming, and are meant for more advanced readers. If you don't have a whole lot of experience in C programming, just do your best to follow along, and don't worry about it too much!
The MySQL Source Code and Documentation
Since we're going to be looking at the MySQL server source code, you'll want to download a copy of the latest MySQL source code so that you can follow along, as well as embark on your own code review adventures. The source code used in this article comes from a copy of the source code for version 5.0.2. To download a copy of the source code, head over to MySQL's download site (http://dev.mysql.com/downloads/mysql/5.0.html) and download the version of interest to you.
Caution: The source distribution we used for this article's analysis came from the 5.0.2-alpha source tree. Bear in mind that MySQL is an evolving piece of software, and as such, various implementation details discussed in this article may change over time. Always obtain the proper source versions of development documentation before you assume anything is the case for a particular version of MySQL.
The Source Code
The source code is organized into a shallow directory tree containing the major libraries of the MySQL server and its different extensions.
Table 1 shows all the major top-level directories, with a brief description of the files contained in each directory and the general purpose of those files. As we progress through the article, we'll break down the information in this table into smaller groups that relate to each subsystem, but you may use this larger table as a reference.
Table 1. Top-Level Directories in the Source Tree
|/bdb||The Berkeley DB storage engine handler implementation files|
|/BUILD||Program compilation files|
|/client||The mysql command tool (client program) implementation files|
|/data||The mysql database (system database) schema, data, and index files|
|/dbug||Debugging utility code|
|/Docs||The documentation, both internal developer documents and the MySQL online manual|
|/heap||The MEMORY storage engine handler implementation files|
|/include||Core system header files and type definitions|
|/innobase||The InnoDB storage engine handler implementation files|
|/isam||The old ISAM storage engine handler implementation files|
|/libmysql||The MySQL C client API (all C source and header files)|
|/libmysqld||The MySQL server core library (C, C++, and some header files)|
|/libmysqltest||A simple program to test MySQL|
|/merge||The old Merge storage engine handler implementation files|
|/myisam||The MyISAM storage engine handler implementation files|
|/myisammrg||The MyISAM Merge storage engine handler implementation files|
|/mysys||The core function library, with basic low-level functions|
|/regex||The regular expression function library|
|/scripts||Shell scripts for common utilities|
|/share||Internationalized error messages|
|/sql||The meat of the server's implementation, with core classes and implementations for all major server and client activity|
|/sql- bench||MySQL benchmarking shell scripts|
|/strings||Lower-level string-handling functions|
|/support-files||Preconfigured MySQL configuration files (such as my-huge. cnf)|
|/tests||Test programs and scripts|
|/vio||Network/socket utility functions, virtual I/O, SSL, and so on|
|/zlib||Compression function source files|
You can take some time now to dig through the source code a bit, for fun, but you will most likely find yourself quickly lost in the maze of classes, structs, and C functions that com-pose the source distribution. The first place you will want to go is the documentation for the distribution, located in the /Docs directory. Then follow along with us as we discuss the key subsystems and where you can discover the core files that correspond to the different system functionality.