Friday, October 30, 2009

Setting up SMX4 failover with MySQL

This post is an update to my previous post on setting up SMX4 with high availability in mind.

Since the transition to Apache Felix Karaf the support for more database locking table back ends has expanded. Specifically you can now use Apache Derby, MySQL, or a generic JDBC connector. The Derby and Generic JDBC locks make use of the 'FOR UPDATE' feature common to many databases. The MySQL lock however makes use of the ' LOCK TABLES' directive, this is due to the 'FOR UPDATE' command not returning a ResultSet on more recent versions of MySQL (see some discussions on this here).

The following system.properties configuration will allow your Karaf based SMX4 instance to use a MySQL JDBC lock:

karaf.lock=true
karaf.lock.level=20
karaf.lock.delay=1000
karaf.lock.class=org.apache.felix.karaf.main.MySQLJDBCLock
karaf.lock.jdbc.url=jdbc:mysql://address:port/dbname
karaf.lock.jdbc.driver=com.mysql.jdbc.Driver
karaf.lock.jdbc.user=root
karaf.lock.jdbc.password=karaf
karaf.lock.jdbc.table=KARAF_LOCK
karaf.lock.jdbc.clustername=mycluster
karaf.lock.jdbc.timeout=30


Please note that the MySQL JDBC driver will have to be made available on the classpath.

5 comments:

cmoulliard said...

Hi Jamie,

Thanks for the tip. Can we create a cluster solution like that without using a DB (using a SAN, fileshare directory, ...) ?

Regards,

Charles

Jamie Goodyear said...

It's been done before however I'd tend to shy away from NFS. The simple lock file approach was implemented with the intent of using it on deployments where we have multiple karaf instances running on one host.

Is there a particular reason to not use a JDBC lock in your setup? I've tested with Derby, MySQL, and Oracle. If there is an additional database back end for JDBC that you'd like to see specific support for please add a feature request to Karaf's JIRA :)

Paul Park said...

What are your comments on using SAN for file locking in a HA clustered environment with many nodes (>2)?

What would be your choice in implementation for a high load system where hundred of thousands messages must be delivery quickly and without any msg loss. Would you choose SAN/file or JDBC?

Jamie Goodyear said...

I tend to be weary of relying on the file system lock - it all depends on the file system under the cluster... not all file systems are created equally (interesting issues can crop up from time to time when VMs have different file systems than the SAN they are hosted on (I remember one such problem of EXT3 on a NFS SAN...fun times)). I tend to use the file lock when I have multiple instances residing on the same host.

I have seen many instances of users using the JDBC lock mechanism for their large deployments - most of those are using Oracle as the back end database, however that is not a technical requirement just a result of having an available Oracle instance to use. MySQL, as described in this article, should provide just as good a back end support as Oracle -if not please feel free to submit a bug report to the project JIRA :) If there is a DB back end that you'd like to use that is not covered by the currently available Lock classes please submit an improvement request to Apache Karaf.

Borut BolĨina said...

If one wants to use MySql locking in plain Karaf, is it enough to put org.apache.felix.karaf.main-1.4.0.jar into karaf/lib/ext directory with the above configuration? And mysql-connector jar of course.