Serious Bug in Aurora MySQL: tables with foreign key relationships are not placed on the LRU list for memory eviction

Just so anyone is aware, AWS support replied to this issue via internal support ticket, i'm leaving their answer below in case anyone is facing the same problem, but unfortunately there wont be any fix on their side. #############################################################################

Dear AWS Customer,

Thanks for reaching back!

I understand your issue that you want to know if AWS has acknowledged this issue as a bug.

From my observation, as we can see that it is already acknowledged by mysql. [+] InnoDB releases memory of table only on shutdown if table has FK constraint: https://bugs.mysql.com/bug.php?id=95898

We can be only fixing the issue in aurora when it is fixed by the mysql community.

As mentioned in the AWS Public Documentation:

When Aurora MySQL accesses tables that are connected through foreign keys, it loads and keeps the related table metadata in memory. This creates a snowball effect where accessing one table triggers the loading of multiple related table definitions into the dictionary cache.

The increase indicates:

1. Schema Evolution:Your application is adding new tables with foreign key relationships, expanding dictionary cache requirements
2. Query Pattern Changes: Different access patterns are loading more table definitions into memory
3. Interconnected Dependencies: As your schema grows, the foreign key relationships create more interconnected metadata that must be cached together

To resolve this issue you can check how many open table definitions are in memory (Open_table_definitions) to confirm how many are being held. If this turns out to be the cause, there are a few things you can explore:

1. Limit the number of open tables (or check for unnecessary foreign key relations).
2. Consider refactoring schema if feasible — especially to reduce circular or deep foreign key dependencies.
3. Set table_definition_cache appropriately — but this is only part of it.

Refer to the below document to troubleshoot the same [+]https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/ams-workload-memory.html#ams-workload-memory.example3

A few Immediate Recommendations:

1. Proactive Monitoring Setup: I recommend setting up CloudWatch alarms on the FreeableMemory metric with thresholds at 15GB and 8GB to provide early warning [1]
2. Memory Instrumentation: Implement continuous monitoring using the memory instrumentation queries to track trends over time [2][3][4]
3. Aurora OOM Response Configuration:Configure the aurora_oom_response parameter to preserve diagnostic information during memory pressure events [5]
4. Schema Review: Consider reviewing your foreign key relationships for optimization opportunities, particularly checking for:
   • Circular dependencies
   • Unnecessary constraints
   • Tables that could be accessed independently
5. Connection Management: Monitor and manage idle/sleep connections to optimize memory usage [6]
6. Scaling Consideration: Given your current memory pressure (only 10GB free), consider temporarily scaling to a larger instance class while we address the root cause
7. Workload Analysis: Make sure you do not commit large number of changes together , commit in batches. Run EXPLAIN on queries to understand the query execution plan.

I hope the above Information helped you answer your question. If you have any further queries with respect to the above issue, kindly feel free to update/email it on the case, we will be happy to assist you further.

Thank you.

Have a great day and stay safe.

—REFERENCES—— [1] Cloudwatch Alarms: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/creating_alarms.html

[2] Aurora MySQL Memory Workload Management: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/ams-workload-memory.html

[3] MySQL Stored Procedures for Ending Sessions: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-stored-proc-ending.html

[4] Troubleshooting Low Freeable Memory in RDS MySQL/MariaDB: https://repost.aws/knowledge-center/low-freeable-memory-rds-mysql-mariadb

[5] Aurora MySQL Out-of-Memory Response Configuration: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQLOOM.html

[6] Data Dictionary object cache: https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-object-cache.html