RDS Proxy query time is slower than expected

0

Hello there,

We have a RDS proxy setup with a Postgres RDS instance (db.t3.small) connected with TLS disabled. We ran benchmarks against direct connection to RDS for a simple query for different numbers of concurrent connections e.g. 1, 50, 100 ran from a m5.4xlarge EC2 instance (all in the same VPC). Our script uses python's pscyopg2 for connection and everything is same for both setup.

We are seeing high numbers on both connection time + query time for RDS proxy as opposed to RDS direct. We ran our experiments at least 5 times and observed the same behavior. The following are the numbers in p75 and p90 for connection and query times for each in seconds.

RDS Direct v. RDS Proxy [1 connection] Connection Time: 0.02s v. 0.05s (2.5x) Query Time: 0.002s v. 0.025s (12.5x)

[50 connections] Connection Time: p75: 0.38s v. 0.44s (1.15x) p90: 0.41s v. 0.46s (1.13x) Query Time: p75: 0.03s v. 0.32s (10.7x) p90: 0.05s v. 0.35s (7x)

[100 connections] Connection Time: p75: 0.39s v. 0.72s (1.85x) p90: 0.45s v. 0.73s (1.6x) Query Time: p75: 0.03s v. 0.26s (8.67x) p90: 0.06s v. 0.37s (6.17x) Note: In our experiments, we also had time for RDS as high as 0.52s (p75) and 0.55s (p90).

Questions here:

  1. Why is the query time so high? Even on a single connection it shows a query time 12.5x compared to direct connection. An API code will have multiple queries and Such times will make RDS Proxy impractical for most APIs. What's going on here?
  2. Why is the time for connections higher? Is it because of an SSL layer being involved? Is it recommended that the same connection be reused in an API code?

Please let us know if anyone has faced this and any info regarding this. Thanks for your time. - F.

  • please accept the answer if it was useful

1 Answer
0

Possible Reasons for Higher Query Times

1. Proxy Overhead: RDS Proxy introduces some overhead due to connection pooling and managing connections between your application and the database. This overhead can result in slightly higher query times compared to direct connections.

2. Connection Pooling Configuration: If the connection pool settings are not optimized, it can lead to inefficient connection handling, increasing query latency. Ensure the pool settings are tuned for your workload.

3. Warm-up Time: RDS Proxy may need time to warm up, especially under high concurrency. Initial connections might take longer as the proxy sets up its internal state.

4. Proxy Caching: Check if the proxy is correctly caching prepared statements and handling frequent queries efficiently. Misconfigured caching might lead to higher query times.

Possible Reasons for Higher Connection Times

1. TLS Overhead: You mentioned that TLS is disabled, so this should not be a factor. However, double-check to ensure that TLS is indeed disabled in all parts of your configuration.

2. Connection Pooling Behavior: RDS Proxy might be opening new connections to the database instead of reusing existing ones. Ensure your application is reusing connections efficiently.

3. Proxy Scaling: If the proxy has to scale up to handle a sudden influx of connections, this can introduce latency. Ensure your proxy is appropriately scaled for your workload.

Recommendations for Optimization

1. Optimize Connection Pool Settings: Tune the minimum and maximum connections, idle connection timeout, and other pool settings to match your application's workload.

2. Reuse Connections: Reusing database connections in your application code can significantly reduce connection overhead. Using a connection pool within your application, in addition to RDS Proxy, can help.

3. Monitor and Analyze Metrics: Use Amazon CloudWatch to monitor RDS Proxy metrics. Look for metrics such as DatabaseConnections, ClientConnections, ActiveClientConnections, ActiveDatabaseConnections, and MaxConnectionsLimitReached.

4. Review Application Code: Ensure your application is not frequently opening and closing connections. Use connection pools in your application to maintain persistent connections.

profile picture
EXPERT
answered 6 days ago
  • Hi there, thanks for your response. I have few follow up questions.

    1. Connection Pooling Configuration: What configuration would need to be controlled for making query response faster?
    2. Proxy Caching: We don't have any prepared statement and in this case, the test is for a simple query which shouldn't need any caching. Could you please explain more on this and does RDS proxy have a control for this?
    3. Proxy Scaling: How do we make sure of RDS proxy is ready for scale? It's a managed service and looks like we don't have much control over it.

    Thanks for your time.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions