The DHIS2 2.19 is installed on a Linux OS running inside an Amazon web services. The server has in total 30GB RAM. The amount of memory allocated to JVM is now 2GB. The query I am trying to execute is a simple SQL select which intend to pull out the data values for a specified period of one data set. The timeout is occurring through the web, as I can access to the DHIS2 instance after killing the browser process. SQL view is giving the output if the SQL script is limited to show only 1000 records for example.
···
On Tue, Apr 5, 2016 at 9:23 AM, Jason Pickering jason.p.pickering@gmail.com wrote:
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@mikolo.org 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.
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.
–
On Tue, Apr 5, 2016 at 3:57 AM, Jason Pickering jason.p.pickering@gmail.com 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@mikolo.org wrote:
Thank you Randy. I am going to check this postgresql.conf file to troubleshoot this issue.
Best Regards.
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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
–
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+46764147049
On Mon, Apr 4, 2016 at 4:42 PM, Wilson, Randy rwilson@msh.org 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
–
Tantely Raminosoa
Data Officer
Management Sciences for Health
Antananarivo Madagascar
Mobile: 0321180040
E-mail: traminosoa@mikolo.org
Skype: traminosoa
Stronger health systems. Greater health impact.
www.msh.org
On Mon, Apr 4, 2016 at 3:10 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org 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.
–
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@msh.org
Skype: wilsonrandy_us
Stronger health systems. Greater health impact.
www.msh.org
On Mon, Apr 4, 2016 at 3:32 PM, Wilson, Randy rwilson@msh.org 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@mikolo.org 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.
–
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@msh.org
Skype: wilsonrandy_us
Stronger health systems. Greater health impact.
www.msh.org
On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy rwilson@msh.org 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@gmail.com 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
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
–
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@msh.org
Skype: wilsonrandy_us
Stronger health systems. Greater health impact.
www.msh.org
On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org 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@lists.launchpad.net
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
–
Tantely Raminosoa
Data Officer
Management Sciences for Health
Antananarivo Madagascar
Mobile: 0321180040
E-mail: traminosoa@mikolo.org
Skype: traminosoa
Stronger health systems. Greater health impact.
www.msh.org