MySQL Architecture

MySQL Architecture

MySQL Follow the RDBMS conceptual architecture.  MySQL Architecture shows how the request flow is going on and how the data is retrieving.

 MySQL Architecture is divided into 2 layers

1. Application layer

The application layer represents the interface for all the users of the system. And it shows how the clients and users interact with the MySQL RDBMS.

There are three components in this layer.

    Administrator      Clients    Query Users

Administrators performs the various administrative operations like taking dump, reset the root password and repair the tables,etc. by using administrative interface and utilities like mysqladmin, myqsldump and myisamchk etc.

Clients communicate with the MySQL RDBMS through various interfaces and utilities like the MySQL APIs. (PHP, JAVA, .NET)... (Like insert,update,delete,....)

Query users interact with MySQL RDBMS using “mysql”.(Only select statements)

2. Logical layer

 The logical layer of MySQL architecture is divided into various subsystems.

        1. Query Processor.

        2. Transaction Management.

        3. Recovery Management.

        4. Storage Management.

The above mentioned sub systems work together to process the requests issued to the MySQLdatabase server.

 

MySQL Architecture

1.Query Processor

Query Processor further consists of the following systems.

1.  Embedded DML Precompiler.

2.  DDL Compiler.

3.  Query Parser.

4.  Query Preprocessor.

5.  Security/Integration Manager.

6.  Query Optimizer.

7.  Execution Engine.

·     The output of one of the above component becomes the input for another.

1. Embedded DML Precompiler

When a request is received from a client in the application layer, it is the responsibility of the embedded DML (Data Manipulation Language) precompiler to extract the relevant SQL statements embedded in the client API(Java,.NET,PHP).

2. DDL Compiler.

Requests to access the MySQL databases received from an administrator are processed by the DDL (DataDefinition Language) compiler. The DDL compiler compiles the commands (which are SQL statements) to interact directly with the database. The administrator and administrative utilities do not expose an interface,and hence execute directly to the MySQL server. Therefore, the embedded DML precompiler does not process it, and this explains the need for a DDL compiler.

3 Query Parser

In this stage, the objective of the query parser is to

 create a parse tree structure based on the query so that it can be easily understood by the other components later in the pipeline.

4 Query Preprocessor

The query parse tree, as obtained from the query parser, is then used by the query preprocessor to check the SQL syntax and check the semantics of the MySQL query to determine if the query is valid. If it is a valid query, then the query progresses down the pipeline. If not, then the query does not proceed and the client is notified of the query processing error

.

5. Security/Integration Manager

·       Checks access control list.

·       Checks whether client has proper access for connection.

·       Checks table and record level privileges.

·

6. Query Optimizer

MySQL uses the query optimizer for executing SQL queries as fast as possible.

7. Execution Engine

Once the MySQL query optimizer has optimized the MySQL query, the query can then be executed against the database. This is performed by the query execution engine, which then proceeds to execute the SQL statements and access the data from MySQL database.

 2. Transaction Management.

1 Transaction Manager

A transaction is a single unit of work that has one or more MySQL commands in it. The transaction manager is responsible for making sure that the transaction is logged and executed atomically. It does so through the aid of the log manager and the concurrency-control manager. Moreover, the transaction manager is also responsible for resolving any deadlock situations that occur.

2 Concurrency- Control Manager

The concurrency-control manager is responsible for making sure that transactions are executed separately and independently.

3. Recovery Management.

1. Log Manager

• Logs every operation executed in the database.
• Stores the operations logs as MySQL Commands.
• In case of SYSTEM crash executing these commands will bring back the database to its last stable state.

•  

2. Recovery Manager

• Responsible for recovering the database to its last stable state.
• Uses the logs created by the log manager.

4. Storage Management.

Storage is physically done on some type of secondary storage, however dynamic access of this medium is not practical. Thus, all work is done through a number of buffers. The buffers reside in main and virtual memory and are managed by a Buffer Manager. This manager works in conjunction with two other manager entities related to storage: the Resource Manager and the Storage Manager.

1. Storage Manager

• It acts like an interface with the OS.
• Its main job is efficient data write to the disk.
• Storage Manager writes to disk all of the data in the user tables, indexes, logs as well as the internal system data.

• 
  

2. Buffer Manager

• It allocated memory resources.

• It decides

• 
  

• how much memory to allocate per buffer.
• how may buffers to allocate per memory.

• 
  

3. Resource Manager

• Accepts the requests from execution engine.
• Requests the details from buffer manager.
• It actually receives references of data with memory from buffer manager.
• Returns this data to the upper layer.