Aurora MySql upgrade from 2 (MySql 5.7) to 3 (MySql 8.0.36) causing performance degradation

Question

We did Aurora upgrade on test environment 3 months ago and went go live last week. We didn't notice any performance issues on test environment earlier (may be we didn't test enough) but on Production we are seeing performance degradation.

Interestingly, today when we run same query on test env it runs faster (0.5 s) but on prod it takes 8 seconds. Schema is same, RDS group parameters are same, cluster configs are same. Not sure if Aurora optimized DB on Test in last 3 months.

Some of the details I found appears weird but I don't know how to correlate it. Any pointers on this would be helpful. Thanks.

```
Innodb_buffer_pool_pages_misc
Test: 94
Production: 18446744073709550716

OS file reads/writes
Test:       0 OS file reads, 0 OS file writes, 0 OS fsyncs
Production: 470218 OS file reads, 472682 OS file writes, 0 OS fsyncs
```

SQL
```
SELECT projects.id
FROM projects
WHERE projects.current_status != ? AND projects.stage IN ( ? ) 
    AND ( ( projects.current_status = ? and projects.stage != ? ) ) 
    AND ( projects.funding_date IS ? or projects.funding_date > ? ) 
    AND projects.id NOT IN ( ? ) AND projects.stage != ? 
    AND projects.company_id IS NOT ? AND projects.deleted_at IS ? 
    AND ( ( projects.office_id IN ( ? ) OR projects.office_id IS ? AND projects.company_id = ? ) OR ? = ? )
GROUP BY projects.id
ORDER BY projects.updated_at DESC, projects.id DESC
LIMIT ? OFFSET ?
```

`Explain` shows both servers are using same index `index_projects_on_current_status,index_projects_on_deleted_at`

re:Post Agent · Answer

The performance degradation you're experiencing after upgrading Aurora MySQL from version 2 to version 3 is concerning, especially given the significant difference in query execution time between your test and production environments. There are several factors to consider and steps you can take to investigate and potentially resolve this issue:

1. Query Execution Plan: Although you mentioned that both servers are using the same index, it's important to double-check the full execution plan. Sometimes, even with the same index, the optimizer might choose a different strategy. Use EXPLAIN ANALYZE to get a more detailed view of how the query is actually executed.

2. Data Distribution: The difference in performance could be due to different data distributions between test and production. If the production environment has significantly more data or a different distribution of values, it might affect query performance.

3. Buffer Pool Usage: The Innodb_buffer_pool_pages_misc value for your production environment seems unusually high. This could indicate a problem with memory usage or configuration. Verify your buffer pool settings and ensure they are appropriate for your production workload.

4. I/O Operations: Your production environment shows a high number of OS file reads and writes, while the test environment shows none. This suggests that the production query is not able to serve the data entirely from memory and is resorting to disk I/O, which can significantly slow down query execution.

5. Parameter Groups: Although you mentioned that the RDS group parameters are the same, it's worth double-checking all relevant parameters, especially those related to query optimization and buffer pool configuration.

6. Statistics: Ensure that table statistics are up to date on both environments. Run ANALYZE TABLE on the relevant tables in both environments.

7. Query Optimization: Consider using Performance Insights or slow query logs to identify if this query is consistently slow or if there are other queries contributing to overall performance degradation.

8. Workload Differences: The test environment might have benefited from query caching or optimization over time that hasn't yet occurred in production. Consider capturing a sample of your production workload and replaying it on the test environment to see if you can reproduce the performance discrepancy.

9. Version-Specific Changes: Some queries may perform differently in MySQL 8.0 due to changes in the query optimizer. Review the MySQL 8.0 changelog for any relevant optimizer changes that might affect your specific query.

10. Monitoring: Use Amazon CloudWatch, Enhanced Monitoring, and Performance Insights to gather more detailed performance metrics and identify any bottlenecks or resource constraints in your production environment.

If these steps don't lead to a resolution, consider engaging AWS Support for a more in-depth analysis of your specific situation. They can provide tailored advice and potentially identify any Aurora-specific optimizations that might be applicable.  
**Sources**  
[Troubleshooting Amazon Aurora MySQL database performance - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-troubleshooting.html)  
[Upgrade to Amazon Aurora MySQL version 3 (with MySQL 8.0 compatibility) | AWS Database Blog](https://aws.amazon.com/blogs/database/upgrade-to-amazon-aurora-mysql-version-3-with-mysql-8-0-compatibility/)

rePost-User-8830651 · Answer

Hello There,

I understand that you have noticed a performance degradation on your production environment post upgrading your Aurora MySQL DB Cluster to Aurora version v3 (MySQL 8.0) from Aurora version v2 (MySQL 5.7) and you are looking for some pointers and insights around the same.

===========================

To begin with, I would like to shed some light on the poor performance after upgrading the Aurora version to v3 (MySQL 8.0 compatible) from Aurora version v2 (MySQL 5.7 Compatible). Here, we must always note that the MySQL engine may perform differently after a major version upgrade this is mainly because the MySQL community implements new features and improvements, this can lead to the engine choosing a different execution plan for 8.0, and the queries optimized for 5.7 might not have the same performance.

A major version upgrade usually requires a lot of query tuning and optimization in order to achieve the performance level of a previous major version.

As you may already know that there are significant differences between the MySQL 5.7 Aurora v2, and MySQL 8.0 Aurora v3 engines. Even though the underlying data is the same between upgrades, there are significant differences to the engine design that affect query execution and may lead to locks or latency as a result. Let me provide a simple example for you, MySQL 5.7 decides to execute a query the choices made by the optimizer which will differ at 8.0.

In MySQL, Query cache is a prominent MySQL feature that speeds up data retrieval from a database. Although MySQL Query Cache was meant to improve performance, it has serious scalability issues and it can easily become a severe bottleneck. Therefore, the query cache is removed from community MySQL 8.0 and as a result from Aurora MySQL version 3.

In Aurora 2.x.x , the query is being retrieved directly from the Query Cache. The same process is not followed in 3.x.x because the Query Cache has been removed, and the complete execution needs to be performed.

—————————————

As iterated, A major version upgrade usually requires a lot of query tuning and optimization in order to achieve the performance level of a previous major version and this is not only specific to the database parameters but also few DBA tasks which needs to followed to achieve the same.

In order to do so, For your production workload and environment these multiple recommended actions that can be performed. You will need to engage your on-premise DBA to review these actions and make decisions regarding implementing the recommendations.

> Update Statistics

Most generally, the cause of query performance degradation is due to changes in query behavior as a result of changes to query optimizer at the DB engine level.

As MySQL is using the statistics information to find the optimize plan, we need to ensure the statistics are up to date and the tables are not fragmented. In order to do so (if it has not been done recently or after the upgrade) please use the command below:

$ ANALYZE TABLE

This command performs a key distribution analysis and stores the distribution for the named table or tables.

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

So, primarily after a major version upgrade it is recommended to run ANALYZE TABLE on each table to update table and indexes statistics.

> Drop and recreating indexes

In some cases, recreating indexes can change the plan that engine picks for a query. You may try this option for tables that have performance issue in the production environment.

If you would like to explore the same you can refer the below handy documentation from the MySQL Community:
[+] : Rebuilding Tables - https://dev.mysql.com/doc/refman/8.0/en/rebuilding-tables.html

> Using Index Hint

Index hints give the query optimizer information about how to choose indexes during query processing. Index hints apply to SELECT and UPDATE statements. They also work with multi-table DELETE statements, but not with single-table DELETE. If the performance issue is limited to some queries, you may consider this option and see if using the index hint can force the MySQL engine to pick a plan similar to Aurora 2.x version.

[+] : Index Hints - https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

> Use Profiling

Moving further, in order to get the execution time of a particular query from the server side, you can use profiling in MySQL.

The SHOW PROFILE statement displays profiling information that indicates resource usage for statements executed during the course of the current session. Please consider profiling the query to determine at which stage the most time is being spent. This is the stage that will be the optimal candidate for optimization.

[+] : Profile - https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

To determine what differences may have occurred to the query execution plan between versions 3.x and 2.x you may consider running the EXPLAIN statement, the changes to the query execution plan would be responsible for the higher average LATENCY, once it is determined what changed it will be better to work on query or parameter optimizations.

[+] : EXPLAIN - https://dev.mysql.com/doc/refman/8.0/en/explain-extended.html

===========================

That being said, since this issue might need deeper investigation you can always open a support case with AWS Support Engineering Team. Certain non-public information might be required such as your RDS instance and metric details and information regarding your database usage hence you can open a support case with AWS using the following support link and this issue can be investigated for further troubleshooting.

[+] : AWS Support team - https://console.aws.amazon.com/support/

I hope that the above information was quite helpful. Have a great day ahead!

Aurora MySql upgrade from 2 (MySql 5.7) to 3 (MySql 8.0.36) causing performance degradation

Add your answer

Relevant content