MySQL thread_concurrency、innodb_thread_concurrency，该不该用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13195129/
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
MySQL thread_concurrency, innodb_thread_concurrency, should I use or not?
提问by Alexandru Trandafir Catalin
I have a dedicated server with 24 CPUs and 32GB of ram.
我有一台带有 24 个 CPU 和 32GB 内存的专用服务器。
This server serves website and mysql.
该服务器提供网站和 mysql。
I don't know what is the difference between those two variables, if there is any.
我不知道这两个变量之间有什么区别，如果有的话。
I don't know if I should use them because after reading on Google some say that those variables might be ignored depending on the OS or MySQL versión.
我不知道我是否应该使用它们，因为在 Google 上阅读后有人说这些变量可能会被忽略，具体取决于操作系统或 MySQL 版本。
So should I use them?
那么我应该使用它们吗？
回答by Dima Tisnek
Please read Mysql Performance Blogcarefully, select decent initial values, monitor performance of your server during busy hours of the day and tune accordingly.
请仔细阅读Mysql 性能博客，选择合适的初始值，在一天的繁忙时段监控服务器的性能并进行相应调整。
There are no simple answers, because your workload is uniquely yours.
没有简单的答案，因为您的工作量是独一无二的。
Off the top of my head your balance of CPU and RAM seems wrong. I think 1~4 cores for 64GB of ram, or 24 cores for max ram you can get, 192GB perhaps? CPU needs to be provisioned for query rate, while RAM for active/hot dataset size. I can imagine a weird workload where your CPU/RAM makes sense, but I'm not sure innodb is in fact the best solution for such workload.
在我的脑海中，您的 CPU 和 RAM 平衡似乎是错误的。我认为 64GB 内存需要 1~4 个内核，或者您可以获得的最大内存需要 24 个内核，也许是 192GB？需要为查询速率提供 CPU，而为活动/热数据集大小提供 RAM。我可以想象一个奇怪的工作负载，其中您的 CPU/RAM 有意义，但我不确定 innodb 实际上是否是此类工作负载的最佳解决方案。
Coming back to your question: "thread concurrency doesn't do what you expect"in short most likely you should not use. innodb_thread_concurrencyis just a cutoff, I'd say if your workload is all hot (i.e. mysql doesn't use much disk(?)), it should not be higher than number of cores. Do read up the blog, these settings are not as simple as they seem.
回到您的问题：简而言之，您很可能不应该使用“线程并发性不符合您的预期”。innodb_thread_concurrency只是一个截止点，我想说如果你的工作负载很热（即mysql 不使用太多磁盘（？）），它不应该高于核心数。请阅读博客，这些设置并不像看起来那么简单。
Also you may want to pay attention to: thread cache, innodb buffer pool, add mem pool, heap table size, sort/key buffer size, flush log at tx commit, log file size. And probably a few more I couldn't think of right now.
您可能还需要注意：线程缓存、innodb 缓冲池、添加内存池、堆表大小、排序/键缓冲区大小、tx 提交时刷新日志、日志文件大小。可能还有一些我现在想不到。
回答by Queasy
The thread_concurrency option in MySQL is mainly for Solaris systems and will also be depreciated in version 5.6, so tuning it might be a waste of time.
MySQL 中的 thread_concurrency 选项主要用于 Solaris 系统，并且在 5.6 版本中也会折旧，因此调整它可能是浪费时间。
thread_concurrency
Also please read: https://www.percona.com/blog/2012/06/04/thread_concurrency-doesnt-do-what-you-expect/
另请阅读：https: //www.percona.com/blog/2012/06/04/thread_concurrency-doesnt-do-what-you-expect/
The innodb_thread_concurrency can be adjusted for performance, but I've found no performance increases using it.
innodb_thread_concurrency 可以根据性能进行调整，但我发现使用它没有性能提升。
I found the best information from https://www.percona.com/blog/. All others suggesting and giving advice might deem MySQL inoperable.
我从https://www.percona.com/blog/找到了最好的信息。所有其他建议和提供建议的人都可能认为 MySQL 无法运行。
回答by Heather McVay
(According to manual "thread_concurrency" variable is usable only for Solaris OS)
（根据手册“thread_concurrency”变量仅可用于 Solaris OS）
回答by Heather McVay
This will depend on a number of issues, the operating system, the scheduler options, the I/O subsystem, and the number and type of CPUs, as well as the type and number of queries being run.
这将取决于许多问题、操作系统、调度程序选项、I/O 子系统、CPU 的数量和类型，以及正在运行的查询的类型和数量。
The only way you can tell for certain on your system is to adjust the value of innodb_thread_concurrency and run typical workloads to benchmark. A reasonable starting point is from 0 to 48 (in your case) x2 times the number of CPU cores available. You could then increase this until the point at which you start to see the system become CPU bound and throttle it back a bit.
您可以确定系统上的唯一方法是调整 innodb_thread_concurrency 的值并运行典型工作负载以进行基准测试。合理的起点是可用 CPU 内核数的 0 到 48（在您的情况下）x2 倍。然后，您可以增加它，直到您开始看到系统变得受 CPU 限制并稍微降低它的速度。
This doesn't take into account the disk activity that your transactions will generate, From there you can then look at disk I/O and make adjustments from there.
这不考虑您的事务将生成的磁盘活动，然后您可以从那里查看磁盘 I/O 并从那里进行调整。
Setting this to 0 is setting this to unlimited ** so that by default there is no limit on the number of concurrently executing threads
将此设置为 0 是将其设置为无限制 ** 以便默认情况下并发执行的线程数没有限制
http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-thread_concurrency.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-thread_concurrency.html
