By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Data API - Aurora Serverless V2 - MySQL 8.0.36 - "Too Many Connections" executing a single query

0

When executing a query with nested resolvers from AppSync , it looks like the Data API is managing connections in a really inefficient way, since it reaches the max connections available instantly.

I get a response where the first item contains this error:

RDSHttp:{"message":"Too many connections; Error code: 1040;"}

And from the second item onwards, the error is the following one:

RDSHttp:{"message":"The request was throttled because of insufficient resources on the database."}

The unexpected amount of connections can be checked by running this query from the AWS RDS console, as explained here:

SHOW FULL PROCESSLIST

Starting from zero, it returns a single page of results (12 records), and it contains just 4 records related to connections belonging to the user I have configured for the app. They are all with the Command field set with the Sleep value.

Now, if I run the query with nested resolvers and quickly check again SHOW FULL PROCESSLIST, it returns 5 pages (96 records). And all of them with records related to connections of the same user (only 6 records are not related). They are all with the Command field set with the Sleep value, as well.

I have tried to solve it with the proposed and accepted solution of this post (disable and enable the Data API again), but I still get the same behaviour.

The RDSHttp:{"message":"The request was throttled because of insufficient resources on the database."} error also happens when executing a certain amount of queries, even if they don't contain nested resolvers. It is the case I have encountered when running my AppSync's suite of tests.

With Aurora Serverless V1 I have never experienced any error; but with V2, after some tests have run, I get that error, too.

According to the Cluster's ACUs configuration (MIN 0.5 - MAX 4) and the CloudWatch's DatabaseConnectionsmetric, I have 90 connections available.

Could anyone please help me to troubleshoot this issue?

I have everything set up to be able to upgrade from Aurora Serverless V1 to V2; but because of these errors, I cannot proceed to update the production environment.

Thank you in advance,

Ignasi

Topics
ServerlessFront-End Web & MobileApplication IntegrationAnalyticsDatabase
Tags
AWS AppSyncServerlessAmazon AuroraAurora MySQL
Language
English
ignasi
asked a month ago79 views
1 Answer
0

The issue you're experiencing with the Data API for Aurora Serverless v2 and MySQL 8.0.36 appears to be related to connection management and resource allocation. The Data API has been redesigned for Aurora Serverless v2 to improve scalability and remove the previous 1,000 requests per second limit. However, it seems that in your case, the connections are not being managed as efficiently as expected.

The "Too Many Connections" error you're encountering suggests that the Data API is creating new connections for each query rather than effectively reusing existing connections from the connection pool. This behavior is contrary to the intended design of the Data API, which should automatically pool and share database connections to improve application scalability.

The fact that you're seeing a large number of sleeping connections in the process list indicates that connections are being created but not properly closed or reused. This can quickly exhaust the available connection limit, leading to the errors you're experiencing.

The throttling error ("The request was throttled because of insufficient resources on the database") is likely a result of the database reaching its maximum connection limit or other resource constraints.

To troubleshoot and potentially resolve this issue, consider the following steps:

  1. Verify that you're using the latest version of the AWS SDK and CLI, as there may have been updates to address connection management issues.

  2. Review your application code to ensure you're not inadvertently creating new connections for each query. The Data API should handle this automatically, but it's worth double-checking.

  3. Consider increasing the maximum ACUs for your Aurora Serverless v2 cluster temporarily to see if it alleviates the issue. This could provide more resources for handling connections.

  4. Contact AWS Support for further assistance, as this behavior seems to be inconsistent with the expected performance of the redesigned Data API for Aurora Serverless v2.

  5. Monitor the CloudWatch metrics for your database cluster closely, paying attention to connection counts, CPU utilization, and memory usage to identify any potential resource bottlenecks.

  6. If possible, try to reproduce the issue with a simpler query structure to isolate whether the problem is specific to nested resolvers or a more general connection management issue.

  7. Consider implementing a retry mechanism with exponential backoff in your application to handle temporary connection issues.

If these steps don't resolve the issue, it may be worth considering alternative approaches, such as using a traditional database driver with a properly configured connection pool, at least until the Data API behavior can be fully resolved.

It's important to note that while Aurora Serverless v2 and the redesigned Data API offer improvements over v1, they may still have some limitations or issues that are being addressed. Continuing to work with AWS Support and providing detailed feedback about your use case can help improve the service for all users.

Sources
Introducing the Data API for Amazon Aurora Serverless v2 and Amazon Aurora provisioned clusters | AWS Database Blog
Amazon Aurora MySQL now supports RDS Data API - AWS
Amazon Aurora PostgreSQL now supports RDS Data API

profile picture
re:Post Agent
answered a month ago
  • ignasi
    a month ago

    The gist is exactly in this paragraph:

    The "Too Many Connections" error you're encountering suggests that the Data API is creating new connections for each query rather than effectively reusing existing connections from the connection pool. This behavior is contrary to the intended design of the Data API, which should automatically pool and share database connections to improve application scalability.

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

Relevant content