NF6.2 Reporting from Read-Only Replica

From iDempiere en
Jump to navigation Jump to search

Feature: Reporting from Read-Only Replica

Goal: Technical (Performance)

Sponsor: Trek Global

Developer: Carlos Ruiz

Description:

Some reports can create huge CPU and disk stress on the database, the reporting engine from iDempiere provides an ad-hoc way for users to create and shape their own reports, that's a big advantage, but also it can have a penalty on performance of the production database.

In order to relieve the production database from the stress of some heavy reports, it is possible now to configure a read-only replication and reports can be issued directly from the replica database, not affecting the CPU and disk from the master database, but from the replica server.

Configuration:

Configuring this new feature is simply filling properly the following SysConfig parameters:

Verification of replica synchronization:

Just reports running without a transaction can be executed from the replica, this is because the replica is not synchronized until the transaction is committed, so is not possible to read an uncommitted record from the replica server.

Before executing a report from the replica there are several validations that are checked to ensure the report is consistent with what the user expects:

  • The system connects to the first replica, if the verifications don't pass, then it connects and verify the second replica ... and so on
  • Firstly it verifies if the value of AD_System.DBAddress matches in master and replica database
    • if the DBAddress doesn't match then the replica is marked as invalid and is not used anymore
  • Next it verifies if the replica is synchronized, mechanism works this way:
    • Before executing the report the system register the timestamp in the unique record of the table DBReplicaSyncVerifier.LastUpdate, and then when reading the replica verifies if that timestamp has already being recorded in the replica
  • if the replica is not in sync, then tries the next URL, when all URLs are exhausted then the system waits for DB_READ_REPLICA_NORMAL_TIMEOUT_IN_MILLISECONDS milliseconds, and then try again for a maximum number of DB_READ_REPLICA_NORMAL_MAX_ITERATIONS times
  • if after all iterations it cannot find a valid replica in sync, then the report is executed from the master database

Technical Notes:

This feature opens a direct connection to the replica database, not using a connection pool, it simply opens the connection, execute the report and close the connection.

Technical Notes for PostgreSQL:

Sometimes postgresql throws error when running a big query on a read-only replica - this can happen because the query takes too long.

In such cases, the following postgresql.conf parameters can help:

max_standby_archive_delay=-1
max_standby_streaming_delay=-1

See runtime-config-replication on PostgreSQL documentation

Technical Info: IDEMPIERE-3850