In this article, Vikram gives us a sneak-peek under the hood of MySQL to see what makes it tick, all the while explaining the various MySQL subsystems and how they interact with each other. This excerpt comes from Chapter two of MySQL: The Complete Reference (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004).
In addition to the five primary subsystems, the MySQL architecture contains the following two support components:
The Process Manager
The Process Manager This component performs two functions in the system. First, it manages user connections, via modules for network connection management with clients. Second, it synchronizes competing tasks and processes, via modules for multi-threading, thread locking, and performing thread-safe operations.
Function Libraries This component contains general-purpose routines that are used by all the other subsystems. It includes routines for string manipulation, sorting operations, and such operating-system-specific functions as memory management and file I/O.
Subsystem/Component Interaction and Control Flow The Query Engine requests that data be read from or written to the Buffer Manager to satisfy a user query. It depends on the Transaction Manager to request the locking of data so that concurrency is ensured. To perform table creation and drop operations, the Query Engine accesses the Storage Manager directly, bypassing the Buffer Manager, to create or delete files in the file system.
The Buffer Manager caches data from the Storage Manager for efficient retrieval by the Query Engine. It depends on the Transaction Manager to check the locking status of the data before it performs any modification operations.
The Transaction Manager depends on the Query Cache and the Storage Manager to place locks on data in memory and in the file system, respectively.
The Recovery Manager uses the Storage Manager to store command/event logs and backups of the data in the file system. It depends on the Transaction Manager to obtain locks on the log files being written. The Recovery Manager also needs to use the Buffer Manager during recovery from crashes.
The Storage Manager depends on the operating system file system for persistent storage and retrieval of data. It depends on the Transaction Manager to obtain locking status information.
Remember: this is chapter two of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed. Buy this book now.