MySQL Open thread pool

before the advent of MySQL5.6, MySQL handled the connection in One-connection-per-thread, that is, for each database connection, Mysql-server creates a separate thread service that, after the request is finished, destroys the thread. One more connection request, then create a connection, and then destroy the end. This approach leads to frequent creation and release of threads in high concurrency situations. Of course, with Thread-cache, we can cache threads for next use, avoid frequently created and released problems, but cannot solve the problem of high number of connections. One-connection-per-thread mode as the number of connections increases, resulting in the need to create the same number of service threads, high concurrent threads mean high memory consumption, more context switching (decreased CPU cache hit rate), and more resource contention, resulting in service jitter. In relation to one-thread-per-connection mode, a thread corresponds to a connection, in Thread-pool implementation, the minimum unit of thread processing is statement (statement), and one thread can handle multiple connection requests. In this way, the server jitter caused by the sudden increase in the number of transient connections can be avoided by ensuring that the hardware resources are fully utilized (setting the thread pool size reasonably).

Summarize:

One-thread-per-connection mode: One thread corresponds to a connection;

Thread-pool: The minimum unit of thread handling is statement (statement), and one thread can handle multiple connections requests.

There's just one test environment on hand, Enterprise Edition 5.6.23

Version information for the library:

/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.6.23, for linux-glibc2.5 (x86_64) using Editline Wrapper

Connection id:373

Current database:

Current User: [email protected]

Ssl:not in use

Current Pager:stdout

Using outfile: '

Using delimiter:;

Server version:5.6.23-enterprise-commercial-advanced-log MySQL Enterprise server-advanced Edition (commercial)

Protocol version:10

Connection:localhost via UNIX socket

Server Characterset:utf8

Db Characterset:utf8

Client Characterset:utf8

Conn. Characterset:utf8

UNIX Sockets:/tmp/mysql3307.sock

Uptime:16 min sec

threads:686 questions:10436 Slow queries:0 opens:80 Flush tables:1 Open tables:73 queries per second avg:10.6 48

View the variable values before opening thread_pool:

#没有thread_pool变量信息

Mysql> show global variables like '%pool% ';

+-------------------------------------+----------------+

| variable_name | Value |

+-------------------------------------+----------------+

| Innodb_additional_mem_pool_size | 8388608 |

| Innodb_buffer_pool_dump_at_shutdown | OFF |

| Innodb_buffer_pool_dump_now | OFF |

| Innodb_buffer_pool_filename | Ib_buffer_pool |

| innodb_buffer_pool_instances | 8 |

| Innodb_buffer_pool_load_abort | OFF |

| Innodb_buffer_pool_load_at_startup | OFF |

| Innodb_buffer_pool_load_now | OFF |

| Innodb_buffer_pool_size | 1073741824 |

+-------------------------------------+----------------+

9 Rows in Set (0.00 sec)

#每个线程一个连接

Mysql> show global variables like '%thread_handling% ';

+-----------------+---------------------------+

| variable_name | Value |

+-----------------+---------------------------+

| thread_handling | one-thread-per-connection |

+-----------------+---------------------------+

1 row in Set (0.01 sec)

====================================================================================

Turn on Thread_pool operation

Method One: Add--plugin-load when the database is started

Method Two: Add the configuration in the MY.CNF configuration file

There are two ways to add configurations to a configuration file:

First single-line configuration:

[Mysqld]

Plugin-load=thread_pool=thread_pool.so;tp_thread_state=thread_pool.so;tp_thread_group_state=thread_pool.so;tp_ Thread_group_stats=thread_pool.so

The second multi-line configuration is separate:

[Mysqld]

Plugin-load-add=thread_pool=thread_pool.so

Plugin-load-add=tp_thread_state=thread_pool.so

Plugin-load-add=tp_thread_group_state=thread_pool.so

Plugin-load-add=tp_thread_group_stats=thread_pool.so

I use the second kind in my configuration file, recommend the second kind of convenient human reading:

[Mysqld]

Plugin-load-add=thread_pool=thread_pool.so

Plugin-load-add=tp_thread_state=thread_pool.so

Plugin-load-add=tp_thread_group_state=thread_pool.so

Plugin-load-add=tp_thread_group_stats=thread_pool.so

Restart the database, this is a test environment, this is the early morning restart no impact, no one will find (O (∩_∩) o~).

Check to see if the plug-in was loaded successfully after rebooting. Show plugins; it is possible. The official is viewed in the way below.

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 |

+-----------------------+---------------+

4 rows in Set (0.01 sec)

When turned on, look at the variable values at this time and find more thread_pool related information.

Mysql> show global variables like '%pool% ';

+--------------------------------------+----------------+

| variable_name | Value |

+--------------------------------------+----------------+

| Innodb_additional_mem_pool_size | 8388608 |

| Innodb_buffer_pool_dump_at_shutdown | OFF |

| Innodb_buffer_pool_dump_now | OFF |

| Innodb_buffer_pool_filename | Ib_buffer_pool |

| innodb_buffer_pool_instances | 8 |

| Innodb_buffer_pool_load_abort | OFF |

| Innodb_buffer_pool_load_at_startup | OFF |

| Innodb_buffer_pool_load_now | OFF |

| Innodb_buffer_pool_size | 1073741824 |

| Thread_pool_algorithm | 0 |

| thread_pool_high_priority_connection | 0 |

| Thread_pool_max_unused_threads | 0 |

| Thread_pool_prio_kickup_timer | 1000 |

| Thread_pool_size | 16 |

| Thread_pool_stall_limit | 6 |

+--------------------------------------+----------------+

Rows in Set (0.00 sec)

#线程动态加载

Mysql> show global variables like '%thread_handling% ';

+-----------------+--------------------+

| variable_name | Value |

+-----------------+--------------------+

| thread_handling | loaded-dynamically |

+-----------------+--------------------+

1 row in Set (0.00 sec)

Monitor the status of the thread pool:

Mysql> Show tables like ' tp_thread% ';

+-------------------------------------------+

| Tables_in_information_schema (tp_thread%) |

+-------------------------------------------+

| Tp_thread_group_stats |

| Tp_thread_state |

| Tp_thread_group_state |

+-------------------------------------------+

3 Rows in Set (0.00 sec)

Official advice:

InnoDB Thread_pool_size set the range from 16 to 36, and most people choose a value of 24-26. Over 36 performance will degrade.

MyISAM thread_pool_size set range from 4 to 8, check this range performance will decrease.

If The primary storage engine is InnoDB, the optimal thread_pool_size setting are likely to be between Most common optimal values tending to is from 36. We have no seen any situation where the setting has been optimal beyond 36. There may special cases where a value smaller than are optimal.

For workloads such as DBT2 and Sysbench, the optimum for InnoDB seems to be usually around 36. For very write-intensive workloads, the optimal setting can sometimes is lower.

If The primary storage engine is MyISAM, the thread_pool_size setting should was fairly low. We tend to get optimal performance for values from 4 to 8. Higher values tend to has a slightly negative but not dramatic impact on performance.

This article is from the "Deep Mountain" blog, please make sure to keep this source http://kenneyzhou.blog.51cto.com/12427643/1885332

MySQL Open thread pool