RDS Aurora problem engine 5.7.mysql_aurora.2.11.2

After any version upgrade it is expected that a query gives different performance, especially for major engine upgrade as the execution plan might have changed after an upgrade.

To rule out the possibility of an increased workload on the instance after the upgrade you can analyse the cloudwatch metrics on your Aurora instance.

You can refer to the doc below on monitoring Amazon RDS metrics with Amazon CloudWatch

[+] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/monitoring-cloudwatch.html

If there is no increase in the workload on the instance, then you can analyse the workload on your Aurora instance using performance insights and filter the load by top waits, SQL statements, hosts, or users. Additionally, you can enable slow query logs for your DB instance with preferable output as File, for tracking long queries executed in your Aurora instance. With parameter 'long_query_time' you can specify the time in seconds for queries to be considered slow and therefore be logged.

You can refer to the doc below on monitoring DB load with Performance Insights on Amazon RDS:-

[+] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.html

Additionally, you can refer to the doc below for info on Aurora MySQL slow query logs:-


[+] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.MySQL.LogFileSize.html#USER_LogAccess.MySQL.Generallog

Once you identify the slow queries from performance insights and slow query logs, you can check the query execution plan in older and the newer version and compare them to know which step is taking more time, you can use Profiler and based on that optimise the query.

You can consider the information given below for query optimisation:-

1. You can run explain command if it is a select query. This will show you the individual steps involved in query execution. You may also want to consider running a check of the queries being executed by sessions that are active and run an “explain” command on them. This will tell you if you are doing full table scans or using a bad index.

Reference link: https://dev.mysql.com/doc/refman/5.7/en/explain.html

2. Moreover, if you want to know how long a query normally runs for, after having the query, you may use profiling to measure it when it is not blocked, a quick example:



SET profiling = 1;
 select/insert/update/delete ....    <------ run your query here
 SHOW PROFILES;  <------ find the id for above query
 SHOW PROFILE FOR QUERY #;   To disable it: SET profiling = 0;

The SHOW PROFILE and SHOW PROFILES statements will display you the profiling information that indicates resource usage for statements executed during the course of the current session.

Reference Link: https://dev.mysql.com/doc/refman/8.0/en/show-profile.html



3. ANALYZE : Refresh the metadata statistics for the tables, that will optimize the execution plan with the current data

[+] https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html

4.  OPTIMIZE : Refresh the statistics, rebuild the indexes and defragment the tablespace to improve read times and reclaim unused space

[+] https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

If you still face any issues, please reach out to AWS Support via a case so that they can help you after checking your resources

This QUERY "gives different performance" and I can't do anything about it.