Application of JDBC in Performance Testing

Author: Huang Yandi

foreword

Can we bypass the http protocol and directly test the performance of the database? Do you find it troublesome to export CSV files from the database to construct stress measurement data? How to do data cleaning after the stress test is over? Can you make assertions on stress testing requests through insert (delete) records in the database? Using the Alibaba Cloud performance testing tool PTS can easily solve the above problems.

what is JDBC

JDBC (Java DataBase Connectivity, Java Database Connectivity) is a Java API for executing SQL statements that can provide unified access to a variety of relational databases. It consists of a set of classes and interfaces written in the Java language. JDBC provides a benchmark upon which to build more advanced tools and interfaces that enable database developers to write database applications.

Simply put, JDBC can do three things: establish a connection to a database, send statements that manipulate the database, and process the results.

JDBC Design Principles

Overall structure

JDBC has developed a set of standards for interacting with databases, and database manufacturers provide the implementation of this set of standards, so that various databases can be connected through a unified JDBC interface. It can be said that the role of JDBC is to shield the difference of the underlying database, so that the code written by the user according to JDBC can be executed on various databases. So how is this achieved? As shown below:

JDBC defines the Driver interface. This interface is the driver of the database. All operations dealing with the database will ultimately come down to this. The database manufacturer must implement this interface, and through this interface, the caller of the upper-layer application can interact with the underlying specific database. . The Driver is registered through the DriverManager provided by JDBC. The registration code is written in the static block of the Driver. For example, the registration code of MySQL is as follows:

 static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

As a driver-defined specification Driver, its main purpose is to establish a connection with the database, so its interface is also very simple, as shown below:

 public interface Driver {
  //建立连接
    Connection connect(String url, java.util.Properties info)
        throws SQLException;
    boolean acceptsURL(String url) throws SQLException;
    DriverPropertyInfo[] getPropertyInfo(String url, java.util.Properties info)
                         throws SQLException;
    int getMajorVersion();
    int getMinorVersion();
    boolean jdbcCompliant();
    public Logger getParentLogger() throws SQLFeatureNotSupportedException;
}

As the manager of the Driver DriverManager, it is not only responsible for the registration/deregistration of the Driver, but also can directly obtain the connection. How does it do it? Observe the following code and find that it is actually by traversing the registered Drivers, finding a Driver that can successfully establish a connection, and returning the Connection. The DriverManager is like a proxy, and the process of actually establishing a connection is still handed over to the specific Driver.

 for(DriverInfo aDriver : registeredDrivers) {
            // If the caller does not have permission to load the driver then
            // skip it.
            if(isDriverAllowed(aDriver.driver, callerCL)) {
                try {
                    println("    trying " + aDriver.driver.getClass().getName());
                    Connection con = aDriver.driver.connect(url, info);
                    if (con != null) {
                        // Success!
                        println("getConnection returning " + aDriver.driver.getClass().getName());
                        return (con);
                    }
                } catch (SQLException ex) {
                    if (reason == null) {
                        reason = ex;
                    }
                }

            } else {
                println("    skipping: " + aDriver.getClass().getName());
            }

        }

Connection Design

From the previous section, we know that the database provider provides services to users by implementing the Driver interface. The core method of the Driver interface is to obtain a connection. Connection is the core interface for dealing with databases. Let's take a look at its design.

By observing the design diagram, we found that there are mainly two types of interfaces: DataSource and Connection. Below we will introduce them one by one.

• DataSource

Looking directly at the source code, as shown below, it is found that its core method is the same as that of Driver, which is to obtain a connection. So why bother with a DataSource? Isn't the Driver itself getting the connection? Let's take a look at how the DataSource gets the connection.

 public interface DataSource  extends CommonDataSource, Wrapper {
  Connection getConnection() throws SQLException;
  Connection getConnection(String username, String password)
    throws SQLException;
}

However, we found that JDBC only defines the interface of DataSource, and does not give a specific implementation. Let's take the SimpleDriverDataSource implemented by Spring as an example to see how it does it. The code is as follows, and find the getConnection(.. .) method, and finally it is still handed over to driver.connect(...) to actually establish a connection. So back to what we described at the beginning, Driver is the real interface for dealing with the database.

 protected Connection getConnectionFromDriver(Properties props) throws SQLException {
        Driver driver = getDriver();
        String url = getUrl();
        Assert.notNull(driver, "Driver must not be null");
        if (logger.isDebugEnabled()) {
            logger.debug("Creating new JDBC Driver Connection to [" + url + "]");
        }
        return driver.connect(url, props);
    }

So the question is, why do you need an interface like DataSource? Obviously not. DataSource is an enhanced version of Driver. It entrusts the core process of establishing a connection to the Driver, and handles things that seem to have nothing to do with establishing a connection, such as establishing a cache, handling distributed transactions and connection pooling. As shown in the design diagram of the class, take the Druid connection pool used by PTS as an example:

• ConnectionPoolDataSource: The implementation of the connection pool. This data source implementation does not directly create a database physical connection, but a logical implementation. Its role is to pool the database physical connection.
• PooledConnection: With ConnectionPoolDataSource, it obtains a pooled object PooledConnection, and then indirectly obtains the physical connection through the PooledConnection.

Obviously, through the connection pool, we can get away from the management of the connection, improve the utilization efficiency of the connection, and also improve the pressure capacity of the press.

Statement design

After the connection is established, the user may start writing SQL statements, which are then executed by the database. These are achieved through Statement. mainly divided:

• Statement: Define a static SQL statement. The database needs to be recompiled every time it is executed. Generally, it is used to execute a query only once and return the result.
• PreparedStatement: Defines a precompiled SQL statement with parameters. The next time it is executed, the subsequent statements will be retrieved from the cache without recompiling. It is suitable for executing SQL statements with the same logic multiple times. Of course, it also has Anti-SQL injection and other functions have high security and efficiency, and are frequently used. For performance testing, choosing PreparedStatement is the most appropriate.
• CallableStatement: used to call stored procedures.

ResultSet design

JDBC uses the ResultSet interface to accept the execution result of the Statement. ResultSet uses the pointer method (next()) to obtain the retrieval results one by one. When the pointer points to a certain piece of data, the user can freely choose to obtain the data of a certain column. By converting the ResultSet into a CSV file, PTS assists the user to construct complex pressure measurement data with a single SQL statement.

JDBC Architecture Summary

Through the above introduction, we found that the design of JDBC is still layered.

(1) Driver and DriverManager are database-oriented, and a set of specifications for accessing data in Java has been designed, and database vendors only need to implement this set of specifications;

(2) DataSource and Connection are for application developers. They don't care about how JDBC interacts with the database. The Connection can be obtained through the unified DataSource interface, and the user's data operations can be realized through this Connection. ;

(3) Statement carries specific SQL commands, and users can define different Statements to send instructions to the database;

(4) ResultSet is used to carry the execution result of the SQL command.

So far, the whole process of interacting with the database, such as loading the driver -> establishing a connection -> executing the command -> returning the result, is completed. If this process is flexibly embedded in the PTS performance test, the various problems mentioned in the preface can be solved.

Application of JDBC in Performance Testing

Database performance test

• background

Most operations on the database are performed over HTTP, FTP, or other protocols, but in some cases it also makes sense to test the database directly, bypassing the intermediary protocol. For example, we want to not fire all relevant queries, but only test the performance of specific high-value queries; verify the performance of a new database under high load. 2. Verify certain database connection pool parameters, such as the maximum number of connections 3. Save time and resources. When we want to optimize SQL, it is very cumbersome to modify SQL statements and other database operations in the code. Through JDBC stress testing, we can avoid intrusion into the code and focus on SQL tuning.

• step

1. Create a scene. We create a PTS stress test scene in the [Stress Test Center] -> [Create Scenario] of the PTS console;

2. Scene configuration. PTS supports stress testing of four databases including MySQL and PostgreSQL. The user can initiate a stress test by filling in the JDBC URL, user name, password and SQL. At the same time, PTS also supports extracting the data in the ResultSet as an output parameter for use by the downstream API; making assertions on the response.

3. Monitoring and stress testing reports during stress testing. PTS supports binding Alibaba Cloud RDS cloud resource monitoring, and observes RDS real-time performance indicators during the stress test process. In addition, PTS also provides clear and complete stress test reports and sampling logs for users to view at any time.

Pressure measurement data structure

• background

In scenarios such as simulating different user logins and transferring parameters of stress testing services, the parameter function needs to be used to implement various dynamic operations in stress testing requests. If you use the traditional CSV file parameters, you will be limited by the file size and will be laborious to create manually. Using JDBC to construct pressure measurement data can avoid the above problems.

• step

1. Add a data source. In Scene Edit - Data Source Management, select Add DB Data Source, enter URL, Username, Password and SQL.

2. Add parameters. Fill in the custom parameter name and column index.

3. Debug verification. Click Debug Scenario to verify that the extracted result set is as expected. Then, we can use ${} references anywhere we want to use parameters.

Pressure measurement dirty data cleaning

• background

Stress testing for write requests will generate a large amount of dirty data in the database. How to automatically clean up after the stress test is over?

• step

PTS provides users with solutions. PTS supports the logical ordering of serial links, namely pre-link, normal link and post-link. The order of execution is first to last. Set a serial link as a rear link, and fill in the number of cycles.

For more communication, welcome to the DingTalk group to communicate, PTS user exchange DingTalk group number: 11774967.

In addition, PTS has recently made a new upgrade to the sales method, and the price of the basic version has dropped by 50%! The price of 5W concurrency is only 199, eliminating the trouble of self-operation and maintenance pressure testing platform! There are also 0.99 trial version for new users and VPC stress test exclusive version, welcome to buy!