How to fix OpenERP performance issues

(This post is a reply to this thread http://www.openerp.com/forum/topic22033.html, but could not be posted because of a fault that wrongly rejected this reply as spam.)

I suspect that there are a few things that need fixing in OpenERP for performance:

  1. Add indexes where appropriate/needed.
  2. The database structure does not have fields for running totals for Inventory Quantities on Hand or General Ledger Account totals, or Partner Balances. Hence, these figures have to be calculated every time by summing up all contributing transactions – very inefficient.
  3. Order the updating of data so that deadlocks are precluded – see deadlocks experienced by Saïd in the example in the forum (see the link above). In other words, if you have two sales orders, one with product A on line 1 and product B on line 2, and one with product B on line 1 and product A on line 2, then when you go to update those products in the one database transaction per user, you can get a deadlock if your timing is right, and with 40 users this would probably be the case. The way to prevent deadlocks is simply to enforce that the required resource is locked in a predefined order such as alphabetical order, e.g. you want the first user to lock  A then B, and second user must also do A then B, not have the first user lock A then B and the second user lock B then A and so be prone to a deadlock. The same goes for any other commonly-used resource.
  4. I suspect that the ORM needs to be industrial-quality so that it can not only be multi-threaded and also multi-instance, in which case it will need to support distributed caching. NHibernate (or its equivalent for Python), anybody? Or ActiveRecord?
    See this issue in the OpenERP Forum for related details here.
  5. Make sure all tables have a sensible index. OpenERP doesn’t create any indexes by default; this will cause serious performance issues once data grows to a reasonable size. Solution: Add indexes!
  6. Run on Jython or IronPython, to resolve Python’s performance issues, as noted here: Python’s GIL is evil, and Google searches for the holy grail of Python performance.

See also

Advertisements
This entry was posted in OpenERP. Bookmark the permalink.

3 Responses to How to fix OpenERP performance issues

  1. Vince Clark says:

    I have a question/comment about recommendation 1, running totals. Two other examples in the open source world of how this is handled:
    1. Open For Business – uses a similar approach to OpenERP. It does not persist running totals. A service is used to calculate inventory quantities and I believe it somehow uses the data cache so the calculation is fast but am not an expert on how that works.
    2. Compiere and derivatives such as ADempiere – uses a field to maintain running totals. I worked on an implementation with high volume inventory transactions and we had serious database performance issues. The query that consistently came up in the logs as the source of the problem was a “select… for update” statement which locked the row to update the running balance. There was high contention on the inventory table with long waits and many deadlocks. We had other details that were compounding the problem so it is possible that the real problem was a combination of many things.
    I would like to hear more thoughts on the two different approaches.

    • philu says:

      Good points/experiences, thanks!
      My point 2 would prevent the deadlocks in your point 2; I worked on a previous system where this was implemented and it worked very well.
      Yes, the calculate-it-on-the-fly approach, as used by Open for Business and OpenERP, also avoids the deadlock problem… at the cost of performance.

  2. Billrobo says:

    Re point 4. It appears theOpenERP “intends” to move to SQLAlchemy at some point:
    http://openerp-team.blogspot.com/2009/05/r-planning-of-may_15.html
    http://openerp-team.blogspot.com/2009/08/open-erp-server-with-mysql.html
    (2009 post though and the rationale appears to be database independence)

    Ref:
    http://www.sqlalchemy.org/
    http://en.wikipedia.org/wiki/SQLAlchemy

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s