在MySQL5.6出现以前，MySQL处理连接的方式是One-Connection-Per-Thread,即对于每一个数据库连接，MySQL-Server都会创建一个独立的线程服务，请求结束后，销毁线程。再来一个连接请求，则再创建一个连接，结束后再进行销毁。这种方式在高并发情况下，会导致线程的频繁创建和释放。当然，通过thread-cache，我们可以将线程缓存起来，以供下次使用，避免频繁创建和释放的问题，但是无法解决高连接数的问题。One-Connection-Per-Thread方式随着连接数暴增，导致需要创建同样多的服务线程，高并发线程意味着高的内存消耗，更多的上下文切换(cpu cache命中率降低)以及更多的资源竞争，导致服务出现抖动。相对于One-Thread-Per-Connection方式，一个线程对应一个连接，Thread-Pool实现方式中，线程处理的最小单位是statement(语句)，一个线程可以处理多个连接的请求。这样，在保证充分利用硬件资源情况下(合理设置线程池大小)，可以避免瞬间连接数暴增导致的服务器抖动。
总结:
One-Thread-Per-Connection方式: 一个线程对应一个连接;
Thread-Pool方式: 线程处理的最小单位是statement(语句)，一个线程可以处理多个连接的请求。
手上正好有一个测试环境，企业版5.6.23
库的版本信息：
/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: root@localhost
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 socket: /tmp/mysql3307.sock
Uptime: 16 min 20 sec
Threads: 686 Questions: 10436 Slow queries: 0 Opens: 80 Flush tables: 1 Open tables: 73 Queries per second avg: 10.648
开启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)
====================================================================================
开启thread_pool操作
方法一:在数据库启动的时候加--plugin-load
方法二:在my.cnf配置文件中添加配置
配置文件中添加的配置也有两种方法：
第一种 单行配置：
[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
第二种 多行分开配置：
[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
我在我的配置文件中用的第二种，推荐第二种 方便人类阅读:
[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
重启数据库，这个是个测试环境，这都凌晨了重启一下没影响，没人会发现（O(∩_∩)O~）。。
重启后查看插件是否加载成功。show plugins也可以，官方使用的是下边的方法。
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)
开启后，查看此时的变量值，发现多了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 |
| 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 |
+--------------------------------------+----------------+
15 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)
监控线程池的状态:
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)
官方建议:
InnoDB thread_pool_size 设置范围16到36之间，使用最普遍的优化值是24到26. 超过36性能会下降。
MyISAM thread_pool_size 设置范围4到8之间,查过这个范围性能会有所下降。
If the primary storage engine is InnoDB, the optimal thread_pool_size setting is likely to be between 16 and 36, with the most common optimal values tending to be from 24 to 36. We have not seen any situation where the setting has been optimal beyond 36. There may be special cases where a value smaller than 16 is 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 be lower.
If the primary storage engine is MyISAM, the thread_pool_size setting should be fairly low. We tend to get optimal performance for values from 4 to 8. Higher values tend to have a slightly negative but not dramatic impact on performance.
转载于:https://blog.51cto.com/kenneyzhou/1885332
224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
