We all know the devil is in the details when it comes to technology.
Yet, our recent vFabric SQLFire performance test (a benchmark from vFabric SQLFire Best Practices Guide) is certainly worth review if you need to scale a Java app, .NET app, or other legacy data source.
- Apache Derby is used for its RDBMS components, JDBC driver, query engine, and network server.
- The partitioning technology of GemFire is used to implement horizontal partitioning features of vFabric SQLFire.
- vFabric SQLFire specifically enhances the Apache Derby components, such as the query engine, the SQL interface, data persistence, and data eviction, as well as adding additional components like SQL commands, stored procedures, system tables, functions, persistence disk stores, listeners, and locators, to operate a highly distributed and fault tolerant data management cluster.
What impact can SQLFire have on my Java or .NET Application?
In short, SQLFire can have an order of magnitude impact on response time (R/T) and scale.
Well, if you are working with a traditional RDBMS, the writing is on the wall.
One might even profess that the “DBA” job title is probably going to evolve into a “Data Grid Administrator,” “Data Fabric Administrator,” “Data Service Administrator,” or something like that. This might happen because the traditional, disk-based RDBMS model cannot compete with an in-memory data fabric or distributed data platform on cost or scale. Eventually, economics will push every business and IT shop in a new data platform direction, and our job titles may go along with this trend.
The graph above is a powerful depiction of just how much better response time is in SQLFire at scale as compared to a traditional RDBMS. Response time increases along the vertical axis, and concurrent number of threads increases along the horizontal axis. SQLFire’s response time actually gets better and, then, is nearly constant as threads are added while the traditional RDBMS response slows almost linearly with an increase in concurrent threads.
(Of course, more about the application and methodology of the test is further below. These comparisons were intended to be as even and objective as possible.)
In the graph above, we see an expansion of the previous graph but from a different perspective. Response time increases along the vertical axis, and concurrent number of threads increases along the horizontal axis. The test results show how SQLFire scales to 7200 threads with a 1 second response time (in blue). The traditional RDBMS fails and cannot respond at 1850 threads. Notice that SQLFire’s response time is close to zero at over 3000 threads, well past the failure point of the RDBMS.
Similar to the previous graph, in the graph above, we see how SQLFire scales to 7200 threads with a 1 second response time (in blue) and 98% CPU utilization while the traditional RDBMS fails at 1850 threads with almost 80% CPU utilization.
In the table above, we see the actual data points comparing SQLFire and traditional RDBMS on response time and CPU % by number of threads. Failed means no data could be collected because the disk-based RDBMS failed to respond.
About the SQLFire vs RDBMS Test and Information Above
This test simulated a customer scenario with no specific assumptions about specialized tuning. Essentially, this is the type of test a software engineer might attempt when conducting an evaluation of vFabric SQLFire versus another RDBMS. In our approach, a load test was run against the Spring Travel application with the RDBMs as-is. Then the Spring Travel schema was converted to run against vFabric SQLFire, also without tuning, and the results were plotted side by side.
To demonstrate how quickly this change could be made without any code intrusion/change assumptions, the methodology also simulated how a developer might download the Spring Travel application, run the DDLUtils conversion utility to generate the vFabric SQLFire schema and data load file, then quickly test to see the performance improvements. (Note: The Apache DDLUtils utility is explained in the vFabric SQLFire Best Practices Guide so you can try this test yourself. DDLUtils allows you to move data from Axion, DB2, Derby/Cloudscape, Firebird, HSQLDB, Interbase, MaxDB/SapDB, McKoi, MySql, Oracle, PostgreSQL, SQL Server, and Sybase.)
The Spring Travel application can be downloaded from http://www.springsource.org and is found under the
booking-mvc project within a subdirectory of the Spring Web Flow project, identified as
To make this study as fair as possible, we also assumed that the total compute resources in both scenarios should be the exact same.
- A total of 4GB of RAM and 8 vCPUs in one virtual machine were dedicated to the traditional RDBMS, and the same amount of compute resources were dedicated to vFabric SQLFire.
- In the vFabric SQLFire case, two vFabric SQLFire virtual machines were used–each of the virtual machines had 4 vCPUs and 2GB of RAM, for a total of 4GB RAM and 8 vCPUs.
These compute resource requirements were the default for the RDBMS used to remain consistent with the objective of absolutely no tuning on any system, and the default compute resource requirements were not altered. This resembles what customer developers might simulate in a development environment on the first day of inspecting and evaluating vFabric SQLFire versus traditional RDBMS.
About The SQLFire Best Practices Guide
The SQLFire Best Practices Guide is an excellent resource for anyone looking to understand how this “distributed data grid world” works and provides a lot of detail for implementing and managing SQLFire specifically along with tuning SQLFire JVMs.
The guide is almost 100 pages and has 50 diagrams. It covers:
- Topologies, Server Groups, Partitioning, Redundancy, and Colocation
- Disk Persistence, Transactions, and how SQLFire can act as a Cache, Persist Data to Disk, and work with existing data stores like in these examples
- DDLUtils (as mentioned above)
- ACID and Durability
- Design and Sizing
- GC Tuning
- Best Practices on vSphere
- An Example Performance Test (as shared above)
- Troubleshooting and Monitoring
The guide was led by Emad Benjamin and includes contributions by many of the VMware engineers who contribute to this blog. Their collective experience encompasses over 100 years of work with large, distributed data stores, and virtualized solutions.
|>> Download the SQLFire Best Practices Guide here.
>> Read more articles on SQLFire.
>> Download or try SQLFire.