Open Source ERP Academy

Previously Recorded Meetings Archive 4

The purpose of this page is to archive previously recorded videos. If you are new to the class and you want to review previous discussions. Start from the bottom of the archive page (first recording) and work your way up.

Meeting Video Archives

2014-12-30 AM - Open Discussion

Topic: Editing Print Format

2014-12-23 AM - Open Discussion

2014-12-11 AM 2 - Open Discussion 2

This discussion provides the roadmap for how an IT professional can get up to speed as quick as possible.

2014-12-11 AM - Open Discussion

Scripts from the above discussion

Creating cost records for new cost type where 1000003 is the ID of the new cost type: 
insert into m_cost
select ad_client_id, ad_org_id, ..., 1000003, generate_uuid() from m_cost
;
Creating cost records for different orgs to support Cost Level = "Organization": 
insert into m_cost
select c.ad_client_id, o.ad_org_id, ..., generate_uuid()
from m_cost c
join ad_org o on c.ad_client_id = o.ad_client_id and o.isactive = 'Y'
;

2014-12-09 AM - Open Discussion

2014-12-02 - PM Open Discussion

2014-11-20 PM Open Discussion

2014-11-20 AM - Open Discussion

Sample code from "prevent or disallow negative inventory" discussion above: 
Model Validator (chuckSteakValidator)
BeforeCompleteIt()
{
if issotrx = Y
//from the header perspective
select m_product_id, m_locator_id, m_attributesetinstance_id , sum(iol.movementqty)
from m_inoutline iol
where iol.movementqty > 
	(select sum(qtyonhand) 
		from m_storage 
		where m_product_id = iol.m_product_id 
			and m_locator_id = iol.m_locator_id 
			and m_attributesetinstance_id = iol.m_attributesetinstance_id
	)
	and iol.m_inout_id = ?
group by m_product_id, m_locator_id, m_attributesetinstance_id 
}

If the abvoe query returns a result, then
return "Product = , Locator = , ASI =  brings the quantity on hand below zero"
 

2014-11-18 PM Open Discussion

2014-11-18 AM - Open Discussion

2014-11-13 PM - Open Discussion

2014-11-13 AM - Open Discussion

2014-11-11 PM - Open Discussion

2014-11-11 AM - Open Discussion

2014-11-06 PM Open Discussion - Update RV_Storage to Include Sales and Replenish Data

This videos walks you through the process up extending RV_Storage (Storage Detail Report) to include sales and replenish data. This video is quite long because it brings you through the entire thought process including trial and error on SQL query. The query was created with phppgadmin.

2014-11-06 PM Open Discussion - Intro European Accounting

2014-11-06 Daily Banking Discussion

2014-11-06 AM - Open Discussion

2014-10-28 PM - Open Discussion

2014-10-23 PM - Open Discussion

2014-10-23 AM - Open Discussion

2014-10-21 Landed Costing Review and Standard Costing

2014-10-21 AM - Open Discussion

2014-10-16 AM - Open Discussion

2014-10-14 AM - Open Discussion

2014-10-09 - Batch Deposit - Payments into Batch

2014-10-09 AM - Open Discussion

Discussed how to upgrade (migrate of migration) your database when you did not use my script to install iDempiere.

2014-10-02 PM - Open Discussion

The purpose of this page is to help you get started when trying to maximize performance, diagnose issues and maintain a very fast system.

Index Performance Tuning on Like Query

https://www.postgresql.org/docs/current/pgtrgm.html gin - fast for read (preferred for materialized view) gist - more balanced between insert and read

Installation Script Performance Tuning

The installation script can performance tune your system depending on how your install iDempiere.

Database Quick Summary

Start here... Also run postgresqltuner ubuntu cli application. Note that you can install it on any server (not necessarily the db server). Example usage: 
./postgresqltuner.pl --host=somehost --database=idempiere --user=adempiere --password='somepassword'
There are generally three tasks associated with making PostgreSQL run faster and more efficiently:
  1. Update PostgreSQL settings to meet your system and your business needs
  2. Identify problems
  3. Fix problems
Regarding #1, the best way to tune PostgreSQL for your needs is via this tool: https://www.pgconfig.org/#/tuning. Note: this tool is automatically run if you install iDempiere via my script on dedicated machines. Regarding #2, the single best way to identify performance issues is pgbadger. There is a pgbadger installation script here. This link includes details about PostgreSQL log management as well. This is a tool that runs periodically, generates static html files and shows you what queries are consuming the most resources. Regarding #3, fixing issues requires the most institutional knowledge. If you can perform steps 1 and 2 and you are unsure about step 3, then email the group the pgbadger results and join the Open Discussion meetings so that we can identify the culprit and fix.

Database and iDempiere Performance Tuning

NOTE: the installation script uses the results of this site to tune the database when installing the database on a dedicated machine. NOTE: run the process Enable Native Sequence to move system ID generation to the database. This option is much faster for importing high record counts. Hans' memory comment This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter. You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 415776768 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50000) and/or its max_connections parameter (currently 12).

Building a Scalable and Highly Available PostgreSQL System

For more information about performance tuning for postgreSQL applications, I highly recommend the PostgreSQL 9 High Availability Cookbook. It is worth every penny.

"Table" and "Table Direct" are very Expensive

Here is an article I published about performance tuning. In it, it explains how iDempiere's caching of drop-down fields for very large tables is very expensive. The better alternative is to use the "Search" option instead of Table or Table Direct when the target table has more than 20 records. Here are queries that will help identify likely culprits. SQL to show rows by table: 
SELECT schemaname,relname,n_live_tup
 FROM pg_stat_user_tables
 ORDER BY n_live_tup DESC;
SQL to show iDempiere Columns that are either Table or Table Direct 
select c.ad_column_id, c.columnname, t.tablename
 from ad_column c
 join ad_table t on c.ad_table_id = t.ad_table_id
 where c.AD_Reference_ID in (18,19)
 order by lower(t.tablename), lower(c.columnname);
Start with the columns that reference tables with the greatest row counts first. The following query shows you row count (entries): 
SELECT
 relname AS objectname,
 relkind AS objecttype,
 reltuples AS entries, pg_size_pretty(relpages::bigint*8*1024) AS size
 FROM pg_class
 WHERE relpages >= 8
 ORDER BY entries DESC;

How to Fix

NOTES Create a backup just in case: 
Create table chuboe_perfmax_columnsback_20180709 as select ad_column_id, ad_reference_id from ad_column;
Update all views to use Search instead of Table or Table Direct. The reason is that no view should ever present a user with dropdown since all fields are read only. You can run this query as often as you wish since new views will default to Table references. 
update ad_column
set ad_reference_id = 30
where ad_column_id in (
select c.ad_column_id
 from ad_column c
 join ad_table t on c.ad_table_id = t.ad_table_id
 where isview = 'Y'
  and c.AD_Reference_ID in (18,19)
);
Update all CreatedBy and UpdatedBy references to Search.The reason is that no one should editing these fields. 
update ad_column
set ad_reference_id = 30
where ad_column_id in (
select c.ad_column_id
 from ad_column c
 where c.columnname in ('CreatedBy','UpdatedBy')
  and c.AD_Reference_ID in (18,19)
);
In order to update references to active and editable windows, we need to create a quick list of tables that have high record counts: 
DROP TABLE IF EXISTS chuboe_perfmax_bigtables;
CREATE TABLE chuboe_perfmax_bigtables AS 
SELECT
 relname AS objectname, reltuples AS entries, pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
 WHERE reltuples >= 55
and relkind = 'r';
Extract out the TableName from the Column Name: 
DROP TABLE IF EXISTS chuboe_perfmax_coltotable;
CREATE TABLE chuboe_perfmax_coltotable AS
Select 
c.ad_column_id, c.columnname, coalesce(lower(rtt.tablename),lower(SUBSTR (TRIM (c.columnname), 1, LENGTH (TRIM (c.columnname)) - 3))) as tablename
from ad_column c
left outer join ad_reference r on c.ad_reference_value_id = r.ad_reference_id
left outer join AD_Ref_Table rt on rt.ad_reference_id = r.ad_reference_id
left outer join ad_table rtt on rt.ad_table_id = rtt.ad_table_id
where c.AD_Reference_ID in (18,19)
and lower(columnname) like '%_id';
Update the necessary columns. Note that I included a line about ad_val_rule_id (Dynamic Validation). The reason is that Search dialogs with Dynamic Validation that exist in a subtab might not perform as expected (see this ticket for details). 
update ad_column set ad_reference_id = 30
--select columnname, ad_reference_id from ad_column
where ad_column_id in 
(
select ad_column_id 
from chuboe_perfmax_coltotable
where tablename in
(
select objectname from chuboe_perfmax_bigtables 
)
)
and ad_val_rule_id is null
and lower(columnname) not in ('ad_org_id','user1_id','user2_id','ad_val_rule_id')
;
The following query will help you focus on creating the proper indexes. It shows you what dynamic validations exist for all remaining Table Direct and Table references. 
select count(*) as count, c.columnname, r.name as DynValName, r.code as validationcode,
(select array_to_string(array(
select t.tablename
--|| coalesce('_'||(select entries from chuboe_perfmax_bigtables where lower(t.tablename) = objectname),'')
from ad_table t where t.ad_table_id in 
(
select xc.ad_table_id 
from ad_column xc 
where xc.columnname = c.columnname
and xc.ad_val_rule_id = c.ad_val_rule_id
and xc.AD_Reference_ID in (18,19)
)
order by lower (t.tablename)
),', ')
) as RefFromTables
from ad_column c
join AD_Val_Rule r on c.ad_val_rule_id = r.ad_val_rule_id
where c.AD_Reference_ID in (18,19)
group by c.columnname, r.name, r.code, c.ad_val_rule_id
order by c.columnname;

Notes About the "Search" Reference

Run Away Locators

In a past ADempiere project, we discovered there was a bug in the locator lookup field. This this is iDempiere's default field type to help users find the right locators. The issue was that the locator would load every locator's model every time the locator control was used. If you had an instance with 150K locators, the server CPU would hit 100% and it would go into a non-usable state for about 10 minutes. I let the developers know; however, I never followed up to test if the issue was resolved. Let me know if you think this issue is impacting you.

Configuring Streaming Synchronous Replication in PostgreSQL

Cheatsheet that is current as of PostgreSQL 9.6

2014-10-07 AM - Open Discussion

2014-10-02 - Print Invoice Run and Dunning Run

2014-10-02 AM - Open Discussion

2014-09-30 AM - Open Discussion Including Simple Document Validation

2014-09-25 - Replication, Stability and Performance Tuning

2014-09-26 - AM Open Discussion

2014-09-23 - Open Discussion about European Accounting and Business Partner Tagging

I plan on re-recording this video; therefore, it is not transcribed.

2014-09-23 AM - Open Discussion

This is a good video to watch to better understand the limitations of Production and how to work through them.

2014-09-18 Security and Stability Discussion

2014-09-18 AM - Open Discussion