The reason why Odoo is slow when there is huge data in the database

Question

We observed one problem in Postgresql, because it does not use multi-core processors for a single query. For example, I have 8 cores in a CPU. We have 40 million entries in the table. When we use a massive query in conjunction with one database to create reports and monitor on the server side, we see that only one core is used 100%, where all the other 7 are free. Due to the fact that the query execution time takes longer, and our system is slow. While the problem is inside the postgresql kernel. If in any case we can share a query between two or more kernels, we can improve performance when executing a postgresql query.

I’m sure that by solving the query in parallel, we can make Odoo’s performance even faster. Anyone have any suggestions on this?

----------- * Editing this question to show you the answer from the Postgresql Core committee * ---------

Here I post the answer I received from one of the best authors of the Postgresql database. (I hope this information is helpful)

Hi Hiren,

Expected Behavior. PostgreSQL does not support parallel CPUs for a single query. This topic is under development, and probably this feature will be in the upcoming release 9.6 ~ September 2016. But a table with 40M rows is not too big, so probably more CPUs should not help you too much (there is some overhead when starting and processing several request processors). You should use some common tricks, such as materialized representation, pre-aggregations, ... the main idea of ​​these tricks is do not try to repeat the same calculation often. Check the performance of PostgreSQL - indexes, vacuum processing, statistics, .. Check hw - I / O speed. Check PostgreSQL Configuration - shared_buffers, work_mem. Some queries may be slow due to poor grades - check the explanation of slow queries. There are some tools that can split a certain request into more requests and start parallel execution, but I did not use it. https://launchpad.net/stado http://www.pgpool.net/docs/latest/tutorial-en.html#parallel

Regards, Pavel Stehule

Answer 1

Well, I think you have your answer - PostgreSQL does not yet support concurrent query. General performance recommendations are very relevant, and you can also consider a section that can allow you to trim partitions rather than delete parts of a table, or increase memory allocation. It is impossible to give good advice about this without knowing more about the request.

Having experience with similar problems in non-parallel Oracle query systems, I suggest you also consider what equipment you use.

The current trend towards processors with a very large number of cores is excellent help for web servers or other multiprocessor systems with many short-lived transactions, but you have a data processing system with small large transactions. For this you need the right equipment. CPUs with fewer more powerful cores are the best choice, and you should pay attention to memory and memory bandwidth.

That's why engineering systems were popular in large data and data warehouses.