MySQL thread_cache_size 减少 CPU 和最大连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8665233/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
thread_cache_size reduce CPU and max connection?
提问by flyclassic
Recently found out my MySQL server hits 90% high CPU utilization when simulating over concurrent 100-500 threads request
最近发现我的 MySQL 服务器在模拟 100-500 个线程并发请求时达到了 90% 的高 CPU 利用率
with the default settings plus following in my.cnf
使用默认设置加上 my.cnf 中的以下内容
max_connections = 500 max_allowed_packet = 16M
I notice the max_connection can hit up to 500, threads_created can also go high to 200-500 and i'm thinking this has actually cause abnormally high CPU
我注意到 max_connection 可以达到 500，threads_created 也可以达到 200-500，我认为这实际上导致 CPU 异常高
Hence instead of using default settings i adjusted
因此，而不是使用我调整的默认设置
innodb_buffer_pool_size = 2G #32bit linux server innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_thread_concurrency = 16 innodb_flush_method = O_DIRECT innodb_additional_mem_pool_size = 20M table_cache = 1028 thread_cache_size = 16 key_buffer_size=32M query_cache_size=32M join_buffer_size=1M
With the same load testing, the CPU dived down to 10% and below... However i notice the max_connection never hits 500 anymore. It is less than 50 now...
使用相同的负载测试，CPU 下降到 10% 及以下……但是我注意到 max_connection 再也没有达到 500。现在不到50...
Is this caused by thread_cache_size i've adjusted? by default it is 0. Or is there something wrong somewhere ... I'm wondering in that case if the mysql server is correctly tested with the max connection. I want to test how if concurrent threads can hit the max_connections but somehow it never hit with the same amount i tested before. Since the change, it never hits above 50 now.
这是由我调整的 thread_cache_size 引起的吗？默认情况下它是 0。或者有什么地方有问题......我想知道在这种情况下是否使用最大连接正确测试了 mysql 服务器。我想测试并发线程是否可以达到 max_connections 但不知何故它从未达到我之前测试过的相同数量。自更改以来，它现在从未超过 50。
Any idea?
任何的想法？
回答by 1tiger1
Creating new threads (since I'm guessing your application doesn't use connection pooling) takes a decent amount of overhead. With MySQL re-using threads in this manner (up to 16 of them) it is no longer utilizing the additional CPU to create threads as often and it also is likely finishing the operations in a timely manner, therefore closing the connection faster and therefore holding less connections open at a time.
创建新线程（因为我猜测您的应用程序不使用连接池）需要相当多的开销。通过以这种方式（最多 16 个）重用 MySQL 线程，它不再经常使用额外的 CPU 来创建线程，并且它也可能及时完成操作，因此更快地关闭连接并因此保持一次打开的连接较少。
Just a guess :-)
只是一个猜测:-)
回答by Wilson Hauck
Your use of
您的使用
innodb_thread_concurrency = 16
is a major player in limiting activity. See
是限制活动的主要参与者。看
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_thread_concurrency
https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_thread_concurrency
for detailed explanation and testing recommendations.
详细解释和测试建议。
The default was 0 = unlimited used of concurrent innodb_threads - but the danger is overdriving your systems context switching for so many active innodb_threads running concurrently.
默认值为 0 = 无限制地使用并发 innodb_threads - 但危险是过度驱动您的系统上下文切换，以便同时运行这么多活动的 innodb_threads。
