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

Aurora Mysql Serverless v2 Writer "Stuck"

0

We have a containerized and distributed tomcat webapp workload that serves up to about 3,000-12,000 simultaneous users on a typical day, which suddenly froze last week. The primary database that seized up was Aurora MySQL (serverless v2), which is deployed across two availability zones.

It was running with about 200-300 connections, before it spiked to over 1000 connections in under a minute, and then hung. The event that was reported for the cluster was:

"Attempting to restart the database with zero downtime because the writer is stuck."

The database was not able to recover until we completely shut down everything that could possibly create or hold a connection. A few minutes after killing all the connections, the database became responsive again and the writer role finally failed-over to what was the reader at that time.

At first, I thought that maybe a long running transaction against the writer database might have prevented scaling in a critical moment. But after scouring our logs, I could not find any evidence of a long running transaction.

Furthermore, it seems that my theory would really have only been applicable in the context of "serverless v1". According to the documentation, long running transactions do not prevent scaling operations. Additionally, based on the traffic metrics we have, it shouldn't have needed to scale anyway.

We are running engine version 3.08.2.

Question: What could have caused this error? Could the patches in 3.09 or 3.10 potentially have prevented this? (3.10 was not available at the time this event occurred, but lists an improvement about preventing unnecessary restarts during recovery). https://docs.aws.amazon.com/AmazonRDS/latest/AuroraMySQLReleaseNotes/AuroraMySQL.Updates.3100.html

Many thanks to anyone with a moment to take a look at this and offer their advice!

Topics
Database
Tags
MySQLAmazon Relational Database ServiceAvailabilityDatabaseAmazon Aurora
Language
English

asked a year ago449 views

1 Answer
0

The "writer is stuck" error you experienced with your Aurora MySQL Serverless v2 database likely occurred due to a combination of factors related to connection management and failover behavior.

When your connection count rapidly spiked from 200-300 to over 1000 in under a minute, this may have overwhelmed the writer instance, causing it to become unresponsive. Aurora's automatic monitoring detected this condition and attempted to initiate a failover to restore service.

However, the failover process was likely impeded because the writer instance couldn't properly complete the handoff while under extreme connection load. This explains why the database only recovered after you manually terminated all connections, which allowed the failover mechanism to complete successfully.

Aurora Serverless v2 has different scaling characteristics than v1. While long-running transactions don't prevent scaling operations in v2 as they did in v1, a sudden spike in connections can still overwhelm the system before it has time to scale appropriately.

Regarding the patches in 3.09 or 3.10, the improvement in 3.10 about "preventing unnecessary restarts during recovery" could potentially help in similar situations, as it suggests enhancements to the recovery process. However, without specific details about that improvement, it's difficult to say with certainty whether it would have prevented your particular issue.

To prevent similar issues in the future, consider:

  1. Implementing connection pooling to manage connection spikes
  2. Setting appropriate failover priorities for your reader instances
  3. Monitoring connection counts closely and setting alerts for rapid increases
  4. Ensuring your minimum ACU setting provides enough capacity to handle sudden connection spikes
  5. Upgrading to the latest engine version when available
    Sources
    Unexpected Aurora RDS Failover Promoted Smaller Reader to Writer and Demoted Writer to Reader | AWS re:Post
    AppSync + Aurora Serverless V2 - The request was throttled because of insufficient resources on the database | AWS re:Post

answered a year ago

  • Shane Farrar
    a year ago

    Neat AI feature. One nit I have with this assessment is that it treats what is probably a symptom (the connection spike), like the cause. Interesting none the less.

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.

Relevant content