Comments (17)
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!!
from aws-mysql-jdbc.
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
from aws-mysql-jdbc.
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?
from aws-mysql-jdbc.
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.
from aws-mysql-jdbc.
Can you provide some more information ?
Are you doing 1M inserts in a loop ?
from aws-mysql-jdbc.
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?
from aws-mysql-jdbc.
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!
from aws-mysql-jdbc.
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.
from aws-mysql-jdbc.
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
from aws-mysql-jdbc.
Any update on this?
from aws-mysql-jdbc.
Any update on this?
from aws-mysql-jdbc.
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.
from aws-mysql-jdbc.
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.
from aws-mysql-jdbc.
@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?
from aws-mysql-jdbc.
Closing this issue due to lack of feedback. Please re-open if this is still a problem.
from aws-mysql-jdbc.
We have not tried, but I found this issue very similar to this thread. Thanks!
from aws-mysql-jdbc.
We can try it next week to see if things get improved or not.
from aws-mysql-jdbc.
Related Issues (20)
- Random IndexOutOfBoundsException occurences HOT 5
- Redundant "Reset stmt" when setting useServerPrepStmts&cachePrepStmts to true HOT 2
- Failover with a autocommit=false setup lead to broken connections HOT 8
- Setting allowMultiQueries leads to different results HOT 4
- BatchUpdateException.getUpdateCounts returns unexpected value -1 HOT 6
- Be more verbose about connecting to the read-only endpoint of an aurora RDS instance. HOT 2
- Default clusterId doesn't include user HOT 2
- During batchUpdate() Single line comments are causing error "Statement.executeUpdate() or Statement.executeLargeUpdate() cannot issue statements that produce result sets." HOT 4
- Potentially corrupt connection in pool HOT 4
- getHoldability() and getResultSetHoldability() return different results HOT 2
- Statement.getGeneratedKeys() returns unexpected value HOT 3
- Error Initializing DB Connection to Aurora DB in `AWS-CN` without `enableClusterAwareFailover=false` HOT 6
- Setting a large timeout leads to errors when executing SQL HOT 1
- Execution of executeUpdate Closes Previously Acquired ResultSet HOT 2
- Statement.getResultSetType () seems failed to change the result set type HOT 1
- Statement.cancel() does not work HOT 3
- what is the different between aws-mysql-jdbc and aws-advanced-jdbc-wrapper HOT 4
- docs: clarify differences between MySQL JDBC and JDBC Wrapper
- Is it possible to exclude Readers with different instance types if they are included? HOT 2
- NodeMonitoringConnectionPlugin causes Exceptions HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from aws-mysql-jdbc.