dhis2-users team mailing list archive

["Abdul karim Jaafar" <abdulkarim.jaafar87@xxxxxxxxx>]

Dears,

 

I changed the setting 

 

-          Infrastructural period type to ‘Daily’ instead of ‘Yearly’

-          Default relative period for analysis to ‘This week’ instead of ‘last 12 months’

Then analytic take just 1:30 hour

 

So, I want to know which recommended setting, tomcat version and how to take advantage of all the features to make analytic at best performance

 

We have:

Server dell PowerEdge R730,

OS windows server 2012, 

RAM 32GB, DB: PostgreSQL 9.5,

Tomcat 9.0.0.M6

And about 50 user working as (Data Entries and Report Generators) on DHIS2

 

Best regards,

 

From: Abdul karim Jaafar [mailto:abdulkarim.jaafar87@xxxxxxxxx] 
Sent: Tuesday, May 31, 2016 6:20 PM
To: 'Halvdan Hoem Grelland' <halvdan@xxxxxxxxx>; 'Hannan Khan' <hannank@xxxxxxxxx>
Cc: 'DHIS 2 Users list' <dhis2-users@xxxxxxxxxxxxxxxxxxx>
Subject: RE: [Dhis2-users] Analytics period

 

Dears,

 

I forgot to tell you analytic was taking from 2 to 7 hour, then we made hosting in ‘BAO system’ for two month and then analytic take 18 hour in there system,

 

Then gradually began to decrease, but when I took DB backup from them and start it in our old local server and analytic take now too much time.

 

Also in tomcat server after analytic I see this error

 

 

“* INFO  2016-05-31 13:21:26,787 Query failed, likely because the requested analytics table does not exist (JdbcEventAnalyticsManager.java [http-apr-8085-exec-5])

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select count(coalesce("u52tYTj6mV8",0)) as value,"yearly" from analytics_event_2015_qmal2yvmlpc where Yearly in ('2015') and (uidlevel1 = 'Z8WFmqm0AvD' ) and ("u52tYTj6mV8" is not null) group by "yearly" ]; nested exception is org.postgresql.util.PSQLException: ERROR: COALESCE types text and integer cannot be matched

  Position: 37”

 

 

I did tuning for PostgreSQL put analytic still take too much time.

Regards,

 

From: Halvdan Hoem Grelland [mailto:halvdan@xxxxxxxxx] 
Sent: Tuesday, May 31, 2016 12:12 PM
To: Hannan Khan <hannank@xxxxxxxxx <mailto:hannank@xxxxxxxxx> >
Cc: Abdul karim Jaafar <abdulkarim.jaafar87@xxxxxxxxx <mailto:abdulkarim.jaafar87@xxxxxxxxx> >; DHIS 2 Users list <dhis2-users@xxxxxxxxxxxxxxxxxxx <mailto:dhis2-users@xxxxxxxxxxxxxxxxxxx> >
Subject: Re: [Dhis2-users] Analytics period

 

18 hours seems very excessive. Did you do any performance tuning of Postgres? You could have a look at section 8.3.6 here <http://dhis2.github.io/dhis2-docs/master/en/implementer/html/ch08s03.html> 

 

On Tue, May 31, 2016 at 6:47 AM, Hannan Khan <hannank@xxxxxxxxx <mailto:hannank@xxxxxxxxx> > wrote:

Dear Abdul Karim

 

This is quite unusual for this amount of data. We have several million tracked entity and took only half an hour for run analytics for last three years. 

 

Can you please check your postgres tuning parameter, server tuning parameters and get back to me.

 

Regards

 

Hannan Khan  

 

On Tue, May 31, 2016 at 10:05 AM, Abdul karim Jaafar <abdulkarim.jaafar87@xxxxxxxxx <mailto:abdulkarim.jaafar87@xxxxxxxxx> > wrote:

Hi Lars,

 

I have 337460 value in data value, 692719 in ‘tracked entity attribute value’ and 994129 in ‘tracked entity data value’

 

also these inputs are increasing daily,

 

I have upgraded PostgreSQL to 9.5 but analytic still take much time

 

now it take about 19 hour!

 

I use DHIS2 Version: 2.22

 

Build revision:22057

Java version:1.8.0_92

 

Time since last analytics table generation:

34 h, 32 m, 5 s

Last analytics table runtime:

18 h, 44 m, 55 s

 

Best regards,

 

 

 

From: Lars Helge Øverland [mailto:lars@xxxxxxxxx <mailto:lars@xxxxxxxxx> ] 
Sent: Tuesday, May 31, 2016 4:19 AM
To: Abdul karim Jaafar <abdulkarim.jaafar87@xxxxxxxxx <mailto:abdulkarim.jaafar87@xxxxxxxxx> >
Cc: DHIS 2 Users list <dhis2-users@xxxxxxxxxxxxxxxxxxx <mailto:dhis2-users@xxxxxxxxxxxxxxxxxxx> >
Subject: Re: [Dhis2-users] Analytics period

 

Hi Abdul,

 

okay. Can you let us know a bit more info here, such as:

 

- how many data values do you have in your database

- how long does analytics take to complete

- tomcat log output from the process

 

Upgrading to PostgreSQL 9.5 will make the process significantly faster compared to 9.3.

 

regards,

 

Lars

 

 

On Mon, May 30, 2016 at 10:56 PM, Abdul karim Jaafar <abdulkarim.jaafar87@xxxxxxxxx <mailto:abdulkarim.jaafar87@xxxxxxxxx> > wrote:

Dears all,

 

I have a problem with analytics period, It take too much time to finish, I did ‘Maintenance’ from ‘Data Administration’ and fixed all problems in ‘Data Integrity’

 

Sometime when I change tomcat version, analytic goes better.

 

I want to know what things that make analytic take much time, and how to make it faster

 

I have server, OS Windows 64 bit, ram 32 GB, hard 2 TB

 

Best regards,

Abdul Karim,


_______________________________________________
Mailing list: https://launchpad.net/~dhis2-users
Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx <mailto:dhis2-users@xxxxxxxxxxxxxxxxxxx> 
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp





 

-- 

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@xxxxxxxxx <mailto:lars@xxxxxxxxx> 

http://www.dhis2.org <https://www.dhis2.org/> 

 


_______________________________________________
Mailing list: https://launchpad.net/~dhis2-users
Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx <mailto:dhis2-users@xxxxxxxxxxxxxxxxxxx> 
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp





 

-- 

Muhammad Abdul Hannan Khan

Secretary

HISP Bangladesh 

 

T +880-2- 8816459 <tel:%2B880-2-%208816459> , 8816412 ext 118

F +88 02 8813 875

M+88 01819 239 241

M+88 01534 312 066

E hannank@xxxxxxxxx <mailto:hannank@xxxxxxxxx> 

S hannan.khan.dhaka

B hannan-tech.blogspot.com <http://hannan-tech.blogspot.com> 




_______________________________________________
Mailing list: https://launchpad.net/~dhis2-users
Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx <mailto:dhis2-users@xxxxxxxxxxxxxxxxxxx> 
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp





 

-- 

Halvdan Hoem Grelland

Software developer, DHIS 2

University of Oslo

 <https://www.dhis2.org/> http://www.dhis2.org

Attachment: setting.PNG
Description: PNG image