How is an SQL query executed? — Handling connections

This is the seventh day of my participation in the First Challenge 2022. For details: First Challenge 2022.

MySQL is a typical C/S architecture (client/server architecture), the client process sends a piece of text (MySQL instructions) to the server process, the server process does the statement processing and then returns the execution result.

Here’s the problem. What exactly does the server process do with the request sent by the client?

This document uses query requests as an example to explain how to process MySQL server processes.

As shown in the figure below, the server process takes three steps to process a client request:

• Deal with connection
• Analysis and Optimization
• The storage engine

Let’s take a closer look at what each step does.

1. Process the connection

The client sends a request to the server and eventually receives a response, which is essentially an interprocess communication process.

MySQL has a special module for handling connections called connectors.

1.1 Communication mode between the client and server

1.1.1 TCP/IP protocol

TCP/IP is the most commonly used communication method between MySQL client and server.

By default, the MySQL server listens on port 3306. This assumes that the client process and server process use TCP/IP protocol to communicate.

When we use mysql command to start the client program, as long as the IP address is followed by the -h parameter as the host address of the server process, then the communication mode is TCP/IP protocol.

If the client process and server process reside on the same host and want to use TCP/IP to communicate, the IP address must be set to 127.0.0.1 instead of localhost

1.1.2 UNIX domain sockets

If both the client process and the server process are running on a Unix-like host (MacOS, Centos, Ubuntu, etc.) and the client program is started without specifying a host name, localhost, or –protocol=socket as the startup parameter, The client and server processes then use UNIX domain sockets for interprocess communication.

Sock is the UNIX domain socket file that the MySQL server process listens to by default. The client process also connects to this UNIX domain socket file by default when it starts.

If you don’t know what a UNIX domain socket is, it’s a way for processes to communicate with each other

1.1.3 Naming Pipes and Shared Memory

If your MySQL is installed on a Windows host, client and server processes can communicate using named pipes and shared memory.

However, using these methods of communication requires adding some startup parameters when the server and client start up.

• Use named pipes for communication. It needs to be added when the server is started--enable-named-pipeParameter and is added when the client process is started--pipeor--protocol=pipeparameter

• Use shared memory for communication. It needs to be added when the server is started--shared-memoryParameter, the shared memory becomes the default connection mode of the local client program after successful startup. It can also be added to the command to start the client process--protocol=memoryParameter explicitly specifies the use of shared memory for communication

If you don’t know what named pipes and shared memory are, it’s ok to know that they are a way for processes to communicate with each other. The main purpose of this article is to know that MySQL clients and processes communicate with each other through more than TCP/IP

1.2 Permission Verification

Once the communication method has been confirmed and the connection has been successfully established, the connector begins to verify your identity using your username and password.

• If the user name or password is incorrect, the client is immediately disconnected
• If the user name and password are authenticated, the connector will check the permissions of the current logged-in user in the permission table. Then the permission logic inside the connection will depend on the permissions read at this point.

1.3 Checking the MySQL Connection

Each time a client connects to the server, the server process creates a separate thread to handle the current client interaction.

How to check all connections in MySQL?

mysql> show global status like 'Thread%';
​
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+
Copy the code

The following table describes the meanings of the fields

Once a connection is established, the server waits for the client to send a request unless the client voluntarily disconnects. However, the creation and maintenance of threads consumes server resources, so the server disconnects clients that have been inactive for a long time.

There are two parameters that control this automatic disconnection behavior, each of which defaults to 28800 seconds for 8 hours.

-- non-interactive timeout, such as mysql> show global variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ -- mysql> show global variables like 'interactive_timeout'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | interactive_timeout | 28800 | +---------------------+-------+Copy the code

Since connections consume resources, does MySQL have a default limit on the maximum number of connections? That’s right! The default maximum number of connections is 151.

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
Copy the code

Note: Some MySQL queries contain the global keyword. What does the global keyword mean?

MySQL system variables have two (case insensitive) scopes, respectively

• GLOBAL(Global scope) : Variable Settings affect the server and all clients
• SESSION(session scope) : Variable Settings affect only the current connection (session)

However, not every parameter has two scopes; for example, the number of clients allowed to connect to the server at the same time max_connections is only global.

When there is no scope keyword, the default is SESSION level, including query and modify operations.

For example, after modifying a parameter, it takes effect in the current window, but not in other Windows

show VARIABLES like 'autocommit';
set autocommit = on;
Copy the code

Therefore, if the change is temporary, use the SESSION level. If you want the current setting to take effect in other sessions, use the GLOBAL keyword.

At this point, the server process has established a connection with the client process, and the next step is to process the requests from the client.