MySQL 服务器仅使用 48 个 CPU 内核中的 1 个进行 GROUP BY 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42528943/
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 server uses only 1 out of 48 CPU cores with GROUP BY queries
提问by Matthias
I have high end 48 CPU core server with latest MySQL 5.7 installed
我有安装了最新 MySQL 5.7 的高端 48 CPU 核心服务器
I'm bit surprised that when executing queries with GROUP BY, no matter how I format my query I get only 1 CPU core used for that query, clearly illustrated by htop output
我有点惊讶，当使用 GROUP BY 执行查询时，无论我如何格式化查询，我都只得到 1 个用于该查询的 CPU 核心，由 htop 输出清楚地说明
Of course I can perform multiple queries at the same time, and that's how I can use all the cores but it seems not too convenient and not every query can be split to use full server's power
当然我可以同时执行多个查询，这就是我可以使用所有内核的方式，但它似乎不太方便，而且不是每个查询都可以拆分以使用完整服务器的功能
Are there any MySQL extensions or SQL hints that allow to use multiple cores while processing data with GROUP BY?
是否有任何 MySQL 扩展或 SQL 提示允许在使用 GROUP BY 处理数据时使用多个内核？
回答by Shadow
As the accepted answer by RolandoMySQLDBA to Possible to make MySQL use more than one core?question says:
作为 RolandoMySQLDBA 对可能使 MySQL 使用多个内核的公认答案？问题说：
I actually discussed innodb_thread_concurrency with a MySQL Expert at the Percona Live NYC conference back in May 2011.
I learned something surprising: In spite of the documentation, it is best to leave innodb_thread_concurrency at 0 (infinite concurrency). That way, InnoDB decides the best number of innodb_concurrecy_tickets to open for a given MySQL instance setup.
Once you set innodb_thread_concurrency to 0, you can set innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) to the maximum value of 64. This should engage more cores.
实际上，我在 2011 年 5 月的 Percona Live NYC 会议上与 MySQL 专家讨论了 innodb_thread_concurrency。
我学到了一些令人惊讶的东西：尽管有文档，但最好将 innodb_thread_concurrency 保留为 0（无限并发）。这样，InnoDB 决定为给定的 MySQL 实例设置打开的最佳 innodb_concurrecy_tickets 数量。
将 innodb_thread_concurrency 设置为 0 后，您可以将 innodb_read_io_threads 和 innodb_write_io_threads（均自 MySQL 5.1.38 起）设置为最大值 64。这应该会占用更多内核。
This is the best guidance I ever found on how to make MySQL use more cores in general.
这是我对如何使MySQL使用更多的内核迄今发现的最好的指导一般。
回答by Rick James
Aside from background threads, one connection will use only one CPU core. The applies to GROUP BY, UNION, PARTITION, and anything else you might think it should manage to do in parallel.
除了后台线程，一个连接将只使用一个 CPU 内核。适用于GROUP BY、UNION、PARTITION以及您可能认为它应该设法并行执行的任何其他操作。
The statement applies for InnoDB at least through MySQL 8.0 and MariaDB 10.2.
该声明至少适用于通过 MySQL 8.0 和 MariaDB 10.2 的 InnoDB。
A third-party software can make use of multiple cores: https://www.percona.com/blog/2014/05/01/parallel-query-mysql-shard-query/.
第三方软件可以使用多核：https: //www.percona.com/blog/2014/05/01/parallel-query-mysql-shard-query/。
Also, the "column store" in MariaDB 10.2 probably uses multiple cores.
此外，MariaDB 10.2 中的“列存储”可能使用多个内核。
回答by Thomas Decaux
This is a very known limitation of mySQL and thats why mysql is not commonly used for analytics purpose.
这是 mySQL 的一个众所周知的限制，这就是为什么 mysql 不常用于分析目的。
As a workaround, you can use Apache Spark (well known for analytics) in front of mysql, here:
作为一种解决方法，您可以在 mysql 前面使用 Apache Spark（以分析而闻名），这里：
- Apache Spark will be the "SQL Engine", you can run it as a cluster of workers
- mysql will be a simple data storage
- Apache Spark 将成为“SQL 引擎”，您可以将它作为一个工作集群运行
- mysql 将是一个简单的数据存储
I let you discovery how to use Apache Spark, and how to use the native JDBC connector.
我让您了解如何使用 Apache Spark，以及如何使用本机 JDBC 连接器。
Also, it seems this is possible also with PrestoDB (analytics engine from Facebook) (https://prestodb.github.io/docs/current/connector/mysql.html).
此外，PrestoDB（来自 Facebook 的分析引擎）（https://prestodb.github.io/docs/current/connector/mysql.html）似乎也可以做到这一点。
