DHIS2 Analytics taking too much time to load

Honour · 12 August 2021 10:15

Dear experts

I would like to ask for asistance with a DHIS analytics problem we are facing.
We have recently seen a growth in the volume of data being fed to the DHIS2 system so we are now experiencing slowness in the time the analytics server processes querries. I set up 3 read-only database replicas to complement the existing Postgres DB but I am not seeing an improvement in the time it takes the Analytic engine to process queries.

It used to take one 1hour for the Analytics to process queries but now it takes about 8hrs or so. I fine-tuned the Postgres master and replica DBs in accordance with the DHIS manual. How best can I troubleshoot this slowness issue.

Thanks in advance.

Honour

Gassim · 13 August 2021 21:32

Hey @Honour, welcome to the community!

May I ask you what is the version of the DHIS2 instance that you are using? (:

Thank you!

Honour · 16 August 2021 16:22

Hi @Gassim, thank you for the assistance. We are using DHIS2 version 2.35.2

Gintare · 18 August 2021 09:01

Hey @Honour,

When you say “time to process queries”, do you mean the API calls to /analytics (loading visualizations) or the time it takes to run the analytics process ( from data administration or scheduler apps)?

Honour · 18 August 2021 09:09

Hey @Gintare ,

I mean the time it takes to run the analytics process. below is a snippet of the processes I am referring to. It will take several hours at the “Populating analytics tables” stage.

|2021-08-10 23:03:11|Updating resource tables |
|2021-08-10 23:04:16|Updating tables: DATA_VALUE |
|2021-08-10 23:04:16|Performing pre-create table work |
|2021-08-10 23:04:16|Dropping temp tables |
|2021-08-10 23:04:16|Creating analytics tables |
|2021-08-10 23:04:16|Populating analytics tables |
|2021-08-10 23:04:16|Invoking analytics table hooks |
|2021-08-10 23:04:16|Applying aggregation levels |
|2021-08-10 23:04:16|Creating indexes |
|2021-08-10 23:04:17|Analyzing analytics tables |
|2021-08-10 23:04:17|Removing updated and deleted data |
|2021-08-10 23:04:17|Swapping analytics tables |
|2021-08-10 23:04:17|Table update done |
|2021-08-10 23:04:17|Updating tables: COMPLETENESS |
|2021-08-10 23:04:17|No complete registrations exist, not updating completeness analytics tables |
|2021-08-10 23:04:17|Updating tables: COMPLETENESS_TARGET |
|2021-08-10 23:04:17|Performing pre-create table work |
|2021-08-10 23:04:17|Dropping temp tables |
|2021-08-10 23:04:17|Creating analytics tables |
|2021-08-10 23:04:17|Populating analytics tables |
|2021-08-10 23:04:17|Invoking analytics table hooks |
|2021-08-10 23:04:17|Applying aggregation levels |
|2021-08-10 23:04:17|Creating indexes |
|2021-08-10 23:04:20|Analyzing analytics tables |
|2021-08-10 23:04:20|Removing updated and deleted data |
|2021-08-10 23:04:20|Swapping analytics tables |
|2021-08-10 23:04:20|Table update done |
|2021-08-10 23:04:20|Updating tables: ORG_UNIT_TARGET |
|2021-08-10 23:04:20|Performing pre-create table work |
|2021-08-10 23:04:20|Dropping temp tables |
|2021-08-10 23:04:20|Creating analytics tables |
|2021-08-10 23:04:20|Populating analytics tables |
|2021-08-10 23:04:20|Invoking analytics table hooks |
|2021-08-10 23:04:20|Applying aggregation levels |
|2021-08-10 23:04:20|Creating indexes |
|2021-08-10 23:04:20|Analyzing analytics tables |
|2021-08-10 23:04:21|Removing updated and deleted data |
|2021-08-10 23:04:21|Swapping analytics tables |
|2021-08-10 23:04:21|Table update done |
|2021-08-10 23:04:21|Updating tables: EVENT |
|2021-08-10 23:23:01|Performing pre-create table work |
|2021-08-10 23:23:01|Dropping temp tables |
|2021-08-10 23:23:03|Creating analytics tables |
|2021-08-10 23:23:05|Populating analytics tables|

Regards

Honour

Gintare · 18 August 2021 10:40

Thanks for clarifying. Which postgres version are you using? We have seen reports that suggest that PG13 works best for analytics. Do you have any monitoring tools installed on the server? Would be interesting to see the server performance, test the disk speed during the analytics run. You could try to increase shared_buffers pg setting and “keyDatabaseServerCpus” systemSetting (/api/systemSettings).

Tagging @bobj and @Lars for their input as well.

taufiqhs · 19 August 2021 22:44

I have the same issue here. I am using PG 13

taufiqhs · 20 August 2021 01:29

Hi @Honour

there is a problem-related database replication during the analytic engine here. I have the solution and you can try it.

Create New Tomcat and pointed to DB Master only (in dhis.conf), without read db.
Run analytic using terminal or postman (hit the run analytic endpoint).
my case using this endpoint on postman

https://new-server-for-analytic-only/api/resourceTables/analytics?skipResourceTables=true&skipEvents=false&skipEnrollment=false&lastYears=3

You can check the result from here:

https://new-server-for-analytic-only/api/system/tasks/ANALYTICS_TABLE

and you must check regularly until step # 59 and

completed: true

After finish. you can run manually from your new tomcat server, or create CRONJOB inside that server.
Don’t run again from your old Tomcat, you old tomcat only for users not for analytic.

My case is 14GB data and takes +/- 40 minutes to complete.

Taufiq

Honour · 20 August 2021 02:20

We are running Postgres version 12. I have increased the shared_buffers parameter from 3GB to 16GB but there isn’t much change.
“keyDatabaseServerCpus” is currently set to “0” does this translate to "unlimited’’ ? .

I have attached images showing the disk throughput
. db disk io 20 aug

disk io 0330 image 2

Today’s logs

2021-08-20 01:19:54	Analytics table update process started
2021-08-20 01:19:54	Updating resource tables
2021-08-20 01:20:59	Updating tables: DATA_VALUE
2021-08-20 01:20:59	Performing pre-create table work
2021-08-20 01:20:59	Dropping temp tables
2021-08-20 01:20:59	Creating analytics tables
2021-08-20 01:20:59	Populating analytics tables
2021-08-20 01:20:59	Invoking analytics table hooks
2021-08-20 01:20:59	Applying aggregation levels
2021-08-20 01:20:59	Creating indexes
2021-08-20 01:20:59	Analyzing analytics tables
2021-08-20 01:20:59	Removing updated and deleted data
2021-08-20 01:20:59	Swapping analytics tables
2021-08-20 01:21:00	Table update done
2021-08-20 01:21:00	Updating tables: COMPLETENESS
2021-08-20 01:21:00	No complete registrations exist, not updating completeness analytics tables
2021-08-20 01:21:00	Updating tables: COMPLETENESS_TARGET
2021-08-20 01:21:00	Performing pre-create table work
2021-08-20 01:21:00	Dropping temp tables
2021-08-20 01:21:00	Creating analytics tables
2021-08-20 01:21:00	Populating analytics tables
2021-08-20 01:21:00	Invoking analytics table hooks
2021-08-20 01:21:00	Applying aggregation levels
2021-08-20 01:21:00	Creating indexes
2021-08-20 01:21:00	Analyzing analytics tables
2021-08-20 01:21:01	Removing updated and deleted data
2021-08-20 01:21:01	Swapping analytics tables
2021-08-20 01:21:01	Table update done
2021-08-20 01:21:01	Updating tables: ORG_UNIT_TARGET
2021-08-20 01:21:01	Performing pre-create table work
2021-08-20 01:21:01	Dropping temp tables
2021-08-20 01:21:01	Creating analytics tables
2021-08-20 01:21:01	Populating analytics tables
2021-08-20 01:21:01	Invoking analytics table hooks
2021-08-20 01:21:01	Applying aggregation levels
2021-08-20 01:21:01	Creating indexes
2021-08-20 01:21:01	Analyzing analytics tables
2021-08-20 01:21:01	Removing updated and deleted data
2021-08-20 01:21:01	Swapping analytics tables
2021-08-20 01:21:01	Table update done
2021-08-20 01:21:01	Updating tables: EVENT
2021-08-20 01:33:12	Performing pre-create table work
2021-08-20 01:33:12	Dropping temp tables
2021-08-20 01:33:12	Creating analytics tables
2021-08-20 01:33:13	Populating analytics tables
2021-08-20 03:19:26	Invoking analytics table hooks
2021-08-20 03:19:26	Applying aggregation levels
2021-08-20 03:19:26	Creating indexes
2021-08-20 03:58:13	Analyzing analytics tables
2021-08-20 04:00:22	Removing updated and deleted data
2021-08-20 04:00:22	Swapping analytics tables
2021-08-20 04:00:40	Table update done
2021-08-20 04:00:40	Updating tables: VALIDATION_RESULT
2021-08-20 04:00:40	No validation results exist, not updating validation result analytics tables
2021-08-20 04:00:40	Updating tables: ENROLLMENT
2021-08-20 04:00:40	Performing pre-create table work
2021-08-20 04:00:40	Dropping temp tables
2021-08-20 04:00:41	Creating analytics tables
2021-08-20 04:00:41	Populating analytics tables
2021-08-20 04:09:49	Invoking analytics table hooks
2021-08-20 04:09:49	Applying aggregation levels
2021-08-20 04:09:49	Creating indexes
2021-08-20 04:12:24	Analyzing analytics tables
2021-08-20 04:12:33	Removing updated and deleted data
2021-08-20 04:12:33	Swapping analytics tables
2021-08-20 04:12:36	Table update done
2021-08-20 04:12:36	Analytics tables updated: 02:52:41.806

Honour · 20 August 2021 02:24

Thanks for the suggestion @taufiqhs. Will this not have the same impact on the DB I/O performance?

taufiqhs · 20 August 2021 08:13

Hi @Honour

Not sure, because duration of analytic is very short rather then using replication setting.

Taufiq