MySQL middleware Proxysql (5): threads, thread pools, connection pools

1. Thread of ProxySQL

Proxysql consists of multiple modules and is a multithreaded daemon class program. Each module has one or more threads to perform the task.

For example, the following is the process when the Proxysql was started, a main process, a main thread, and 21 threads.

[[email protected] ~]# pstree | grep proxy    
        |-proxysql---proxysql---21*[{proxysql}]

The following is a list of threads used by the normal runtime:

1.1 Main Thread

This is actually a process that is responsible for booting, starting the core module, and starting other core threads.

1.2 Admin Thread

The thread is responsible for several things:

• Initializes and boots the admin interface.
  
• Loads the configuration from the disk database or configuration file, providing an environment for Proxysql to run.
  
• Start a listener that listens for and accepts new connections to the admin interface, and creates a new thread for each of these connections.

So, every time the admin interface is connected, it will be reborn into a thread. Each time you exit the admin interface, subtract one thread.

1.3 MySQL Workers

mysql-threadsThe thread is responsible for processing MySQL traffic, including all connections from the client and all connections to the back-end server nodes. That is, to handle any number of connections with a few threads.

The MySQL workers thread listens on the same port. When a new client initiates a connection request, one of the MySQL worker threads will successfully accept the connection and create a MySQL session: The client and session are bound on the worker thread until the connection is broken. In other words, all connections to a client are always handled by the same worker thread before disconnecting.

By default, the number of threads for MySQL worker is 4.

mysql> select @@mysql-threads;
+-----------------+
| @@mysql-threads |
+-----------------+
| 4               |
+-----------------+

mysql-threadsOnce a variable has been modified, proxysql must be restarted to take effect, which is one of the few variables that need to be restarted (the other ismysql-stacksize). For example, modify the 8 MySQL worker thread.

set mysql-threads=8;
save mysql variables to disk;

select * from runtime_global_variables 
 where variable_name='mysql-threads';
+---------------+----------------+
| variable_name | variable_value |
+---------------+----------------+
| mysql-threads | 4              |
+---------------+----------------+

service proxysql stop
service proxysql start

select * from runtime_global_variables 
 where variable_name='mysql-threads';
+---------------+----------------+
| variable_name | variable_value |
+---------------+----------------+
| mysql-threads | 8              |
+---------------+----------------+

1.4 MySQL Auxiliary Threads

These threads are actually idle threads (idle threads).

If Proxysql--idle-threadsis started with an option, each worker thread will be accompanied by the start of a auxiliary thread. Each worker thread and its auxiliary thread work together: the first thread processes the active connection and dispatches all the idle connections to the second thread, but the second thread waits for an event (or timeout) that occurs on an idle connection, giving the connection back to the first thread.

"Idle Threads" is strongly recommended when the number of active client connections is much less than the number of idle client connections. This allows the proxysql to handle hundreds of thousands of connections (100W connections at the time of the test).

1.5 Monitor module-related threads

The monitor module has its own thread management system, as well as its own thread pool. Under normal circumstances, the monitor module has several threads:

• A master thread that is responsible for generating and coordinating other monitor-related threads.
  
• A thread that is responsible for monitoringconnect.
  
• A thread that is responsible for monitoringping.
  
• A thread that is responsible for monitoringread_only.
  
• A thread that is responsible for monitoringreplication lag.
  
• A thread pool provides a monitor worker thread, each of which is the producer of the task, and the worker thread consumes a task from the task queue and executes the task. The thread pool initially defaults to twice times the MySQL thread.

The thread pool is responsible for performing all the inspection tasks and monitoring the scheduling situation through the above scheduling threads. The thread pool automatically grows and shrinks based on how much of the monitoring queue is being inspected. Based on the results of the check, the results are processed using the same thread, such as avoiding a node and reconfiguring a host group.

1.6 Query Cache Purge thread

The thread is generated when needed, and it acts as a garbage collector and reclaims the query cache. The garbage collector ensures that the cache is never recycled while the client waits for a response.

1.7 Other threads

Occasionally, temporary threads are derived during the Proxysql run in order to send the KILL statement to the back end to kill the query thread on the backend server that is unresponsive to the query for a long time.

In addition, the Ilbmariadbclient library uses some background threads to perform some specific asynchronous interaction tasks with the backend MySQL server.

There are also modules, such as the built-in HTTP server, cluster, Clickhouse server, and SQLite3 server, which are in the experimental phase, and if these are enabled, the corresponding threads are created on demand.

2. Thread Pool of ProxySQL

In Proxysql, there are two places where the thread pool is used:

• Quickly establish and back-end MySQL connections: Proxysql to establish a new TCP connection with the backend MySQL as soon as possible, a thread pool is used to wait for the accept () to return the new connection.
  
• Monitor module: To perform monitoring tasks for each monitoring thread as soon as possible, the monitor module provides a monitor worker thread pool that can quickly consume tasks from the task queue.

It is important to note that, under normal circumstances, the MySQL worker thread is the busiest and most CPU-intensive part, but in an extremely busy environment, the monitor module requires too many connections to be monitored and the CPU consumed is not negligible.

3.ProxySQL Connection Pool

Proxysql also has two connection pools, and the thread pool portion is the corresponding one.

3.1 Fast connection to back-end MySQL node

The thread pool is designed to quickly and backend establish a new TCP connection, where the connection pool is for quick and backend connections.

Proxysql uses a connection pool to hold a certain number of connections that have previously established a connection to a backend, but are currently idle connections. When new packets need to be sent to the back end of these connections, the connection can be retrieved quickly because the connections have been opened earlier.

When the application sends a MySQL request to proxysql, proxysql first resolves which backend to route to, and if the connection pool already has a connection to that backend, the connection will be reused, otherwise a new and backend connection will be created.

When the client's request is processed, the connection also returns to the host group Manager (HostGroup Manager). If the host group manager determines that the connection can be safely shared and the connection pool is not full, the connection is put into the connection pool.

Connections that are placed into the connection pool are idle connections, and connections that are in use are not likely to enter the connection pool. Proxysql will periodically send ping messages to maintain idle connections. If a connection has not been used since the last ping, the connection is defined as an idle connection. The time interval for idle connection pings is controlled by the variable mysql-ping_interval_server_msec.

However, not all unused connections will be placed in the connection pool. This variable is used to control the percentage of idle connections and the maximum total number of connections for a backend. For each hostgroup/backend, the host group manager will only keep the maximum number of connections in the connection poolmysql-free_connections_pct * mysql_servers.max_connections / 100. Each idle connection in the pool maintains its open state through a intermittent ping.

When a connection is put back into the connection pool, how many statements can be processed after the connection is calculated, and when the number of statements processed reaches that threshold, the connection is closed (before v1.4.3) or the connection is reset (starting with v1.4.4).

3.1.1 Related variables

mysql-ping_interval_server_msec
Proxysql in order to maintain and back-end idle connections, a ping is sent every once in a while, and the variable specifies the interval at which the pings are initiated. The default value is 10000 milliseconds (that is, 10 seconds).

mysql-ping_timeout_server
Proxysql to maintain and back-end idle connections, send a ping every once in a while. This variable specifies the time-out when the ping gets a reply. The default value is 200 milliseconds.

mysql-connection_max_age_ms
When the variable is set to a value greater than 0 o'clock (in milliseconds), the connection is closed if an idle connection (which is not currently used by any session) exceeds the value set here. The default value is 0, which means that idle connections are not closed because of the time to live.

3.2 Connection pool for monitor module

Monitor has its own pool of connections. When an idle connection in the connection pool reaches the idle time3 * mysql-monitor_ping_interval(in milliseconds), the idle connection is automatically purge.

mysql-monitor_ping_intervalthe default value for a variable is 1 minutes (60000 milliseconds), so idle connections in the monitor connection pool are maintained for a maximum of 3 minutes by default.

MySQL middleware Proxysql (5): threads, thread pools, connection pools