MySQL Bugs: #92502: too much memory tagged "memory/innodb/row0sel" in row_sel_prefetch_cache

Bug #92502	too much memory tagged "memory/innodb/row0sel" in row_sel_prefetch_cache_init
Submitted:	19 Sep 2018 13:27	Modified:	29 Aug 2022 20:30
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	5.7.23	OS:	Any
Assigned to:	Daniel Price	CPU Architecture:	Any
Tags:	excessive memory

Description:
Affects 5.7.

Certain table structures consume excessive memory in the server table cache 
or innodb row caches.

Here are outputs after running testcase that creates 1000 tables with about 16 short rows each.

mysql> select event_name,count_alloc,CURRENT_NUMBER_OF_BYTES_USED from performance_schema.memory_summary_global_by_event_name where event_name not like '%performance%' order by CURRENT_NUMBER_OF_BYTES_USED desc limit 5;
+----------------------------------+-------------+------------------------------+
| event_name                       | count_alloc | CURRENT_NUMBER_OF_BYTES_USED |
+----------------------------------+-------------+------------------------------+
| memory/innodb/row0sel            |       10881 |                   4052258288 |
| memory/sql/TABLE                 |       84736 |                   1555676478 |
| memory/innodb/mem0mem            |      253733 |                    948990804 |
| memory/innodb/buf_buf_pool       |           1 |                    137428992 |
| memory/sql/TABLE_SHARE::mem_root |        7522 |                     53342040 |
+----------------------------------+-------------+------------------------------+
5 rows in set (0.03 sec)

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 6324 sss   20   0  146124   2156   1452 R   0.7  0.0   0:15.01 top
 7774 sss   20   0 8655220 2.738g  12632 S   0.7 36.3  17:08.34 mysqld

Running under heap profile...

Dumping heap profile to mybin.hprof.6042.heap (6683 MB currently in use)
Dumping heap profile to mybin.hprof.6043.heap (6684 MB currently in use)
Dumping heap profile to mybin.hprof.6044.heap (6685 MB currently in use)
Dumping heap profile to mybin.hprof.6045.heap (6687 MB currently in use)
Dumping heap profile to mybin.hprof.6046.heap (6688 MB currently in use)
Dumping heap profile to mybin.hprof.6047.heap (6689 MB currently in use)
Dumping heap profile to mybin.hprof.6048.heap (6690 MB currently in use)

How to repeat:
For the purpose of this testcase, start mysqld with options
--performance-schema-instrument="%=ON" 
--table-open-cache=16000 
--table-definition-cache=16000

Import the leak1.sql file using mysql command line client.

mysql -uroot test < leaker.sql

Monitor memory with top and performance_schema outputs.

testcase example, leaker.sql

Attachment: leaker.zip (application/x-zip-compressed, text), 40.75 KiB.

Attaching the heap profiles.  It seems,  this is the culprit.

0   0.0% 100.0% 4061461600  57.8% row_sel_fetch_last_buf (inline) ./mysql-5.7.23/storage/innobase/row/row0sel.cc:4005
0   0.0% 100.0% 4061461600  57.8% row_sel_prefetch_cache_init (inline) ./mysql-5.7.23/storage/innobase/row/row0sel.cc:3970

/********************************************************************//**
Initialise the prefetch cache. */
UNIV_INLINE
void
row_sel_prefetch_cache_init(
/*========================*/
	row_prebuilt_t*	prebuilt)	/*!< in/out: prebuilt struct */
{
	ulint	i;
	ulint	sz;
	byte*	ptr;

	/* Reserve space for the magic number. */
	sz = UT_ARR_SIZE(prebuilt->fetch_cache) * (prebuilt->mysql_row_len + 8);
	ptr = static_cast<byte*>(ut_malloc_nokey(sz));

memory heap 5.7.23 .txt and .pdf

Attachment: 6059_5.7.23_memory_heap.zip (application/x-zip-compressed, text), 608.01 KiB.

would be nice to get some solid documentation on the row fetch cache used in innodb

InnoDB is getting passed this from MySQL in row_create_prebuilt
mysql_row_len	50007	unsigned __int64

So perhaps it is not a bug!  But still a surprise and lucky I dug into it. 
Maybe then a documentation request. Something like (correct me please!):
InnoDB can use up to 64k per row for the fetch cache.
Fetch cache can have up to 8 rows per table in it.
Hence every table handler object could in worst case lead to about 512k additional ram.
Table handler objects are duplicated in each table_cache_instance and connection object while accessing tables.