iDempiere Scaling - DB Perspective - PostgreSQL Load Balancing vs ReadOnly Transactions

[Chuck Boecking]

Hi Team,

I would like your thoughts on database performance scaling if you do not mind sharing. There are two ways I can see iDempiere scaling to very large systems. Both options support a single-write master with multi-read replicas. The question is "who is responsible for brokering between the write and read db servers.

1. pgpool-style database load balancing where iDempiere only sees one database and the load balancer handles the complexity of brokering write-based transactions to the write master and read-based transactions to one of the many read replicas. I have created this scenario in a lab. The replication is easy; however, pgpool is harder. I am concerned about its stability and efficiency given iDempiere's transaction complexity. 
2. The other option would require modifying iDempiere to know which transactions will include a write and which transaction (or no transaction) would perform only reads. I believe this means:
1. adding a new 'read only' type of transaction.
2. adding the concept of read-only database connection
3. iDempiere would send all transactions that include the possibility of a write to the write-master database
4. iDempiere would send all read transactions to the read replica database
5. you can have multiple read replicas behind a haproxy load balancer (I have not done this - only read about it) to increase throughput and minimize complexity from iDempiere's perspective.

Most processes would not be a candidate for a read-only transaction. Loading data into a window would be a candidate. Reports would be a candidate.

We are adding more and more functionality. I see iDempiere supporting operations for larger and larger organizations. I have already supported two systems with user counts well in excess of 100 users. The trend line is up.

Thanks for reading,

Chuck Boecking

www.chuckboecking.com

[norber...@multimageweb.com]

Hi Chuck.

#1 pgpool - i played with basic scenarios, but pgpool being slower then direct connection. i had scenario to open 100 connections instead opening connection on request. maybe i made something wrong i ran it 1 day in production then shutdown. i should continue with testing - resetup. I reading some topics in pgpool community, and looks there are some very missing features. cant namely describe here, but if you are interested i can look into details well.

#2. idempiere modification - simple but effecience way would be implement something like http://blogs.walkingtree.in/2013/03/07/seperate-database-for-read-and-write-in-adempiere/. With this solution (if working technically) we should split read/write. not big concepts. im consultant so cant say more here... your approach is much more complex...

also i'm thinking about in memory databases. like e.g. magento able to use and improve performance in areas of products, customers etc. i wondering it is not a better way to implement inmemory drivers.  maybe this direction totally wrong..... 

norbert bede

This e-mail is confidential and may contain legally privileged information. It is intended only for the addressees and may not be reviewed or used in any way by other recipients. If you have received this e-mail in error, kindly notify us immediately by telephone or e-mail and delete the message and any attachments thereto from your system.

[Chuck Boecking]

Norbert,

Thanks! It is my understand that pgpool's connection pooling is not as good as pgbouncer. These two are commonly used together. pgbouncer was created by skype. When skype sold to M$, they had the biggest PostgreSQL isntance in the world according to my enterpriseDB instructor.

Nice find on the walking tree post! 

The aging report represents an iconic example of where pgpool's smarts would be put to the test:

• a process populates what iDempiere calls a temporary table, T_Aging
• then immediately calls on a report. 

I doubt the two happen in the same transaction, and if pgpool reads from a read replica, streaming speed would be put to the test (and would probably fail).

Regards,


Chuck Boecking
www.ChuckBoecking.com

[Carlos Antonio Ruiz Gomez]

Chuck, I made lots of tests with replication+read-load-balance on postgresql with version 9.x (don't remember exactly the version but was the first that included replication in core).

The results were not as good as I wanted:

- postgresql replication + pgpool read-load-balance
the postgresql replication slowed the write performance by a factor of 2 or 3
didn't work as the replication is not guaranteed at commit time, so in some cases a transaction can write a record on the master database and commit it - and if the next statement is a read of such record is possible that the record doesn't appear in the read-only replicas resulting in a failure

- pgpool replication + pgpool read-load-balance
the pgpool replication slowed the write performance by a factor of almost 5
it worked correctly, this guaranteed the committed record is committed in master and replica

I tested just with one read-only-replica, and my tests were in amazon normal instances so the I/O is expected to be slow.

We were thinking to use that for reports, then we figured out that the most expensive report for database are the Financial Reports, and that this scenario will not help at all with Financial Reports as they create a T_ table reading from Fact_Acct within a transaction (meaning just the master can be used) and this is the heavy part of financial reports, then the rendering of the report is reading from T_ table, this can be done on a read-only replica, but this is the light part of the report.

Those are my 2 cents on this  :)

Regards,

Carlos Ruiz

--

[Chuck Boecking]

Thank you Carlos,

Regarding PostgreSQL performance:

If you are interested, I would be willing to (1) fire up a heavy duty PostgreSQL instance, (2) create a read replica, and (3) tune them for maximum performance. I would like to know how far my 'large system' formula can grow. I would appreciate it because I am not as strong with load testing technologies. 

Side note: I have had success with using haproxy to load balance multiple webui severs. I have had a quad-server in production for just over a year. We can throw this into the mix as well.

Regarding pgpool, I agree with you. I do not believe it is an option. At least not as a write/read load balancer.

Regarding two data sources:

I believe the single biggest read load is populating window data/records. I believe it is also the one performance area most closely tied to how a user perceives performance. The shear amount of foreign references when opening a Sales Order window in grid view is impressive. Other examples include opening a Material Receipt, Invoice, Change Log, etc... window. We would need to take care because opening the window the first time is different than (a) reading data immediately before or (b) repopulating sub-tab data immediately after a record's save. Generally speaking, I think any given Table should know if it is capable of being read from a read replica. T_ tables would not. 

I am not too concerned with T_ style reporting performance. Reports like aging, inventory valuation and financial reports are generally limited to just a small percentage of the overall iDempiere users at an organization. There are other creative ways to help these users with speed.

You can query a read replica to find out how many bytes it is behind the master. If the threshold grows too big, we can revert back to reading the master.

If we were to follow walking's tree example, do you feel the solution would fit the iDempiere's way to thinking? Do you think we would we be able to keep the installation as simple as it is today and still be able to support the added complexity? 

I feel like this topic is pretty important because of iDempiere's position in open source ERP. iDempiere is a great solution for mid-sized, multi-national, complex organizations. These organizations typically carry a higher user count because of their size. They also carry a high user count because there is no per-seat license to prevent everyone from working in the same system. Most people looking for an open source version of QuickBooks will choose OpenERP. I feel like iDempiere should dominate the mid-tier market. The features are coming along nicely. I hope this discussion helps increase its performance abilities.

Thank you everyone for your time and consideration!

Regards,

Chuck Boecking

www.chuckboecking.com

[Carlos Antonio Ruiz Gomez]

Hi Chuck,

> Regarding pgpool, I agree with you. I do not believe it is an option.
At least not as a write/read load balancer.

I think is the opposite, I think postgresql replication doesn't fit - at
least until they solve the commit issue (which I read in some forums
they're not interested to solve), but pgpool worked pretty fine - just
slower.


I have a different view of the issue, opening a window and bringing many
records is not a big deal, and when it is I usually recommend to
restrict the number of records in role to force the users to filter.
Also, most of the time users look for the same columns and you can tune
that creating indexes.

But, when running heavy processes the overall system is affected, if two
users run a heavy financial report everything is affected, opening
windows, searching records, requery a list, etc.

The walking tree solution is basically the same solution as pgpool and
it suffers from the same issues (if using postgresql continuous
replication then there is a chance that just committed records are not
available in the read-only db, and using pgpool is slower than
continuous replication). But, if you use pgpool instead of internal
solution, you have big advantages.

The other thing you mention is if you can cope with a little "delay"
(how big the delay your reports/processes can support?)
Non-continuous replication is very fast, almost unnoticeable in my
tests, and the delay can be measured in seconds most of the time.
So if you have reports that can cope with some delay this could be an
interesting option to check.

Regards,

Carlos Ruiz