MySQL Bugs: #111654: Memory usage increasing with truncate

Bug #111654	Memory usage increasing with truncate
Submitted:	4 Jul 2023 10:33	Modified:	5 Jul 2023 4:22
Reporter:	Vikrant Kumar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	8.0.31	OS:	Any (Mac, Alpine docker image)
Assigned to:		CPU Architecture:	Any (Arm 64)
Tags:	Memorey leak, memory consumption, procedure call, truncate, truncate table

Description:
We have a procedure which does some sorting and update rows in a table, then we truncate another table and insert some rows into it. All the previous mentioned things we do in a single procedure. Problem is our memory usage increases over time. Initially it was 9% but gradually in a month it reached around 70%. We used mysql in GCP. I was able to reproduce the issue in local mysql server also in mac os. We also did not have any index in our tables.

How to repeat:
Create a procedure and truncate a table in it using truncate command. Call this procedure multiple times continuously and also observe increase in memory of mysqld process in activity monitor in macOS.

Change truncate to delete in above procedure and then again call it multiple times, you will not see increase in memory.

The above is observed in a blank table without any index.

Suggested fix:
Avoid using truncate in procedure. Also restart the mysql server to flush already  leaked memory.

Hi Mr. Latwai,

Thank you for your bug report.

However, it is not a bug.

Malloc libraries on most of the operating systems tend to leave the memory chunks as part of the process that takes most of the memory. Hence, free() makes that pointer inaccessible to the program, but when new allocation is performed, malloc library does not have to make kernel calls in order to allocate memory. It simply uses this (hidden) cache attached to the process to make it available to that process again.

This is a very well known phenomenon on many operating systems , including macOS , which we use as well.

There is a way to reduce memory that is bound, but not allocated, to some process. See how much free memory is left on OS and then write and execute a simple C program then will malloc() and free() a memory that is larger then what OS reports as the available memory.

You will see that mysqld process size will be reduced. However, speed of execution will be reduced too, since then, for memory allocation, OS kernel will have to do the complex work of memory allocation.

This is a very well known phenomena on many operating systems.

Not a bug.

Hi,

Small correction.

Use calloc() instead of malloc().

Hi,

Actually , it is irrelevant.

All our modules fill up the allocated memory and that is what forces malloc library to allocate memory to the process.

That is why you should use calloc() instead of malloc() to free up memory. We already wrote to you that this freeing of memory attached to the mysqld process will slow down all future operations. This is due to the fact that malloc library will have to make OS calls for new memory.

hi 
If the value of the Innodb_buffer_pool_pages_data status continues to rise, does it indicate a potential issue with memory allocation in this scenario?