MySQL Bugs: #95898: InnoDB releases memory of table only on shutdown if table has FK constraint

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:	None
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

Description:
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.

Hello Satya,

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

regards,
Umesh

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.

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!

regards,
Umesh

Thank you Umesh!

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

It exists in 8.0 too. I verified from code. Will also try to get "dictionary memory allocated" from SHOW ENGINE INNODB STATUS

dict/dict0dict.cc

dict_foreign_add_to_cache():

 /* 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) {
    dict_table_prevent_eviction(ref_table);
  }

  if (for_table != NULL) {
    dict_table_prevent_eviction(for_table);
  }

Thank you Satya for confirming.
I'll try to run provided mtr test case against 8.0.17 build and update here.

regards,
Umesh

Test run result with mysql 8.0.17 confirmed that this exists in 8.0 as well.

DD memory allocation before shutdown.
----------------------
BUFFER POOL AND MEMORY
----------------------
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

DD memory test

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

DD memory test opt

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

5.7.27, 8.0.17 - test results

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

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.

It is the same. The issue exists on all MySQL versions. 5.7.x, 8.0.x, 8.1.