Thread Pool Plugin Usage Example

Using the Thread Pool Plugin

After the thread pool plugin is successfully installed, a new connector is generated, that is, the thread pool plugin connector. The original default connector is retained and used to process query requests for connections established before the thread pool plugin is installed. The thread pool plugin connector is used to process query requests for new connections.

After the patch is applied, directly compile and install the thread pool plugin. Alternatively, after the compilation is successful, copy the thread_pool.so file from the plugin_output_directory/ directory in the compilation path to the directory specified by plugin_dir of the target MySQL, and then install the plugin.

Installation methods:

Method 1: Run the following SQL statement to install the thread pool plugin.

The thread pool plugin installed using this method takes effect immediately.

          
               INSTALL PLUGIN thread_pool SONAME "thread_pool.so";
INSTALL PLUGIN THREAD_POOL_GROUPS SONAME "thread_pool.so";
INSTALL PLUGIN THREAD_POOL_QUEUES SONAME "thread_pool.so";
INSTALL PLUGIN THREAD_POOL_STATS SONAME "thread_pool.so";
INSTALL PLUGIN THREAD_POOL_WAITS SONAME "thread_pool.so";

The thread_pool.so file contains the five plugins displayed in the preceding command. Among them, thread_pool is the thread pool connector plugin, and THREAD_POOL_GROUPS, THREAD_POOL_QUEUES, THREAD_POOL_STATS, and THREAD_POOL_WAITS are the thread pool plugin status monitoring tables. After the installation is complete, you can view them in the INFORMATION_SCHEMA table of MySQL. For details, see New information_schema Tables.

Method 2: Add the thread pool plugin information to the MySQL configuration file.

After installing the thread pool plugin in this method, restart the database for the installation to take effect.

          
               plugin-load-add=thread_pool.so

After the installation is complete, you can run the following SQL statement to check whether the installation is successful:

          
               show plugins;

If the status is ACTIVE in the command output, the plugin has been successfully installed.

Adjust and tune the MySQL thread pool based on the following tuning suggestions.

The default path of the database configuration file is /etc/my.cnf. If you want to use a configuration file in another path, you can use the --defaults-file option to specify the configuration file, for example, /tmp/myconfig.txt.

          
               mysqld --defaults-file=/tmp/myconfig.txt

Parameter	Description	Recommended Configuration
thread_pool_size	Number of thread groups in the thread pool.	The default value indicates that the number of thread groups is the same as that of CPU cores. To achieve optimal performance, you can set the number of thread groups to one to three times that of CPUs or the optimal number of concurrent threads based on the actual scenario. For example, the number of connections exceeds the number of logical CPU cores, the performance bottleneck does not lie in lock contention, and the CPU pressure is not full.
thread_pool_oversubscribe	Oversubscribing number of threads in each thread group. If this parameter is set to the default value, this parameter indicates the oversubscribing number of threads of each CPU core. The default value is 3, which is an empirical value that can fully utilize CPU resources. If this parameter is set to a value smaller than 3, more sleep and wake-up events may occur.	If the number of active working threads in a thread group exceeds the value of this parameter, the number of active working threads is too large and you need to reduce this number. You are advised to set this parameter to the number of concurrent threads or the thread_pool_size value that delivers optimal performance.
thread_pool_toobusy	Threshold of the number of threads that determines whether a thread group is too busy.	When the number of active working threads in the thread group plus the number of working threads in lock or I/O waiting is greater than the threshold plus 1, the thread group is considered too busy and does not process low-priority tasks. Instead, the thread group only processes ongoing tasks and those tasks in the high-priority queue until the thread group returns to the non-busy state. You are advised to set this parameter to the same value as thread_pool_oversubscribe.
thread_pool_dedicated_listener	Indicates whether the listener thread only waits for network events by calling epoll_wait.	Set this parameter to ON. After network events are obtained, the listener thread puts all network event tasks in the priority queue or common queue, and then calls epoll_wait to wait for network events. In this way, network events can be obtained more efficiently.

Perform a TPC-C test to obtain the performance improvement data after the MySQL thread pool feature is used. For details about the test, see BenchMarkSQL Test Guide.
A thread pool connector is suitable for scenarios where there are a wealth of connections for OLTP short queries. In OLTP TPC-C scenarios, before the thread pool is enabled, the performance of MySQL running 10,000 concurrent tasks is only about 10% of the original performance. After the thread pool feature is enabled, the performance can be maintained at 85%. Figure 1 shows the comparison effect.

Figure 1 Performance comparison before and after the MySQL thread pool is enabled

Uninstalling the Thread Pool Plugin

After the uninstall plugin thread_pool statement is executed to uninstall the thread pool plugin, MySQL is switched back to the original connector. The existing connections will be still running in the thread pool, and subsequent new connections will be running in the original MySQL connector. If there are still user connections in the thread pool when the thread pool plugin is being uninstalled, the status of the thread pool plugin changes from ACTIVE to DELETE (intermediate status of uninstalling the thread pool plugin). After all connections in the thread pool are terminated, run the uninstall plugin thread_pool command to uninstall the thread pool plugin; otherwise, the thread pool plugin uninstallation completes only when the MySQL service is shut down.

Uninstall the thread pool plugin using either of the following methods:

Method 1: Run the UNINSTALL command.

          
               UNINSTALL PLUGIN THREAD_POOL_GROUPS;
UNINSTALL PLUGIN THREAD_POOL_QUEUES;
UNINSTALL PLUGIN THREAD_POOL_STATS;
UNINSTALL PLUGIN THREAD_POOL_WAITS;
UNINSTALL PLUGIN thread_pool;

When the preceding uninstallation command is being executed, if a user connection exists in the thread pool connector, a message is displayed stating "plugin is busy and will be installed on shutdown." The status of the thread pool plugin changes from ACTIVE to DELETE (intermediate status of uninstalling the thread pool plugin). After all connections in the thread pool are terminated, run the uninstall plugin thread_pool command to uninstall the thread pool plugin; otherwise, the thread pool plugin uninstallation completes only when the MySQL service is shut down.

Method 2: If there is a parameter setting for loading the thread pool plugin in the configuration file, you can delete the setting after running the UNINSTALL command.

After uninstalling the thread pool plugin in this method, restart the database for the uninstallation to take effect.

          
               plugin-load-add=thread_pool.so

Check whether the thread pool plugin has been successfully uninstalled:
1

show plugins;

Parent topic: Parameters