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.
Top-Level Directories
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
| Directory | Contents |
|---|---|
| /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.
| C AND C++ PROGRAMMING TERMS |
|---|
|
We’ll be referring to a number of C and C++ programming paradigms in this article. C source code files are those files in the distribution that end in c. C++ source files end in cc, or on some Windows systems, .cpp. Both C and C++ source files can include (using the #include directive) header files, identified by an .h extension. In C and C++, it is customary to define the functions and variables used in the source files in a header file. Typically, the header file is named the same as the source file, but with an . h extension, but this is not always the case. One of the first tasks you’ll attempt when looking at the source code of a system is identifying where the variables and functions are defined. Sometimes, this task involves looking through a vast hierarchy of header files in order to find where a variable or function is officially defined. Undoubtedly, you’re familiar with what variables and functions are, so we won’t go into much depth about that. In C and C++ programming, however, some other data types and terms are frequently used. Most notably, we’ll be using the following terms in this article:
A struct is essentially a container for a bunch of data. A typical definition for a struct might look something like this:
typedef struct st_heapinfo /* Struct heap_info */
{
ulong records; /* Records in database */
ulong deleted; /* Deleted records in database */
ulong max_records;
ulong data_length;
ulong index_length;
uint reclength; /* Length of one record */
int errkey;
ulonglong auto_increment;
} HEAPINFO;
This particular definition came from /include/heap.h. It defines a struct (st_heapinfo) as having a number of member variables of various data types (such as records, max_records) and typedefs (aliases) the word HEAPINFO to represent the st_heapinfo struct. Comments in C code are marked with the // or /* … */ characters. A class, on the other hand, is a C++ object-oriented structure that is similar to a C struct, but can also have member methods, as well as member variables. The member methods are functions of the class, and they can be called through an instance of the class. |
Doxygen for Source Code Analysis
A recommended way to analyze the source code is to use a tool like Doxygen (www.stack.nl/~dimitri/doxygen/index. html), which enables you to get the code structure from a source distribution. This tool can be extremely useful for navigating through functions in a large source distribution like MySQL, where a single execution can call hundreds of class members and functions. The documented output enables you to see where the classes or structs are defined and where they are implemented.
Doxygen provides the ability to configure the output of the documentation produced by the program, and it even allows for UML inheritance and collaboration diagrams to be produced. It can show the class hierarchies in the source code and provide links to where functions are defined and implemented.
On Unix machines, download the source code from the Doxygen web site, and then follow the manual instructions for installation (also available online at the web site). To produce graphical output, you’ll want to first download and install the Graph visualization toolkit from http://www.graphviz.org/. After installing Doxygen, you can use the following command to create a default configuration file for Doxygen to process:
# doxygen -g -s /path/to/newconfig.file
The option /path/to/newconfig.fileshould be the directory in which you want to even-tually produce your Doxygen documentation. After Doxygen has created the configuration file for you, simply open the configuration file in your favorite editor and edit the sections you need. Usually, you will need to modify only the OUTPUT_DIRECTORY, INPUT, and PROJECT_NAME settings. Once you’ve edited the configuration file, simply execute the following:
# doxygen </path/to/config-file>
For your convenience, a version of the MySQL 5.0.2 Doxygen output is available at http://www.jpipes.com/mysqldox/.
The MySQL Documentation
The internal system documentation is available to you if you download the source code of MySQL. It is in the Docs directory of the source tree, available in the internals.texi TEXI document.
The TEXI documentation covers the following topics in detail:
- Coding guidelines
- The optimizer (highly recommended reading)
- Important algorithms and structures
- Charsets and related issues
- How MySQL performs different SELECT operations (very useful information)
- How MySQL transforms queries
- Communication protocol
- Replication
- The MyISAM record structure
- The .MYI file structure
- The InnoDB record structure
- The InnoDB page structure
Although the documentation is extremely helpful in researching certain key elements of the server (particularly the query optimizer), it is worth noting that the internal documentation does not directly address how the different subsystems interact with each other. To determine this interaction, it is necessary to examine the source code itself and the comments of the developers.2
Caution Even the most recent internals.texi documentation has a number of bad hyperlinks, references, and incorrect filenames and paths, so do your homework before you take everything for granted. The internals.texi documentation may not be as up-to-date as your MySQL server version!
TEXI and texi2html Viewing
TEXI is the GNU standard documentation format. A number of utilities can convert the TEXI source documentation to other, perhaps more readable or portable, formats. For those of you using Emacs or some variant of it, that editor supports a TEXI major mode for easy reading.
If you prefer an HTML version, you can use the free Perl-based utility texi2html, which can generate a highly configurable HTML output of a TEXI source document. texi2html is available for download from https://texi2html.cvshome.org/. Once you’ve downloaded this utility, you can install it, like so:
# tar -xzvf texi2html-1.76.tar.gz # cd texi2html-1.6 # ./configure # make install
Here, we’ve untarred the latest (as of this writing) texi2html version and installed the software on our Linux system. Next, we want to generate an HTML version of the internals.texi document available in our source download:
# cd /path/to/mysql-5.0.2-alpha/ # texi2html Docs/internals.texi
After installation, you’ll notice a new HTML document in the /Docs directory of your source tree called internals.html. You can now navigate the internal documentation via a web browser. For your convenience, this HTML document is also available at http://www.jpipes.com/mysqldox/.
MySQL Architecture Overview
MySQL’s architecture consists of a web of interrelated function sets, which work together to fulfill the various needs of the database server. A number of authors3 have implied that these function sets are indeed components, or entirely encapsulated packages; however, there is little evidence in the source code that this is the case.
Indeed, the architecture includes separate function libraries, composed of functions that handle similar tasks, but there is not, in the traditional object-oriented programming sense, a full component-level separation of functionality. By this, we mean that you will be disappointed if you go into the source code looking for classes called BufferManager or QueryManager. They don’t exist. We bring this point up because some developers, particularly ones with Java back-grounds, write code containing a number of “manager” objects, which fulfill the requests of client objects in a very object-centric approach. In MySQL, this simply isn’t the case.
In some cases—notably in the source code for the query cache and log management subsystems—a more object-oriented approach is taken to the code. However, in most cases, system functionality is run through the various function libraries (which pass along a core set of structs) and classes (which do the dirty work of code execution), as opposed to an encapsulated approach, where components manage their internal execution and provide an API for other components to use the component. This is due, in part, to the fact that the system archi-tecture is made up of both C and C++ source files, as well as a number of Perl and shell scripts that serve as utilities. C and C++ have different functional capabilities; C++ is a fully object-oriented language, and C is more procedural. In the MySQL system architecture, certain libraries have been written entirely in C, making an object-oriented component type architec-ture nearly impossible. For sure, the architecture of the server subsystems has a lot to do with performance and portability concerns as well.
Note As MySQL is an evolving piece of software, you will notice variations in both coding and naming style and consistency. For example, if you compare the source files for the older MyISAM handler files with the newer query cache source files, you’ll notice a marked difference in naming conventions, commenting by the developers, and function-naming standards. Additionally, as we go to print, there have been rumors that significant changes to the directory structure and source layout will occur in MySQL 5.1.
Furthermore, if you analyze the source code and internal documentation, you will find little mention of components or packages.4 Instead, you will find references to various task-related functionality. For instance, the internals TEXI document refers to “The Optimizer,” but you will find no component or package in the source code called Optimizer. Instead, as the internals TEXI document states, “The Optimizer is a set of routines which decide what execution path the RDBMS should take for queries.” For simplicity’s sake, we will refer to each related set of functionality by the term subsystem, rather than component, as it seems to more accurately reflect the organization of the various function libraries.
Each subsystem is designed to both accept information from and feed data into the other subsystems of the server. In order to do this in a standard way, these subsystems expose this functionality through a well-defined function application programming interface (API).5. As requests and data funnel through the server’s pipeline, the subsystems pass information between each other via these clearly defined functions and data structures. As we examine each of the major subsystems, we’ll take a look at some of these data structures and methods.
MySQL Server Subsystem Organization
The overall organization of the MySQL server architecture is a layered, but not particularly hierarchical, structure. We make the distinction here that the subsystems in the MySQL server architecture are quite independent of each other.
In a hierarchical organization, subsystems depend on each other in order to function, as components derive from a tree-like set of classes. While there are indeed tree-like organiza-tions of classes within some of the subsystems—notably in the SQL parsing and optimization subsystem—the subsystems themselves do not follow a hierarchical arrangement.
A base function library and a select group of subsystems handle lower-level responsibilities. These libraries and subsystems serve to support the abstraction of the storage engine systems, which feed data to requesting client programs. Figure 1 shows a general depiction of this layering, with different subsystems identified.
Note that client programs interact with an abstracted API for the storage engines. This enables client connections to issue statements that are storage-engine agnostic, meaning the client does not need to know which storage engine is handling the data request. No special client functions are required to return InnoDB records versus MyISAM records. This arrangement enables MySQL to extend its functionality to different storage requirements and media.
Figure 1. MySQL subsystem overview
Base Function Library
All of MySQL’s subsystems share the use of a base library of common functions. Many of these functions exist to shield the subsystem (and the developers) from needing to operate directly with the operating system, main memory, or the physical hardware itself.6 Additionally, the base function library enables code reuse and portability. Most of the functions in this base library are found in the C source files of the /mysys and /strings directories. Table 2 shows a sampling of core files and locations for this base library.
Table 2. Some Core Function Files
| File | Contents |
|---|---|
| /mysys/array. c | Dynamic array functions and definitions |
| /mysys/hash. c/. h | Hash table functions and definitions |
| /mysys/mf_qsort . c | Quicksort algorithms and functions |
| /mysys/string. c | Dynamic string functions |
| /mysys/my_alloc . c | Some memory allocation routines |
| /mysys/mf_pack. c | Filename and directory path packing routines |
| /strings/* | Low-level string and memory manipulation functions, and some data type definitions |
Endnotes
- At the time of this writing, the MySQL server consists of roughly 500,000 lines of sourcecode.
- Whether the developers chose to purposefully omit a discussion on the subsystem’s communication in order to allow for changes in that communication is up for debate.
- For examples, see MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne) and http://wiki.cs.uiuc.edu/cs427/High-Level+Component+Diagram+of+the+MySQL+Architecture.
- The function init_server_components() in /sql/mysqld.cpp is the odd exception. Really, though, this method runs through starting a few of the functional subsystems and initializes the storage handlers and core buffers.
- This abstraction generally leads to a loose coupling, or dependence, of related function sets to each other. In general, MySQL’s components are loosely coupled, with a few exceptions.
- Certain components and libraries, however, will still interact directly with the operating system or hardware where performance or other benefits may be realized.
Conclusion
We hope this article has been a fun little excursion into the world of database server
internals. So, what have we covered in this article? Well, we started off with some instructions on
how to get your hands on the source code, and configure and retrieve the documentation in
various formats. Then we outlined the general organization of the server’s subsystems.
About the Authors
Mike Kruckenberg is a long-time MySQL devotee who has used MySQL personally and professionally since the early days of web-based applications. Besides having been the go-to guy for all things MySQL at his day (and night) jobs over the years, Mike is an active member of the MySQL community. In addition to being the coauthor of Pro MySQL, he is a coauthor on the MySQL Cluster Certification Study Guide and periodically writes about MySQL for Linux Magazine. He did the technical review for the soon-to-be published Expert MySQL (Apress) on MySQL source code modifications. Mike is a member of the MySQL Speakers, Writers, and Experts Guilds, regularly presents at tech conferences, and actively writes about MySQL and other (mostly) technical things at mike.kruckenberg.com.
Jay Pipes is the North American Community Relations Manager at MySQL. Coauthor of Pro MySQL (Apress, 2005), Jay has also written articles for Linux Magazine and regularly assists software developers in identifying how to make the most effective use of MySQL. He has given sessions on performance tuning at the MySQL Users Conference, RedHat Summit, NY PHP Conference, OSCON, and Ohio LinuxFest, among others. He lives in Columbus, Ohio, with his wife, Julie, and his four animals. In his abundant free time, when not being pestered by his two needy cats and two noisy dogs, he daydreams in PHP code and ponders the ramifications of __clone().
Source of This Material
Pro MySQL
By Michael Kruckenberg, Jay Pipes
Published: July 2005, Paperback: 768 pages
Published by Apress
ISBN: 159059505X
Price: $49.99
eBook Price: $25.00
This material is from Chapter 4 of the book.
Reprinted with the publisher’s permission.
