How MySQL Server Processes Network Requests

This article provides a technical overview of how the MySQL server daemon (mysqld) manages and processes incoming network requests from clients. It covers the lifecycle of a database request, including network listening, connection handling, authentication, query parsing, execution by the storage engine, and the transmission of results back to the client.

1. Listening and Connection Acceptance

The MySQL daemon (mysqld) runs continuously as a background process, listening for incoming connection requests. By default, it listens on TCP port 3306, though it can also listen on a Unix socket file for local connections or named pipes/shared memory on Windows.

The main thread of the MySQL server initializes the network communication system. It utilizes system calls (like select(), poll(), or epoll() depending on the operating system) to monitor the configured port for incoming TCP connection attempts from client applications.

2. Thread Allocation and Connection Handling

Once a connection request is detected and accepted, MySQL delegates the connection to a thread. MySQL primarily uses one of two connection-handling models:

• Thread-Per-Connection (Default): The server spawns a new thread or retrieves an idle thread from the internal thread cache for every client connection. This thread remains dedicated to the specific client until the connection is closed.
• Thread Pool (Enterprise/Plugins): To handle thousands of concurrent connections efficiently, a thread pool can be used. Instead of a dedicated thread per connection, a limited pool of worker threads multiplexes requests from multiple client connections, reducing thread-creation overhead and context switching.

3. Handshake and Authentication

Before any queries can be executed, the allocated thread manages the authentication handshake:

1. Protocol Handshake: The server sends an initial handshake packet to the client, specifying the protocol version, server version, connection ID, and supported capabilities (like SSL support).
2. SSL/TLS Negotiation: If configured and supported, the client and server negotiate an encrypted SSL/TLS session.
3. Credential Verification: The client sends its username, encrypted password, and target database name. The MySQL thread verifies these credentials against the internal grant tables (mysql.user).
4. Host Verification: MySQL checks if the client’s host IP is permitted to connect. If authentication succeeds, the connection is established; otherwise, the connection is terminated with an error packet.

4. Command Dispatching and Query Parsing

Once authenticated, the dedicated thread enters a loop, waiting for commands from the client sent via the MySQL Client/Server Protocol. When a network packet containing a SQL statement arrives:

1. Command Dispatcher: The server reads the packet and routes it to the appropriate command handler (e.g., COM_QUERY for SQL queries).
2. Query Cache Check (Legacy): In older MySQL versions (prior to 8.0), the server checked the Query Cache. If an exact match was found, the cached result was sent immediately. This feature has been entirely removed in modern MySQL versions.
3. Parsing: The SQL parser breaks the query string into tokens and builds a logical parse tree structure to ensure the SQL syntax is valid.
4. Preprocessing (Resolution): The preprocessor checks the parse tree for semantic correctness. It verifies that tables and columns exist, resolves aliases, and confirms that the authenticated user has the necessary privileges to perform the requested operation on those objects.

5. Query Optimization and Execution

With a valid parse tree, MySQL prepares to execute the query:

1. Query Optimizer: The optimizer analyzes the parse tree and generates multiple execution plans. It calculates the “cost” (mostly based on estimated disk I/O and CPU usage) of different access paths, such as using specific indexes versus performing a full table scan. The optimizer then selects the lowest-cost plan.
2. Query Execution Engine: The execution engine translates the selected plan into a series of instructions and coordinates with the storage engine API.
3. Storage Engine Interaction: MySQL utilizes a pluggable storage engine architecture. The execution engine calls the Handler API (e.g., handler::ha_index_read or handler::ha_write_row) to request data from the active storage engine, such as InnoDB. The storage engine retrieves the data from its buffer pool or reads it directly from the physical disk.

6. Result Packaging and Network Transmission

After the execution engine receives the raw data from the storage engine:

1. Formatting: The server formats the rows into MySQL protocol packets.
2. Buffering: The formatted packets are written to the network send buffer associated with the client socket.
3. Transmission: The TCP/IP stack transmits the data packets back to the client application.
4. Clean Up: Once the complete result set is sent, the thread frees the memory allocated for the query execution and waits for the next command from the client.