Hello DHIS team,
Postgres is eating of all the CPU - (the server has 10 co’s), I have queries running for more than 45 minutes, this happens when Users are Updating TEI’s… below is a sample query
select count(distinct trackedent0_.trackedentityinstanceid) as col_0_0_ from trackedentityinstance trackedent0_ left outer join trackedentityattributevalue trackedent1_ on trackedent0_.trackedentityinstanceid=trackedent1_.trackedentityinstanceid cross join organisationunit organisati2_ where trackedent0_.organisationunitid=organisati2_.organisationunitid and (organisati2_.path like '/akV6429SUqu/VA90IqaI4Ji/aXmBzv61LbM/LYuMx5oPsR5%' or organisati2_.path like '/akV6429SUqu/VA90IqaI4Ji/aXmBzv61LbM/kKEBeO1LL7S%' or organisati2_.path like '/akV6429SUqu/VA90IqaI4Ji/aXmBzv61LbM/FbuhMjEaMXt%' or organisati2_.path like '/akV6429SUqu/VA90IqaI4Ji/aXmBzv61LbM/zNdkt1W9dcu%' or organisati2_.path like '/akV6429SUqu/VA90IqaI4Ji/aXmBzv61LbM/oweSpFBNbmi%' or organisati2_.path like '/akV6429SUqu/VA90IqaI4Ji/aXmBzv61LbM%') and (exists (select trackedent8_.trackedentityinstanceid, trackedent8_.trackedentityattributeid from trackedentityattributevalue trackedent8_ cross join trackedentityattribute trackedent9_ where trackedent8_
For now we have used a quick fix, monitoring Postgres log, then kill queries running beyond a given time to allow kick-start new process… Though this results into bad user experience
Improvements on this is welcome…
Regards
Isaac M