Open Source ERP Academy

Database and iDempiere Performance Tuning

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