MariaDB Java Connector Driver Performance

MARIADB JAVA CONNECTOR PERFORMANCE

We always talk about performance. But the thing is always “Measure, don’t guess!”. A lot of performance improvement has been done lately on the MariaDB Java Connector. So, what the current driver performance? Let me share a benchmark result of 3 jdbc drivers permitting access to a MySQL/MariaDB database: DrizzleJDBC, MySQL Connector/J and MariaDB java connector. Driver’s versions are the latest GA available version at the time of writing this blog:

• MariaDB 1.5.3
• MySQL 5.1.39
• Drizzle 1.4

THE BENCHMARK

JMH is an Oracle micro-benchmarking framework tool developed by Oracle, delivered as openJDK tools, that will be the official java 9 microbenchmark suite. Its distinctive advantage over other frameworks is that it is developed by the same guys in Oracle who implement JIT (Just In Time compilation) and permit to avoid most of micro-benchmark pitfalls. Benchmark source: https://github.com/rusher/mariadb-java-driver-benchmark. Tests are pretty straightforward if you are familiar with java. Example:

public class BenchmarkSelect1RowPrepareText extends BenchmarkSelect1RowPrepareAbstract {

    @Benchmark
    public String mysql(MyState state) throws Throwable {
        return select1RowPrepare(state.mysqlConnectionText, state);
    }

    @Benchmark
    public String mariadb(MyState state) throws Throwable {
        return select1RowPrepare(state.mariadbConnectionText, state);
    }
  
    @Benchmark
    public String drizzle(MyState state) throws Throwable {
        return select1RowPrepare(state.drizzleConnectionText, state);
    }
  
}

public abstract class BenchmarkSelect1RowPrepareAbstract extends BenchmarkInit {
    private String request = "SELECT CAST(? as char character set utf8)";

    public String select1RowPrepare(Connection connection, MyState state) throws SQLException {
        try (PreparedStatement preparedStatement = connection.prepareStatement(request)) {
            preparedStatement.setString(1, state.insertData[state.counter++]);
            try (ResultSet rs = preparedStatement.executeQuery()) {
                rs.next();
                return rs.getString(1);
            }
        }
    }
}

Tests using INSERT’s queries are sent to a BLACKHOLE engine with the binary log disabled, to avoid IO and dependency on the storage performance. This permit to have more stable results. (Without using the blackhole engine and disabling binary log, execution times would vary up to 10%). Benchmark have been executed on MariaDB Server 10.1.17 and MySQL Community Server 5.7.13 databases. The following document show results using the 3 drivers with MariaDB Server 10.1.17. For the complete results including the ones with MySQL Server 5.7.13, please see the link at the bottom of the document.

ENVIRONMENT

Execution (client and server) is done on a single server droplet on digitalocean.com using the following parameters:

• Java(TM) SE Runtime Environment (build 1.8.0_101-b13) 64bits (actual last version when running this benchmark)
• Ubuntu 16.04 64bits
• 512Mb memory
• 1 CPU
• database MariaDB “10.1.17-MariaDB”, MySQL Community Server build “5.7.15-0ubuntu0.16.04.1” using default configuration files and these additional options :
  • max_allowed_packet = 40M #exchange packet can be up to 40mb
  • character-set-server = utf8 #to use UTF-8 as default
  • collation-server = utf8_unicode_ci #to use UTF-8 as default

When indicated “distant”, benchmarks are runs with separate client and server on 2 identical hosts on same datacenter with an average ping of 0.350ms.

RESULTS SAMPLE EXPLANATIONS

Benchmark                                           Score     Error  Units
BenchmarkSelect1RowPrepareText.mariadb              62.715 ±  2.402  µs/op
BenchmarkSelect1RowPrepareText.mysql                88.670 ±  3.505  µs/op
BenchmarkSelect1RowPrepareText.drizzle              78.672 ±  2.971  µs/op

This means that this simple query will take an average time of 62.715 microseconds using the MariaDB driver with a variation of ± 2.402 microseconds for 99.9% of queries. Same execution using drizzle driver will take an average time of 88.670 microseconds, and 78.672 microseconds using MySQL connector(smaller execution time the better). Displayed percentages are set according to the mariadb first result as reference (100%), permitting to easily compare other results.

PERFORMANCE COMPARISONS

The benchmark will test the performances of the 3 main different behaviour using a same local database (same server), and a distant database (another identical server) on same datacenter with an average ping of 0.450ms Different behaviours:

Text protocol

This corresponds to option useServerPrepStmts disabled. Queries are sent directly to the server with sanitized parameters replacement done on client side. Data is sent like text. Example: A timestamp will be sent like text “1970-01-01 00:00:00.000500” using 26 bytes

Binary protocol

This corresponds to the option useServerPrepStmts enabled (default implementation on MariaDB driver). Data is sent in binary. Example timestamp “1970-01-01 00:00:00.000500” will be sent using 11 bytes. There are up to 3 exchanges with the server for one query :

1. PREPARE – Prepares statement for execution.
2. EXECUTE – Send parameters
3. DEALLOCATE PREPARE – Releases a prepared statement.

See Server prepare documentation for more information. PREPARE results are stored in cache on driver side (default size 250). If Prepare is already in cache, PREPARE will not be executed, DEALLOCATE will be executed only when PREPARE is not used anymore and not in cache. That means that some query execution will have 3 round trips, but some will just have one round trip, sending a PREPARE identifier and parameters.

Rewrite

This corresponds to the option rewriteBatchedStatements enabled. Rewrite uses the text protocol and concern only batches. The driver will rewrite the query for faster results. Example: Insert into ab (i) values (?) with first batch values [1] and [2] will be rewritten to Insert into ab (i) values (1), (2). If query cannot be rewritten in “multi-values”, rewrite will use multi-queries : Insert into table(col1) values (?) on duplicate key update col2=? with values [1,2] and [2,3] will be rewritten to Insert into table(col1) values (1) on duplicate key update col2=2;Insert into table(col1) values (3) on duplicate key update col2=4 Downsides of this option are:

• Auto increment ids cannot be retrieved usingStatement.html#getGeneratedKeys().
• Multi-queries in one execution are enabled. That’s not a problem forPreparedStatement, but if the application uses Statement that can be a security degradation (SQL injection).

* MariaDB and MySQL have those 3 behaviours implemented, Drizzle only the Text protocol.

BENCHMARK RESULTS

MariaDB driver results

SINGLE SELECT QUERY

private String request = "SELECT CAST(? as char character set utf8)";

public String select1RowPrepare(Connection connection, MyState state) throws SQLException {
    try (PreparedStatement preparedStatement = connection.prepareStatement(request)) {
        preparedStatement.setString(1, state.insertData[state.counter++]); //a random 100 bytes.
        try (ResultSet rs = preparedStatement.executeQuery()) {
            rs.next();
            return rs.getString(1);
        }
    }
}

LOCAL DATABASE:
BenchmarkSelect1RowPrepareHit.mariadb               58.267 ±  2.270  µs/op
BenchmarkSelect1RowPrepareMiss.mariadb             118.896 ±  5.500  µs/op
BenchmarkSelect1RowPrepareText.mariadb              62.715 ±  2.402  µs/op

DISTANT DATABASE:
BenchmarkSelect1RowPrepareHit.mariadb               394.354 ±  13.102  µs/op
BenchmarkSelect1RowPrepareMiss.mariadb              709.843 ±  31.090  µs/op
BenchmarkSelect1RowPrepareText.mariadb              422.215 ±  15.858  µs/op

When the PREPARE result for this exact query is already in cache (cache hit), query will be faster (7.1% in this example) than using text protocol. Due to the additional request PREPARE and DEALLOCATE exchanges, cache miss is 68.1% slower. This emphasis the advantages and inconvenients of using a binary protocol. Cache HIT is important.  

SINGLE INSERT QUERY

private String request = "INSERT INTO blackholeTable (charValue) values (?)";

public boolean executeOneInsertPrepare(Connection connection, String[] datas) throws SQLException {
    try (PreparedStatement preparedStatement = connection.prepareStatement(request)) {
        preparedStatement.setString(1, datas[0]); //a random 100 byte data
        return preparedStatement.execute();
    }
}

LOCAL DATABASE:
BenchmarkOneInsertPrepareHit.mariadb                 61.298 ±  1.940  µs/op
BenchmarkOneInsertPrepareMiss.mariadb               130.896 ±  6.362  µs/op
BenchmarkOneInsertPrepareText.mariadb                68.363 ±  2.686  µs/op

DISTANT DATABASE:
BenchmarkOneInsertPrepareHit.mariadb                379.295 ±  17.351  µs/op
BenchmarkOneInsertPrepareMiss.mariadb               802.287 ±  24.825  µs/op
BenchmarkOneInsertPrepareText.mariadb               415.125 ±  14.547  µs/op

Results for INSERTs are similar to SELECTs results.

BATCH : 1000 INSERT QUERY

private String request = "INSERT INTO blackholeTable (charValue) values (?)";

public int[] executeBatch(Connection connection, String[] data) throws SQLException {
  try (PreparedStatement preparedStatement = connection.prepareStatement(request)) {
    for (int i = 0; i < 1000; i++) {
      preparedStatement.setString(1, data[i]); //a random 100 byte data
      preparedStatement.addBatch();
    }
    return preparedStatement.executeBatch();
  }
}

LOCAL DATABASE:        
PrepareStatementBatch100InsertPrepareHit.mariadb    5.290 ±  0.232  ms/op
PrepareStatementBatch100InsertRewrite.mariadb       0.404 ±  0.014  ms/op
PrepareStatementBatch100InsertText.mariadb          6.081 ±  0.254  ms/op

DISTANT DATABASE:        
PrepareStatementBatch100InsertPrepareHit.mariadb    7.639 ±   0.476  ms/op
PrepareStatementBatch100InsertRewrite.mariadb       1.164 ±   0.037  ms/op
PrepareStatementBatch100InsertText.mariadb          8.148 ±   0.563  ms/op

  Using binary protocol is here more significant,having results 13% faster than using text protocol. Insert’s are send by bulk and results read asynchronously (that corresponds to optionuseBatchMultiSend). This permit to have distant results with performance not far from those local. Rewrite has amazing good performance, but will not have auto-increment ids. If you don’t need ids immediatly and don’t use ORM, this solution will be the fastest. Some ORM permit configuration to handle sequence internally to provide increment ids, but those sequences aren’t distributed, so won’t work on clusters.

COMPARISON WITH OTHER DRIVERS

SELECT query with one row result

BenchmarkSelect1RowPrepareHit.mariadb                58.267 ±  2.270  µs/op
BenchmarkSelect1RowPrepareHit.mysql                  73.789 ±  1.863  µs/op
BenchmarkSelect1RowPrepareMiss.mariadb              118.896 ±  5.500  µs/op
BenchmarkSelect1RowPrepareMiss.mysql                150.679 ±  4.791  µs/op
BenchmarkSelect1RowPrepareText.mariadb               62.715 ±  2.402  µs/op
BenchmarkSelect1RowPrepareText.mysql                 88.670 ±  3.505  µs/op
BenchmarkSelect1RowPrepareText.drizzle               78.672 ±  2.971  µs/op
BenchmarkSelect1RowPrepareTextHA.mariadb             64.676 ±  2.192  µs/op
BenchmarkSelect1RowPrepareTextHA.mysql              137.289 ±  4.872  µs/op

HA stands for “High Availability” using the Master-Slave configuration (connection URL is “jdbc:mysql:replication://localhost:3306,localhost:3306/testj”). These results are due to a lot of different implementation choice. Here is some reasons that explain time differences:

• MariaDB driver is optimized for UTF-8, allowing less creation of bytes array, avoiding array copy, and memory consumption.
• HA implementation : MariaDB and MySQL drivers use a java dynamic Proxyclass sitting between Statement objects and sockets, permitting to add failover behaviour. Those addition will cost an overhead of 2 microseconds per query (62.715 without become 64.676 microseconds). In MySQL implementation, nearly all internal methods are proxied, adding an overhead for lots of methods that have nothing to do with failover, adding a total overhead of 50 microseconds to every query.

(Drizzle has no PREPARE, neither HA functionality)

“Select 1000 rows”

private String request = "select * from seq_1_to_1000"; //using the sequence storage engine

private ResultSet select1000Row(Connection connection) throws SQLException {
  try (Statement statement = connection.createStatement()) {
    try (ResultSet rs = statement.executeQuery(request)) {
      while (rs.next()) {
        rs.getString(1);
      }
      return rs;
    }
  }

BenchmarkSelect1000Rows.mariadb                     244.228 ±  7.686  µs/op
BenchmarkSelect1000Rows.mysql                       298.814 ± 12.143  µs/op
BenchmarkSelect1000Rows.drizzle                     406.877 ± 16.585  µs/op

When using a lot of data, time is mostly spent on reading from socket, and storing result in memory for sending it back to client. If the benchmark was only executing the SELECT without reading the results, MySQL and MariaDB execution time would be equivalent. Since the goal of a SELECT query is to have results, the MariaDB driver is optimized to give back results (avoiding the creation of bytes arrays).

“Insert 1000 rows”

LOCAL DATABASE:        
PrepareStatementBatch100InsertPrepareHit.mariadb    5.290 ±  0.232  ms/op
PrepareStatementBatch100InsertPrepareHit.mysql      9.015 ±  0.440  ms/op
PrepareStatementBatch100InsertRewrite.mariadb       0.404 ±  0.014  ms/op
PrepareStatementBatch100InsertRewrite.mysql         0.592 ±  0.016  ms/op
PrepareStatementBatch100InsertText.mariadb          6.081 ±  0.254  ms/op
PrepareStatementBatch100InsertText.mysql            7.932 ±  0.293  ms/op
PrepareStatementBatch100InsertText.drizzle          7.314 ±  0.205  ms/op

DISTANT DATABASE:        
PrepareStatementBatch100InsertPrepareHit.mariadb     7.639 ±   0.476  ms/op
PrepareStatementBatch100InsertPrepareHit.mysql      43.636 ±   1.408  ms/op
PrepareStatementBatch100InsertRewrite.mariadb        1.164 ±   0.037  ms/op
PrepareStatementBatch100InsertRewrite.mysql          1.432 ±   0.050  ms/op
PrepareStatementBatch100InsertText.mariadb           8.148 ±   0.563  ms/op
PrepareStatementBatch100InsertText.mysql            43.804 ±   1.417  ms/op
PrepareStatementBatch100InsertText.drizzle          38.735 ±   1.731  ms/op

MySQL and Drizzle bulk insert are like X INSERT’s: Driver send 1 INSERT, wait for insert result, and send next insert. The network latency between each insert will slow down insertions.

Store procedures

PROCEDURE CALL

//CREATE PROCEDURE inoutParam(INOUT p1 INT) begin set p1 = p1 + 1; end
private String request = "{call inOutParam(?)}";

private String callableStatementWithOutParameter(Connection connection, MyState state) 
		throws SQLException {
  try (CallableStatement storedProc = connection.prepareCall(request)) {
    storedProc.setInt(1, state.functionVar1); //2
    storedProc.registerOutParameter(1, Types.INTEGER);
    storedProc.execute();
    return storedProc.getString(1);
  }
}

BenchmarkCallableStatementWithOutParameter.mariadb   88.572 ±  4.263  µs/op
BenchmarkCallableStatementWithOutParameter.mysql    714.108 ± 44.390  µs/op

MySQL and MariaDB implementations completely differ. Mysql driver will use many hidden queries to obtain output result :

• SHOW CREATE PROCEDURE testj.inoutParam to identify IN and OUT parameters
• SET @com_mysql_jdbc_outparam_p1 = 1 to send data according to IN / OUT parameters
• CALL testj.inoutParam(@com_mysql_jdbc_outparam_p1) call procedure
• SELECT @com_mysql_jdbc_outparam_p1 to read output result

MariaDB implementation is straightforward using a capability to have OUT parameter in the server response without any additional queries. (That’s the main reason why MariaDB driver require MariaDB/MySQL server version 5.5.3 or later).    

CONCLUSION

MariaDB driver rocks ! The binary protocol has different advantages but relies on having the PREPARE results already in cache. If applications have a lot of different kind of queries and database is distant, that may not be the better solution. Rewrite has amazing results to write data in batch Driver holds well versus other drivers. And there is much to come, but that’s another story.  

---

Raw results:

1. with a MariaDB 10.1.17 database local, distant
2. with a MySQL Community Server 5.7.15 database (build 5.7.15-0ubuntu0.16.04.1) local