An Overview of MySQL Architecture (cont'd) - MySQL

The Storage Manager
The Storage Manager interfaces with the operating system (OS) to write data to the disk efficiently. Because the storage functions reside in a separate subsystem, the MySQL engine operates at a level of abstraction away from the operating system. This means that if you port to a different operating system that uses a different storage mechanism, for example, you can rewrite only the storage portion of the code while leaving the rest of the engine as is. With the help of MySQL’s Function Libraries (discussed shortly in the section “Support Components”), the Storage Manager writes to disk all of the data in the user tables, indexes, and logs as well as the internal system data.

The Query Cache   If a query returns a given set of records, repeating the same query should return the same set of records unless the underlying data has somehow changed. As obvious as this sounds, few of the other major relational database management system (RDBMS) vendors provide features that take advantage of this principle. Other database products are efficient in storing optimized access plans that detail the process by which data is retrieved; such plans allow queries similar to those that have been issued previously to bypass the process of analyzing indexes yet again to get to the data.

Result set caching takes this principle a step further by storing the result sets themselves in memory, thus circumventing the need to search the database at all.  The data from a query is simply placed in a cache, and when a similar query is issued, this data is returned as if in response to the query that created it in the first place.

The MySQL engine uses an extremely efficient result set caching mechanism, known as the Query Cache, that dramatically enhances response times for queries that are called upon to retrieve the exact same data as a previous query.

This mechanism is so efficient that a major computing publication declared MySQL queries to be faster than those of Oracle and SQL Server (which are both known for their speed). If implemented properly, decision support systems using MySQL with canned reports or data-driven web pages can provide response speeds far beyond those that would be expected without the Query Cache.

The Buffer Manager
This subsystem handles all memory management issues between requests for data by the Query Engine and the Storage Manager. MySQL makes aggressive use of memory to cache result sets that can be returned as-is rather than making duplicate requests to the Storage Manager; this cache is maintained in the Buffer Manager.

This is also the area where new records can be cached while waiting for availability of targeted tables and indexes. If any new data is needed, it’s requested from the Storage Manager and placed in the buffer before then being sent to the Query Engine.

The Transaction Manager
The function of the Transaction Manager is to facilitate concurrency in data access. This subsystem provides a locking facility to ensure that multiple simultaneous users access the data in a consistent way, without corrupting or damaging the data in any way. Transaction control takes place via the Lock Manager subcomponent, which places and releases locks on various objects being used in transactions. Each transactional table handler implements its own Transaction Manager to handle all locking and concurrency needs.

The Recovery Manager
The Recovery Manager’s job is to keep copies of data for retrieval later, in case of a loss of data. It also logs commands that modify the data and other significant events inside the database.

So far, only the InnoDB and BDB table handlers provide recovery management. The MyISAM handler doesn’t have transactional recovery procedures, but it does provide mechanisms that apply certain recovery features in case of a server outage; these features “fix” any internal inconsistencies that might occur as the result of such a crash. Such inconsistencies are usually related to indexes not being properly updated to reflect the contents of a table or records being incompletely written to a database.

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.