Hi Everyone,
The purpose of this post is to let you know about a performance tuning issue that was recently resolved in ADempiere and iDempiere, open source ERP. This post applies to anyone performing wholesale distribution or manufacturing across multiple locations or a single large location.
The Symptom:
Java CPU usage on a single core of a specific WebUI server was spiking to 100%. The server would eventually become unresponsive.
The Culprit:
By using the topics discussed in this post, we determined the cause of the CPU spike was due to the Locator Reference. In this instance, there are over 130K locators spread over 12 warehouses. When the Locator field was being loaded as part of opening a new window, the WebUI server would spend about 1 minute processing all the Locator options for each occurrence of the Locator field on the window. The Product window has about 3 occurrences; therefore, every time a user would open the Product window, the server would get hit hard. This issue was not obvious because the product window would load as if all were OK; however, behind the scenes, the server would stay busy churning away. If ten people concurrently open the product window on the same server, the system would grind to a halt.
The Solution:
Do not use the Locator Reference. Instead change the M_Locator_ID Reference to Search. The Search Reference is much easier to use, and it is more consistent with how ADempiere and iDempiere function everywhere else. Here are my thoughts:
- You can type in the search field, and if what you type matches a locator value, it will automatically find your record. This point is especially valuable for distribution centers that make heavy use of barcode scanners.
- The Locator Info window that pops up for Reference gives you the ability to search by the search key, Aisle (x), Bin (y), and/or Level (z). Unfortunately, the Locator Info window does not include the Warehouse. Let me know if you need help adding it, or just add the warehouse code to the beginning of your Aisle (x) field.
- The old Locator Reference allows anyone to create locators. This behavior is typically not desirable.
- The old Locator Reference drop down list of locators is clumsy.
Instructions:
To globally update ADempiere or iDempiere to use the search dialog, use the following query:
update ad_column set ad_reference_id = 30 where ad_reference_id = 31 and columnname = ‘M_Locator_ID’;Note that there are a couple columns named M_LocatorTo_ID, that you need to update to use Search. The reason I call them out separately is because you also need to perform one additional step: set the Reference to ‘Search’ AND set the Reference Key to ‘M_Locator’. It is easiest to do this by hand. Here is a query that will point you to the tables:
select t.tablenamefrom ad_column c
join ad_table t on (c.ad_table_id = t.ad_table_id)
where c.ad_reference_id = 31
and c.columnname = ‘M_LocatorTo_ID’
Disclaimer and Hint:
Any time you perform an update on a table, you run the risk of contaminating your data. When contamination happens, it can cost your hours or even days to recover. Here is a quick shortcut to ensure you do not waste time when bad things happen. Any time you are updating data in a table, make a quick backup using the following statement:
create table deleteme_ad_column_back asselect * from ad_column
The above query is super fast. You can create a simple update statement to pull the original values from the backup table if needed. And, if you ever see a table named deleteme* in the future, you can always delete it because you know it was used for a temporary backup.
ADempiere vs iDempiere vs Openbravo vs Compiere
Please note that ADempiere, iDempiere and Openbravo are forks or copies from Compiere. Therefore, they have similar abilities mentioned above. The biggest difference is that ADempiere and iDempiere are pure open source. There are no features held behind a commercial or paid license.
About Chuck Boecking: I am an ERP educator. I believe that open source ERP have achieved mainstream capabilities, and as a result, more companies can create greater efficiency across their organization. I started using the iDempiere code base in 2003. Back then, it was called Compiere. In 2006, I started my first multi-million dollar installation. Since then, ADempiere has helped me create great success with distribution and manufacturing companies all over the world. My vision of success is to find companies that can best use open source ERP to help them achieve a single, global instance that drives a discontinuous increase in profitability. I believe that organizations win when they own their technology.
If you have questions, comments or concerns, let me know. I definitely want your feedback.
You can contact me by phone using 512.850.6068.
My email is chuck@chuboe.com.
You can complete the form on this page.
Thank you for taking the time. I look forward to speaking with you.
Regards,
Chuck Boecking
http://www.linkedin.com/pub/chuck-boecking/10/970/17b