performance between AWS JDBC Connector and MySQL JDBC Connecto

[leeabuan]

I'm submitting a performance concern...

Describe the issue

using MySQL JDBC Connector - (https://dev.mysql.com/downloads/connector/j/)

the insert takes 23 sec for inserting 1,000,000 rows

on the otherhand

using AWS JDBC Connector - (https://awslabs.github.io/aws-mysql-jdbc/)

the same insert takes 39s for inserting 1,000,000 rows

A clear and concise description of what the issue is.

Driver Version? aws-mysql-jdbc-1.0.0.jar

MySQL Version? aurora mysql 3

To Reproduce
run the following insert via java using the above drivers

"INSERT INTO test.table_row (A_COL, B_COL, C_COL) VALUES (?,?,?)"

Expected behaviour
the insert time should be the same.

[sergiyvamz]

Hello @leeabuan

Thank you for giving the AWS JDBC Driver for MySQL a try!

The latest version 1.0.0 of the driver includes an Aurora cluster failover support and Enhanced Failure Monitoring features. These 2 new features help the driver to detect various DB cluster and network outages faster and minimize failover downtime. These features are enabled by default and they add a small footprint to overall driver performance and utilized resources. Should driver performance become a priority over DB node connection reliability, the driver could be configured accordingly. One of suggestion might be not to load Failover Plugin, and/or Enhanced Failure Monitoring Plugin. More details about driver plugins can be found at https://github.com/awslabs/aws-mysql-jdbc#connection-plugin-manager-parameters

[csyperski]

I think the discount is that Aurora is billed and sold as a high performance AND reliable HA database, not OR. So when the driver can't live up to that I can understand the frustration as after purchased a database that you believe does both, you are force to choose between them.

Do you believe this driver will get to the same performance tier as the reference driver?

[karenc-bq]

Hello @csyperski,

Agreed. We aim to keep the driver a close to performance as the reference driver.

For visibility, we have identified a potential improvement for the monitoring thread that should improve performance.

The monitoring thread is intended for applications that do not have much context into the timings of their query workflows. The driver does leverage the socketTimeout option from the reference driver. If for example you know your query workflow executes queries within 10 seconds, you can set a socket timeout of 15 seconds. This would be the max wait time that the driver waits before initializing failover. Since this leverages the reference parameter, we should observe performance coming in line to the community.

For example, the following connection attributes would need to be added to the connection string: socketTimeout=15;connectionPluginFactories=com.mysql.cj.jdbc.ha.plugins.failover.FailoverConnectionPluginFactory. For example, this would set socket timeout to 15 seconds and disable the monitoring thread. Failover is still enabled within the driver.

[davecramer]

@leeabuan

Can you provide some more information ?

Are you doing 1M inserts in a loop ?

[maksimsc]

Hi
We are facing the same issue after migrating from mariadb-java-client. The overall latency of our service had an increase of ~40%.
Here are the metrics before and after:

You can see average latency in ms on the top chart and % of time spent on SQL on the bottom one.

We didn't make any specific configuration, and we still want to use the failover feature.

Can I provide you with any additional info to help improve the performance?

[sergiyvamz]

Hello @maksimsc

Would you mind to provide more details?

• What connection string and configuration parameters do you use?
• What AWS JDBC MySQL Driver version do you use? Did you try to use a latest snapshot build?
• What are usual SQL statements that your service executes? I can see from the chart that SQL execution times are quite short but that is more like an assumption.
• Did you have a chance to try MySQL JDBC Connector/J Driver instead of MariaDb driver? Any observations about performance? https://github.com/mysql/mysql-connector-j
• Does your application use any connection pool?
• What database access frameworks your application uses?
• Did you try to apply suggestions above (provided by @karenc-bq) to your application? Did you notice any performance improvements?
• What DB cluster configuration do you use? How many nodes are in a cluster? What's MySQL database version it runs?
• Can you provide a driver logs? That may help to investigate the issue.

Thank you!

[maksimsc]

Hey @sergiyvamz, answering your questions:

What connection string and configuration parameters do you use?

We are using the following configuration params:

spring:
  config:
    activate:
      on-profile: production
  datasource:
    url: jdbc:mysql:aws://cluster_url:3306/schema_name?useAwsIam=true
    driver-class-name: software.aws.rds.jdbc.mysql.Driver
    username: iam_username

What AWS JDBC MySQL Driver version do you use? Did you try to use a latest snapshot build?

We are on 1.1.0 version. We didn't try snapshots.

<dependency>
    <groupId>software.aws.rds</groupId>
    <artifactId>aws-mysql-jdbc</artifactId>
    <version>1.1.0</version>
</dependency>

What are usual SQL statements that your service executes? I can see from the chart that SQL execution times are quite short but that is more like an assumption.

I would say, it is approximately 50/50 reads and writes. Most of the time it is some specific entries, selected/updated using indices.

Did you have a chance to try MySQL JDBC Connector/J Driver instead of MariaDb driver? Any observations about performance?

No, we never tried mysql-connector. At least, we don't have any statistics on it for sure.

Does your application use any connection pool?

Yes, we are using hikari connection-pool with the following config:

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      minimum-idle: 10
      maximum-pool-size: 20
      pool-name: company-pool

What database access frameworks your application uses?

We use spring-data-jpa and respective JpaRepository 99% of the time.

Did you try to apply suggestions above to your application? Did you notice any performance improvements?

Not yet, but we could give it a try

What DB cluster configuration do you use? How many nodes are in a cluster? What's MySQL database version it runs?

We are using the Aurora MySQL cluster with 2 nodes: 1 writer and 1 reader. Instance type is db.r6g.8xlarge with engine version 5.7.mysql_aurora.2.10.0.

Can you provide a driver logs? That may help to investigate the issue.

Sure, any info on how to enable and get them?

If you need anything else from me, please let me know.

[sergiyvamz]

Hello @maksimsc

Thank you for provided details. Driver logs can be enabled by adding a configuration parameter logger. More info can be found at https://github.com/awslabs/aws-mysql-jdbc#enable-logging

Since your application uses Hikari connection pool, we'd suggest to apply a solution mentioned at https://github.com/awslabs/aws-mysql-jdbc#connection-pooling

[kenshih]

Just adding to the conversation, in case it helps...

Context, my company has been looking into migrating our main production database from single region Mysql Aurora 2.x to Aurora Global Database. Because Global Database does not support RDS Proxy, we're looking to mitigate risk of cut-over & looking at the AWS JDBC Connector to help manage failover.

Looking to get a feel for the driver & seeing this thread, I ran some dumb-simple synthetic benchmarks (outside of a db-pool) just to get a sense of what we're dealing with here & how it might aid (or not) in our migration. Here's a gist of the benchmark run and the following sample, but representative, output of throughput/timing on non-global db using mysql-connector-java vs. aws-mysql-jdbc...

For baseline using mysql:mysql-connector-java:5.1.49 (though I got very similar results from mysql:mysql-connector-java:8.0.3), I saw examples like this:

Benchmark Mode Cnt Score Error Units
ChapsitckDbRunner.benchmarkTester thrpt 5 124.615 ± 106.460 ops/s
ChapsitckDbRunner.benchmarkTester avgt 5 0.007 ± 0.003 s/op

On the other hand software.aws.rds:aws-mysql-jdbc:1.1.0,

Benchmark Mode Cnt Score Error Units
ChapsitckDbRunner.benchmarkTester thrpt 5 73.579 ± 109.469 ops/s
ChapsitckDbRunner.benchmarkTester avgt 5 0.018 ± 0.052 s/op

Note the similar ~40% decrease in throughput consistent with @maksimsc's observations.

Consider this a 👍 on @karenc-bq note above and on any work to improve performance of this driver.

For visibility, we have identified a potential improvement for the monitoring thread that should improve performance.

I would love to be able to use aws-mysql-jdbc, if we can get it to get comparative performance to mysql-connector-java while gaining the advantage of some network architecture-awareness. Right now, it may not be feasible, so I'll be keeping an eye on this issue.

Thank you for your work on this driver!!

[chenrui333]

Any update on this?

[chenrui333]

Any update on this?

[davecramer]

Sorry the delayed response. We investigating the source of the overhead. We should have an update in the next 2 weeks. Thanks for your patience.

[karenc-bq]

The AWS JDBC Driver for MySQL needs to initialize the plugin pipeline and setup the failover plugin for every new connection, so it introduces additional performance overhead when creating new connections.

@kenshih - We noticed that the benchmarks you provided create a new connection for each iteration. Here is a slightly modified benchmark based on the one you provided that reuses the same connection object. The results suggest there are minimal performance overhead (~0.4%) when executing queries with an existing connection.

Could you please test it out and let us know if you still observe the performance overhead in your scenario?

We recommend reusing an existing connection object, wherever possible, when executing queries to reduce performance overhead from creating new connections. During the mean time, we will keep looking for ways to optimize our connection process.

[hsuamz]

@kenshih @chenrui333 , following up with @karenc-bq 's response. Have you been able to try the recommendation in reusing the existing connection object and seeing if the observed performance overhead persists in your scenario?

[hsuamz]

Closing this issue due to lack of feedback. Please re-open if this is still a problem.

[chenrui333]

We have not tried, but I found this issue very similar to this thread. Thanks!

[chenrui333]

We can try it next week to see if things get improved or not.