I’ve had the pleasure of doing some performance testing against our supported databases lately. It’s really interesting to see concrete performance metrics to compare the impact that the database has on the performance of Flux.

What did we find? Well, we’ve used MySQL internally with Flux and have been very satisfied with the performance. The metrics show why too, MySQL is the fastest database to use with Flux. Of course, HSQLDB (http://hsqldb.sourceforge.net/) is faster, but an in-memory database does not work for all solutions.

A bit of tweaking is required for DB2 and SQL Server 2005 to avoid deadlocks. Flux is a highly concurrent application that executes many transactions simultaneously. You will most likely encounter deadlocks with the default configuration of DB2 or SQL Server 2005 as the default settings of these databases do not work well with highly concurrent applications.

For DB2, setting the DB2_SKIPINSERTED, DB2_SKIPDELETED, and DB2_EVALUNCOMMITTED system registry options in DB2 will help. Also, regularly executing RUNSTATS on all Flux tables will improve performance.

For SQL Server 2005, the new row versioning feature helps out significantly with deadlocks and performance. This feature makes SQL Server 2005 operate much like Oracle, which allows quick SELECT statements and does not block SELECT statements when waiting for uncommitted transactions. Information about enabling row versioning can be found at: http://msdn2.microsoft.com/en-us/library/ms175095.aspx. SQL Server 2000 is not officially supported, but we did run Flux against it and resolved some deadlocks encountered.

We resolved deadlocks with DB2 and SQL Server 2005 as well while gathering performance metrics. All of the deadlock fixes will be in Flux 7.4.2, due out on Tuesday, and also Flux 7.5, which is due mid-September.

We’ll be posting official performance metrics to our website soon. But to give you a general idea, DB2 is the slowest. In comparison to DB2: Oracle is 66% faster, SQL Server 2005 is 70% faster, MySQL is 79% faster, and HSQLDB is 90% faster. These are not final numbers, and not very specific, but they do give a general idea as to what database perform Flux performs best with.