Bug #95898 InnoDB releases memory of table only on shutdown if table has FK constraint
Submitted: 20 Jun 14:02 Modified: 12 Jul 7:52
Reporter: Satya Bodapati (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7, 5.7.26 OS:Any
Assigned to: CPU Architecture:Any

[20 Jun 14:02] Satya Bodapati
The testcase for this bug is same as https://bugs.mysql.com/bug.php?id=95895.

I create a separate bug because this is a different problem observed.

When a table with FK is loaded to InnoDB cache, it puts both parent and child in non_LRU cache (dict_sys->table_non_LRU)

These are never subject to eviction. When many tables with FK are used, the memory growth used by InnoDB table cache grows to 13-15GB. Memory is only released at shutdown.

This is too much.

This is one of the reason why bug#95895 happened. The background thread couldn't evict any of the table.

I don't think this changed with 8.0 too but please verify.

How to repeat:
Run the testcase attached in bug#95895. add  

"show engine innodb status" before shutdown. ie

before this line:

--echo # shut server down
--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect

Suggested fix:
Allow tables using FK constraint to be evictable after its use.
[21 Jun 12:41] Umesh Shastry
Hello Satya,

Thank you for the report and test case.
Verified as described with 5.7.26 build.

[12 Jul 7:21] Satya Bodapati
Hey Umesh,

May I know why this is S3? Non-critical. It is in fact a serious issue.

With this bug, you can make mysql occupy entire ram and crash the server.

Also, I have seeen this 13-15GB RAM of dict_cache from customer production environment.
[12 Jul 7:51] Umesh Shastry
Hello Satya,

I didn't change initial severity set at the time of bug report, moreover triage has already classified it as *serious* and so it has already got attention internally. For now setting in here as well to Sev2. Thank you!

[12 Jul 7:52] Satya Bodapati
Thank you Umesh!