# DHIS2 Analytics taking too much time to load **URL:** \ **Category:** Implementation - Implémentation\ **Tags:** analytics\ **Created:** [12 August 2021 10:15 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919 "2021-08-12T10:15:40Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Honour](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/honour/32/32799_2.png) [@Honour](https://community.dhis2.org/u/Honour)\ **Post date:** [12 August 2021 10:15 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/1 "2021-08-12T10:15:41Z")
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 ---
**Author:** ![Gassim](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/gassim/32/35347_2.png) [@Gassim](https://community.dhis2.org/u/Gassim)\ **Post date:** [13 August 2021 21:32 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/2 "2021-08-13T21:32:08Z")
Hey @Honour, welcome to the community! May I ask you what is the version of the DHIS2 instance that you are using? (: Thank you! ---
**Author:** ![Honour](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/honour/32/32799_2.png) [@Honour](https://community.dhis2.org/u/Honour)\ **Post date:** [16 August 2021 16:22 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/3 "2021-08-16T16:22:29Z")
Hi @Gassim, thank you for the assistance. We are using DHIS2 version 2.35.2 ---
**Author:** ![Gintare](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/gintare/32/33758_2.png) [@Gintare](https://community.dhis2.org/u/Gintare)\ **Post date:** [18 August 2021 09:01 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/4 "2021-08-18T09:01:58Z")
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)? ---
**Author:** ![Honour](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/honour/32/32799_2.png) [@Honour](https://community.dhis2.org/u/Honour)\ **Post date:** [18 August 2021 09:09 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/5 "2021-08-18T09:09:18Z")
> [@Gintare](#): > > the time it takes to run the analytics process 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 ---
**Author:** ![Gintare](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/gintare/32/33758_2.png) [@Gintare](https://community.dhis2.org/u/Gintare)\ **Post date:** [18 August 2021 10:40 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/6 "2021-08-18T10:40:24Z")
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. ---
**Author:** ![taufiqhs](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/t/8797f3/32.png) [@taufiqhs](https://community.dhis2.org/u/taufiqhs)\ **Post date:** [19 August 2021 22:44 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/7 "2021-08-19T22:44:10Z")
I have the same issue here. I am using PG 13 ![Screen Shot 2021-08-20 at 05.43.35](https://dhis2.b-cdn.net/uploads/default/original/3X/5/8/581873b38f5ef48b442b13a74847d0fdab81b2ec.png) ---
**Author:** ![taufiqhs](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/t/8797f3/32.png) [@taufiqhs](https://community.dhis2.org/u/taufiqhs)\ **Post date:** [20 August 2021 01:29 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/8 "2021-08-20T01:29:54Z")
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](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](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 ---
**Author:** ![Honour](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/honour/32/32799_2.png) [@Honour](https://community.dhis2.org/u/Honour)\ **Post date:** [20 August 2021 02:20 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/9 "2021-08-20T02:20:23Z")
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](https://dhis2.b-cdn.net/uploads/default/original/3X/4/f/4fb1d1595406e1a973a9c7dbdefe004392c1e3ea.png) ![disk io 0330 image 2](https://dhis2.b-cdn.net/uploads/default/original/3X/7/1/71f9d119809b9aab34ea31b162bbc12d802bf8be.png) 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 | ---
**Author:** ![Honour](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/honour/32/32799_2.png) [@Honour](https://community.dhis2.org/u/Honour)\ **Post date:** [20 August 2021 02:24 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/10 "2021-08-20T02:24:41Z")
Thanks for the suggestion @taufiqhs. Will this not have the same impact on the DB I/O performance? ---
**Author:** ![taufiqhs](https://dhis2.b-cdn.net/letter_avatar_proxy/v4/letter/t/8797f3/32.png) [@taufiqhs](https://community.dhis2.org/u/taufiqhs)\ **Post date:** [20 August 2021 08:13 UTC](https://community.dhis2.org/t/dhis2-analytics-taking-too-much-time-to-load/43919/11 "2021-08-20T08:13:07Z")
Hi @Honour Not sure, because duration of analytic is very short rather then using replication setting. Taufiq