Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Hi there, the analytics table generation is bound by db indexing speed / disk write speed. It scales almost linearly with the number of CPU cores available and disk write speed. regards, Lars On Mon, Oct 24, 2016 at 7:33 AM, Neeraj Gupta wrote: > Hi Calle, > > Thanks for all this. > > Yes,

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Hi Calle, Thanks for all this. Yes, we will have a test server available with us by first week of November, I can start test scenarios suggested by you after that. Thanks, Neeraj On Mon, Oct 24, 2016 at 3:50 PM, Calle Hedberg wrote: > Neeraj, > > Thanks for that, it is useful info. > > The in

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Hi Calle I think Lars would probably know better, but given the kinds of cross tabulation that is happening with analytics, I doubt that it will scale linearly. Would be good to get some empirical data but I think you are probably going to have something more approaching n^2 time complexity. On

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Neeraj, Thanks for that, it is useful info. The instance has a very skewed balance between data elements/catcombos and indicators, but what else is new - outside of South Africa, most countries have the same: they collect a huge number or data elements but turn very few of them into indicators (S

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Much appreciated Bob and Neeraj, much appreciated ... On Mon, Oct 24, 2016 at 3:27 PM, Knut Staring wrote: > It would be very interesting to know of other similarly large > installations. DATIM, Bangladesh, PSI perhaps - others? > > On Mon, Oct 24, 2016 at 10:39 AM, Neeraj Gupta > wrote: > >> C

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

It would be very interesting to know of other similarly large installations. DATIM, Bangladesh, PSI perhaps - others? On Mon, Oct 24, 2016 at 10:39 AM, Neeraj Gupta wrote: > Calle, > > We have around 500 million record in database with 3666 data elements > having 26 category combinations and 201

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

For the benefit of others on this thread, this is the tweaking I shared with Neeraj - He had 3200MB shared_buffers set (as per the online manual). I suggested with his 48G of RAM he tries: shared_buffers = 12GB effective_cache_size = 20GB (remember the manual is based on an exampe machine with

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Calle, We have around 500 million record in database with 3666 data elements having 26 category combinations and 201 indicators and there are 14398 organisation units. Thanks, Neeraj On Mon, Oct 24, 2016 at 1:50 PM, Calle Hedberg wrote: > Neeraj, > > You never stated the number of records you

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Neeraj, You never stated the number of records you have in the datavalue table - what is it? In the same context: anybody have a rough idea of how many datavalue records there are in the global DATIM database - which I think currently might be the largest DHIS2 instance around? Given our own rec

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Congratulation Neeraj and team ...it much appreciated On Mon, Oct 24, 2016 at 11:08 AM, Neeraj Gupta wrote: > Dear Team, Thanks for all your suggestions. > > Now the time of analytic is reduced to 10 hours 41 minutes. > > We tried to VACUUM as Sam suggested but it didn't help then we upgraded >

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Dear Team, Thanks for all your suggestions. Now the time of analytic is reduced to 10 hours 41 minutes. We tried to VACUUM as Sam suggested but it didn't help then we upgraded postgres from 9.4 to 9.5.4 and as Calle and Bob suggested we made some changes in configuration file of postgres and it r

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Thank you everyone. I will give try to each options you gave and post the result on the mail. Thanks, Neeraj On Wed, Oct 19, 2016 at 6:46 PM, Calle Hedberg wrote: > Neeraj, > > It's always an element of uncertainty linked to database sizes - ref Sam's > post over. So indicating the number of re

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Dear Sir, I had issues in connecting to PoastgreSQL 9.6 due to HSBC error has that been resolved now. Please let me know. I believe 9.6 can handle parallel queries & will have better handling of vacuum, which might be usesful for large databases as handled by Neeraj. Dr. Sunil Gavaskar Partha

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Neeraj, It's always an element of uncertainty linked to database sizes - ref Sam's post over. So indicating the number of records you have in the datavalue table & key meta-data tables would be useful + indicating whether you are running other instances on the same server. Some comments - I've bee

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

One other point to mention: if you do try runing VACUUM FULL, you should do it before running the analytics (ie on your initial 100Gb database, not the 500Gb database you end up with after running the analytics), as my guess is that the DHIS2 analytics tables are dropped and re-created anyway, s

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Hi Neeraj, Using VACUUM and ANALYZE Like Brajesh, my background is MySQL, and one database admin task that is often overlooked in MySQL is OPTIMIZE TABLEs. This reclaims unused space (we’ve had 100Gb databases files drop to half their size) and refreshes index statistics (if the shape of your

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Hi Neeraj, not sure about the relevance of this detail: when I upgraded from 9.4 to 9.5 I found out that there was a postgres parameter (wal_buffers) removed from the current DHIS2 implementer's guide (relating to 9.5), which was still in there up to 2.24 (relating to 9.4). Also I had the feeling

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Thanks Knut and Brajesh, Lars we are using 12 Core CPu with SSD and having 48GB RAM but using Postgres 9.4. We will try with Postgres 9.5 now and let you know the results. Do you recommend anything else other than this? Thanks, Neeraj On Wed, Oct 19, 2016 at 12:35 PM, Knut Staring wrote: > Ju

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Dear Neeraj, The physical database size doesn't matter much, even the number of records don't matter. In my experience the biggest problem that one can going to run in to is not size, but the number of queries you can handle at a time instance specially during analytic functionality execution. Mos

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Just a heads-up that there seems to be a JDBC issue with Postgres 9.6, so perhaps you should try upgrading to 9.5 first. On Wed, Oct 19, 2016 at 8:58 AM, Lars Helge Øverland wrote: > > Hi Neeraj, > > what usually helps to improve runtime is to improve/increase: > > - ssd (read and write speed) >

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Thanks Lars, we will give this a try and let you know. How much time do you expect to run for 100 GB database in ideal conditions? Thanks, Neeraj On Wed, Oct 19, 2016 at 12:28 PM, Lars Helge Øverland wrote: > > Hi Neeraj, > > what usually helps to improve runtime is to improve/increase: > > - s

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Hi Neeraj, what usually helps to improve runtime is to improve/increase: - ssd (read and write speed) - number of CPUs - using latest postgresql (9.6 claims to have even better indexing performance than 9.5) - tuning

Re: [Dhis2-devs] [Dhis2-users] 25 hours in completing Analytic

Hi Neeraj, what usually helps to improve runtime is: - ssd () - number of CPUs - On Wed, Oct 19, 2016 at 7:13 AM, Neeraj Gupta wrote: > Dear team, > > In one of the Indian state we are using DHIS 2.23 and the database size is > going around 100 GB and once we run analytic it reaches to 500