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

Aurora Serverless v2 in production - very slow queries

0

I currently use 2 x 4xlarge instances for our Aurora production cluster (35% utilisation during peak periods).

I intend to migrate to Serverless V2 instances. However, our initial tests show queries on aurora serverless v2 to be much much slower than provisioned instances.

The speed difference is very dramatic for some queries (0.2 seconds in provisioned vs 10 seconds in serverless). I have tried everything - trying to warm up the buffer pool, and increasing the min ACU to 10. Still, no luck.

Any advise on what I should look into?

Topics
ServerlessAnalyticsDatabase
Tags
ServerlessAmazon AuroraAurora MySQL
Language
English
vmit
asked a year ago1.7K views
2 Answers
0

Hi,

you should try to run EXPLAIN on your queries with highest latency differences on the 2 engines.

See https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

You can then see if there is a major difference in access strategy leading to this much higher latency and fix it.

This other article gives you some tracks to explore as well: https://repost.aws/knowledge-center/aurora-mysql-slow-select-query

But, it's mostly related to context with high loads. So, I don't think it will match your situation where you seem to be in initial unit testing mode.

Best,

Didier

profile pictureAWS
EXPERT
Didier_Durand
answered a year ago
profile picture
EXPERT
MaxCloud
reviewed a year ago
  • vmit
    a year ago

    (sorry, moving this as a comment, instead of an answer)

    Hi Didier

    Thanks for your reply.

    For most queries, the "EXPLAIN" is giving exactly the same result. Which makes sense - because the mysql engine is identical. Only the hardware is different.

    For some large queries, the "EXPLAIN" query is giving very similar result - except the serverless shows the query with "filesort", whereas the provisioned server does not. My guess is that this is because the dataset is not in the RAM in the serverless, whereas the whole query dataset lies in the RAM for provisioned. Would this be correct?

    I feel like this is an issue with buffer pool. Serverless v2 keeps evicting the buffer pool during downsizing, whereas the provisioned server does not. So, I don't understand how anyone can use Serverless V2 without making the min ACU so high that it effectively stops the scaling activity altogether, and defeats the purpose of serverless scaling.

0

The answer about analyzing your queries and looking for differences in the query plans is an important strategy -- especially if you are changing engine versions. But additionally in my testing, I saw a substantial performance improvement by selecting the Aurora I/O-Optimized option. If your query plans seem very similar, give this option a try to see if you find the same.

Joe A
answered 3 months ago

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