dhis2-users team mailing list archive

[Calle Hedberg <calle.hedberg@xxxxxxxxx>]

Hannan,

Out of interest: how do you measure your DB size? "127GB" does not really
say much - is it with or without analytics tables etc. Are you just using
pg_database_size?

I just ran that on my local instance of the South African "national" HMIS
instance - it is 202 GB (NOTE: that includes analytics), and running
analytics takes around 1-1.5 hours. So 12-13 hours on your side seems
excessive (I presume you are using either high-speed RAID rust platters or
else SSD).

Regards
calle



On 30 April 2018 at 19:24, Hannan Khan <hannank@xxxxxxxxx> wrote:

> Thanks Bob.
>
> I remembered that. 1st step I takes was upgrade postgres to 9.6 and tuning
> parameters which reduced time from 58 to 13 hour. And after that I
> removed/update the data types of the aggregated data set what was
> changed/amended that reduce the time to 8 hour.
>
> 1. this time it is gradually increased from 8 hour to 2/13 hour now. This
> time it seems that tracker contribute to the delays.
>
> * INFO  2018-04-30 03:24:56,323 Populated table in 8717.87 seconds:
> analytics_enrollment_temp_xggeyhhpmkp (AbstractJdbcTableManager.java
> [taskScheduler-25])
> * INFO  2018-04-30 04:01:53,261 Populated table in 10919.99 seconds:
> analytics_enrollment_temp_knjmf7ttlad (AbstractJdbcTableManager.java
> [taskScheduler-9])
> * INFO  2018-04-30 07:03:46,687 Populated table in 10913.42 seconds:
> analytics_enrollment_temp_fokn0tj7lmi (AbstractJdbcTableManager.java
> [taskScheduler-9])
> * INFO  2018-04-30 08:18:49,534 Populated table in 26237.16 seconds:
> analytics_enrollment_temp_oo3ormkzucj (AbstractJdbcTableManager.java
> [taskScheduler-22])
> * INFO  2018-04-30 09:02:22,728 Populated table in 7116.00 seconds:
> analytics_enrollment_temp_vu0q0uahxxx (AbstractJdbcTableManager.java
> [taskScheduler-9])
> * INFO  2018-04-30 12:22:26,364 Populated table in 32250.03 seconds:
> analytics_enrollment_temp_swqlgvqvifa (AbstractJdbcTableManager.java
> [taskScheduler-25])
>
> 2. Yes! Good suggestion. This maintenance work_mem is low compared to
> these trackers. I am changing now.
>
> automated procedures are not causing so much problem.
>
> Lets see the progress tomorrow. I will get back to you all.
>
> Regards
>
> Hannan
>
>
>
> On Mon, Apr 30, 2018 at 7:48 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>
> wrote:
>
>> Hi Hannan
>>
>> I recall you had a similar request which I responded to back in Jan 11.
>> Maybe worth re-reading that thread as some of it seems still to be
>> relevant.  In particular:
>>
>> 1.  The first question, as always, is has this suddenly happened or has
>> something recently changed?  Back then there had been some new datasets
>> created by UNICEF which you were suspecting.  I recall at the time you said
>> that analytics then was taking 13 hours.  So 12 hours is an improvement?
>> Is this a sudden problem, like from a recent system upgrade or some changes
>> to datasets,  or just the way it has been for some time .. unacceptably
>> slow.
>>
>> 2.  A lot of analytics generation time is taken up on CREATE INDEX.  You
>> might try to increase maintenance_work_mem.  I think I had suggested back
>> then pushing as far as 4G on your system.
>>
>> 3.  You still have some automated creature (username elmisinterop)
>> logging into your system at a crazy rate per second after 1am.  As I recall
>> this user is trying to download all the data on your system which is
>> probably not a good thing to do during analytics generation.  Whatever it
>> is, you need to disable that.
>>
>> Try 2 and 3 and see where that gets you.
>>
>> Any chance you can also try to get access to some faster disk resource?
>>
>> Hope this helps.
>>
>> Bob
>>
>> On 30 April 2018 at 12:36, Hannan Khan <hannank@xxxxxxxxx> wrote:
>>
>>> Dear Experts
>>>
>>> I am drawing your attention to one problem and need your kind attention.
>>> In one of our DHIS2 instances, 'Central Server' analytics generation taking
>>> too much time  12 h, 22 m, 34 s. DB size is 127 GB and have aggregated and
>>> trackers as. During analytics run-time the processor utilization and RAM
>>> utilization is normal. But from the log it is clear that the trackers are
>>> taking too much time though they are not having much data. "EPI Cold Chain"
>>> tracker taking near about 9 hour, "Cause of death (MCCOD)"tracker taking 3
>>> hour and "Cervical and Breast Cancer Screening Program" tracker taking 7
>>> hour. What seems to be the reason? Is something wrong with the tracker
>>> system?
>>>
>>> We are using DHIS2 version 2.28 release 533e983.
>>>
>>> Need immediate help.
>>>
>>> Regards
>>>
>>> Muhammad Abdul Hannan Khan
>>> Team Leader
>>> Support to the National HMIS
>>> MIS, Directorate General of Health Services
>>> Ministry of Health and Family Welfare
>>>
>>> T +880-2- 58816459, 58816412 ext 118
>>> F +88 02 58813 875
>>> M+88 01819 239 241
>>> M+88 01534 312 066
>>> E hannank@xxxxxxxxx
>>> S hannan.khan.dhaka
>>> B hannan-tech.blogspot.com
>>> L https://bd.linkedin.com/in/hannankhan
>>>
>>>
>>>
>>>
>>
>
>
> --
> Muhammad Abdul Hannan Khan
> Team Leader
> Support to the National HMIS
> MIS, Directorate General of Health Services
> Ministry of Health and Family Welfare
>
> T +880-2- 58816459, 58816412 ext 118
> F +88 02 58813 875
> M+88 01819 239 241
> M+88 01534 312 066
> E hannank@xxxxxxxxx
> S hannan.khan.dhaka
> B hannan-tech.blogspot.com
> L https://bd.linkedin.com/in/hannankhan
>
>
>
>


-- 

*******************************************

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19119

Email: calle.hedberg@xxxxxxxxx

Skype: calle_hedberg

*******************************************