JDBC wrapper Increasing CPU utilization and API latency after migrating from RDS proxy

[avnish-choudhary]

Describe the bug

we are currently using AWS RDS proxy with IAM authentication. We implement proxy mainly for failover, IAM authentication reasons.
Now after the release of new advanced jdbc wrapper we got all features inside the advanced jdbc wrapper library itself. so we want to implement this library and directly use RDS cluster with IAM authentication and failover support. we are using spring boot (2.1.8) and using hikari pool using this example: https://github.com/aws/aws-advanced-jdbc-wrapper/tree/main/examples/SpringBootHikariExample .

we observed that instance which was directly using advanced-jdbc-wrapper library was consuming lot of CPU and latency of one of the api was more than 6 seconds and same api was taking less than 1.5 second in other instance which was using AWS RDS proxy.

That api loads lot of data in memory (200 KB) and in case of parallel requests it was able to handle with proxy implementation. But same thing is failing with advanced-jdbc-wrapper.

Expected Behavior

Ideally there should be very minor difference in latency and CPU utilization.
CPU utilization without jar - 30%
CPU utilization after jar - 60%
API latency could have been near by upto 2 seconds as compared to proxy.

What plugins are used? What other connection properties were set?

readWriteSplitting,failover,efm2,iam

Current Behavior

CPU utilization without jar - 30%
CPU utilization after jar - 60%

Latency for memory intensive apis are more than 6 seconds as compared to 1.5 seconds(without wrapper).

Reproduction Steps

1. Create 2 API which is fetching lot of data from database atleast (200 MB).
2. Implement aws-advanced-jdbc wrapper library in spring boot (with 25 pool size)
3. Implement AWS RDS proxy with hikari pool (with 25 pool size)
4. Call those apis concurrently.
5. Observe the CPU and memory utilization and also see the load in RDS cluster
6. With proxy it is able to handle load upto some extent but with wrapper library it will start failing since beginning.

Possible Solution

No response

Additional Information/Context

No response

The AWS Advanced JDBC Driver version used

2.3.6

JDK version used

11

Operating System and version

UBUNTU 22

[sergiyvamz]

Hi @avnish-choudhary ,

Thanks for reaching out and raising this issue.

Would you give more details about your application:

• What connection string do you use to connect to your Hikari CP? Could you provide configuration file for your data source(s)?

• Could you provide driver logs representing the issue? It's important to verify internal driver workflows and, possibly, to identify bad performant areas.

• Would you be able to provde a sample app that replicate the issue?

• How important for your application to use readWriteSplitting plugin?

• Did you have a chance to check out other examples? For instance, the following two samples could meet your requirements.
  https://github.com/aws/aws-advanced-jdbc-wrapper/tree/main/examples/SpringHibernateBalancedReaderOneDataSourceExample
  and
  https://github.com/aws/aws-advanced-jdbc-wrapper/tree/main/examples/SpringHibernateBalancedReaderTwoDataSourceExample

• Did you try to turn on the driver telemetry? That would give you driver insights and narrow down investigation.
  https://github.com/aws/aws-advanced-jdbc-wrapper/blob/main/docs/using-the-jdbc-driver/Telemetry.md

We'll take a look at this and keep you updated as we investigate. Thank you for your patience!

[avnish-choudhary]

Hi @avnish-choudhary ,

Thanks for reaching out and raising this issue.

Would you give more details about your application:

• What connection string do you use to connect to your Hikari CP? Could you provide configuration file for your data source(s)?
• Could you provide driver logs representing the issue? It's important to verify internal driver workflows and, possibly, to identify bad performant areas.
• Would you be able to provde a sample app that replicate the issue?
• How important for your application to use readWriteSplitting plugin?
• Did you have a chance to check out other examples? For instance, the following two samples could meet your requirements.
  https://github.com/aws/aws-advanced-jdbc-wrapper/tree/main/examples/SpringHibernateBalancedReaderOneDataSourceExample
  and
  https://github.com/aws/aws-advanced-jdbc-wrapper/tree/main/examples/SpringHibernateBalancedReaderTwoDataSourceExample
• Did you try to turn on the driver telemetry? That would give you driver insights and narrow down investigation.
  https://github.com/aws/aws-advanced-jdbc-wrapper/blob/main/docs/using-the-jdbc-driver/Telemetry.md

We'll take a look at this and keep you updated as we investigate. Thank you for your patience!

Hi @sergiyvamz

we are using these wrapper plugins : readWriteSplitting,failover,efm2,iam
connection string: jdbc:aws-wrapper:postgresql://cluster-url:5432/testdb?currentSchema=example&ApplicationName=example&statement_timeout=5min

I tried both of the example and I found that its not supporting IAM authentication with internal pool (SF_F0).
is it a best way to use hikari with two datasource for reader and writer ?

[karenc-bq]

Hi @avnish-choudhary, we have seen an issue describing a similar performance degradation in the past. Do you see the same issue with IAM disabled and are you also using a Postgres RDS instance?

To answer your question, yes we recommend setting up two datasources and disabling the readWriteSplitting plugin when using an external pool for reasons discussed here.

Could you please elaborate more on how internal pool does not work with IAM authentication? Were there connection errors? While the profile provides a set of predefined properties, client application can still override or add properties through the connection string: &wrapperPlugins=initialConnection,auroraConnectionTracker,failover,efm2,iam&iamRegion=us-west-2.