Issue in Performance between Aurora Serverles V1 and Serverless v2

Question

We have an Aurora Serverless V1 instance that inserts 750k rows to a DB in 10-15 minutes executing a Java Spring Boot service, we tried calling the same service with a V2 instance but it takes around 2.5 hours if we are lucky, the ACU and CPU are always up to 100%, we tried adding connection pools, and changing the values for the number of ACU's (currently 0.5 to 64), but the performance is really bad. I would like to know if we missed something or where we can check how to tackle these huge differences in performance.

- For our use case the 10 minutes is fine since we need to do some work processing the data before inserting it in the v1 DB.

- ACUs and CPU reach 100% when using v2 and the Cloud Watch graphs show that are auto-scaling but it stays way too much in that state.

- We are using batch uploads, the code for both DBs is the same, and the only change is in the DB URL.

- for v2 there are issues with the connection pools, but adding more is not doing anything at all, it always uses 10, we increased it to 50 but still use the same 10, and the other 40 are idle, the log on the service on v2 is always showing "Connection not available, request time out after 30000ms", for v1 there is no such error.

If we are just changing the DB URL for v1 and v2 is unclear why is the difference so big between both. ?

I've noticed tho that for v1 I can see the "insert into" statements which are a lot faster but for v2 we see some selects and at the end COMMIT, which I think is causing the bottleneck.

Answer

It seems like you've already done some troubleshooting and identified potential issues with the connection pooling and differences in SQL statements between the Aurora Serverless V1 and V2 instances. Here are some additional steps you can take to further investigate and improve the performance:

Database Configuration: Check if the configuration settings for your Aurora Serverless V2 instance are optimized for your workload. You may need to adjust parameters such as max_connections, max_parallel_workers, and other relevant settings based on your application's requirements.

Monitoring: Use Amazon CloudWatch metrics and Aurora Performance Insights to monitor the performance of your Aurora Serverless V2 instance. Look for any bottlenecks or resource constraints that could be impacting performance, such as CPU utilization, memory usage, or I/O activity.

Query Optimization: Analyze the SQL queries being executed on the Aurora Serverless V2 instance and identify any potential performance bottlenecks. Use tools like EXPLAIN and ANALYZE to analyze query execution plans and optimize your SQL queries for better performance.

Connection Pooling: Double-check your connection pooling configuration and ensure that it is correctly configured to manage database connections efficiently. Consider using connection pool management libraries like HikariCP or Tomcat JDBC Pool, which are optimized for high-performance and reliability.

Database Schema: Review your database schema and indexing strategy to ensure that it is optimized for your workload. Consider creating indexes on frequently queried columns and optimizing data retrieval queries to minimize overhead.

Testing and Benchmarking: Perform load testing and benchmarking on both Aurora Serverless V1 and V2 instances to compare performance under different scenarios. This will help identify any specific areas where performance is degraded on the V2 instance and guide optimization efforts.

Issue in Performance between Aurora Serverles V1 and Serverless v2

Relevant content