dhis2-users team mailing list archive

[Hannan Khan <hannank@xxxxxxxxx>]

Dear Calle

Thank you for your information. 127 GB Databases size is including
analytics. Yes disks are in RAID 5.

Do you have tracker on that instances? Possible to share tomcat and DB
tuning parameters?

Please suggest.

Regards

Hannan

On Tue, May 1, 2018 at 3:36 AM, Calle Hedberg <calle.hedberg@xxxxxxxxx>
wrote:

> 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
> <https://maps.google.com/?q=46D+Alma+Road,+7700+Rosebank,+SOUTH+AFRICA&entry=gmail&source=g>
>
> Tel/fax (home): +27-21-685-6472
>
> Cell: +27-82-853-5352
>
> Iridium SatPhone: +8816-315-19119
>
> Email: calle.hedberg@xxxxxxxxx
>
> Skype: calle_hedberg
>
> *******************************************
>
>


-- 
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