Bug #95898 InnoDB releases memory of table only on shutdown if table has FK constraint
Submitted: 20 Jun 14:02 Modified: 13 Aug 4:43
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

[20 Jun 14:02] Satya Bodapati
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.
[21 Jun 12:41] Umesh Shastry
Hello Satya,

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

regards,
Umesh
[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!

regards,
Umesh
[12 Jul 7:52] Satya Bodapati
Thank you Umesh!
[12 Aug 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 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

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);
  }
[13 Aug 9:24] Umesh Shastry
Thank you Satya for confirming.
I'll try to run provided mtr test case against 8.0.17 build and update here.

regards,
Umesh
[13 Aug 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.
----------------------
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
[13 Aug 9:54] lalit Choudhary
DD memory test

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

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

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

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

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

[13 Aug 15:34] Umesh Shastry
5.7.27, 8.0.17 - test results

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