How To Install and configure MySQL Enterprise Thread Pool plugin
How To Install and configure MySQL Enterprise Thread Pool plugin
MySQL Enterprise Edition includes MySQL Enterprise Thread Pool, implemented using a server plugin. The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. The thread pool plugin provides an alternative thread-handling model designed to reduce overhead and improve performance. The plugin implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections.
The thread pool addresses several problems of the model that uses one thread per connection:
- Too many thread stacks make CPU caches almost useless in highly parallel execution workloads. The thread pool promotes thread stack reuse to minimize the CPU cache footprint.
- With too many threads executing in parallel, context switching overhead is high. This also presents a challenge to the operating system scheduler. The thread pool controls the number of active threads to keep the parallelism within the MySQL server at a level that it can handle and that is appropriate for the server host on which MySQL is executing.
- Too many transactions executing in parallel increases resource contention. In InnoDB, this increases the time spent holding central mutexes. The thread pool controls when transactions start to ensure that not too many execute in parallel.
Thread Pool Installation:-
To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.
Thread Pool Installation as of MySQL 8.0.37:-
In MySQL 8.0.14 and higher, the thread pool monitoring tables are Performance Schema tables that are loaded and unloaded along with the thread pool plugin. The INFORMATION_SCHEMA versions of the tables are deprecated but still available; they are installed per the instructions in Thread Pool Installation Prior to MySQL 8.0.14.
To enable thread pool capability, load the plugin by starting the server with the --plugin-load-add option. To do this, put these lines in the server my.cnf file, adjusting the .so suffix for your platform as necessary:
[mysqld]
plugin-load-add=thread_pool.so
To verify plugin installation, examine the Information Schema PLUGINS table or use the SHOW PLUGINS statement
SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'thread%';
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'performance_schema'
AND TABLE_NAME LIKE 'tp%';
If the server loads the thread pool plugin successfully, it sets the thread_handling system variable to loaded-dynamically.
Thread Pool Installation Prior to MySQL 8.0.37:-
Prior to MySQL 8.0.14, the thread pool monitoring tables are plugins separate from the thread pool plugin and can be installed separately.
To enable thread pool capability, load the plugins to be used by starting the server with the --plugin-load-add option. For example, if you name only the plugin library file, the server loads all plugins that it contains (that is, the thread pool plugin and all the INFORMATION_SCHEMA tables). To do this, put these lines in the server my.cnf file, adjusting the .so suffix for your platform as necessary:
[mysqld]
plugin-load-add=thread_pool.so
That is equivalent to loading all thread pool plugins by naming them individually:
If desired, you can load individual plugins from the library file. To load the thread pool plugin but not the INFORMATION_SCHEMA tables, use an option like this:
[mysqld]
plugin-load-add=thread_pool=thread_pool.so
To load the thread pool plugin and only the TP_THREAD_STATE INFORMATION_SCHEMA table, use options like this:
[mysqld]
plugin-load-add=thread_pool=thread_pool.so
plugin-load-add=tp_thread_state=thread_pool.so
To verify plugin installation, examine the Information Schema PLUGINS table or use the SHOW PLUGINS statement
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'thread%' OR PLUGIN_NAME LIKE 'tp%';
+-----------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-----------------------+---------------+
| thread_pool | ACTIVE |
| TP_THREAD_STATE | ACTIVE |
| TP_THREAD_GROUP_STATE | ACTIVE |
| TP_THREAD_GROUP_STATS | ACTIVE |
+-----------------------+---------------+
The main advantage of the thread pool is that it generally limits the number of running operations to the value of thread_pool_size which is generally set to the number of CPUs on the server or how many MySQL will be using. It prevents context switching and contention issues with other internal structures. It is particularly helpful when there are many more queries to run than there are available CPUs.
Limiting connections with max-connections would limit the number of running queries by limiting the number of connections but any connections over that limit are denied with an error. When using the thread pool more connections are allowed but the queries are queued by the thread pool. Another thing to consider is that connections aren't always running queries so limiting the connections does not effectively limit running queries.
To the point about the thread cache, it does help when using thread per connection but the thread pool does its own thing with reusing threads.
max-connections can still be used to limit the connections but as more of a safeguard with a value of something like 10000 rather than a limiter for the amount of work for the server.
For example if you have 100 clients that want to run queries with a desired limit of 10 queries running concurrently, setting the connections limit to 10 will only allow 10 client to connect with the others being rejected with an error. There would be at most 10 queries running at the same time but it is likely that on average there will be less than 10 queries running at a time. Without limiting the connections, if all 100 of the clients wanted to run a query at the same time, there would be 100 threads executing at the same time which could cause contention issues leading to less throughput than with 10 connections.
If you were to use a thread pool size of 10 instead, all 100 clients would be able to connect and run their queries. If all of the clients try to run a query at the same time, the thread pool will queue the queries and generally execute 10 in parallel which can have a higher throughput than the 100 concurrent queries in the previous example.
Comments
Post a Comment