Read-only Replica setup problem

Dear All,
Setup:
We have configured 1 master and 3 read-only replica databasse in GCP CloudSQL (postgre) for our DHIS2 implementation. We have configure the read replicas in dhis.conf in all the application servers.

Problem:
However, based on our monitoring, most of the read queries are still directed toward the master database. This created high CPU usage only on our master, and almost no activities in the replicas, thus negating our objective to scale out the database.

Question:
Is there any other settings need to be configured in order to use the read replicas more effectively?

1 Like

Hey @Kresna_Adiprawira, welcome to the community! I hope that others will join the discussion; however, while asking for support, I got some info from @bobj and would like to quote his words:

It is a vexing issue. Many have tried and not succeeded. I believe the only successful implementation of replicated read only database is BAO. I have asked them before about whether they have any performance metrics from that. @Lars do you have any wisdom to add? (generally when people ask me about load balancing postgres I tell them not to do it. Would be great to be more constructive than that).

Hi guys,

We tried replication in South Africa using one master(bare-metal) and one read-only replica(VM) and we found the opposite.
All read queries to the database were being directed exclusively to the read-only replica even when the the master database server was idle. This caused high CPU usage on the read-only.

We’d hoped that the read-only would carry the bulk of the read-only queries and still dedicate a few to the master database server when the master is idle. Sadly this was not the case.

We are also very interested to see or hear what metrics BAO could come up with.

I hope this helps.

1 Like

Is there any update? I’m facing the same issue as the OP described. All queries are directed to the master db and none to the replica. I’m using DHIS2 version 2.37.9 and Postgres 13. DB replication is working fine.

1 Like

Thanks @potlaki and @riaz.somc!

Tagging @BAO_Systems (@katherinelew @alanivey @Busoye @dhuser @plinnegan …) if you’d like to share with the community some helpful insight about this issue, please. Thank you!

Hi @Gassim
Thanks for looping us in again on this thread; would be happy to share any info we have.

@potlaki and @riaz.somc I will tag @alanivey and follow up internally to see if he can help.

Best,
Busoye

2 Likes

Thank you @riaz.somc! Looking forward to hearing the response. :grin:

@riaz.somc DHIS2 2.36.0 introduced the configuration setting active.read.replicas for dhis.conf which looks to be expecting a number (source). Last time we tried read-replicas, it was before 2.36 so I can’t say for sure, but I’d say set that value to the number of read-replicas you have, restart Tomcat, and see if it helps.

EDIT: On second thought, after reviewing 2.37/DefaultReadOnlyDataSourceManager.java, it looks like that configuration setting is not designed to be set in dhis.conf but used internally when detecting if read-replicas are available. Also judging by the source there should be one of two messages for each read-replica:

  • Connection test failed for read replica database pool (source)
  • Read only configuration initialized, read replicas found (source)

If neither of those show in the logs, it may be a formatting problem with dhis.conf. You could post your dhis.conf contents here (redact any passwords if you do!)

2 Likes

Hi @alanivey, any solution to read replica

Thank you @alanivey for your suggestions. Here is the dhis.conf

# ----------------------------------------------------------------------
# Database connection
# ----------------------------------------------------------------------

# JDBC driver class
connection.driver_class = org.postgresql.Driver

# Database connection URL
connection.url = jdbc:postgresql://192.168.180.130/dhis2

# Database username
connection.username = dhis

# Database password
connection.password = xxxx

active.read.replicas=2

# Read replica 1 configuration

# Database connection URL, username and password
read1.connection.url = jdbc:postgresql://192.168.180.110/dhis2
read1.connection.username = dhis
read1.connection.password = xxxx

# Read replica 2 configuration
read2.connection.url = jdbc:postgresql://192.168.180.207/dhis2
read2.connection.username = dhis
read2.connection.password = xxx

But in catalina.out I found nothing about read replicas. Here is the complete catalina.out

NOTE: Picked up JDK_JAVA_OPTIONS:  --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED
<4>[SetAllPropertiesRule]{Server/Service/Connector} Setting property 'workerFile' to '/home/dhis/tomcat-dhis/conf/worker.properties' did not find a matching property.
<4>[SetAllPropertiesRule]{Server/Service/Connector} Setting property 'workerName' to 'dhis2' did not find a matching property.
<6>Server version name:   Apache Tomcat/9.0.31 (Ubuntu)
<6>Server built:          Nov 18 2022 19:07:15 UTC
<6>Server version number: 9.0.31.0
<6>OS Name:               Linux
<6>OS Version:            5.15.30-2-pve
<6>Architecture:          amd64
<6>Java Home:             /usr/lib/jvm/java-11-openjdk-amd64
<6>JVM Version:           11.0.17+8-post-Ubuntu-1ubuntu220.04
<6>JVM Vendor:            Ubuntu
<6>CATALINA_BASE:         /home/dhis/tomcat-dhis
<6>CATALINA_HOME:         /usr/share/tomcat9
<6>Command line argument: --add-opens=java.base/java.lang=ALL-UNNAMED
<6>Command line argument: --add-opens=java.base/java.io=ALL-UNNAMED
<6>Command line argument: --add-opens=java.rmi/sun.rmi.transport=ALL-UNNAMED
<6>Command line argument: -Djava.util.logging.config.file=/home/dhis/tomcat-dhis/conf/logging.properties
<6>Command line argument: -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager
<6>Command line argument: -Xms2000m
<6>Command line argument: -Xmx4000m
<6>Command line argument: -Djdk.tls.ephemeralDHKeySize=2048
<6>Command line argument: -Djava.protocol.handler.pkgs=org.apache.catalina.webresources
<6>Command line argument: -Dorg.apache.catalina.security.SecurityListener.UMASK=0027
<6>Command line argument: -Dignore.endorsed.dirs=
<6>Command line argument: -Dcatalina.base=/home/dhis/tomcat-dhis
<6>Command line argument: -Dcatalina.home=/usr/share/tomcat9
<6>Command line argument: -Djava.io.tmpdir=/home/dhis/tomcat-dhis/temp
<6>The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: [/usr/java/packages/lib:/usr/lib/x86_64-linux-gnu/jni:/lib/x86_64-linux-gnu:/usr/lib/x86_64-linux-gnu:/usr/lib/jni:/lib:/usr/lib]
<6>Initializing ProtocolHandler ["http-nio-8080"]
<6>Initializing ProtocolHandler ["ajp-nio-127.0.0.1-8009"]
<6>Server initialization in [573] milliseconds
<6>Starting service [Catalina]
<6>Starting Servlet engine: [Apache Tomcat/9.0.31 (Ubuntu)]
<6>Deploying web application archive [/home/dhis/tomcat-dhis/webapps/ROOT.war]
<6>At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
* INFO  2023-03-14T20:11:52,325 System property dhis2.home not set (LogOnceLogger.java [main])
* INFO  2023-03-14T20:11:52,335 Environment variable DHIS2_HOME points to /home/dhis/config (LogOnceLogger.java [main])
* INFO  2023-03-14T20:11:52,365 File /home/dhis/config/dhis-google-auth.json does not exist (LogOnceLogger.java [main])
* INFO  2023-03-14T20:11:52,366 Could not find dhis-google-auth.json (LogOnceLogger.java [main])
* INFO  2023-03-14T20:11:54,977 Monitoring metric for key monitoring.hibernate.enabled is disabled (MetricsEnabler.java [main])
* INFO  2023-03-14T20:11:54,979 Monitoring metric for key monitoring.dbpool.enabled is disabled (MetricsEnabler.java [main])
* INFO  2023-03-14T20:11:54,981 Monitoring metric for key monitoring.cpu.enabled is disabled (MetricsEnabler.java [main])
* INFO  2023-03-14T20:11:54,983 Monitoring metric for key monitoring.uptime.enabled is disabled (MetricsEnabler.java [main])
* INFO  2023-03-14T20:11:54,986 Monitoring metric for key monitoring.jvm.enabled is disabled (MetricsEnabler.java [main])
* INFO  2023-03-14T20:11:55,363 Monitoring metric for key monitoring.api.enabled is disabled (MetricsEnabler.java [main])
* INFO  2023-03-14T20:11:59,785 Hibernate configuration loaded: dialect: 'org.hisp.dhis.hibernate.dialect.DhisPostgresDialect', region factory: 'org.hibernate.cache.ehcache.EhCacheRegionFactory', connection pool max size: null (DefaultHibernateConfigurationProvider.java [main])
* INFO  2023-03-14T20:12:01,479 Local Cache (forced) instance created for region:'userIdCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:01,610 Local Cache (forced) instance created for region:'currentUserGroupInfoCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:03,575 Simple Local Cache instance created for region:'periodCache' (SimpleCacheBuilder.java [main])
* WARN  2023-03-14T20:12:04,800 HHH90001007: Using legacy cache name [org.hibernate.cache.spi.UpdateTimestampsCache] because configuration could not be found for cache [default-update-timestamps-region]. Update your configuration to rename cache [org.hibernate.cache.spi.UpdateTimestampsCache] to [default-update-timestamps-region]. (EhcacheRegionFactory.java [main])
* WARN  2023-03-14T20:12:04,803 HHH90001007: Using legacy cache name [org.hibernate.cache.internal.StandardQueryCache] because configuration could not be found for cache [default-query-results-region]. Update your configuration to rename cache [org.hibernate.cache.internal.StandardQueryCache] to [default-query-results-region]. (EhcacheRegionFactory.java [main])
* INFO  2023-03-14T20:12:11,594 Local Cache (forced) instance created for region:'userGroupNameCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:11,740 Local Cache instance created for region:'systemSetting' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:11,755 Local Cache (forced) instance created for region:'userDisplayNameCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:11,769 Local Cache instance created for region:'userSetting' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:12,293 Local Cache (forced) instance created for region:'periodIdCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:13,579 Local Cache (forced) instance created for region:'defaultObjectCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:16,067 File store configured with provider: 'filesystem', container: 'files' and location: ''. (JCloudsFileResourceContentStore.java [main])
* INFO  2023-03-14T20:12:16,117 Local Cache instance created for region:'catOptOrgUnitAssocCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:16,139 Local Cache instance created for region:'pgmOrgUnitAssocCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:16,424 Local Cache (forced) instance created for region:'inUserOuHierarchy' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:16,426 Local Cache (forced) instance created for region:'inUserSearchOuHierarchy' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:16,427 Local Cache (forced) instance created for region:'userCaptureOuCountThreshold' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:16,428 Local Cache (forced) instance created for region:'isUserViewOuHierHierarchy' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:16,503 Local Cache (forced) instance created for region:'programRuleVariablesCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:16,577 Local Cache (forced) instance created for region:'programHasRulesCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:16,634 Local Cache instance created for region:'programOwner' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:16,635 Local Cache instance created for region:'programTempOwner' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:17,235 Local Cache (forced) instance created for region:'allConstantsCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:17,319 Local Cache (forced) instance created for region:'analyticsSql' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:17,750 Local Cache instance created for region:'analyticsResponse' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:17,752 Analytics server-side cache is enabled with expiration time: 60 s (AnalyticsCache.java [main])
* INFO  2023-03-14T20:12:18,084 Local Cache (forced) instance created for region:'canDataWriteCocCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:18,097 Local Cache (forced) instance created for region:'attrOptionComboIdCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:18,177 Local Cache instance created for region:'isDataApproved' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:18,402 Local Cache (forced) instance created for region:'metadataAttributes' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:18,454 Local Cache (forced) instance created for region:'programWebHookNotificationTemplateCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:18,455 Local Cache (forced) instance created for region:'programStageWebHookNotificationTemplateCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:18,541 Initialized NoOp leader manager with node UUID: 'fe21341d-e66f-47b8-a385-745ef3556c12' and node ID: '' (NoOpLeaderManager.java [main])
* INFO  2023-03-14T20:12:18,545 Local Cache instance created for region:'runningJobsInfo' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:18,546 Local Cache instance created for region:'completedJobsInfo' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:18,548 Local Cache instance created for region:'jobCancelRequested' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:18,707 DHIS 2 Version: 2.37.9, Revision: c829b69, Build date: Thu Dec 22 12:22:27 BDT 2022, Database name: dhis2, Java version: 11.0.17 (DefaultSystemService.java [main])
* INFO  2023-03-14T20:12:18,712 Scheduled monitoring service (DefaultMonitoringService.java [main])
* INFO  2023-03-14T20:12:19,011 Local Cache (forced) instance created for region:'propertyTransformerCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:19,357 Initializing Log4j, max file size: '100MB', max file archives: 1 (Log4JLogConfigInitializer.java [main])
* INFO  2023-03-14T20:12:19,383 Added logger: org.hisp.dhis.resourcetable using file: /home/dhis/config/logs/dhis-analytics-table.log (Log4JLogConfigInitializer.java [main])
* INFO  2023-03-14T20:12:19,383 Added logger: org.hisp.dhis.analytics.table using file: /home/dhis/config/logs/dhis-analytics-table.log (Log4JLogConfigInitializer.java [main])
* INFO  2023-03-14T20:12:19,385 Added logger: org.hisp.dhis.dxf2 using file: /home/dhis/config/logs/dhis-data-exchange.log (Log4JLogConfigInitializer.java [main])
* INFO  2023-03-14T20:12:19,386 Added logger: org.hisp.dhis.dxf2.sync using file: /home/dhis/config/logs/dhis-data-sync.log (Log4JLogConfigInitializer.java [main])
* INFO  2023-03-14T20:12:19,387 Added logger: org.hisp.dhis.dxf2.metadata using file: /home/dhis/config/logs/dhis-metadata-sync.log (Log4JLogConfigInitializer.java [main])
* INFO  2023-03-14T20:12:19,387 Added logger: org.hisp.dhis.pushanalysis using file: /home/dhis/config/logs/dhis-push-analysis.log (Log4JLogConfigInitializer.java [main])
* INFO  2023-03-14T20:12:19,396 Added logger: org.hisp.dhis.audit using file: /home/dhis/config/logs/dhis-audit.log (Log4JLogConfigInitializer.java [main])
* INFO  2023-03-14T20:12:19,397 Added root logger using file: /home/dhis/config/logs/dhis.log (Log4JLogConfigInitializer.java [main])
* INFO  2023-03-14T20:12:19,838 Local Cache instance created for region:'userFailedLoginAttempt' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:19,839 Local Cache instance created for region:'userAccountRecoverAttempt' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:20,403 File store configured with provider: 'filesystem', container: 'files' and location: ''. (JCloudsAppStorageService.java [main])
* INFO  2023-03-14T20:12:20,405 Local Cache instance created for region:'appCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:20,406 Old apps folder does not exist, stopping discovery (LocalAppStorageService.java [main])
* INFO  2023-03-14T20:12:20,408 Starting JClouds discovery (JCloudsAppStorageService.java [main])
* INFO  2023-03-14T20:12:20,486 Found potential app: apps/cod-app-modified/ (JCloudsAppStorageService.java [main])
* INFO  2023-03-14T20:12:20,518 Found potential app: apps/cod-app/ (JCloudsAppStorageService.java [main])
* INFO  2023-03-14T20:12:20,521 Discovered app 'Cause of Death' from JClouds storage  (JCloudsAppStorageService.java [main])
* INFO  2023-03-14T20:12:20,521 Discovered app 'Cause of Death' from JClouds storage  (JCloudsAppStorageService.java [main])
* INFO  2023-03-14T20:12:20,746 Following listeners are registered: [org.hisp.dhis.sms.listener.AggregateDataSetSMSListener@39423f50, org.hisp.dhis.sms.listener.SimpleEventSMSListener@5f3ab3c0, org.hisp.dhis.sms.listener.TrackerEventSMSListener@3c3e501c, org.hisp.dhis.sms.listener.UnregisteredSMSListener@754047e2, org.hisp.dhis.sms.listener.SingleEventListener@3dc53ae0, org.hisp.dhis.sms.listener.DeleteEventSMSListener@184b4f35, org.hisp.dhis.sms.listener.RelationshipSMSListener@15b2a1e, org.hisp.dhis.sms.listener.DataValueSMSListener@33ab3383, org.hisp.dhis.sms.listener.EnrollmentSMSListener@5f0ffb8e, org.hisp.dhis.sms.listener.ProgramStageDataEntrySMSListener@300e728e, org.hisp.dhis.sms.listener.TrackedEntityRegistrationSMSListener@24c67975, org.hisp.dhis.sms.listener.J2MEDataValueSMSListener@516757b7, org.hisp.dhis.sms.listener.DhisMessageAlertListener@475baac1] (SmsConsumerThread.java [main])
* INFO  2023-03-14T20:12:20,756 SMS consumer started (DefaultSmsConsumerService.java [main])
* INFO  2023-03-14T20:12:20,977 Starting embedded Artemis ActiveMQ server. (ArtemisManager.java [main])
* INFO  2023-03-14T20:12:22,027 Local Cache instance created for region:'dataIntegritySummaryCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:22,028 Local Cache instance created for region:'dataIntegrityDetailsCache' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:22,994 Local Cache instance created for region:'googleAccessToken' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:23,092 Local Cache (forced) instance created for region:'dataItemsPagination' (ExtendedCacheBuilder.java [main])
* INFO  2023-03-14T20:12:23,380 Session timeout set to 3600 seconds (DefaultAuthenticationSuccessHandler.java [main])
* WARN  2023-03-14T20:12:24,234 You are asking Spring Security to ignore Ant [pattern='/api/staticContent/**']. This is not recommended -- please use permitAll via HttpSecurity#authorizeHttpRequests instead. (WebSecurity.java [main])
* WARN  2023-03-14T20:12:24,236 You are asking Spring Security to ignore Ant [pattern='/dhis-web-commons/oidc/**']. This is not recommended -- please use permitAll via HttpSecurity#authorizeHttpRequests instead. (WebSecurity.java [main])
* WARN  2023-03-14T20:12:24,236 You are asking Spring Security to ignore Ant [pattern='/dhis-web-commons/javascripts/**']. This is not recommended -- please use permitAll via HttpSecurity#authorizeHttpRequests instead. (WebSecurity.java [main])
* WARN  2023-03-14T20:12:24,236 You are asking Spring Security to ignore Ant [pattern='/dhis-web-commons/css/**']. This is not recommended -- please use permitAll via HttpSecurity#authorizeHttpRequests instead. (WebSecurity.java [main])
* WARN  2023-03-14T20:12:24,236 You are asking Spring Security to ignore Ant [pattern='/dhis-web-commons/flags/**']. This is not recommended -- please use permitAll via HttpSecurity#authorizeHttpRequests instead. (WebSecurity.java [main])
* WARN  2023-03-14T20:12:24,237 You are asking Spring Security to ignore Ant [pattern='/dhis-web-commons/fonts/**']. This is not recommended -- please use permitAll via HttpSecurity#authorizeHttpRequests instead. (WebSecurity.java [main])
* WARN  2023-03-14T20:12:24,237 You are asking Spring Security to ignore Ant [pattern='/api/files/style/external']. This is not recommended -- please use permitAll via HttpSecurity#authorizeHttpRequests instead. (WebSecurity.java [main])
* WARN  2023-03-14T20:12:24,237 You are asking Spring Security to ignore Ant [pattern='/external-static/**']. This is not recommended -- please use permitAll via HttpSecurity#authorizeHttpRequests instead. (WebSecurity.java [main])
* WARN  2023-03-14T20:12:24,237 You are asking Spring Security to ignore Ant [pattern='/favicon.ico']. This is not recommended -- please use permitAll via HttpSecurity#authorizeHttpRequests instead. (WebSecurity.java [main])
* INFO  2023-03-14T20:12:26,834 Gateway configuration found: [SmsGatewayConfig{uid='SAOMIepk1P', name='Test SMS', username='null', isDefault=true, urlTemplate='https://bulksmsbd.net/api/smsapi'}] (DefaultGatewayAdministrationService.java [main])
* INFO  2023-03-14T20:12:26,841 Executing startup routine [1 of 9, runlevel 2]: defaultAdminUserPopulator (DefaultStartupRoutineExecutor.java [main])
* INFO  2023-03-14T20:12:26,848 Executing startup routine [2 of 9, runlevel 3]: PeriodTypePopulator (DefaultStartupRoutineExecutor.java [main])
* INFO  2023-03-14T20:12:26,863 Executing startup routine [3 of 9, runlevel 3]: TwoFAPopulator (DefaultStartupRoutineExecutor.java [main])
* WARN  2023-03-14T20:12:26,866 Cannot get users with disjoint roles as user does not have any user roles (DefaultUserService.java [main])
* INFO  2023-03-14T20:12:26,868 Executing startup routine [4 of 9, runlevel 4]: DataElementDefaultDimensionPopulator (DefaultStartupRoutineExecutor.java [main])
* INFO  2023-03-14T20:12:27,184 Executing startup routine [5 of 9, runlevel 7]: ModelUpgrader (DefaultStartupRoutineExecutor.java [main])
* INFO  2023-03-14T20:12:27,202 Executing startup routine [6 of 9, runlevel 12]: ConfigurationPopulator (DefaultStartupRoutineExecutor.java [main])
* WARN  2023-03-14T20:12:27,204 Encryption not configured: Missing encryption.password in dhis.conf (ConfigurationPopulator.java [main])
* INFO  2023-03-14T20:12:27,206 Executing startup routine [7 of 9, runlevel 13]: I18nLocalePopulator (DefaultStartupRoutineExecutor.java [main])
* INFO  2023-03-14T20:12:27,220 Executing startup routine [8 of 9, runlevel 14]: SettingUpgrader (DefaultStartupRoutineExecutor.java [main])
* INFO  2023-03-14T20:12:27,223 Executing startup routine [9 of 9, runlevel 15]: SchedulerStart (DefaultStartupRoutineExecutor.java [main])
* INFO  2023-03-14T20:12:27,252 Setting up default jobs. (SchedulerStart.java [main])
* INFO  2023-03-14T20:12:27,293 Scheduling job: JobConfiguration{uid='TVPnxGwokzA', name='Cause of Death', jobType=ANALYTICS_TABLE, cronExpression='0 0 * ? * *', delay='null', jobParameters=org.hisp.dhis.scheduling.parameters.jackson.AnalyticsJobParametersDeserializer$CustomJobParameters@63d28e2a, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:10.024', userUid='null', leaderOnlyJob=false, jobStatus=SCHEDULED, nextExecutionTime=Tue Mar 14 21:00:00 BDT 2023, lastExecutedStatus=FAILED, lastExecuted=2023-03-14 20:00:10.029} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,296 Scheduled job: JobConfiguration{uid='TVPnxGwokzA', name='Cause of Death', jobType=ANALYTICS_TABLE, cronExpression='0 0 * ? * *', delay='null', jobParameters=org.hisp.dhis.scheduling.parameters.jackson.AnalyticsJobParametersDeserializer$CustomJobParameters@63d28e2a, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:10.024', userUid='null', leaderOnlyJob=false, jobStatus=SCHEDULED, nextExecutionTime=Tue Mar 14 21:00:00 BDT 2023, lastExecutedStatus=FAILED, lastExecuted=2023-03-14 20:00:10.029} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,323 Scheduling job: JobConfiguration{uid='wwFI7EstaCn', name='Cause of Death Resource Table', jobType=RESOURCE_TABLE, cronExpression='0 0 * ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:20.743', userUid='null', leaderOnlyJob=false, jobStatus=SCHEDULED, nextExecutionTime=Tue Mar 14 21:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 20:00:20.748} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,324 Scheduled job: JobConfiguration{uid='wwFI7EstaCn', name='Cause of Death Resource Table', jobType=RESOURCE_TABLE, cronExpression='0 0 * ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:20.743', userUid='null', leaderOnlyJob=false, jobStatus=SCHEDULED, nextExecutionTime=Tue Mar 14 21:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 20:00:20.748} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,357 Scheduling job: JobConfiguration{uid='Js3vHn2AVuG', name='Validation result notification', jobType=VALIDATION_RESULTS_NOTIFICATION, cronExpression='0 0 7 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.044', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 07:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 07:00:00.092} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,358 Scheduled job: JobConfiguration{uid='Js3vHn2AVuG', name='Validation result notification', jobType=VALIDATION_RESULTS_NOTIFICATION, cronExpression='0 0 7 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.044', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 07:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 07:00:00.092} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,389 Scheduling job: JobConfiguration{uid='fUWM1At1TUx', name='User account expiry alert', jobType=ACCOUNT_EXPIRY_ALERT, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.188', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.258} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,391 Scheduled job: JobConfiguration{uid='fUWM1At1TUx', name='User account expiry alert', jobType=ACCOUNT_EXPIRY_ALERT, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.188', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.258} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,424 Scheduling job: JobConfiguration{uid='YvAwAmrqAtN', name='Dataset notification', jobType=DATA_SET_NOTIFICATION, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.185', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.255} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,425 Scheduled job: JobConfiguration{uid='YvAwAmrqAtN', name='Dataset notification', jobType=DATA_SET_NOTIFICATION, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.185', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.255} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,459 Scheduling job: JobConfiguration{uid='BFa3jDsbtdO', name='Data statistics', jobType=DATA_STATISTICS, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.495', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.585} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,461 Scheduled job: JobConfiguration{uid='BFa3jDsbtdO', name='Data statistics', jobType=DATA_STATISTICS, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.495', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.585} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,494 Scheduling job: JobConfiguration{uid='sHMedQF7VYa', name='Credentials expiry alert', jobType=CREDENTIALS_EXPIRY_ALERT, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.366', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.578} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,495 Scheduled job: JobConfiguration{uid='sHMedQF7VYa', name='Credentials expiry alert', jobType=CREDENTIALS_EXPIRY_ALERT, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.366', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.578} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,528 Scheduling job: JobConfiguration{uid='uwWCT2BMmlq', name='Remove expired or used reserved values', jobType=REMOVE_USED_OR_EXPIRED_RESERVED_VALUES, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.639', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.71} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,529 Scheduled job: JobConfiguration{uid='uwWCT2BMmlq', name='Remove expired or used reserved values', jobType=REMOVE_USED_OR_EXPIRED_RESERVED_VALUES, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.639', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.71} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,560 Scheduling job: JobConfiguration{uid='pd6O228pqr0', name='File resource clean up', jobType=FILE_RESOURCE_CLEANUP, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.379', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.738} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,561 Scheduled job: JobConfiguration{uid='pd6O228pqr0', name='File resource clean up', jobType=FILE_RESOURCE_CLEANUP, cronExpression='0 0 2 ? * *', delay='null', jobParameters=null, enabled=true, inMemoryJob=false, lastRuntimeExecution='00:00:00.379', userUid='null', leaderOnlyJob=true, jobStatus=SCHEDULED, nextExecutionTime=Wed Mar 15 02:00:00 BDT 2023, lastExecutedStatus=COMPLETED, lastExecuted=2023-03-14 02:00:00.738} (DefaultSchedulingManager.java [main])
* INFO  2023-03-14T20:12:27,664 All startup routines done (DefaultStartupRoutineExecutor.java [main])
<6>Deployment of web application archive [/home/dhis/tomcat-dhis/webapps/ROOT.war] has finished in [53,381] ms
<6>Starting ProtocolHandler ["http-nio-8080"]
<6>Starting ProtocolHandler ["ajp-nio-127.0.0.1-8009"]
<6>Server startup in [53,502] milliseconds

I also tried without

active.read.replicas=2

Result was the same.

And sorry for my delayed response.

@alanivey could you please look into the log and direct me to the right path. I’m still stuck here. And I didn’t find anything you mentioned in the log.

HI Dr Riazul,

Please confirm that you have allowed your replicas to connect to the databases to which they are pointing by editing the pg_hba.conf file on each replicated database engine. If possible, please share a snippet of each pg_hba.conf file.

You will also need to ensure the dhis user and related credentials exist on each database server.

1 Like

@riaz.somc I tried pgpool works well with large database. Give it a try.

2 Likes

Tried Patroni, etcd, HA and postgresql in test environment. It is working properly. I will try to test in a production environment soon.

1 Like