Performance issues with Aurora MySQL Version 8.0

Question

I came to notice the version upgrade of Aurora MySQL version 5.7 to version 8.0 has been completed and I could see the performance issues with the instance and causing the error whenever I tried to run a complex query on top of it and most of the other users too experiencing the same.

Sharing the Error: 
SQL Error [1114] [HY000]: The table '/rdsdbdata/tmp/#sql103_1cae2a_1e' is full

Tried checking the parameters and able to see all set in the best practice

Accepted Answer

Thanks Osvaldo,

I too even ended my surfing with the AWS Documentation later the same day and found the following few **Root Cause Analysis** which helped me to understand the concept of the Aurora MySQL version 3 instance behavior changes.

This probably provided me the insights
- https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/ams3-temptable-behavior.html

And moreover, to have a deeper understanding on this storage behind the Temp Tables, ***I will suggest the other developers/users*** to have a quick walkover on this documentation since it contains the required information to the over the optimization that needs to be done.

- https://aws.amazon.com/blogs/database/use-the-temptable-storage-engine-on-amazon-rds-for-mysql-and-amazon-aurora-mysql/

![Conceptual Diagram](/media/postImages/original/IMPd-t-fe6SiyW0A4Mydjl-g)

In General, the following instance parameters were changed, and the issue was resolved without any downtime

* ` temptable_max_ram` - From the Engine Default (1Gigs) was increased to (2Gigs)
* ` temptable_max_mmap` - From the Engine Default (1Gigs) was increased to (8Gigs)

Answer

Hey there!

It seems like you're experiencing some trouble with your **MySQL database** after upgrading to a newer version, particularly with an error indicating that a temporary table is full. This issue typically arises when **MySQL** tries to create temporary tables during complex queries but runs out of allocated space to do so. Let's break down why this might be happening and how you can fix it:

### Why It's Happening

1. **Temporary Table Overflows:** MySQL uses temporary tables to hold intermediate results. These tables can be stored in memory (using the TempTable or MEMORY storage engine) or on disk (using InnoDB). When the data exceeds the memory limits, it spills over to disk, and if the disk space or specific configurations limit this spill-over, you'll encounter the "table is full" error.

2. **Configuration Limits:** With the upgrade to MySQL 8.0, new settings like `temptable_max_ram` and `temptable_max_mmap` were introduced to manage how memory and disk space are used by temporary tables. If these are set too low, or if your queries are particularly resource-intensive, you'll hit these limits more frequently.

### How to Fix It

1. **Adjust `internal_tmp_mem_storage_engine`:** Consider changing this setting from the default `TempTable` to `MEMORY` if your workload benefits from it. This change can help manage how temporary tables are stored in memory more efficiently for certain types of queries.

2. **Increase `tmp_table_size` and `temptable_max_ram`:** By increasing these limits, you allow more space for temporary tables in memory, reducing the likelihood of spilling over to disk. This can help avoid the "table is full" error for memory-related issues.

3. **Configure `temptable_max_mmap`:** If you're using memory-mapped files for TempTable, make sure the `temptable_max_mmap` setting is high enough to accommodate your needs without taking up too much space in the temporary directory (`tmpdir`).

4. **Permissions for Session Changes:** Remember, adjusting session settings like `internal_tmp_mem_storage_engine` requires certain privileges (`SESSION_VARIABLES_ADMIN` or `SYSTEM_VARIABLES_ADMIN`) as of MySQL 8.0.27.

5. **Optimize Your Queries:** Beyond configuration, optimizing your queries to be more efficient can also reduce the strain on temporary table resources.

### Implementation

To implement these changes, you'll need to access your database's configuration file or use administrative commands to adjust these settings. Remember to monitor the impact of any changes and adjust as necessary to find the right balance for your specific workload.

If you have any questions or need further clarification as you go through these steps, please don't hesitate to reach out!

Here are some helpful resources for your reference:

1. [Internal Temporary Tables](https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html)
2. [SQL-ERROR-1114](https://stackoverflow.com/questions/77327919/sql-error-1114-hy000-the-table-rdsdbdata-tmp-sql161-17a011-a-is-full)
3. [1114-HY000 The table is full](https://stackoverflow.com/questions/730579/1114-hy000-the-table-is-full)
4. [Aurora User Guide - MySQL](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.mysql80-upgrade-procedure.html?sc_channel=sm&sc_campaign=Support&sc_publisher=REDDIT&sc_country=global&sc_geo=GLOBAL&sc_outcome=AWS%20Support&sc_content=Support&trk=Support&linkId=231633538#AuroraMySQL.mysql80-post-upgrade)
5. [AuroraMySQL Reference](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.ParameterGroups.html#AuroraMySQL.Reference.Parameters.Instance)
6. [AuroraMySQL-Performance](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.Performance.html#AuroraMySQL.Managing.MaxConnections)

Performance issues with Aurora MySQL Version 8.0

Why It's Happening

How to Fix It

Implementation

Relevant content