dhis2-users team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Could you provide more information about your setup?

 How much RAM does your server or servers have?

What is the query you are attempting to execute?

Where is the timeout occurring (through the Web or from the database
itself)?

What does your "About DHIS2"  say?

Have you performed and of the suggested performance upgrades in the
implementation manual?

Regards


On Tue, Apr 5, 2016, 11:02 Raminosoa Rabemanantsoa, Tantely <
traminosoa@xxxxxxxxxx> wrote:

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