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 <hann...@gmail.com> 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 <bobjolli...@gmail.com> > 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 <hann...@gmail.com> 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 hann...@gmail.com >>> 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 hann...@gmail.com > 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.hedb...@gmail.com Skype: calle_hedberg *******************************************
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-users Post to : dhis2-users@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp
