MySQL Architecture -Client Server Architecture

Client/Server Overview

The MySQL database system operates using a client/server architecture. The server is a central program that manages database contents, and client programs connect to the server to retrieve or modify the data. MySQL also includes non-client utility programs and scripts.

MySQL Server: 

This is the mysqld program that manages database and tables. Most users choose binary MySQL distribution that includes a server ready to run with the capabilities they need, but it's also possible to compile MySQL from source.

Client Programs: These are programs that communicate with the server by sending requests to it over a network connection. The server acts on each request and returns a response to the client. For example you can use the mysql client to send queries to the server, and the server returns the query results. A client program can connect locally to a server running on the same machine or remotely to a server running on a different machine.

Non-client utility programs: These are programs that generally used for special purposes and do not acts as clients of the server. They do not connect to the server, for example mysqld_safe is a script for starting and stopping the server. Myisamchk is a standalone utility for table check and repair.

Application layer

This is where the client interact with the RDBMS. This layer is used by three users:

Administrator.

client.

User.

i) Administrative Interface and Utilities 

          This layer is used by Administrator. This includes utilities such as mysqladmin, myisamchk, mysqldump etc.

ii) Client Interface and Utilities

This layer is used by the clients to communicate with RBDMS. The client interface uses MySQL APIs for various different programming languages such as the  PHP API, Java API, etc.

iii) Query Interface

• This use the mysql as a “interactive tool” that issue sql statements to the server and displays results to the screen.

Logical layer

All the DML statements that are sent from the application layer are parsed and optimized in the query processor.

It is again sub-divided into following layers:

i) 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 commands, or to translate the client commands into the corresponding SQL statements.

ii) DDL Compiler

• Request received from the administrator are processed by the DDL Compiler.
• The administrative utility does not expose any interface and hence executed directly by the server.

iii) Query Preprocessor 

• The query preprocessor parses the query. The parsing is of 2 types

1) syntactical

2) semantical

If the query syntax is right then it is sent to the next pipe . Else it inform to the client with an a error message.

iv)Query Optimizer

• Query optimizer is the brain of the MySQL.
• Once client has permission to execute query, the query is optimized by the query optimizer. The task of the query optimizer is analyze the processed query to see if it can take advantage of any optimizations that will allow it to process the query more quickly.MySQL query optimizer uses indexes.

v) 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 physical layer. As well the database administrator can execute commands on the database to perform specific tasks such as repair, recovery, copying and backup, which it receives from the DDL compiler.

MySQL Transaction Management

i) 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.  And also prevents from deadlocks. Furthermore, the transaction manager is responsible for issuing the COMMIT and the ROLLBACK SQL commands. The COMMIT command commits to performing a transaction. Thus, a transaction is incomplete until it is committed to. The ROLLBACK command is used when a crash occurs during the execution of a transaction. If  a transaction were left incomplete, the ROLLBACK command would undo all changes made by that transaction. The result of executing this command is restoring the database to its last stable state.

• Ensures Atomicity
• Avoids Deadlocks
• Responsible for Commit & Rollback

ii) Concurrency- Control Manager

          The concurrency-control manager is responsible for making sure that transactions are executed separately and  independently. It does so by acquiring locks. Once the lock is acquired, only the operations in one transaction can manipulate the data. If a different transaction tries to manipulate the same locked data,  the concurrency-control manager rejects the request until the first transaction is complete .

Recovery Management

iii) Log Manager

The log manager is responsible for logging every operation executed in the database. It does so by storing the log on disk through the buffer manager. The operations in the log are stored as MySQL commands. Thus, in the case of a system crash, executing every command in the log will bring back the database to its last stable state.

 iv) Recovery Manager

The recovery manager is responsible for restoring the database to its last stable state. It does so by using the log for the database, which is acquired from the buffer manager, and executing each operation in the log. Since the log manager logs all operations performed on the database, executing each command in the log file would recover the database to its last stable state.

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.

i) Resource Manager

The purpose of the Resource Manager is to accept requests from the execution engine, put them into table requests, and request the tables from the Buffer Manager. The Resource Manager receives references to data within memory from the Buffer Manager and returns this data to the upper layers.

ii) Buffer Manager

The role of the Buffer Manager is to allocate memory resources for the use of viewing and manipulating data. The Buffer Manager takes in formatted requests and decides how much memory to allocate per buffer and how many buffers to allocate per request. All requests are made from the Resource Manager.

(key_buffer, sort_buffer, myisam_sort_buffer)

iii) Storage Manager

The storage manager acts as a mediator to send request between buffer manager and secondary storage.