postgresql 数据库中有大量数据时Odoo变慢的原因
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31177905/
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
Reason why Odoo being slow when there is huge data inside the database
提问by Emipro Technologies Pvt. Ltd.
We have observed one problem in Postgresql as it doesn't uses multi core of CPU for single query. For example, I have 8 cores in cpu. We are having 40 Million entries in stock.move table. When we apply massive query in single database connection to generate reporting & observe at backend side, we see only one core is 100% used, where as all other 7 are free. Due to that query execution time takes so longer and our odoo system being slow. Whereas problem is inside postgresql core. If by anyhow we can share a query between two or more cores than we can get performance boost in postgresql query execution.
我们在 Postgresql 中观察到一个问题，因为它不使用多核 CPU 进行单个查询。例如，我在 cpu 中有 8 个内核。我们在 stock.move 表中有 4000 万个条目。当我们在单个数据库连接中应用大量查询来生成报告并在后端观察时，我们看到只有一个内核被 100% 使用，而其他 7 个内核都是免费的。由于查询执行时间太长，我们的 odoo 系统很慢。而问题出在 postgresql 核心内部。如果无论如何我们可以在两个或更多内核之间共享查询，那么我们可以在 postgresql 查询执行中获得性能提升。
I am sure by solving parallel query execution, we can make Odoo performance even faster. Anyone has any kind of suggestions regarding this ??
我确信通过解决并行查询执行，我们可以使 Odoo 性能更快。任何人对此有任何建议？
----------- * Editing this question to show you answer from Postgresql Core committee *---------
----------- * 编辑此问题以向您展示来自 Postgresql 核心委员会的答案 *---------
Here I am posting the answer which I got from one of top contributor of Postgresql database. ( I hope this information will be useful)
在这里，我发布了我从 Postgresql 数据库的一位顶级贡献者那里得到的答案。（我希望这些信息有用）
Hello Hiren,
It is expected behave. PostgreSQL doesn't support parallel CPU for single query. This topic is under high development, and probably, this feature will be in planned release 9.6 ~ September 2016. But table with 40M rows isn't too big, so probably more CPU should not too help to you (there is some overhead with start and processing multi CPU query). You have to use some usual tricks like materialized view, preagregations, ... the main idea of these tricks - don't try to repeat often same calculation. Check health of PostgreSQL - indexes, vacuum processing, statistics,.. Check hw - speed of IO. Check PostgreSQL configuration - shared_buffers, work_mem. Some queries can be slow due bad estimations - check a explain of slow queries. There are some tools that can breaks some query to more queries and start parallel execution, but I didn't use it. https://launchpad.net/stadohttp://www.pgpool.net/docs/latest/tutorial-en.html#parallel
Regards Pavel Stehule
你好海伦，
这是预期的行为。PostgreSQL 不支持单个查询的并行 CPU。这个话题正在高速开发中，这个功能可能会在计划发布 9.6 ~ 2016 年 9 月。但是 40M 行的表不是太大，所以可能更多的 CPU 对你没有太大帮助（启动时有一些开销并处理多 CPU 查询）。你必须使用一些常用的技巧，比如物化视图、预聚合……这些技巧的主要思想 - 不要试图重复经常相同的计算。检查 PostgreSQL 的健康状况 - 索引、真空处理、统计，.. 检查硬件 - IO 速度。检查 PostgreSQL 配置 - shared_buffers、work_mem。由于估计错误，某些查询可能会很慢 - 检查慢查询的解释。https://launchpad.net/stado http://www.pgpool.net/docs/latest/tutorial-en.html#parallel
问候帕维尔·斯图尔
回答by David Aldridge
Well, I think you have your answer there -- PostgreSQL does not currently support parallel query yet. The general advice towards performance is very apt, and you might also consider partitioning, which might allow you to truncate partitions instead of deleting parts of a table, or increasing memory allocation. It's impossible to give good advice on that without knowing more about the query.
好吧，我想你已经有了答案——PostgreSQL 目前还不支持并行查询。关于性能的一般建议非常恰当，您还可以考虑分区，这可能允许您截断分区而不是删除表的一部分或增加内存分配。如果不了解有关查询的更多信息，就不可能就此给出好的建议。
Having had experience with this sort of issue on non-parallel query Oracle systems, I suggest that you also consider what hardware you're using.
在非并行查询 Oracle 系统上有过此类问题的经验后，我建议您还考虑使用什么硬件。
The modern trend towards CPUs with very many cores is a great help for web servers or other multi-process systems with many short-lived transactions, but you have a data processing system with few, large transactions. You need the correct hardware to support that. CPUs with fewer, more powerful cores are a better choice, and you have to pay attention to bandwidth to memory and storage.
CPU 具有非常多内核的现代趋势对于具有许多短期事务的 Web 服务器或其他多进程系统有很大帮助，但您的数据处理系统只有很少的大型事务。您需要正确的硬件来支持它。内核更少、功能更强大的 CPU 是更好的选择，您必须注意内存和存储的带宽。
This is why engineered systems have been popular with big data and data warehousing.
这就是工程系统在大数据和数据仓库中流行的原因。
