Odoo Help

Welcome!

This community is for beginners and experts willing to share their Odoo knowledge. It's not a forum to discuss ideas, but a knowledge base of questions and their answers.

11

Reason why Odoo being slow when there is huge data inside the database

By
Emipro Technologies Pvt. Ltd.
on 7/1/15, 7:24 AM 3,996 views

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. 

I am sure by solving parallel query execution, we can make Odoo performance even faster. Anyone has any kind of suggestions regarding this ?

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

Here I am posting the answer which I got from one of top contributor of Postgresql database. ( I hope this information will be useful)

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/stado
http://www.pgpool.net/docs/latest/tutorial-en.html#parallel

Regards
Pavel Stehule


@Emipro this is actually a great question.. I've seen multiple topics about these things but never a good and solid answer. You should contact Olivier Dony or Antony Lessuise from Odoo, those guys know a lot about this and I'm sure they can help you in the good way. (Don't forget to post back here)

Yenthe
on 7/1/15, 8:52 AM

@yenthe I have personally asked this question to Fabian and one of the other technical developer during Odoo Experience 2015 but they didn't gave any satisfactory answer. However it may happen that this is not topic of their interest. So I asked this question to one of top contributor of Postgresql core committee. ( just got before 15 minutes from now ) Let me edit my question and add their answer. So may be some guys from Odoo will be interested. Please look at my question to know answer from Postgresql side.

Emipro Technologies Pvt. Ltd.
on 7/1/15, 9:16 AM

Thanks for the update! Regarding the statement about postgreSQL.. "PostgreSQL doesn't support parallel CPU for single query." Well, then you're done unless you would like to expand / tweek the whole postgreSQL system.I would assume that Odoo would jump on these features if they're launched in postgreSQL 9.6 though. (which is still a long time away)

Yenthe
on 7/2/15, 1:55 AM

@Yenthe one guy from PostgreSQL suggested me to look on this link for parallel query execution, Postgres-XL: http://www.postgres-xl.org/

Emipro Technologies Pvt. Ltd.
on 7/3/15, 1:39 AM
1

Axel Mendoza

--Axel Mendoza--
10318
| 7 7 8
Camaguey, Cuba
--Axel Mendoza--

DevOps - Full stack - Software Architect - Developer - Technology Integrator

I could help you to develop anything and solve complex problems based on technologies, integrations and tricky stuffs mostly in Python with OpenERP/Odoo, Zato, Django and many others frameworks programming languages and technologies.

I offers consulting services to anyone with an unanswered questions or needs for customizations. Think about it, maybe it's better to have an expert to solve your issues and projects than having a full time employee trying to understand what to do an how

Reach me at aekroft@gmail.com

Axel Mendoza
On 7/26/15, 7:35 PM

In my experience if you create and optimize indexes for your queries and manually test to ensure that PostgreSQL is indeed using your indexes and also giving you a quick response, you would see those queries still slow in Odoo. That's because you also need to take "order by" into account, I face this issue in some large tables and a quick solution was disable ordering for those results and the performance issues was fixed.

0
Sandor Dobi
On 9/23/15, 10:55 AM

It would be helpfull if You post the typical resultset recordcount of thoose problematic queries. If there are thousand of records then the next big obstacle is the Odoo ORM object materialisation layer. The ORM is several magnitude slower than raw query execution, so you can optimise the performance with hand tuned queries issued through the cursor and with hand tuned object materialisation. 

I can foresee that a simple recordset.browse(condition) can be huge bottleneck with several thousand records in resultset. 

0
IGAM Muliarsa
On 7/2/15, 2:22 AM

http://wiki.postgresql.org/wiki/FAQ#How_does_PostgreSQL_use_CPU_resources.3F

"How does PostgreSQL use CPU resources?

The PostgreSQL server is process-based (not threaded), and uses one operating system process per database session. A single database session (connection) cannot utilize more than one CPU. Of course, multiple sessions are automatically spread across all available CPUs by your operating system. Client applications can easily use threads and create multiple database connections from each thread.

A single complex and CPU-intensive query is unable to use more than one CPU to do the processing for the query. The OS may still be able to use others for disk I/O etc, but you won't see much benefit from more than one spare core."

You need multiple sessions connection to utilize multi-core CPU

Your Answer

Please try to give a substantial answer. If you wanted to comment on the question or answer, just use the commenting tool. Please remember that you can always revise your answers - no need to answer the same question twice. Also, please don't forget to vote - it really helps to select the best questions and answers!

About This Community

This community is for professionals and enthusiasts of our products and services. Read Guidelines

Question tools

5 follower(s)

Stats

Asked: 7/1/15, 7:24 AM
Seen: 3996 times
Last updated: 9/24/15, 2:24 AM