performance issue for burst read operations compared with mysql-connector-j

[OHsechun]

Hello, I tested simple read operation performance using application with spring boot 2.7.14 version (connection pooling by HikariCP, JPA) to replace mysql-connector-j with aws mysql jdbc driver for high availability when failover occurs.

I saw there was quite a big differences in performance. Though I used Rest API Https calls, I tried several times, but the result was the same. I did not understand why this huge difference shows.

< AWS resource description >
AWS EKS 1.28 version with node group(m5.4xlarge 3-instances)
application pod per each node (total 3-pods) connected to NodePort k8s service
Application Load balancer points to the above k8s service.
AWS Aurora2 mysql5.7 compatible cluster with one readonly replica (db.r5.4xlarge type)
All AWS resources in VPC private subnets except Application Load balancer.

< Testing tool description >
Ngrinder 3.5.9 with 3-agents (t3.medium)
Ngrinder controller and agents are in same vpc which above aws resources are in
Test configuration:
total vuser 444 (148 vusers per agent)
with no reset connection per request

results:
< mysql-connector-j:8.0.33 >

< aws mysql jdbc driver 1.1.14 >

Can anyone explain this situation? Please help if I missed something when applying aws mysql jdbc driver with spring boot.

[aaronchung-bitquill]

Hi @OHsechun,

Thank you for reaching out and bringing this to our attention.

We'll take a look at this and keep you updated as we investigate.

In the meantime, could you provide some additional details regarding the follow?

• What version of the AWS MySQL JDBC driver are you using?

• What plugins are you using with the AWS MySQL JDBC driver?

• Are you using any other properties/configurations that may be relevant?

• A code sample of how you have set up the the AWS MySQL JDBC driver, and how you are using it?

Also, I should bring to your attention that as per our Maintenance Policy, the AWS JDBC driver for MySQL will be reaching the end of its Maintenance Window on July 25, 2024. I would highly recommend adopting the new aws-advanced-jdbc-wrapper. The aws-advanced-jdbc-wrapper officially supports Aurora MySQL and can be used in conjunction with the Community MySQL JDBC Driver. It is quite a bit more feature rich than the AWS JDBC driver for MySQL, and we are actively adding new features to it. In contrast, the AWS JDBC driver for MySQL no longer receives any new features, and after July 25, we will not back-port fixes or features to it.

If you have any questions about the aws-advanced-jdbc-wrapper, please do not hesitate to ask.

Thank you!

[OHsechun]

• What version of the AWS MySQL JDBC driver are you using?

  • mysql-connector-j:8.0.33

• What plugins are you using with the AWS MySQL JDBC driver?

  • I did not adjust any parameters about plugins on/off. So I expect connection plugin and failover plugin were enabled maybe.

• Are you using any other properties/configurations that may be relevant?

  • I adjust parameters with enableFailoverStrictReader and acceptAwsProtocolOnly, but had no effect. The result was same.

• A code sample of how you have set up the the AWS MySQL JDBC driver, and how you are using it?

< property >
spring:
  datasource:
    app:
      master:
        driver-class-name: software.aws.rds.jdbc.mysql.Driver
        jdbc-url: jdbc:mysql:aws://db-identifier.cluster-XYZ.ap-northeast-2.rds.amazonaws.com:3306/database-name?zeroDateTimeBehavior=convertToNull&useUnicode=yes&characterEncoding=UTF-8&connectTimeout=2000&serverTimezone=Asia/Seoul&useTimezone=true&useSSL=false
        username: username
        password: password
        maximum-pool-size: 100
      slave:
        driver-class-name: software.aws.rds.jdbc.mysql.Driver
        jdbc-url: jdbc:mysql:aws://db-identifier.cluster-ro-XYZ.ap-northeast-2.rds.amazonaws.com:3306/database-name?zeroDateTimeBehavior=convertToNull&useUnicode=yes&characterEncoding=UTF-8&connectTimeout=2000&serverTimezone=Asia/Seoul&useTimezone=true&useSSL=false
        username: username
        password: password
        maximum-pool-size: 100

< datasource config >
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.app.master")
    fun masterConfig(): HikariConfig {
        return HikariConfig()
    }

    @Bean
    fun masterDataSource(): DataSource {
        return HikariDataSource(masterConfig())
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.app.slave")
    fun slaveConfig(): HikariConfig {
        return HikariConfig()
    }

    @Bean
    fun slaveDataSource(): DataSource {
        return HikariDataSource(slaveConfig())
    }

    @Bean
    fun replicationRoutingDataSource(): DataSource {
        val replicationRoutingDataSource = ReplicationRoutingDataSource()

        replicationRoutingDataSource.setTargetDataSources(mapOf(
            DataSourceType.MASTER to masterDataSource(),
            DataSourceType.SLAVE to slaveDataSource()
        ))

        replicationRoutingDataSource.setDefaultTargetDataSource(masterDataSource())

        return replicationRoutingDataSource
    }

    @Bean
    fun lazyConnectionDataSourceProxy(): LazyConnectionDataSourceProxy {
        return LazyConnectionDataSourceProxy(replicationRoutingDataSource())
    }

    @Bean
    fun entityManagerFactory(): LocalContainerEntityManagerFactoryBean {
        val entitymanager = LocalContainerEntityManagerFactoryBean()

        entitymanager.dataSource = lazyConnectionDataSourceProxy()
        entitymanager.setPackagesToScan(
            "{persistence package}"
        )

        entitymanager.jpaVendorAdapter = HibernateJpaVendorAdapter()

        return entitymanager
    }

    @Bean
    fun transactionManager(): PlatformTransactionManager? {
        val transactionManager = JpaTransactionManager()
        transactionManager.entityManagerFactory = entityManagerFactory().getObject()
        return transactionManager
    }

Thank you for your reply and I will take a closer look aws-advanced-jdbc-wrapper.
Please let me know if you need more information.