Open Source ERP Academy

Sample Large Installation for More than 100 Concurrent Users

The purpose of this page is to help you install a system that is capable of scaling well beyond 100 concurrent users.

Introduction

To grow beyond 100 concurrent users you follow this path:

Example Large Configuration in AWS

The below configuration is for a 150 concurrent user system. The expected cost will be about $250/mo USD (less than $2 USD per user per month) on a 3yr AWS contract. After UAT and Test systems, the expected cost is about $3/user/mo USD. The concurrent user count of 150 is conservative - meaning that you should be able to achieve above 150 concurrent users. How much above will depend on your usage profile. Notes:

Start Small - Finish Big

The above prescription includes the use of r5.large servers for the application server and t3.xlarge for database. If you have a situation where you want to start small but know you will finish big, you can simply start with these two servers costing you about $100/mo USD: Doing so will allow you to purchase reserved instances knowing that you can grow the system without wasting money on reserved instances you will not use. You will have ample horsepower to begin with - which makes users happy.

Introduction to Replication

Database replication does not immediately help you scale to more users; however, it is a building block to (1) creating a more highly available system and (2) creating a singe write - multi-read database cluster. I am still not sure how to best implement the database cluster. Here is an iDempiere forum discussion on the topic.

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

HAProxy (WebUI Load Balancing) Server Setup

The purpose of this page is to help you install/setup haproxy on a dedicated machine to load balance two or more application servers. This section/page only discusses the haproxy config.  iDempiere also needs to be configured so that services only run on one of the application servers. HAProxy is an open source load balancer that I have used in the past with good success. AWS also has a load balance service. Here is an example where the services server sits inside the load balanced cluster. .                          10.6.1.230 HAProxy .                      /                |                     \ 10.6.1.231          10.6.1.232             10.6.1.233(main iD/AD sever) Here is an example where the services server sits outside the load balanced cluster (recommended). If any load balanced server goes down, users of that server will lose their session. They will simply be asked to re-login. All other users will be ignorant to the situation. User load should not affect services. .        10.6.1.230 HAProxy .             /                     \ 10.6.1.231        10.6.1.232              10.6.1.233(main iD/AD sever)

haproxy Installation Steps

Create a new Ubuntu 20.04 server. Here is the most updated copy of the HA Proxy config. The following commands install and configure haproxy. 
sudo apt-get update
sudo apt-get install haproxy socat
sudo mv /etc/haproxy/haproxy.cfg{,.original}
Open a new haproxy config file and copy this HA Proxy config contents into the file. Look for the below bolded lines and update according to your situation. 
Sample - use above link for actual config

global
    log 127.0.0.1 local0 notice
    maxconn 2000
    user haproxy
    group haproxy
    stats socket /etc/haproxy/haproxysock level admin

defaults
    log     global
    mode    http
    option  httplog
    option  dontlognull
    retries 3
    option redispatch
    timeout connect  2m
    timeout client  3m
    timeout server  3m

listen idempiere
    bind 0.0.0.0:80
    mode http
    stats enable
    stats uri /haproxy?stats
    stats realm Strictly\ Private
    stats auth admin:ChangeMeN0w111
    stats admin if TRUE
    balance leastconn
    option httpchk
    option http-server-close
    option forwardfor
    stick-table type ip size 1m expire 12h 
    stick on src
    server idempiere1 172.30.0.105:8080 check
    server idempiere2 172.30.0.99:8080 check
Stick-table discussion Balancing on IP discussion Start haproxy. 
sudo service haproxy restart
Historical Reference - installation for 16.04

SSL with HAProxy

Good reference includes ssl termination and forcing http to https

External Health Check

Write an external health check

Troubleshooting HAProxy

Monitoring
http://IP_OF_SERVER/haproxy?stats
Configuration Issues
If you have issues starting HAProxy, use the following command to start it and see the error: 
haproxy -f /etc/haproxy/haproxy.cfg -c
Reviewing logs
Count requests per IP: 
grep -E '(19:[0-5][0-9]:[0-5][0-9]|20:20:00)' /var/log/haproxy.log | awk '{print $6}' | cut - d ':' -f1 | sort | uniq -c | sort
first column - number of requests second column - IP from which requests came
Cookie or Session Issues
If you are having trouble getting iDempiere to behave correctly behind HAProxy, make sure your "stick on req.cook(<cookie_name>)" value above is correct. An easy way to know that your session ID is incorrect is that your login => Username and Password fields will be grayed out. iDempiere changes the cookie name from version to version. It has gone from JSESSIONID to WEBUI_SESSIONID to ROOT_SESSIONID. To know for sure, use your browser developer tools => storage options to see the cookies that are saved by iDempiere.

Control HAProxy from Command Line (CLI)

echo "disable server yourbackendname/yourservername" | socat stdio /etc/haproxy/haproxysock
To Enable, replace "disable" with "enable" in the command above. More details...

HAProxy User Interface

This project looks promising; however, I have not used it yet.

Note About Load Balancing

If you are using HAProxy for load balancing, you need to review your DB Server => /etc/postgresql/x.x/main/postgresql.conf => max_connections setting compared to the application server => /opt/idempiere-server/PostgreSQL/pool.properties. Four servers each running a pool.properties => 90 max connections is a lot of connections for the DB.

Session Details

To learn more about the appsession (cookie), see Chrome => Developer Tools (right-click inspect option) => Application menu => Storage section => Cookies page. You can change the server details located to the left of the login screen by finding a file containing the text ">Database Version<" and modifying it to represent something unique to that server ">Database Version A<" and ">Database Version B<". Here is the command to find the file you want to change. Please keep in mind that we are not creating two database versions. In fact, both servers should be touching the same database. I was just looking for an easy way to distinguish the two application servers. Note: this search might take a while to complete. In my server, it is located in: /opt/idempiere-server/plugins/org.adempiere.ui.zk_3.1.0.v20180301-1816/theme/default/zul/login/version-info.zul. 
grep -rnw /opt/idempiere-server/ -e "Database Version"

Separating WebUI Hosting from Services

When you install iDempiere on a single server, the application server supports two duties: (1) WebUI server and (2) Services server. The purpose of this section is to help you support growth beyond 100 concurrent users by separating these duties onto two or more different servers. The WebUI is what you see when you use iDempiere's web client. Services includes the processes that are needed to support the behind-the-scenes iDempiere operations. The most recognizable service is the accounting engine. As you approach or cross the 100 concurrent user threshold, the application becomes a significant single point of failure. You can minimize risk by spreading the WebUI load across multiple servers. Before you can create multiple WebUI servers, you must first isolate the services server. The below instructions only apply to iDempiere. To isolate services to a single server, perform the following:

Installation Script

When you install iDempiere with my installation script, you can also install a dedicated services instance using the -s option. A dedicated services instance is just another iDempiere server. The importing thing to note is that the database knows the IP of the services instance. Search the installation script for "if [[ $IS_SET_SERVICE_IP == "Y" ]]". This section tells you what statements get executed so that iDempiere knows what server to use for services.

Strategies for Modifying ADempiere's AdempiereServerMgr

This section only applies to ADempiere (not iDempiere). The class you modify is AdempiereServerMgr.java. How you conditionally start (or not start) the services depends on your skill level. Here are some options: I would recommend you reach out to Deepak if you want someone to code this for you. If you do implement this change, please email me the class as a reference implementation for the class.

NOTICE:

I do not use pgbouncer in production. Below was an experiment, and I did not want to lose my notes and thoughts about the topic.

PGBouncer PostgreSQL Connection Pooler

The purpose of this page is to help you install/setup PGBouncer on a dedicated machine as a connection pooler for your database. Perform the following:
  1. Create a new Ubuntu 14.04 installation
  2. Copy the below script to your home directory (call it chuboe_phbouncer.sh)
  3. Update the variables at the beginning of your script to match your environment
  4. Make the script executable using "chmod +x chuboe_phbouncer.sh"
  5. Run the script using ./chuboe_phbouncer.sh
Script to copy to your new Ubuntu server: 
chuboe_dburl=Your_DB.URL
chuboe_dbname=idempiere
chuboe_dbuser=adempiere
chuboe_dbpass=Silly
chuboe_dbport=5432
chuboe_osuser=ubuntu
chuboe_max_client_conn=300 # should be 100 times the number of application servers
chuboe_default_pool_size=25

#get rid of AWS sudo error in a VPC
wget bitbucket.org/cboecking/idempiere-installation-script/raw/default/utils/setHostName.sh
chmod +x setHostName.sh
sudo ./setHostName.sh

#install pgbouncer and postgresql client tools (like psql)
sudo apt-get update
sudo apt-get install -y pgbouncer postgresql-client

#prevent the script from prompting the user for a password
echo "*:*:*:$chuboe_dbuser:$chuboe_dbpass">>/home/$chuboe_osuser/.pgpass
sudo -u $chuboe_osuser chmod 600 .pgpass
sudo cp .pgpass /var/lib/postgresql/
sudo chown postgres:postgres /var/lib/postgresql/.pgpass

#copy usernames and passwords from main database
sudo -u postgres psql -U $chuboe_dbuser -d $chuboe_dbname -h $chuboe_dburl -c "\copy (select '\"'||rolname||'\" \"'||coalesce(rolpassword,'')||'\"'from pg_authid) to '/etc/pgbouncer/userlist.txt';"

#configure pgbouncer.idi
sudo sed -i "/\[databases]/apostgres = host=$chuboe_dburl\ntemplate1 = host=$chuboe_dburl\n$chuboe_dbname = host=$chuboe_dburl" /etc/pgbouncer/pgbouncer.ini
#note: the admin user can connect to a simulated database called pgbouncer. This helps you better understand connection and behavior details
sudo sed -i "/admin_users =/aadmin_users = postgres" /etc/pgbouncer/pgbouncer.ini
sudo sed -i "s|auth_type = trust|auth_type = md5|" /etc/pgbouncer/pgbouncer.ini
sudo sed -i "s|listen_addr = 127.0.0.1|listen_addr = *|" /etc/pgbouncer/pgbouncer.ini
sudo sed -i "s|max_client_conn = 100|max_client_conn = $chuboe_max_client_conn|" /etc/pgbouncer/pgbouncer.ini
sudo sed -i "s|default_pool_size = 20|default_pool_size = $chuboe_default_pool_size|" /etc/pgbouncer/pgbouncer.ini
sudo sed -i "s|listen_port = 6432|listen_port = $chuboe_dbport|" /etc/pgbouncer/pgbouncer.ini
#fix known issue - http://permalink.gmane.org/gmane.comp.db.postgresql.pgbouncer.general/704
sudo sed -i "s|;ignore_startup_parameters|ignore_startup_parameters|" /etc/pgbouncer/pgbouncer.ini

#set pgbouncer to auto start on boot
sudo sed -i "s|START=0|START=1|" /etc/default/pgbouncer

sudo service pgbouncer start

#Clear variables
chuboe_dburl=
chuboe_dbname=
chuboe_dbuser=
chuboe_dbpass=
chuboe_dbport=
chuboe_osuser=
chuboe_max_client_conn=
chuboe_default_pool_size=

How to view PostgreSQL Connection Details

View connection details from SQL: 
SELECT count(*) FROM pg_stat_activity; -- see count
SELECT * FROM pg_stat_activity; -- see the details of the open connections
SELECT sum(numbackends) FROM pg_stat_database; -- similar but different view
View connection details from the OS: 
ps aux | grep postgres | grep -v grep

Connect to the pgbouncer Admin Interface

psql -h localhost -p 6432 -U postgres -d pgbouncer
Show Servers;
Show Clients;
Show Pools;
Show Stats;
Note: if the request_time value should be recent. Otherwise, a connection is being hogged and effectively pulled from the queue.

Resources