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

[19 Sep 2018 13:27] Shane Bester
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.
[19 Sep 2018 13:29] Shane Bester
testcase example, leaker.sql

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

[19 Sep 2018 13:49] Shane Bester
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));
[19 Sep 2018 13:51] Shane Bester
memory heap 5.7.23 .txt and .pdf

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

[19 Sep 2018 14:01] Shane Bester
would be nice to get some solid documentation on the row fetch cache used in innodb
[19 Sep 2018 14:30] Shane Bester
InnoDB is getting passed this from MySQL in row_create_prebuilt
mysql_row_len	50007	unsigned __int64
[19 Sep 2018 15:23] Shane Bester
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.