Performance issue with bulky database after upgrade Aurora Mysql 5.7 to Aurora Mysql 8

The performance issues you're experiencing after upgrading from Aurora MySQL 5.7 to Aurora MySQL 8.0 (version 3.07.1) could be due to several factors. Here are some potential causes and optimizations to consider:

1. Query Execution Plans: After the upgrade, query execution plans may have changed. MySQL 8.0 includes improvements to the query optimizer, which can sometimes lead to different execution plans. Review your query execution plans to see if they've changed and if they're still optimal for your workload.

2. Spatial Indexes: If your database uses spatial data, check for any spatial indexes that might have been dropped during the upgrade. Aurora MySQL 3 only uses spatial indexes containing Spatial Resource Identifiers (SRIDs). You may need to recreate these indexes with SRIDs for optimal performance.

3. Configuration Parameters: Some configuration parameters may need adjustment after the upgrade. Review and update your custom parameter groups to ensure they're compatible with MySQL 8.0 and optimized for your workload.

4. Connection Management: Ensure that the connections between your PHP application and the database are properly managed. Connection issues can lead to performance degradation.

5. Workload Analysis: Use Amazon CloudWatch, Enhanced Monitoring, and Performance Insights to analyze your database workload. This can help identify specific queries or operations that are causing slowdowns.

6. Query Performance: Examine slow query logs to identify problematic queries. You may need to optimize these queries or add appropriate indexes.

7. I/O and Memory Usage: Check if there are any I/O bottlenecks or memory pressure. Aurora MySQL 8.0 may have different resource requirements compared to 5.7.

8. Parallel Query: If you're using parallel query, ensure it's properly configured. Recent Aurora MySQL updates have included optimizations for parallel query performance.

9. Auto-increment Key Recovery: Recent updates have optimized auto-increment key recovery, which can impact performance during restores. Ensure you're on the latest minor version of Aurora MySQL 3.07.

10. EC2 Instance Configuration: Review the configuration of your connected EC2 instance. You may need to adjust its resources or settings to better handle the MySQL 8.0 workload.

11. Application Code: Review your PHP application code for any MySQL version-specific optimizations or deprecated features that might need updating.

By systematically addressing these areas, you should be able to identify and resolve the performance issues you're experiencing after the upgrade. Remember to test any changes in a non-production environment before applying them to your production database.

Sources
Aurora MySQL database engine updates 2024-06-04 (version 3.07.0, compatible with MySQL 8.0.36) - Amazon Aurora
Post-upgrade cleanup for Aurora MySQL version 3 - Amazon Aurora
Troubleshooting Amazon Aurora MySQL database performance - Amazon Aurora
Aurora MySQL database engine updates 2024-06-26 (version 3.04.3, compatible with MySQL 8.0.28) - Amazon Aurora