Postgresql 9.4 master-master replication

Postgresql database has come a long way since its initial release. One of the feature that can come in very handy in handling database that are located across geographical region is multi-master replication. The idea is to provide localised access to the database for each sites and yet still maintain a single view of the database. There are two solution that can get this done rather painlessly.

Option 1: BDR
This is a new replication solution by second quadrant. Still in beta but the ease of installation is very promising. The quirks:
a. Some of the database administrative functions are rendered inoperative.
b. A global sequence need to be explicitly set during table creation. Otherwise, would have a lot of write failure as the sequences are being replicated asynchronously.
c. Need a patched version of Postgresql 9.4.

Option 2: Bucardo
This solution has been around for quite sometime and is quite stable. The latest version which is 5.4 added support for multi-masters. The quirks:
a. DDL is not replicated. Any update to DDL need to be manually handled across all nodes.
b. Sequences is not replicated. A good strategy is to increment the sequence by the number of nodes installed.

Personally I prefer BDR solution as DDL and sequences replication can make deployment so much easier.

Troubleshooting Web Application Memory Problem

A lot of web based framework from both open source and branded vendors are frequently used in black box fashion for developing web applications. Everything worked perfectly during testing, however in production it intermittently crashed with out of memory error. Most developers would just add "-Xms2048m -Xmx2048m -XX:PermSize=256m -XX:MaxPermSize=256m" and the problem went away until the next crash. Once daily crashes started to occur often enough, the next typical ingenious solution is to restart the web application every day using a scheduler. Sometimes even twice in a day. 

There is a better way. It involves looking at memory and thread dump to see what was actually going on under the hood and fix the problem! The steps involved are actually quite simple.

1. Look for java process id.
      jps -l

2. Do a thread dump.
      jstack  [java process id]  > process_thread.dump

3. Do a memory dump.
      jmap -dump:file=process_memory.hprof [java process id]

4. Download MAT from https://eclipse.org/mat/.  Run it and open process_memory.hprof with it.

5. Download ThreadLogic from https://java.net/projects/threadlogic. Make sure to compile from the source as the downloaded ThreadLogic-2.0.215.jar from the site does not seem to work. Run it and open process_thread.dump with it.

These tools help tremendously in hunting down the root cause of the dreaded "out of memory" problem at production environment. Best part is that no changes need to be make to the running application. For weblogic server, the Oracle advisory map that comes with ThreadLogic is invaluable as it can quickly be used to zoom in on the potential problem area.


Reference:
jmap - http://docs.oracle.com/javase/7/docs/technotes/tools/share/jmap.html
jstack - http://docs.oracle.com/javase/8/docs/technotes/tools/unix/jstack.html

Exploring Java Development using Play 2 Framework

Play 2 is a very interesting framework as both Java and Scala language can be used interchangeably in the same project for developing web application. Basically, it follows MVC pattern but breaks a couple of in grained java programming practices if one is well versed with Spring and J2EE development framework. For a more in dept analysis of this framework, I've decided to use it to implement a production grade SaaS application. Over the past one year, as new features were added and bugs fixed, the SaaS application was tinkered along the way to keep in line with each release of Play 2 framework. The current development environment is as follows:

1. Source Code Repository: GIT at bitbucket.org
2. Repository Manager: Artifactory 3.4.0
3. JVM: Oracle JDK 1.7
4. IDE: Eclipse Luna with Scala IDE plugin
5. Database: Postgresql 9.3
6. ORM: JPA 2.1 and Hibernate 4.3.6
7. Security Framework: Social Secure master-snapshot
8. Scala: 2.11.1
9. Charting: DS3.js
10. Template: Bootstrap 3

The features that have been implemented are:

1. Internationalization
2. AJAX invocation
3. Bootstrap 3 based custom components
4. Segregation of functions into modules
5. Custom login page and authorization provider for Social Secure
6. Front end javascript validation
7. Date and numeric field formatting
8. Optimized bulk insert and loading of data

The positives:

• Building a working CRUD page is much faster than with JSF-Spring-JPA. Scala template is simpler than JSF. 
• The performance is very good. The generated code is less cluttered when compared to JSF. Much easier to manipulate it through jQuery and unlike JSF do not have to synchronize the state with the server side managed bean.
• Much easier to add customized components. Just drop the HTML code in a scala wrapper method.
• No need to restart the server when adding the new code.
• Auto-binding of the form data.
• Akka 

The negatives:

• Compilation can be slow. 105 scala sources and 87 java sources, take up to 2 minute to compile from a clean state. Much faster on subsequent compilation. Depending on the changes, some still trigger a lot of recompilation.
• A lot of jar files are added via the transitive dependency. Currently stands at over 60 jars.
• Scala IDE with Eclipse Luna is not working well. Occasionally some jar files could not be cleared until Eclipse is closed. Turning on "Build Automatically" can cause problem when compiling using the command line, activator compile.

Configuring Pentaho CE 5.2 to work with Oracle 12c

Pentaho is just awesome. The fact that it is open source make it really useful for SME that do not have a massive budget. Although its UI may not be flashy and polished, it comes with a suite of powerful tools that can get the job done really well. During recent work on mining data from various database, Oracle 12c seems to be the most problematic to get it working. After experimenting with various configuration, finally got them to talk using JNDI setup.

1. Oracle 12c Configuration
On the Oracle 12c server, the tnsname is configured as follows:

tnsnames.ora

# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

JUPITER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jupiter)
    )
  )

PDBJUPITER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdbjupiter)
    )
  )

* JUPITER         - CDB database
* PDBJUPITER   - PDB database

2. Installing Oracle JDBC Driver in Pentaho
Copy the Oracle JDBC driver, ojdbc7.jar from
       <ORACLE_INSTALLATION_PATH>/product/12.1.0/dbhome_1/jdbc/lib/
to
       <PENTAHO_INSTALLATION_PATH>/lib/

Note: Copy the ojdbc6.jar instead if using JDK 1.6

3. Pentaho DI 5.2 - Spoon Configuration
Locate jdbc.properties file at data_integration/simple_jndi folder. After adding the configuration for Oracle 12c, it becomes as follows:

jdbc.properties

SampleData/type=javax.sql.DataSource
SampleData/driver=org.h2.Driver
SampleData/url=jdbc:h2:file:samples/db/sampledb;IFEXISTS=TRUE
SampleData/user=PENTAHO_USER
SampleData/password=PASSWORD
Quartz/type=javax.sql.DataSource
Quartz/driver=org.hsqldb.jdbcDriver
Quartz/url=jdbc:hsqldb:hsql://localhost/quartz
Quartz/user=pentaho_user
Quartz/password=password
Hibernate/type=javax.sql.DataSource
Hibernate/driver=org.hsqldb.jdbcDriver
Hibernate/url=jdbc:hsqldb:hsql://localhost/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Shark/type=javax.sql.DataSource
Shark/driver=org.hsqldb.jdbcDriver
Shark/url=jdbc:hsqldb:hsql://localhost/shark
Shark/user=sa
Shark/password=

PdbJupiter/type=javax.sql.DataSource
PdbJupiter/driver=oracle.jdbc.pool.OracleDataSource
PdbJupiter/url=jdbc:oracle:thin:@192.168.56.11:1521/pdbjupiter
PdbJupiter/user=biadm
PdbJupiter/password=password

* PdbJupiter    - JNDI name
* pdbjupiter    -  PDB database 
* biadm           - A normal user created for PDB

Launch Spoon, go to Tools -> Repository -> Explore -> Connections and add the connection to Oracle 12c as shown in the screen below.

Spoon should now be able to extract and load data from Oracle 12c.

Note: For Oracle 12c, the database url connection has been changed from
            jdbc:oracle:thin:@192.168.56.11:1521:<SID>
to
            jdbc:oracle:thin:@192.168.56.11:1521/<SERVICE NAME>
Hence, using Native(JDBC) method to connect to Oracle 12c database is not working as it only regconise the first format.

Migrating Spring Application from Tomcat 7.0 to JBoss EAP 5.1

It may appear quite obvious that any Spring based web application that works under Tomcat should be working when deployed to JBoss EAP 5.1. In fact, this is further than the truth. Most often than not, the Spring based web application would just spew out tones of exception messages. This is exactly what happened when I tried to deploy a Spring 3.0.5/Hibernate 3.5.6 web application to JBoss EAP 5.1. I did finally get the application to work but not after spending many hours of trial and error to figure out the exceptions. Here are my eventual fixes.

1. Removed all the XML parsers under WEB-INF/lib directory, i.e. xmlParsers, xml-api, xalan, xerces.

2. Added hibernate-validator-4.1.0.Final.jar to WEB-INF/lib directory. For some unknown reason, JBoss EAP 5.1 absolutely need this.

3. Add 2 entries to hibernate.properties to pass into Spring. See text in red below.

hibernate.properties

hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.show_sql=true
hibernate.bytecode.use_reflection_optimizer=true
#hibernate.cglib.use_reflection_optimizer=true
#hibernate.cache.provider_class=org.hibernate.cache.HashtableCacheProvider
hibernate.cache.use_second_level_cache=true
hibernate.cache.region.factory_class=net.sf.ehcache.hibernate.EhCacheRegionFactory
hibernate.cache.provider_class=org.hibernate.cache.EhCacheProvider
hibernate.hbm2ddl.auto=update
hibernate.generate_statistics=true
hibernate.jdbc.batch_size=20

hibernate.validator.autoregister_listeners=false

hibernate.validator.apply_to_ddl=false

With these changes, the web application finally works.