Bug #95898 InnoDB releases memory of table only on shutdown if table has FK constraint
Submitted: 20 Jun 2019 14:02 Modified: 18 Oct 2023 11:58
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, 8.0.17, 5.7.27 OS:Any
Assigned to: CPU Architecture:Any

[20 Jun 2019 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 2019 12:41] MySQL Verification Team
Hello Satya,

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

[12 Jul 2019 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 2019 7:51] MySQL Verification Team
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 2019 7:52] Satya Bodapati
Thank you Umesh!
[12 Aug 2019 18:09] Peter Zaitsev
So is MySQL 8 also affected or not ?

Considering how much dictionary code was changed in 8.0 I would not just assume it is also affected
[13 Aug 2019 4:43] Satya Bodapati
It exists in 8.0 too. I verified from code. Will also try to get "dictionary memory allocated" from SHOW ENGINE INNODB STATUS



 /* We need to move the table to the non-LRU end of the table LRU
  list. Otherwise it will be evicted from the cache. */

  if (ref_table != NULL) {

  if (for_table != NULL) {
[13 Aug 2019 9:24] MySQL Verification Team
Thank you Satya for confirming.
I'll try to run provided mtr test case against 8.0.17 build and update here.

[13 Aug 2019 9:53] lalit Choudhary
Test run result with mysql 8.0.17 confirmed that this exists in 8.0 as well.

DD memory allocation before shutdown.
Total large memory allocated 21978152960
Dictionary memory allocated 12155115330
Buffer pool size   1310718
Free buffers       7986
Database pages     1302670
Old database pages 480704
Modified db pages  106491
[13 Aug 2019 9:54] lalit Choudhary
DD memory test

Attachment: mysql-memory-shutdown.test (application/octet-stream, text), 2.06 KiB.

[13 Aug 2019 9:54] lalit Choudhary
DD memory test opt

Attachment: mysql-memory-shutdown-master.opt (application/octet-stream, text), 329 bytes.

[13 Aug 2019 9:54] lalit Choudhary
DD memory test opt

Attachment: mysql-memory-shutdown-master.opt (application/octet-stream, text), 329 bytes.

[13 Aug 2019 15:34] MySQL Verification Team
5.7.27, 8.0.17 - test results

Attachment: 95898_5.7.27_8.0.17.results (application/octet-stream, text), 183.11 KiB.

[18 Oct 2023 2:32] Dan Stevens
Is this still an issue on all versions of mysql? This is potentially responsible for ~30G of memory usage on our production database running 5.7.43. We will gladly upgrade to a version where this is not an issue as it has required us to upgrade to the maximum memory allowed by our host.
[18 Oct 2023 11:58] Satya Bodapati
It is the same. The issue exists on all MySQL versions. 5.7.x, 8.0.x, 8.1.