MySQL Bugs: #103133: memory leak when accessing view

Bug #103133	memory leak when accessing view
Submitted:	27 Mar 2021 14:15	Modified:	21 Dec 2023 17:04
Reporter:	Loïc Oudot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	8.0.23	OS:	Windows (Microsoft Windows 10 Pro)
Assigned to:		CPU Architecture:	x86
Tags:	WBBugReporter

Description:
Each SELECT on a view cause a small memory leak. We have to reboot our mysql server every night and the more activities on the server, the more memory leak is consumed.

Whatever the content of a view in a database (simple as the one present in the documentation https://dev.mysql.com/doc/refman/8.0/en/create-view.html, or a more complex one like we use) there is a small increase on the private bytes consumed by mysqld.exe. I can't find any settings in my.ini to limit this memory consumption.

I have made deep search to understand the behavior and I will tend to explain here :
- Whatever the SELECT is in a view, I have a memory leak of about 1Gb by millions of request in my case.
- Repeatedly executing the same SELECT as the one in the view with mysql.exe : no memory leak
- The same SELECT repeated 10_000_000 times in a stored procedure : no memory leak

In conclusion, the memory leak appears only when I directly acces the view with a SELECT. The leak appears with or without the performance_schema, whatever the algorithm of the view and on Debian as on Windows.

How to repeat:
1. create new empty database 'test_leak'
2. Create the view 'CREATE VIEW `v_today` (`today`) AS select curdate() AS `CURRENT_DATE`'
3. Repeat 1_000_000 times the request : select * from test_leak.v_today;
4. Monitor the increasing of private bytes of mysqld.exe.

Hi,

I think I worked on something similar already, need to check it. I think it was windows only.

When you notice this leak, do you need to connect to mysqld every time so

million times:
 - connect
 - select from view
 - disconnect

or you can do it in single connection:
 - connect
 - do million times select from view
 - disconnect

Thanks
Bogdan

Hi,

thank you for your fast response. I use only one connection :
or you can do it in single connection:
 - connect
 - do million times select from view
 - disconnect

Loïc

Hi,

Thanks for your report, I reproduced this on Windows.

all best
Bogdan
v

imho this needs more analysis.  does P_S reveal what leaks?  Does a leak happen forever?  If repeatable on debian, can we get any gperf memory profiles?  I cannot repeat it on fedora with 8.0.23..

I confirm this still affects 8.0.28 on Windows.
Easy to repeat:
Download CoreUtils:
 http://gnuwin32.sourceforge.net/packages/coreutils.htm

extract it.  Now you have the "yes.exe" utility.

yes.exe "select * from leak_test.v_today;" | mysql -uroot

Watch task manager.  Working Set memory grows by about 1M/second, endlessly.

Did some analysis on this.

Following the guide in :
https://docs.microsoft.com/en-us/windows-hardware/drivers/debugger/using-umdh-to-find-a-us...

I took two snapshots of memory allocations of 8.0.28 mysqld.exe
and found the leaks are here:

+ 272379520 ( 272379520 -      0) 2127965 allocs	BackTrace8D9E600A
+ 2127965 ( 2127965 -      0)	BackTrace8D9E600A	allocations

ntdll!RtlpAllocateHeapInternal+A7D
ucrtbase!_malloc_base+36
mysqld!operator new+1F 
mysqld!std::unordered_map
mysqld!DB_restrictions::clear+13 (.\sql\auth\partial_revokes.cc, 269)
mysqld!THD::cleanup_after_query+108 (.\sql\sql_class.cc, 1775)
mysqld!dispatch_command+1095 (.\sql\sql_parse.cc, 1942)
mysqld!do_command+190 (.\sql\sql_parse.cc, 1353)
mysqld!handle_connection+198 (.\connection_handler_per_thread.cc, 302)
mysqld!pfs_spawn_thread+1CF (.\storage\perfschema\pfs.cc, 2950)
mysqld!win_thread_start+1C (.\mysys\my_thread.cc, 74)
ucrtbase!thread_start<unsigned int (__cdecl*)(void *),1>+42

+ 119164696 ( 119164696 -      0) 2127941 allocs	BackTrace8D9E634A
+ 2127941 ( 2127941 -      0)	BackTrace8D9E634A	allocations

ntdll!RtlpAllocateHeapInternal+A7D
ucrtbase!_malloc_base+36
mysqld!operator new+1F 
mysqld!std::unordered_map
mysqld!DB_restrictions::clear+13 (.\sql\auth\partial_revokes.cc, 269)
mysqld!THD::cleanup_after_query+108 (.\sql\sql_class.cc, 1775)
mysqld!dispatch_command+1095 (.\sql\sql_parse.cc, 1942)
mysqld!do_command+190 (.\sql\sql_parse.cc, 1353)
mysqld!handle_connection+198 (.\connection_handler_per_thread.cc, 302)
mysqld!pfs_spawn_thread+1CF (.\storage\perfschema\pfs.cc, 2950)
mysqld!win_thread_start+1C (.\mysys\my_thread.cc, 74)
ucrtbase!thread_start<unsigned int (__cdecl*)(void *),1>+42

After that I searched BUGDB and found existing verified bug that might be interesting:

Bug 31971808 : FIX USAGE OF SECURITY CONTEXT IN THE VIEWS CODE.

I confirm this issue still exists on 8.0.32 for Windows.  In task manager, the "commit size" for mysqld.exe goes up around 1M per second.

This still affects Windows version 8.0.32 with jemalloc.dll as the memory allocator.
I had hoped recently introduced jemalloc on Windows builds would help,  but it doesn't.

Documented fix as follows in the MySQL 8.0.36 and 8.3.0 changelogs:

    In some cases, selecting from a view leaked a small amount of
    memory.

Closed.