dhis2-users team mailing list archive

["Raminosoa Rabemanantsoa, Tantely" <traminosoa@xxxxxxxxxx>]

Hi Jason,

Thnak you for you message. The data value table has about 1,100,000
records. In addition to checking the postgreSQL configuration file,  this
time-out might also be related to the server configuration. Could you
please provide a link for the JVM/Tomcat performance tuning according to
the amount of server RAM?

Best Regards,

Tantely.

On Tue, Apr 5, 2016 at 3:57 AM, Jason Pickering <jason.p.pickering@xxxxxxxxx
> wrote:

> Hi there.
>
> In general, if the query is taking such a long time that it times out,
> then there may be something wrong with the query or the level of
> performance of your server. Disabling that time query time out, could
> result in queries which take an extremely long time to complete, leading to
> the server being overwhelmed. How many records does your data value table
> have? I would be surprised if Postgres would time out unless the size of
> the database is very large (many tens of millions of rows) or the server is
> under-resourced. Another possibility is that if you are seeing a timeout
> from the web (via DHIS2) then the reverse proxy may be timing out. You can
> (but should be careful again) to increase the time out. You can increase
> this with
>
> proxy_read_timeout 600;
>
>
> Also, please have  a look at our section on Postgres performance tuning. I
> have seen very large decreases in query time by performing tweaking the
> default setup to suit DHIS2 better.
>
>
> http://dhis2.github.io/dhis2-docs/master/en/implementer/html/ch08s03.html#d5e464
>
> Regards,
> Jason
>
>
> On Mon, Apr 4, 2016 at 3:45 PM, Raminosoa Rabemanantsoa, Tantely <
> traminosoa@xxxxxxxxxx> wrote:
>
>> Thank you Randy. I am going to check this postgresql.conf file to
>> troubleshoot this issue.
>>
>> Best Regards.
>>
>> On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy <rwilson@xxxxxxx> wrote:
>>
>>> I think the settings are in postgresql.conf.
>>>
>>> You could check the following:
>>> statement_timeout - set it to 0 to disable it.
>>>
>>> I can't seem to find the command limited the number of records a query
>>> can return.  I suppose it varies with the size of the record.
>>>
>>> Randy
>>>
>>>
>>>
>>> On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely <
>>> traminosoa@xxxxxxxxxx> wrote:
>>>
>>>> Hi Randy,
>>>>
>>>> The SQL script is running fine after putting "limit 100" at the end of
>>>> the query. Thanks! This script is answering to my question and I would like
>>>> to customize it further. I am using SSH to connect to the remote server and
>>>> would like to ask you where should be located the postgres config file for
>>>> me to make some change on the size of the query limitation?
>>>>
>>>> Best Regards.
>>>>
>>>> On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy <rwilson@xxxxxxx> wrote:
>>>>
>>>>> Hi Tantely,
>>>>>
>>>>> Are you limiting the data to a single data set at a time?  You might
>>>>> try to limit the result set by putting "limit 100" at the end of the
>>>>> query.  That way you can at least see if the query is working.  There are
>>>>> often limits set on the postgres server for the number of records that can
>>>>> be returned, especially on cloud servers.  You might need to change a
>>>>> parameter in the config.
>>>>>
>>>>> Randy
>>>>>
>>>>> On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely <
>>>>> traminosoa@xxxxxxxxxx> wrote:
>>>>>
>>>>>> Thank you Randy and Knut for the scripts. While I am running the
>>>>>> sample script and customizing with the dataset name, I am facing another
>>>>>> issue with a timed out connection due to a busy server. I cannot output any
>>>>>> result yet. Do you know if it is a server memory issue?
>>>>>>
>>>>>> Best Regards.
>>>>>>
>>>>>> Tantely.
>>>>>>
>>>>>> On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy <rwilson@xxxxxxx>
>>>>>> wrote:
>>>>>>
>>>>>>> Hi Tantely,
>>>>>>>
>>>>>>> Here is a sample query that will give you what I think you want:
>>>>>>> ________
>>>>>>> select dv.sourceid as organisationunitid,
>>>>>>> ou.name, ou.code as facilitycode, de.name, 'Vaccination' as
>>>>>>> datasetname,
>>>>>>> pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value
>>>>>>>
>>>>>>> from datavalue dv
>>>>>>> inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)
>>>>>>> inner join dataelement de on (dv.dataelementid=de.dataelementid)
>>>>>>> inner join period pe on (dv.periodid=pe.periodid)
>>>>>>> inner join _categoryoptioncomboname coc on
>>>>>>> (dv.categoryoptioncomboid=coc.categoryoptioncomboid)
>>>>>>> where dv.dataelementid in
>>>>>>> (select dataelementid from datasetmembers where datasetid in (
>>>>>>> select datasetid from dataset where name='Vaccination'))
>>>>>>> _____
>>>>>>>
>>>>>>> Just replace 'Vaccination' with the name of your dataset.  You can
>>>>>>> also create a variable for the dataset name  of your SQL view in the newer
>>>>>>> versions of DHIS-2.
>>>>>>>
>>>>>>> Good luck.
>>>>>>>
>>>>>>> On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring <knutst@xxxxxxxxx>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi Tantely,
>>>>>>>>
>>>>>>>> You are probably looking for the datasetmembers table and the
>>>>>>>> datasetsource table (confusingly and historically, orgunits used to be
>>>>>>>> referred to as sources, and this is still reflected in a few places).
>>>>>>>>
>>>>>>>> When you say data elements, I assume you mean data values? If so,
>>>>>>>> then something along the lines of the query below (though this didnt work
>>>>>>>> for me right now
>>>>>>>>
>>>>>>>> SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN
>>>>>>>> dataelement de NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource
>>>>>>>> dss
>>>>>>>>
>>>>>>>> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely <
>>>>>>>> traminosoa@xxxxxxxxxx> wrote:
>>>>>>>>
>>>>>>>>> Dear DHIS2 users,
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> I would like to export all data elements related to all
>>>>>>>>> organization units linked to a data set (questionnaire) on a specific
>>>>>>>>> period through the SQL View. Does anyone have some idea of on which table
>>>>>>>>> (/tables) should I make the SQL select script in DHIS2? The reason is that
>>>>>>>>> I have already exported the data through the “data export” (csv) but it
>>>>>>>>> returned me data values along columns and I need to transpose in Excel each
>>>>>>>>> Organization Unit to have data values aligned by row.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Thank you for your suggestions.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Best Regards,
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Tantely
>>>>>>>>>
>>>>>>>>> *This message and its attachments are confidential and solely for
>>>>>>>>> the intended recipients. If received in error, please delete them and
>>>>>>>>> notify the sender via reply e-mail immediately.*
>>>>>>>>> _______________________________________________
>>>>>>>>> Mailing list: https://launchpad.net/~dhis2-users
>>>>>>>>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>>>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-users
>>>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> Knut Staring
>>>>>>>> Dept. of Informatics, University of Oslo
>>>>>>>> Norway: +4791880522
>>>>>>>> Skype: knutstar
>>>>>>>> http://dhis2.org
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> Mailing list: https://launchpad.net/~dhis2-users
>>>>>>>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-users
>>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> *Randy Wilson*
>>>>>>> *Team Leader: **Knowledge Management, Data Use and Research*
>>>>>>> Rwanda Health System Strengthening Activity
>>>>>>> Management Sciences for Health
>>>>>>> Rwanda-Kigali
>>>>>>> Direct: +250 788308835
>>>>>>> E-mail: rwilson@xxxxxxx
>>>>>>> Skype: wilsonrandy_us
>>>>>>> <http://www.msh.org/>
>>>>>>> Stronger health systems. Greater health impact.
>>>>>>> <https://www.facebook.com/ManagementSciencesForHealth>
>>>>>>> <https://twitter.com/MSHHealthImpact>
>>>>>>> <https://www.youtube.com/user/MSHHealthImpact>
>>>>>>> www.msh.org
>>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> *Randy Wilson*
>>>>> *Team Leader: **Knowledge Management, Data Use and Research*
>>>>> Rwanda Health System Strengthening Activity
>>>>> Management Sciences for Health
>>>>> Rwanda-Kigali
>>>>> Direct: +250 788308835
>>>>> E-mail: rwilson@xxxxxxx
>>>>> Skype: wilsonrandy_us
>>>>> <http://www.msh.org/>
>>>>> Stronger health systems. Greater health impact.
>>>>> <https://www.facebook.com/ManagementSciencesForHealth>
>>>>> <https://twitter.com/MSHHealthImpact>
>>>>> <https://www.youtube.com/user/MSHHealthImpact>
>>>>> www.msh.org
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> *Randy Wilson*
>>> *Team Leader: **Knowledge Management, Data Use and Research*
>>> Rwanda Health System Strengthening Activity
>>> Management Sciences for Health
>>> Rwanda-Kigali
>>> Direct: +250 788308835
>>> E-mail: rwilson@xxxxxxx
>>> Skype: wilsonrandy_us
>>> <http://www.msh.org/>
>>> Stronger health systems. Greater health impact.
>>> <https://www.facebook.com/ManagementSciencesForHealth>
>>> <https://twitter.com/MSHHealthImpact>
>>> <https://www.youtube.com/user/MSHHealthImpact>
>>> www.msh.org
>>>
>>
>>
>>
>> --
>> *Tantely Raminosoa*
>> *Data Officer*
>> Management Sciences for Health
>> Antananarivo Madagascar
>> Mobile: 0321180040
>> E-mail: traminosoa@xxxxxxxxxx
>> Skype: traminosoa
>> <http://www.msh.org/>
>> Stronger health systems. Greater health impact.
>> <https://www.facebook.com/ManagementSciencesForHealth>
>> <https://twitter.com/MSHHealthImpact>
>> <https://www.youtube.com/user/MSHHealthImpact>
>> www.msh.org
>>
>> *This message and its attachments are confidential and solely for the
>> intended recipients. If received in error, please delete them and notify
>> the sender via reply e-mail immediately.*
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-users
>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-users
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
>
> --
> Jason P. Pickering
> email: jason.p.pickering@xxxxxxxxx
> tel:+46764147049
>

-- 
*This message and its attachments are confidential and solely for the 
intended recipients. If received in error, please delete them and notify 
the sender via reply e-mail immediately.*