patternsqlMinor
Why SQL inserts are slower in AWS Aurora Serverless than in AWS RDS
Viewed 0 times
whysqlinsertsareslowerserverlessthanauroraawsrds
Problem
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:
My questions are:
EDIT: Also, there is a similar question
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
Solution
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.
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.
Context
StackExchange Database Administrators Q#307843, answer score: 3
Revisions (0)
No revisions yet.