Thanks Saptarshi, this is very helpful.
On Tue, May 7, 2013 at 6:23 PM, Saptarshi Purkayastha <sun...@gmail.com>wrote: > Hello DHIS users and devs, > > Apologies for the long email... Should have probably been multiple > blogpost to reduce its length... > > I recently encountered a situation with a very large implementation of > DHIS2 having problems generating data mart. > Thus no reports were generated and only data entry was being done. I > thought I'd share some of the experiences to solve these issues, so that it > might be useful to other implementers. > Some changes will be needed in the DHIS2 source, so sending this to the > dev list also, where dev-related discussions can follow-up. > > While PostgreSQL is our recommended database, many implementations have > also used MySQL. > My findings clearly highlight that DHIS2 performs much better on > PostgreSQL and there are also some bugs related to MySQL dialect. > Total org units - 26303 > Total Monthly datasets - 9 > Total Daily dataset - 1 > Total Yearly dataset - 3 > > The implementation has about 34 million datavalues (non-zero)... but I > pruned it for my benchmarking. I added 1-million datavalues and ran the > data mart. > The results are from my fairly good laptop (quad-core i7; 8GB RAM; tuned > JVM; tuned MySQL 5.5 (4GB RAM); tuned PostgreSQL 9.2 (4GB RAM); 240GB SSD) > Using DHIS2 2.11. When doing MySQL benchmark turned off all services > including postgres and vice versa. > Java Opts: -Xmx3G -Xms768m -XX:MaxPermSize=512m > Java version: 1.7.0_21 x64 > Java vendor: Oracle Corporation OS name: WindowsMySQL = datamart > completed in 3hrs 46min 12sec > PostgreSQL = datamart completed in 2hrs 5min 16sec > > So, it is obvious that PostgreSQL is doing datamart much faster. The > advantages might scale better if larger number of datavalues > One could argue MySQL 5.6 has many performance improvements, I didn't have > time to explore that. > The migration to PostgreSQL has some challenges. Following are steps I > followed: > > - Take the mysqldump > - replace bit(1) to tinyint(1) in the SQL file > - You'll see that column names are camelCase. This is an issue because > postgres will added a double quotes around to get case-sensitivity, which > MySQL by default nicely excludes. > So you'll have to make all column names to lowercase and remove the quote > characters. I did this with a simple java program. There are 150-odd column > names that need changes. > - Used Navcat premium (trial version or SQLSquirrel also has this > feature). "Data transfer" is the name of the feature that will move data > from MySQL to Postgres > - In MySQL non-standard use of boolean (which came only a few yrs back), > its converted to smallint in Postgres. I wrote a JDBC program to change > column type from smallint to boolean. A single table example is as follows > that can be made into a looping procedure as well in pure PSQL. > ALTER TABLE indicator ALTER COLUMN annualized TYPE boolean > USING CASE WHEN annualized = 0 THEN FALSE > WHEN annualized = 1 THEN TRUE > ELSE NULL > END; > - remove NULL values from minimumvalue column of minmaxdatalement table > DELETE from minmaxdatalement WHERE minimumvalue=NULL > - remove NULL values from maximumvalue column of minmaxdatalement table > DELETE from minmaxdatalement WHERE maximumvalue=NULL > - remove NULL values from name column of relationshiptype table > DELETE from relationshiptype WHERE name=NULL > - blobs to bytea conversion is a mess and I had to truncate. Probably a > JDBC based connector program will do better conversion, but I just > truncated it and accepted the data loss to systemsetting and usersetting :-) > * > Devs*: > We need to make all column names lowercase in hbm.xml files in code. This > will ensure portability and is generally a good practice. > We should also have a convention of using last_updated instead of > lastUpdated in column names, as is the common practice. > > --- > Regards, > Saptarshi PURKAYASTHA > > My Tech Blog: http://sunnytalkstech.blogspot.com > You Live by CHOICE, Not by CHANCE > > _______________________________________________ > Mailing list: https://launchpad.net/~dhis2-devs > Post to : dhis2-devs@lists.launchpad.net > Unsubscribe : https://launchpad.net/~dhis2-devs > More help : https://help.launchpad.net/ListHelp > > -- Regards, Bharath Kumar. Ch
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
