Some SQL performance testing today?
Most performance testing scenarios involve an application or an API presented over the HTTP or HTTPS protocol. The Real Load performance testing framework is capable of supporting essentially any type network application, as long as there is a way to generate valid client requests.
Real Load testing scripts are Java based applications that are executed by our platform. While our portal offers a wizard to easily create tests for the HTTP protocol, you can write write a performance test application for any network protocol by implementing such a Java based application.
This article illustrates how to prepare a simple load test application for a non-HTTP application. I’ve chosen to performance test our lab MS-SQL server. What I want to find out is how the SQL server performs if multiple threads attempt to update data stored in the same row. While the test sounds academic, this is a scenario I’ve seen leading to performance issues in real life applications…
Requirements
Key requirements to implement such an application are:
- You’ll need Java client libraries (… and related dependencies) implementing the protocol you want to test. In this case I’ll use MicroSoft’s JDBC driver and Hikari as the SQL connection pool manager.
- You’ll need to determine what logic your load test application should execute. In this example, I’ll run an update SQL statement.
- You’ll need to determine the metrics you want to measure during test execution. We’ll collect time to obtain a connection from the pool and the time to execute the SQL operation.
- Make sure the Measuring Agent has network access to the service to be tested (… MS-SQL DB in this case).
- Last, you’ll need some Java skills to put together the load testing application or access to somebody that will do that for you.
Step 1 - Implement the test script as a Java application
Using your preferred Java development environment, create a project and add the following dependencies to it:
- DKFQSTools.jar - Required for all performance testing applications
- mssql-jdbc.jar (The MS-SQL JDBC driver)
- hikari-cp.jar (JDBC connection pooling)
- slf4j-api.jar (Required by Hikari)
In NetBeans, the dependencies section would look as follows:
Once the dependencies are configured in your project, we’ll implemented the test logic (the AbstractJavaTest interface). For this application, we’ll create the below class.
Of particular relevance are these methods:
- declareStatistics(): This is where you declare statistics metrics to be collected as the test is executed.
- executeUserSession(): This method is invoked for every virtual user to be simulated. Note the SQL update statement that will be executed as part of this test script.
MSSQLTest.java
import com.dkfqs.tools.javatest.AbstractJavaTest;
import com.dkfqs.tools.javatest.AbstractJavaTestPeriodicThread;
import com.dkfqs.tools.javatest.AbstractJavaTestPeriodicThreadInterface;
import com.dkfqs.tools.logging.CombinedLogAdapter;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.pool.HikariPool;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Instant;
import javax.sql.DataSource;
@AbstractJavaTest.ResourceFiles(fileNames = {})
public class MSSQLTest extends AbstractJavaTest implements AbstractJavaTestPeriodicThreadInterface {
private static HikariPool pool;
private static HikariDataSource dataSource = null;
/**
* Static Main: Create a new instance per simulated user and execute the
* test.
*
* @param args the command line arguments
*/
public static void main(String[] args) throws SQLException, NoSuchFieldException, IllegalArgumentException, IllegalAccessException {
stdoutLog.message(LOG_INFO, "Max. Java Memory = " + (Runtime.getRuntime().maxMemory() / (1024 * 1024)) + " MB");
dataSource = new HikariDataSource();
dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
dataSource.setJdbcUrl("jdbc:sqlserver://192.168.11.61:1433;databaseName=DEMO_DB;multiSubnetFailover=true;applicationName=RealLoad");
dataSource.setUsername("sqluser");
dataSource.setPassword("password");
dataSource.setMinimumIdle(100);
dataSource.setMaximumPoolSize(2000);
dataSource.setAutoCommit(true);
dataSource.setLoginTimeout(3);
dataSource.setConnectionTimeout(3000);
java.lang.reflect.Field field;
field = dataSource.getClass().getDeclaredField("pool");
field.setAccessible(true);
pool = (HikariPool) field.get(dataSource);
// log test specific resource files, annotated by @AbstractJavaTest.ResourceFiles at class level
logTestSpecificResourceFileNames(MSSQLTest.class);
try {
// get all generic command line arguments
abstractMain(args);
// create a new instance per simulated user
for (int x = 0; x < getArgNumberOfUsers(); x++) {
new MSSQLTest(x + 1);
}
// start the test
stdoutLog.message(LOG_INFO, "[Start of Test]");
try {
// start the user threads
startUserThreads();
// wait for the user threads end
waitUserThreadsEnd();
} catch (InterruptedException ie) {
stdoutLog.message(LOG_WARN, "Test aborted by InterruptedException");
}
stdoutLog.message(LOG_INFO, "[End of Test]");
} catch (Exception ex) {
stdoutLog.message(LOG_FATAL, "[Unexpected End of Test]", ex);
} finally {
closeOutputFiles();
}
}
/**
* Close all output files.
*/
private static void closeOutputFiles() {
}
// - - - vvv - - - instance - - - vvv - - -
private CombinedLogAdapter log = new CombinedLogAdapter();
/**
* Constructor: Create a new instance per simulated user.
*
* @param userNumber the simulated user number
* @throws IOException if the user statistics out file cannot be created
*/
public MSSQLTest(int userNumber) throws IOException {
super(userNumber);
addSimulatedUser(this);
}
@Override
public void declareStatistics() {
declareStatistic(0, STATISTIC_TYPE_SAMPLE_EVENT_TIME_CHART, "Get connection from pool", "", "Execution Time", "ms", 0, true, "");
declareStatistic(1, STATISTIC_TYPE_SAMPLE_EVENT_TIME_CHART, "Exec SQL Update stmnt ", "", "Execution Time", "ms", 1, true, "");
}
@Override
public void executeUserStart(int userNumber) throws Exception {
// start a periodic thread that reports summary measurement results measured across all simulated users
if (userNumber == 1) {
AbstractJavaTestPeriodicThread periodicThread = new AbstractJavaTestPeriodicThread(this, 1000L, this);
periodicThread.setName("periodic-thread");
periodicThread.setDaemon(true);
periodicThread.start();
}
}
@Override
public int executeUserSession(int userNumber, int sessionLoopNumber) throws Exception {
long measurementGroupStartTime$0 = System.currentTimeMillis();
registerSampleStart(0);
// 1- Get a connection from pool
Connection connection = null;
try {
connection = dataSource.getConnection();
} catch (Exception e) {
log.message(LOG_ERROR, e.getMessage());
return SESSION_STATUS_FAILED;
}
addSampleLong(0, System.currentTimeMillis() - measurementGroupStartTime$0);
// 2 - Prepare SQL statement
Statement st = connection.createStatement();
String SQL = "update TEST_TABLE set VALUE_NUM = '7058195060625506304' where DATA_URI = '2566' AND DATA_URI = '0' AND DATA_ID = '-1'";
// 3 - Execute statement
registerSampleStart(1);
long measurementGroupStartTime$1 = System.currentTimeMillis();
st.executeUpdate(SQL);
addSampleLong(1, System.currentTimeMillis() - measurementGroupStartTime$1);
st.close();
connection.close();
// end of passed session
return SESSION_STATUS_SUCCESS;
}
@Override
public void executeUserSessionEnd(int sessionStatus, int userNumber, int sessionLoopNumber) throws Exception {
}
/**
* Called periodically by an independent thread with the context of the
* first simulated user. Reports summary measurement results which were
* measured over all simulated users.
*
* @param abstractJavaTest the context of the first simulated user
* @throws Exception if an error occurs - logged to stdout
*/
@Override
public void onPeriodicInterval(AbstractJavaTest abstractJavaTest) throws Exception {
}
@Override
public void onUserSuspend(int userNumber) throws Exception {
}
@Override
public void onUserResume(int userNumber) throws Exception {
}
@Override
public void executeUserEnd(int userNumber) throws Exception {
}
@Override
public void onUserTestAbort(int userNumber) throws Exception {
}
}
Step 2 - Upload Java app and dependencies to Real Load portal
Once you’ve compiled your application and generated a jar file (… make sure the main class is mentioned in the META-INF/MANIFEST.MF file) you’re ready to configure the load test in the Real Load portal.
After logging into the portal, create a new project (… “MSSQL” in the below screenshot) and a new Resource Set (“Test 1”). Upload your performance test application jar file (“RealLoadTest3.jar” in this example) and all other dependencies.
Once everything is uploaded, define a new test by clicking on the Resource Set (“Test 1”). Make sure you select the .jar file you’ve developed as the “Executing Script” and tick all the required dependencies in the Resource list.
Step 3 - Execute load test
You’re now ready to execute your performance test. When starting the test job select how many threads (Users) should execute you test application, ramp up time and test execution duration.
While the performance test is executing, you’ll notice that the metrics you’ve declared in the Java source code appear in the real time monitoring window:
If you keep an eye on MS-SQL Management Studio, in the activity monitor you’ll notice that resource locking is the wait class with the highest wait times. Not so surprisingly I might add, given the nature of the test.
Also note that the waiting task number is very close to the number of virtual users (concurrent threads) simulated, approx. 100.
Once the test completed, you can review collected metrics. The graph at the bottom of this screenshot shows execution times throughout the test of the SQL update statement, as load ramped up.
Summarizing….
As you can see, it’s quite straightforward to prepare an application to performance test almost any network protocol.
Should you have a requirement to performance test an exotic protocol and your current tool doesn’t allow you to do so, do not hesitate to contact us. Perhaps we can help…
Thank you for reading and do not hesitate to reach out should you have any Qs.