Database load testing. ContiPerf + DBUnit

Below is the experience of database load testing using JUnit and its associated DBUnit and ContiPerf.

Contiperf

ContiPerf is a utility that allows you to use JUnit4 for load tests. Easy to use, easy and versatile to configure. Uses Java annotations to set test settings and runtime requirements, creates a detailed report in the form of an html file with a graph of the distribution of runtime. Requires Java at least version 5 and JUnit at least version 4.7.

DBUnit

DBUnit is an extension for JUnit that simplifies testing of programs working with the database. However, it is quite popular and needs no introduction, so I will confine links: introduction for beginners , mention Habré .

Test

First, create a file with database connection parameters. Spring JavaConfig is used here and load testing requires a connection pool, which is the Tomcat JDBC Connection Pool. You can read about the latter and how to use it here and here . The pool was selected by the first principle with examples in the google list of the “connection pool” request.

@Configuration
@Profile("db1")
public class DBConfig {
    @Bean
    public javax.sql.DataSource.DataSource dataSource(){
        org.apache.tomcat.jdbc.pool.DataSource ds = new org.apache.tomcat.jdbc.pool.DataSource();
        ds.setDriverClassName("dbDriver");
        ds.setUrl("dbUrl");
        ds.setUsername("dbUser");
        ds.setPassword("");
        ds.setInitialSize(5);
        ds.setMaxActive(10);
        ds.setMaxIdle(5);
        ds.setMinIdle(2);
        return ds;
    }
}

Next, configure DBUnit. In total, two configuration classes will be obtained. The first - JavaConf - serves to describe the connection settings, if desired, to several databases, and further makes it easy to connect the desired database to the test. A class will be created below, upon inheriting from which it will be possible to use the already configured DBUnit functionality, and it will not be necessary to configure it every time. These classes can be used in a different way, not only during stress testing.

@ContextConfiguration(classes = DBConfig.class)
@ActiveProfiles("db1")
public class DBUnitConfig extends DBTestCase{
    @Autowired
    javax.sql.DataSource dataSource;
    protected IDatabaseTester tester;
    protected IDataSet beforeData;
    private TestContextManager testContextManager;
    @Before
    public void setUp() throws Exception {       
        this.testContextManager = new TestContextManager(getClass());
        this.testContextManager.prepareTestInstance(this);
        tester = new DataSourceDatabaseTester(dataSource);
    }    
    @Override
    protected IDataSet getDataSet() throws Exception {
        return beforeData;
    }
    @Override
    protected DatabaseOperation getTearDownOperation() throws Exception {
        return DatabaseOperation.NONE;
    }
}

It uses a little trick. The fact is that JUnit allows you to use the @RunWith annotation only once, which will be used later, which in turn does not allow you to use the classic @RunWith (SpringJUnit4ClassRunner.class). Therefore, you need to manually create an instance of the context and pull it up with this piece of code (c) :

// танец с бубном
 this.testContextManager = new TestContextManager(getClass());
 this.testContextManager.prepareTestInstance(this);

To understand the type of magic used here, you should refer to the documentation of the corresponding class.

Finally, we go directly to the test class:

// как и обещано RunWith использован, здесь для параллельного выполнения тестовых методов
@RunWith(ParallelRunner.class)
//  настройки нагрузочного теста: продолжительность в мс, количество потоков, 
//  время ожидания между тестами в мс (случайно выбирается из указанного интервала)
@PerfTest(duration = 10000, threads = 50, timer = RandomTimer.class, timerParams = { 5, 15 })
// требования к тесту: максимальное и среднее время выполнения, не является обязательным полем
@Required(max = 500, average = 100)
public class PerfTest extends DBUnitConfig {
// активация ContiPerf
    @Rule
    public ContiPerfRule i = new ContiPerfRule();
// метод для инициализации данных необходимых для теста
    @Before
    public void setUp() throws Exception
    {
        super.setUp();
// тривиальный пример xml файла набора данных: ""
        beforeData = new FlatXmlDataSetBuilder().build(
                Thread.currentThread().getContextClassLoader()
                        .getResourceAsStream("initData.xml"));
        tester.setDataSet(beforeData);
        tester.onSetup();
    }
// тестовый метод
    @Test
    public void test1()
    {
        Connection sqlConnection = null;
        CallableStatement statement = null;
        ResultSet set = null;
        try {
            sqlConnection = tester.getConnection().getConnection();
            sqlConnection.setAutoCommit(true);            
            statement = sqlConnection.prepareCall("select smth from tbl where param=?");
            statement.setString(1, "prm");
            set = statement.executeQuery(); set.next();
            int smth1 = set.getInt(1);
            statement.close();            
            statement = sqlConnection.prepareCall("{ ? = call pkg.function(?) }");
            statement.registerOutParameter(1, (Types.INTEGER));
            statement.setString(2, "prm");
            statement.execute();
            int smth2 = statement.getInt(1);
            statement.close();
        }
        catch(Exception ex){
            System.out.print(ex.getMessage()); }
        finally {
            try {
                set.close();
                statement.close();
                sqlConnection.close();}
            catch (Exception e){
                System.out.print(e.getMessage());}
        }
        Assert.assertEquals(smth1, smth2);
    }
}

In catch, as an idea to add-ons, you can add an error counter and display their number in a method with an after annotation.
More settings for ContiPerf can be found on the official page : for example, in the @PerfTest annotation you can specify not the duration of the test, but the number of its execution using the invocations parameter.
A brief report for each test method is written in the console after execution, a more detailed report (an example is given below) with detailed statistics is in the file target / contiperf-report / index.html.

To solve this problem, at the beginning there was an attempt to use the tool for testing SoapUI and JDBC requests, but there was a problem - a frequent, methodical error: “java.sql.SQLException: Listener refused the connection with the following error: ORA-12519, TNS: no appropriate service handler found ”- not finding a solution. Later, another solution was found using another tool - JMeter, but too late ...

PS: Comments, complaints, additions, objections, parting words are welcome and very useful.