Seperate Database for read and write in ADempiere


Problem Statement:

Most of the software applications relies on databases for effective and efficient management of data  to serve business need in effective manner. Database become integral part of all modern software applications.

Three important factors play major role in success of any software product or application

  • Fulfils definite business objective
  • Ease of use
  • Performance of application

First two factors mentioned are extremely important, but once you achieve them , they will turn out to be static. But the performance of the system may vary based on the load of the system. Database play an important role in defining the performance of an application. If database is effectively used, performance of an application will increase in a big way.

Most of applications will start with single database instance for an application in production and this approach will be fine as long as it supports business with minimum required performance. When same database connection is being used by application read operations and write operations as well. During write operations corresponding resources will be locked and resources will be released, once the transaction either commit or rollback.Once write operation locks the resources, another operation ( either read or write ) should wait until resources release, waiting write operations are make sense to avoid conflicts, but waiting read operations does not make sense (i.e. the resources won’t be locked in read operation), waiting read operations also degrading application performance.

ADempiere application implicitly supports single database support and it also has limitations mentioned in above paragraph. As an ERP application, ADempiere needs to work with huge data sources and needs to support user base ranging from few users to some hundreds of users

If ADempiere can be modified to support separate databases for read and write transactions then performance of the application will be improved in a significant way.

Current architecture of ADempiere in perspective of Database looks as below.SingleDBADempiere

Scope of This Article:

As part of this article, we will cover following things:

  • How to configure multiple databases with Adempiere application
  • How to distinguish read and write operations
  • How to connect with corresponding database, while performing read or write operations

Below diagram will give overview of separate read and write databases for Adempiere application.MultiDB

Prerequisites :

  • We assume devloper has basic understanding of database connections, transactions, result sets, isolation levels
  • Adempiere 360 0r more

Assumptions:

  • how to do DB replication is out of scope

Details:

Step 1: Configuration changes

  1. adempiere.properties allows only one DB connection details as of now. It will be modified to configure two DB connections
  2. Configure separate read and write databases in Adempiere.properties, i.e. instead of Connection statement, will use read connection and write connection statement.

ReadConnection=xyzCConnection[name\=localhost{localhost-readdatabasename},AppsHost\=localhost,AppsPort\=12099,type\=PostgreSQL,DBhost\=localhost, Dbport\=5432,DBname\=readdatabasename,BQ\=false,FW\=false,FWhost\=,FWport\=0, UID\=adempiere,PWD\=adempiere]

WriteConnection=xyzCConnection[name\=localhost{localhost-writedatabasename},AppsHost\=localhost,AppsPort\=12099,type\=PostgreSQL,DBhost\=localhost, Dbport\=5432,DBname\=writedatabasename,BQ\=false,FW\=false,FWhost\=,FWport\=0, UID\=adempiere,PWD\=adempiere]

Step 2: Code changes

Note: Instead of same connection for read and write operations, different connections are being used by read and write operations separately. (i.e. where ever connection is represented in adempiere that was replaced by read connection or write connection correspondingly)

  1. Ini.java to keep read connection and write connection as wellIni1
  2. Create connections with read and write databases in Adempiere.java of startup() method. The below get() method of Cconnection will make read connection when the value of second parameter is false, other wise will make write connection and as well DBTarget also.2CreateConn
    • Cconnection.get():ConnectionGet
    • getWrite():getwrite
    • setDBTarget():setdbtarget
  3. Based on the result set concurrency the operation will be pointed to either read connection or write connection.
    • CpreparedStatement.javacpreparedstatementSetconnections
  4. In Trx.java, Make separate connections for read and write operations.
    • setConnections():initialise prepared statementtrx
  5. If write transaction also has read operation then it will use same transaction .
    • PO.java:po
    • save record and load current recordSave and load record
    • DB.java to create prepare statementdb

With above changes in place, application is ready to support multiple databases for a single isntance.

Summary:

I hope you have enjoyed reading this article and now you are able to setup separate read and write databases for ADempiere application. By using Adempiere Multiple databases concept, application performance will be increased in big way in turn leads to user satisfaction.

Walking Tree promotes ADempiere and we support the users as well as the developers to ensure that the business is able to take complete advantage of ADempiere’s wonderful capability. In case you are looking for a professional assistance then do visit our website to get in touch with us.

References:

Tagged with: , ,
Posted in ADempiere, EagleRP

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

We Have Moved Our Blog!

We have moved our blog to our company site. Check out https://walkingtree.tech/index.php/blog for all latest blogs.

Sencha Select Partner Sencha Training Partner
Xamarin Authorized Partner
Recent Publication
%d bloggers like this: