Creating 4000+ locations slow down the mrp module installations and confirming sale order.

[Sinoj Sebastin]

Hi,
We imported 4197 locations in openerp. After that installation of mrp modules taking hours and conforming one sale order taking more than one minute. The system is slowing down on the function _product_reserve which is in "bin/addons/stock/stock.py" in class stock_location line 362.

Sometimes it is throwing the following error
OperationalError: out of shared memory
HINT: You might need to increase max_locks_per_transaction.

The server we are using 6.0.2 stable.
The locations we used to import is attached with this bug report.

Please improve the performance of _product_reserve function so that it will not hang when the number of locations are in thousands range.

Thank you.

[SnippetBucket.com (tta-openerp)]

Hello Everyone,

Go through https://bugs.launchpad.net/openobject-addons/+bug/507389
Solution was implemented as http://bazaar.launchpad.net/~openerp/openobject-addons/6.0/revision/3164.54.66.

As a part of investigation, note the following things:

1. User might need to increase max_locks_per_transaction. (From config of postgres)
2. User might need to increase SHMMAX memory as well (http://www.postgresql.org/docs/8.4/static/kernel-resources.html#SYSVIPC)
3. Should not the Savepoint be written before the loop starts? (Instead of having it for each location)
4. I am not completely sure, but will it have a trouble when more than one users access the same function with large no. of locations?

Thanks.

[Sinoj Sebastin (ssebastian)]

Hi Tejaskumar Tank,
We tested the performance by increasing max_locks_per_transaction and SHMMAX, but it does not make any difference.
Thank you.

[Sinoj Sebastin (ssebastian)]

Hello,
Is there any update in this issue?

[Olivier Dony (Odoo) (odo-openerp)]

Hello,

Here is a patch with a more efficient rewrite of the stock reservation algorithm. It should be much lighter in terms of time and resources, even with location numbers in the thousands.

You may want to try this patch in a test database, and tell if that works for you, and with or without side effects.

Side note: managing thousands of stock locations in the same OpenERP database indicates that you are either in a very large multi-company setup, or that a granularity level is missing in OpenERP to represent what you need (e.g. sublocations, drawers, etc.) It would be interesting (from R&D point of view) to know the situation you face, so that we could provide better features in the future :-)

I am going to upload this patch as a separate bugfix branch as well.

[Olivier Dony (Odoo) (odo-openerp)]

The above-mentioned patch is provided in revision 4574 rev-id: <email address hidden> of the linked branch: lp:~openerp-dev/openobject-addons/6.0-bug-770243-odo

[Olivier Dony (Odoo) (odo-openerp)]

Filing under addons, this is stock-module-specific.

[Raphaël Valyi - http://www.akretion.com (rvalyi)]

Hello Olivier,

Thanks for your patch will try as soon as I have some time (!).

About the situation we are facing his situation for two customers:
1) one is a book editor selling to around 1500+ bookstore in Brazil. As they
are doing "consignated sale" where only books sold to the final customers
are invoiced, it's required to maintain properly the stock of every
bookstore location, same for their supplier where they buy consigned books.
2) one of our US customer is indeed #2 in what they do in the US, they are
like a 30 employees companies, but make around 500+ sales per day via two
channels: OpenbravoPOS and Magentoerpconnect (both integrated via
base_sale_mulichannels). They need to quickly do some internal moves between
sub-locations o make their pickings more efficient and minimize travel
distance when picking products.

On Tue, May 10, 2011 at 12:14 PM, Olivier Dony (OpenERP) <
<email address hidden>> wrote:

> Hello,
>
> Here is a patch with a more efficient rewrite of the stock reservation
> algorithm. It should be much lighter in terms of time and resources,
> even with location numbers in the thousands.
>
> You may want to try this patch in a test database, and tell if that
> works for you, and with or without side effects.
>
> Side note: managing thousands of stock locations in the same OpenERP
> database indicates that you are either in a very large multi-company
> setup, or that a granularity level is missing in OpenERP to represent
> what you need (e.g. sublocations, drawers, etc.) It would be interesting
> (from R&D point of view) to know the situation you face, so that we
> could provide better features in the future :-)
>
> I am going to upload this patch as a separate bugfix branch as well.
>
> ** Patch added: "rewrite of product reservation code"
>
> https://bugs.launchpad.net/openobject-server/+bug/770243/+attachment/2122828/+files/770243.patch
>
> --
> You received this bug notification because you are a member of OpenERP
> Drivers, which is subscribed to OpenERP Server.
> https://bugs.launchpad.net/bugs/770243
>
> Title:
> Creating 4000+ locations slow down the mrp module installations and
> confirming sale order.
>
> Status in OpenERP Server:
> Fix Committed
>
> Bug description:
> Hi,
> We imported 4197 locations in openerp. After that installation of mrp
> modules taking hours and conforming one sale order taking more than one
> minute. The system is slowing down on the function _product_reserve which is
> in "bin/addons/stock/stock.py" in class stock_location line 362.
>
> Sometimes it is throwing the following error
> OperationalError: out of shared memory
> HINT: You might need to increase max_locks_per_transaction.
>
> The server we are using 6.0.2 stable.
> The locations we used to import is attached with this bug report.
>
> Please improve the performance of _product_reserve function so that it
> will not hang when the number of locations are in thousands range.
>
> Thank you.
>

[David Mitchell (www.novapointgroup.com) (david-novapointgroup.com)]

Hi Olivier,

Allow me to provide some context to the original question and the use case scenario.
Our NovaPoint client in the US is a retail distributor.
They have a large warehouse location (200,000+ sq ft warehouse), and as a result have multiple rows, multiple stops, and several numbers of heights for products and raw materials. Hence the 4000+ locations.

The material handlers need to know specifically where products are for efficient picking and movement of goods and replenishment (stocking primary picking locations). The material receivers need to be able to quickly record products in locations when received as ell.

Transactions volumes are >1,000+ orders per day and are a mix of single product sales and Assembly (sales) kits (kits made up of several products - pulled together at picking). NOTE: We have a new assembly Sales BOM code in lp:openerp-usa that allows for the proper inventory treatment of Sales kits and define assembly BoM as another BOM type. So actually product movements and shipments involve 4000-8000 items a day on sales orders by themselves.

Shipment of goods have 90% same day order-shipping.

The inventory turns are very quick.
Inventory locations are also very dynamic (meaning that product does not have a static "Primary location" which is why OpenERP standard inventory locations needed to be modified - it is based on a relatively static "here is the one location of a product in your warehouse") - with the warehouse setup for efficient picking. Throughput of picking is very important in larger retail distributors.

Products that are frequently shipped are stored in close proximity to packaging stations for efficient picking and warehouse operations. Then products may have multiple overstock locations as well (up to 20 additional locations for a single product).

OpenERP is not setup to support a dynamic warehouse environment so we created a new module in lp:openerp-usa to handle this - including replenishment of primary locations in a warehouse from overstock locations. With this we established replenishment points for each location (not to be confused with reorder points which consider when products should be "re-ordered") under the roof.

This enhancement set should address Raphael's issue #2.

Also - picking reports have also been modified to better help execute quick picking.
We allow the warehouse manager to designate which orders are assigned to a picker.
You'll see this modification in the code as well.
This includes displaying and printing a consolidated picking list that we created so each individual pickers can pull multiple orders in a single picking run. <-- Again this is all about efficiency.

Typically, there is a separate warehouse material handler in place and all they do is look to replenish the primary locations. There is a new replenishment report as well - and setup in the system that allows the material handlers to quickly enter the stock moves. (we see this enhanced going forward as well with a mobile app/bar code for efficiency).

Does this help to outline the use case of the original question of supporting multiple locations?

We actually see this environment as typical in many organizations...

[Sinoj Sebastin (ssebastian)]

Hello,
Thank you for the update. Now the performance improved very much. But there is one issue when two or more people trying to do sale order confirmation simultaneously. The error message is given below.

Environment Information :
System : Linux-2.6.38-8-generic-x86_64-with-Ubuntu-11.04-natty
OS Name : posix
Distributor ID: Ubuntu
Description: Ubuntu 11.04
Release: 11.04
Codename: natty
Operating System Release : 2.6.38-8-generic
Operating System Version : #42-Ubuntu SMP Mon Apr 11 03:31:24 UTC 2011
Operating System Architecture : 64bit
Operating System Locale : en_IN.ISO8859-1
Python Version : 2.7.1+
OpenERP-Client Version : 6.0.2
Last revision No. & ID :0 null:
Traceback (most recent call last):
  File "/home/user/workspace/npg/bin/netsvc.py", line 489, in dispatch
    result = ExportService.getService(service_name).dispatch(method, auth, params)
  File "/home/user/workspace/npg/bin/service/web_services.py", line 599, in dispatch
    res = fn(db, uid, *params)
  File "/home/user/workspace/npg/bin/addons/audittrail/audittrail.py", line 538, in exec_workflow
    return super(audittrail_objects_proxy, self).exec_workflow(db, uid, model, method, *args, **argv)
  File "/home/user/workspace/npg/bin/osv/osv.py", line 122, in wrapper
    return f(self, dbname, *args, **kwargs)
  File "/home/user/workspace/npg/bin/osv/osv.py", line 196, in exec_workflow
    res = self.exec_workflow_cr(cr, uid, obj, method, *args)
  File "/home/user/workspace/npg/bin/osv/osv.py", line 189, in exec_workflow_cr
    return wf_service.trg_validate(uid, obj, args[0], method, cr)
  File "/home/user/workspace/npg/bin/workflow/wkf_service.py", line 80, in trg_validate
    res2 = instance.validate(cr, id, ident, signal)
  File "/home/user/workspace/npg/bin/workflow/instance.py", line 48, in validate
    workitem.process(cr, witem, ident, signal, force_running, stack=stack)
  File "/home/user/workspace/npg/bin/workflow/workitem.py", line 61, in process
    ok = _split_test(cr, workitem, activity['split_mode'], ident, signal, stack)
  File "/home/user/workspace/npg/bin/workflow/workitem.py", line 174, in _split_test
    _join_test(cr, t[0], t[1], ident, stack)
  File "/home/user/workspace/npg/bin/workflow/workitem.py", line 182, in _join_test
    create(cr,[activity], inst_id, ident, stack)
  File "/home/user/workspace/npg/bin/workflow/workitem.py", line 41, in create
    process(cr, res, ident, stack=stack)
  File "/home/user/workspace/npg/bin/workflow/workitem.py", line 61, in process
    ok = _split_test(cr, workitem, activity['split_mode'], ident, signal, stack)
  File "/home/user/workspace/npg/bin/workflow/workitem.py", line 174, in _split_test
    _join_test(cr, t[0], t[1], ident, stack)
  File "/home/user/workspace/npg/bin/workflow/workitem.py", line 182, in _join_test
    create(cr,[activity], inst_id, ident, stack)
  File "/home/user/workspace/npg/bin/workflow/workitem.py", line 41, in create
    process(cr, res, ident, stack=stack)
  File "/home/user/workspace/npg/bin/workflow/workitem.py", line 61, in process
    ok = _split_test(cr, workitem, activity['split_mode'], ident, signal, stack)
  File "/home/user/workspace/npg/bin/workflow/workitem.py", line 174, in _s...

[Olivier Dony (Odoo) (odo-openerp)]

@David, Raphael: thanks a lot for the detailed explanation, I understand
better the use cases now.

I was thinking of the multiple dimensions we have for journal entries:
e.g each entry can be linked to a Partner, which allows per-customer
bookkeeping. Perhaps a similar concept at stock.move level would help in
some cases (e.g. consigned stock). And/or another dimension for
"sublocations" could save the trouble of multiplying first-class
locations. Of course these other dimensions only make sense if you
really have the same concepts in your warehouses, e.g. if you do store
consigned products from multiple customers in the same physical
location, etc.
We're thinking of doing something like that for 6.1, but of course this
doesn't solve the other shortcomings David describes for retail
distributors.
Anyways we'll definitely have a look at the openerp-usa modules too.
I'm cc'ing Quentin who is currently in charge of our Warehouse R&D
projects (Quentin: see
https://bugs.launchpad.net/openobject-addons/+bug/770243 for the full
discussion).

[Olivier Dony (Odoo) (odo-openerp)]

@Sinoj: the traceback you encountered does not come from the stock
reservation itself, but is due to the attribution of a sequence number
for the new picking list (during SO confirmation), and is a different topic.

*Short answer*: it is not technically a bug, and cannot be solved
_properly_ without deep changes. The second user can simply try again
after a few seconds. We are planning improvements in this area in 6.1.

*Long answer*: There are only 2 situations in OpenERP where this
temporary failure may occur:
1) when requesting the next number in a sequence, if another user
request is still being processed, and had requested the previous number
of this same sequence
2) when requesting the reservation of a certain product in a certain
location, if another user request is still being processed and reserved
the same product in the same location
Technically, having this "fail-fast" behavior while trying to acquire
locks on these critical resources is required to be able to guarantee
the transactionality of stock reservation and sequence attribution,
while still making sure deadlocks can never happen.

Granted, the user feedback could be improved...

In 6.1 we will have a new type of sequence that can be used for cases
where "gapless" is not a requirement (e.g. for picking list sequence
numbers), and will not have the resource contention issue.

BTW, for those who are writing RPC automation code, such special cases
must be handled properly, you cannot simply fire-and-forget. You can of
course use heuristics to retry the transaction if you know the typical
usage pattern very well.

I think further discussion on this is off-topic wrt current bug (perhaps
belongs to its own thread on the framework mailing-list)

[Jay Vora (Serpent Consulting Services) (jayvora)]

It has been fixed by revision 4753 <email address hidden>.
Thanks Sinoj and Olivier Dony.

[Kyle Waid (midwest)]

Hello Olivier,

I assume you are talking about the postgres sequence module that Sharoon Thomas has developed? As you have stated there are actually two issues with the sequences, one of which has been addressed by that module.
@ Sinoj, there is already a bug report and proposed solution to that issue here
https://bugs.launchpad.net/openobject-server/+bug/746620

This stock management problem is very interesting.

I have a question though, How can OpenERP ever support the possibility of 1,000s of stock locations with the stock_move database structure. Some consultants have said that every time it tries to compute the stock with that many locations the system will become slower and slower and in 6 months with that many locations it will be unusable. This is because of the function field and it has to compute all of the moves to correctly calculate the value of stock. We too have this problem with multiple stock locations. We have between 1,500-2000 and we have maybe even 1,000 stock moves per day.

Our servers are 2x Quad Xeon and 16GB Memory with separated database server and application server (physical) It takes up to 10 minutes to validate picking on some orders. Will try the patch

[OpenBias (openbias)]

Attached the patch Olivier Dony (OpenERP) (odo-openerp) patch for version lp:openobject-addons/6.1 on revision 6793

[Olivier Dony (Odoo) (odo-openerp)]

Thank you OpenBias, the 6.1 branch indeed requires this patch, so let's reopen the bug for 6.1.

[Paulius Sladkevičius @ hbee (komsas)]

Please include and 7.0/trunk too.