DHIS2 Can't finish the analytic in version 2.37.1

Support - Assistance technique
21

No rights to see? This is strange, my click works

1 Like
22

image
image2063×1294 161 KB

23

Yeah, no permissions. Sorry for that, I have to speak to administrator of TECH2 project

1 Like
24

PR Ready, waiting for reviewers

2 Likes
25

Hi @dusan Happy New Year.

Would like to register our eagerness for this patch. Since we already upgraded 2.37 we cannot go back and also can’t run analytics.

Registering in case it helps in prirotizing.

Thank You

3 Likes
26

Hello.
I’m not sure if this is the right forum but our DHIS2 instance keeps crashing because our analytics_temp keeps filling up to 100GB. Why does this happen and how can we stop this?

Thank you

1 Like
27

Hi @Clara,
Thank you for your post! You’re not the first to mention this and I’ve moved your post to the related topic where many have mentioned the issue. It’s a bug as you can see from Dusan’s last post and it has been fixed, but what version are you using?

Thanks!

28

We are using 2.37.6 @Gassim. Which version has the fix?

1 Like
29

I will let you know as soon as I get an update from @Dusan! Thanks! (:

30

Thank you so much. The server crashes are not great.

31

Can we have access to that issue please - still seeing this on 2.37.6.1SNAPSHOT

1 Like
32

@Gassim should we maybe just open another issue on Jira since we can’t access the current issue?

1 Like
33

Created JIRA ticket

34

@chase.freeman and @Clara,

Thank you for following up on this. The fix will definitely be in one of the upcoming releases; however, for now please use the manual workaround.

@Dusan:

Ok, the fix was released on the 9th of March and most likely is not in 2.37.6 (14th of April). Quick fix is just manually remove (drop table) all analytics temporary tables.

35

@Gassim do you know if the issue has been fixed in 2.38?

36

I am having similar issues after an upgrade from 2.36.12 to 2.37.7.1. Analytics never finish running . I am forced to kill SQL queries lest the database fill/blow up. RAM and DB configs are not the issue here. I have them set to very large and have a server with 128gb of RAM.

Example query that analytics is hanging on (for hours) leading to LWLock: WALWrite in the DB:

2022-09-12 16:32:14.060 UTC [734055] STATEMENT: insert into analytics_event_temp_wshfxyegte5_2021 ("uidlevel1","uidlevel2","uidlevel3","uidlevel4","Ir7fvZkyzuq","daily","weekly","weeklywednesday","weeklythursday","weeklysaturday","weeklysunday","biweekly","monthly","bimonthly","quarterly","sixmonthly","sixmonthlyapril","sixmonthlynov","yearly","financialapril","financialjuly","financialoct","financialnov","C25kY3E21zv","sjggkgBfIWE","jxICmw7evNG","CP82ck7piiL","gaCDwmKm768","LQZywhYcISY_geom","LQZywhYcISY_name","LQZywhYcISY","NoTuZ3jydbw","QznE0JyWMCT","eaqQd1d6yAV","qaQzslMuVac","aC9ec34UGx6","DYsNDR4nCWB","TRm4iaYikdK","nZHB18RzReF","BXdKAz7qu0K","SIESJ4V7d5U","qb4P26MQUqn","jkJY0J8Xrre","TRm4iaYikdK_oPyVHCMErH0","psi","pi","ps","ao","enrollmentdate","incidentdate","executiondate","duedate","completeddate","created","lastupdated","storedby","pistatus","psistatus","psigeometry","longitude","latitude","ou","ouname","oucode","oulevel","ougeometry","pigeometry","tei") select ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ougs."Ir7fvZkyzuq",dps."daily",dps."weekly",dps."weeklywednesday",dps."weeklythursday",dps."weeklysaturday",dps."weeklysunday",dps."biweekly",dps."monthly",dps."bimonthly",dps."quarterly",dps."sixmonthly",dps."sixmonthlyapril",dps."sixmonthlynov",dps."yearly",dps."financialapril",dps."financialjuly",dps."financialoct",dps."financialnov",(select eventdatavalues #>> '{C25kY3E21zv, value}' from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as "C25kY3E21zv",(select cast(eventdatavalues #>> '{sjggkgBfIWE, value}' as double precision) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{sjggkgBfIWE,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "sjggkgBfIWE",(select eventdatavalues #>> '{jxICmw7evNG, value}' from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as "jxICmw7evNG",(select eventdatavalues #>> '{CP82ck7piiL, value}' from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as "CP82ck7piiL",(select eventdatavalues #>> '{gaCDwmKm768, value}' from programstageinstance where programstageinstanceid=psi.programstageinstanceid ) as "gaCDwmKm768",(select ou.geometry from organisationunit ou where ou.uid = (select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=757)) as "LQZywhYcISY",(select ou.name from organisationunit ou where ou.uid = (select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=757)) as "LQZywhYcISY",(select ou.uid from organisationunit ou where ou.uid = (select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=757)) as "LQZywhYcISY",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=764) as "NoTuZ3jydbw",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=755) as "QznE0JyWMCT",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=756) as "eaqQd1d6yAV",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=754) as "qaQzslMuVac",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=53716) as "aC9ec34UGx6",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=759) as "DYsNDR4nCWB",(select cast(value as double precision) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=752 and value ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "TRm4iaYikdK",(select cast(value as double precision) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=751 and value ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "nZHB18RzReF",(select cast(value as double precision) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=750 and value ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "BXdKAz7qu0K",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=53758) as "SIESJ4V7d5U",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=53759) as "qb4P26MQUqn",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=53760) as "jkJY0J8Xrre",(select l.uid from maplegend l inner join trackedentityattributevalue av on l.startvalue <= cast(value as double precision) and l.endvalue > cast(value as double precision) and l.maplegendsetid=728 and av.trackedentityinstanceid=pi.trackedentityinstanceid and av.trackedentityattributeid=752 and value ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "TRm4iaYikdK_oPyVHCMErH0",psi.uid,pi.uid,ps.uid,ao.uid,pi.enrollmentdate,pi.incidentdate,psi.executiondate,psi.duedate,psi.completeddate,psi.created,psi.lastupdated,psi.storedby,pi.status,psi.status,psi.geometry,CASE WHEN 'POINT' = GeometryType(psi.geometry) THEN ST_X(psi.geometry) ELSE null END,CASE WHEN 'POINT' = GeometryType(psi.geometry) THEN ST_Y(psi.geometry) ELSE null END,ou.uid,ou.name,ou.code,ous.level,ou.geometry,pi.geometry,tei.uid from programstageinstance psi inner join programinstance pi on psi.programinstanceid=pi.programinstanceid inner join programstage ps on psi.programstageid=ps.programstageid inner join program pr on pi.programid=pr.programid and pi.deleted is false inner join categoryoptioncombo ao on psi.attributeoptioncomboid=ao.categoryoptioncomboid left join trackedentityinstance tei on pi.trackedentityinstanceid=tei.trackedentityinstanceid and tei.deleted is false inner join organisationunit ou on psi.organisationunitid=ou.organisationunitid left join _orgunitstructure ous on psi.organisationunitid=ous.organisationunitid left join _organisationunitgroupsetstructure ougs on psi.organisationunitid=ougs.organisationunitid and (cast(date_trunc('month', psi.executiondate) as date)=ougs.startdate or ougs.startdate is null) inner join _categorystructure acs on psi.attributeoptioncomboid=acs.categoryoptioncomboid left join _dateperiodstructure dps on cast(psi.executiondate as date)=dps.dateperiod where psi.lastupdated < '2022-09-12T16:15:08' and psi.executiondate >= '2021-01-01T00:00:00' and psi.executiondate < '2022-01-01T00:00:00' and pr.programid=812 and psi.organisationunitid is not null and psi.executiondate is not null and dps.yearly is not null and dps.year >= 1975 and dps.year <= 2024 and psi.deleted is false

37

Further to Steve’s comment (I’m working on the same issue) - this has to do with Event data.

We have two identical instances, one a production database on 2.36.3 with a set of Analytics tables that successfully generate with no issues with the following stats Event Analytic SQL row counts/sizes:

2017: 19772 rows, 27 MB
2018: 16283 rows, 22 MB
2019: 122133 rows, 165 MB
2020: 255679 rows, 343 MB
2021: 404120 rows, 543 MB
2022: 858 rows, 1904 kB

In the other identical version of the database, where we are testing updates (2.37 or now 2.38), the analytics updates blow up the disk space when the process gets to tables 2020 or 2021. The row counts and sizes from the temp tables during the updates before it blows up the 120GB disk (100 GB free at the beginning of analytics run), are;

2017: 19772 rows, 2820 MB
2018: 16283 rows, 2356 MB
2019: 122133 rows, 17 GB (long time to gen, was orange, then red in pgadmin)
2020: 0, 25 GB (red condition in pgadmin)
2021: 0, 28 GB (red condition in pgadmin)
2022: 0

This implies that the internal queries that are running during analytics generation for events somehow changed between 2.36 and later versions. Could someone provide some pointers on where we might look at (extra event data checks that are picking up errors/corruption of event data???)? One other note - we have a very similar data model in another country with similar types of event data, and that has been updated to 2.37.x and runs analytics without issue.

Cheers.

DH

38

Further investigation indicates that the temp event tables being generated during analytics runs are blowing up in 2.37/2.38 because the ougeometry column is included and appears (we assume) to be a copy of the shape data for the org unit linked to the event? (we place our events via the API using the recommended payload examples, and in our case the event is at the province level, given the precision of the information we have about the event).

image

Perhaps we should leave the enrollment linked in the payload, and keep the event orgunit attribute blank?

Thoughts

Cheers

David Hagan

39

@Clara @stevie @djhag , thank you for your posts and sharing the logs, I assure you that this is being discussed with the core team developers and will get back to you as soon as there is an update.

Thank you! Please post back to the community with any issue or more info that you’d like to share.

40

Thanks,

As a temporary workaround, we removed the shapefiles from the org units linked to the enrollments/events and the analytics ran as normal.

All the best,

David

1 Like