The problem

1. What does the connector do? 2. What is a long connection? What is a short link? 3. What is connection thread pool? 4. How to optimize connection number in daily work?

Establish a connection

Connectors establish connections to clients, obtain permissions, and maintain and manage connections. Typically we access the database in the following form. This mode is TCP/IP.

mysql -h$ip -P$port -u$user -p
Copy the code

Of course, if both the client and the database are on the same machine, Unix Sockets can be used

mysql -uroot
Copy the code

After the connection is established, the connector will go to the permission table to find out which permissions you have. After that, the permission determination logic in the connection will depend on the permissions read at that time. After a user successfully establishes a connection, even if you use the administrator account to modify the permission of this user, it will not affect the permission of the existing connection. After the modification is complete, only newly created connections will use the new permission Settings.

When to disconnect?

According to the disconnection time, the connection is divided into: long connection and short connection

  • Long connection: After connecting, multiple requests can be sent until the client manually disconnects. If the thread has been in the sleep state since the connection, the thread will passwait_timeoutThe system automatically disconnects after 8 hours by default.
  • Short connection: After a connection is made, the connection is disconnected after one request is sent. The next database request must be reconnected.

Short connections are frequently created. In a busy system, the number of system ports may be used up and connections cannot be made. A long connection avoids frequent connection creation, but the memory required for the connection is not released until the connection is closed. Therefore, if the long connection time is too long, the database memory may soar. Take PHP as an example:

  1. When we use PHP as a Web service, the database may be accessed multiple times in a single request to query or modify the data. In general (without special Settings), it is usedA long connectionThe way to access. When the request ends, the interface returns a response. The long connection is broken. You can show ProcessList to see all current connections.
  2. If you write a resident script in PHP with database access, the long connection may always exist (and should be avoided).

One situation I encountered in a project was writing a script that subscribed to a message queue, consumed messages from the queue, and then read and write to the database based on the message content. However, due to insufficient traffic in the early stage, the message queue may not consume messages for a long time. After 8 hours, the connection will be automatically disconnected. For such resident scripts, it is recommended to manually disconnect and then reconnect to the database at regular intervals. Or after executing a series of complex database requests, disconnect and reconnect on the next request

What is a connection thread pool?

Typically, there is one thread per connection. The MySQL connection manager thread is responsible for handling client connection requests on the network interface that the server listens to. Whenever a connection request comes in, create a new thread to handle it. However, the cost of creating new threads is expensive, so Mysql uses a thread pool to cache idle threads for later connections. The connection management thread processes the client connection request as follows: 1. Check whether there are idle threads in the thread pool, and if so, fetch a thread for the client connection request. 2. If no idle thread exists, check whether the number of existing connections exceeds the maximum number. If no, create a new thread to process the client connection request.

How to optimize connections

There is an important parameter called max_connections, which refers to the maximum number of Mysql connections. For example, if set to 5, a maximum of 5 clients can connect at the same time. If the number of connections is already full, the root user can still connect to another one. That is, max_connections + 1 root connection is actually allowed. View the maximum number of connections

show variables like '%max_connections%';
Copy the code

If you set the maximum number of connections, the service fails to restart. You can modify the.cnf file if you want to make it permanent.

set GLOBAL max_connections = 200;
Copy the code

We can look at the maximum number of connections in the past and get the maximum number of connections in the past.

show global status like 'max_used_connections';
Copy the code

To give the server some leeway, max_USED_connections /max_connections = 0.8 is recommended

Viewing threads

show status like 'Threads%'
Copy the code
  • Threads_cached identifies the number of idle threads being cached
  • Threads_connected indicates the number of threads in the connected state
  • Threads_running indicates the number of threads that are performing queries, transferring data, and so on

You can control the size of the cache thread pool by controlling thread_cache_size. Increasing this value can improve system performance as concurrency increases. See how many connections you can cache

show global variables like 'thread_cache_size';
Copy the code

conclusion

  • Mysql connection is divided intoA long connectionandShort connection
  • The number of connections is controlled by max_connections and set appropriatelymax_connections 和thread_cache_sizeImproves system performance.