4

We had an AWS MySQL RDS t3.xlarge instance with 2000 IOPS. Also, I have a cron job written in java which inserts data in a table: it can insert up to 12M rows daily. Since AWS RDS cost is quite high we decided to migrate to AWS Aurora Serverless v1.

But what was surprising for us: the job executed on AWS RDS in ~3 hours but on Aurora Serverless it took ~16 hours.

According to my searches, it is suggested to change parameter values: innodb_flush_log_at_trx_commit (1 -> 0) and sync_binlog (1 -> 0). For Aurora Serverless you can modify innodb_flush_log_at_trx_commit but sync_binlog - no. Probably that's why changing innodb_flush_log_at_trx_commit didn't give any results.

My questions are:

  • Why on Aurora Serverless the same job executes more than 5x times slower than on AWS RDS (Although, I understand that t3.xlarge instance with 2000 IOPS is quite powerful)?
  • Is there a solution to accelerate Aurora Serverless inserts?

EDIT: Also, there is a similar question

1 Answer 1

3

Is it possible your Aurora Serverless instance isn't autoscaling appropriately because your cron job is a continuously long running query and / or transaction? According to Autoscaling for Aurora Serverless v1 (in How Aurora Serverless v1 works) autoscaling can timeout due to the aforementioned events:

When it does need to perform a scaling operation, Aurora Serverless v1 first tries to identify a scaling point, a moment when no queries are being processed. Aurora Serverless might not be able to find a scaling point for the following reasons:

  • Long-running queries

  • In-progress transactions

  • Temporary tables or table locks

To increase your Aurora Serverless DB cluster's success rate when finding a scaling point, we recommend that you avoid long-running queries and long-running transactions.

Also mentioned in Best practices for working with Amazon Aurora Serverless:

Scale-blocking operations The capacity allocated to your Aurora Serverless DB cluster seamlessly scales. But using long-running queries or transactions and temporary tables or table locks can delay finding a scaling point.

And:

Long-running queries or transactions For transactions, you should follow standard best practices. For example, keep your transactions simple, short and use a suitable isolation level.

The most important practice is to avoid long-running transactions. In general, for any relational database, long-running transactions can cause performance degradation. Specifically for Aurora Serverless, long-running transactions are blocking operations for scaling unless you use the force scaling parameter. Even in this scenario, you must complete a proper rollback first, which can take significant time. This can have a very negative impact on your application.

If your cron job is a single query or transaction, then re-writing it to insert data in multiple smaller batches, with breaks, in a more iterative manner, might prove a better workflow for you on Aurora Serverless. If your instance's autoscaling is timing out, then your cron job may be running on an under-provosioned instance the entire time.

4
  • I completely agree with you and I have read all your mentioned articles in AWS. Data are inserted batch by batch, in parallel. Aurora Serverless scales up upon the need but the scaling doesn't change things. This shows also this post: dba.stackexchange.com/questions/236721/….
    – Armine
    Feb 23 at 12:20
  • My concern is that the same job works on the same data 10x faster on AWS RDS compared to AWS Aurora Serverless. Of course, I can try to optimize my queries again and again, but in that case, if my job will run faster on Aurora Serverless, then it will run even faster on RDS, but Amazon states that MySQL Aurora Serverless is 5x faster than a usual MySQL AWS RDS. Apparently, I see the opposite.
    – Armine
    Feb 23 at 12:25
  • @Armine You've confirmed the Aurora Serverless instance is actually automatically scaling up successfully and not timing out?
    – J.D.
    Feb 23 at 12:38
  • No significant timing reduction.
    – Armine
    Feb 23 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.