Database write performance test gadget __ Database

Today's work needs to write a small tool to test the write performance of the database, need to support multiple concurrency, and support the adjustment of transaction submission record size, so I wrote one in Java, a class, simpler but basic features have, the following look at the code implementation

Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import Java.sql.ResultSetMetaData;
Import java.sql.SQLException;
Import java.sql.Statement;
Import Java.sql.Timestamp;
Import Java.sql.Types;
Import java.util.ArrayList;
Import Java.util.Calendar;
Import java.util.Collections;
Import Java.util.Formatter;
Import Java.util.LinkedHashMap;
Import java.util.List;
Import Java.util.Map;
Import Java.util.Random;
Import Java.util.UUID;
Import Java.util.concurrent.CountDownLatch;
Import Java.util.logging.Level;
 
Import Java.util.logging.Logger;
 
    public class Inserttest {private static Logger Logger = Logger.getlogger (InsertTest.class.getName ());
    private static final String Db_driver = "Com.ibm.db2.jcc.DB2Driver";
    private static final String Db_url = "JDBC:DB2://9.111.251.152:50000/PADB";
    private static final String Db_username = "Db2inst";
 
    private static final String Db_password = "Letmein"; PrivAte static Random Random = new Random (10000);
 
    Private static final String table_name = "BENCHMARK";
 
    private int batchsize;
 
    private int concurrent;
 
    private int sampling;
 
        public static void Main (string[] args) throws Exception {Printheader ();
        int[] Concurrentlist = new Int[]{1, 5, 10, 20};
        int[] batchsizelist = new int[] {1000, 2000, 5000, 10000}; for (int concurrent:concurrentlist) {for (int batchsize:batchsizelist) {New Inserttest (b
            Atchsize, concurrent). Run (true);
        } thread.sleep (10000); } public inserttest (final int batchsize, final int concurrent) throws Exception {this.batchsize = Bat
        Chsize;
        This.concurrent = concurrent;
    this.sampling = 100; Public inserttest (final int batchsize, final int concurrent, final int sampling) throws Exception {this.b
        Atchsize = batchsize; This.concurrent = ConcurRent
    this.sampling = sampling; public void Run (Boolean printresult) throws Exception {final list<long> results = Collections.synch
        Ronizedlist (New arraylist<long> ());
        Final Countdownlatch startgate = new Countdownlatch (concurrent);
 
        Final Countdownlatch endgate = new Countdownlatch (concurrent);
                for (int idxconcurrent = 0; idxconcurrent < concurrent; idxconcurrent++) {New Thread (new Runnable () {
                    public void Run () {Startgate.countdown ();
                        try {long time = execute ();
                        Long avg = batchsize * sampling * 1000/TIME;;
                    Results.add (long.valueof (avg));
                    catch (Exception ex) {ex.printstacktrace ();
                    finally {Endgate.countdown ();
        }}). Start (); }
        Endgate.await ();
 
        Collections.sort (results);
        if (Printresult) {Printresult (batchsize, concurrent, results);
        } public long execute () throws Exception {Connection conn = getconnection ();
        map<string, integer> columns = QUERYTABLECOLUMNS (conn);
        String insertsql = generateinsertsql (columns);
        PreparedStatement PS = conn.preparestatement (insertsql);
            try {Long start = System.currenttimemillis ();
            for (int i = 0; i < sampling i++) {EXECUTE (conn, PS, columns);
            Long stop = System.currenttimemillis ();
        return stop-start;
            catch (Exception ex) {logger.log (level.severe, NULL, ex);
            Conn.rollback ();
            Conn.close ();
        Throw ex;
        finally {conn.close (); } public void execute (Connection conn, PreparedStatement PS, map<stRing, integer> columns) throws Exception {try {for (int idx = 0; idx < batchsize; idx++) {
                int idxcolumn = 1;
                        For (String Column:columns.keySet ()) {if (Column.equalsignorecase ("ID")) {
                    Ps.setobject (Idxcolumn, Uuid.randomuuid (). toString ());
                    else {ps.setobject (Idxcolumn, Generatecolumnvalue (columns.get (column));
                } Idxcolumn + +;
            } ps.addbatch ();
            } ps.executebatch ();
 
            Conn.commit ();
        Ps.clearbatch ();
            catch (SQLException ex) {logger.log (level.severe, NULL, ex);
            if (null!= ex.getnextexception ()) {logger.log (level.severe, NULL, ex.getnextexception ());
            } conn.rollback ();
        Throw ex; } private String Generateinsertsql (Map<string, integer> columns) throws SQLException {StringBuilder sb = new StringBuilder ();
        StringBuffer sbcolumns = new StringBuffer ();
 
        StringBuffer sbvalues = new StringBuffer ();
 
        Sb.append ("INSERT into"). Append (table_name); For (String Column:columns.keySet ()) {if (sbcolumns.length () > 0) {sbcolumns.append (","
                );
            Sbvalues.append (",");
            } sbcolumns.append (column);
        Sbvalues.append ("?");
        } sb.append ("("). Append (Sbcolumns). Append (")");
        Sb.append ("VALUES");
        Sb.append ("("). Append (Sbvalues). Append (")");
    return sb.tostring (); Private map<string, integer> Querytablecolumns (Connection conn) throws Exception {map<string, in
        teger> columns = new linkedhashmap<string, integer> ();
        String sql = "SELECT * FROM" + table_name + "WHERE 1=0"; Statement stmt = Conn.createstatemENT ();
        ResultSet rs = stmt.executequery (SQL);
        ResultSetMetaData RSMD = Rs.getmetadata (); for (int i = 1; I <= rsmd.getcolumncount (); i++) {Columns.put (Rsmd.getcolumnname (i), Rsmd.getcolumntype (i)
        );
    return columns;
        Private object Generatecolumnvalue (int type) {Object obj = null;
            Switch (type) {case Types.DECIMAL:case Types.NUMERIC:case types.double:
            Case Types.FLOAT:case Types.REAL:case Types.BIGINT:case types.tinyint:
                Case Types.SMALLINT:case Types.INTEGER:obj = Random.nextint (10000);
            Break
                Case Types.DATE:obj = Calendar.getinstance (). GetTime ();
            Break
                Case Types.TIMESTAMP:obj = new TIMESTAMP (System.currenttimemillis ());
            Break Default:oBJ = string.valueof (Random.nextint (10000));
        Break
    return obj;
        Private Connection getconnection () throws Exception {Class.forName (db_driver);
        Connection conn = drivermanager.getconnection (Db_url, Db_username, Db_password);
        Conn.setautocommit (FALSE);
    Return conn;
        private static void Printheader () {StringBuilder sb = new StringBuilder ();
        Sb.append ("\ n"); Sb.append (New Formatter (). Format ("%15s|%15s|%15s|%15s|%15s", "Batch_size", "CONCURRENT", "AVG (r/s)", "MIN (r/s)", "
        MAX (R/S)));
    System.out.println (Sb.tostring ()); private static void Printresult (int batch, int concurrent, list<long> results) {Long total = long.v
        alueof (0);
        for (Long result:results) {total + = result;
        StringBuilder sb = new StringBuilder (); Sb.append (New Formatter (). Format ("%15s|%15s|%15s|%15s|%15s", batch, concurrent, total/results.size ()), Results.get (0), Results.get (Results.size ()-1));
    System.out.println (Sb.tostring ()); }
}

To run the test requires
1. Modify the configuration information of the database
2. Modify table_name the name of the table to be tested in the specified database, and the test program queries the table definition to generate write SQL statements
3. Modify Concurrentlist Specifies that you want to test the concurrency list, the default test 1,5,10,20 concurrency
4. Modify Batchsizelist Specify transaction commit record data for each test, default is 1000,2000,5000,10000

The final run of the test will produce a result similar to the following

batch_size|     Concurrent|      AVG (r/s) |      MIN (r/s) |      MAX (r/s)
      1000|              1|            ...|            ...|            ...
      2000|              1|            ...|            ...|            ...
      5000|              1|            ...|            ...|            ...
     10000|              1|            ...|            ...|            ...
      1000|              5|            ...|            ...|            ...
      2000|              5|            ...|            ...|            ...
      5000|              5|            ...|            ...|            ...
     10000|              5|            ...|            ...|            ...