MySQL Bugs: #113367: Memory of prepared statements is not released

Bug #113367	Memory of prepared statements is not released
Submitted:	8 Dec 2023 2:42	Modified:	11 Dec 2023 7:14
Reporter:	Samuel Liang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	8.0	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
I used sysbench to test mysql with 250 tables and 512 threads and turned on the performance schema. I could see the memory of Prepared_statement::main_mem_root (from performance_schema.memory_summary_global_by_event_name) increased to about 12.3G and the total mysqld's memory (from command top) was 21.7G. When finined the sysbench the memory of the Prepared_statement::main_mem_root disappeared which was right but the actual mysqld's memory was not reduced.

The prepared statement belongs the session and is it better to release the memory after the session ends? 

How to repeat:
./sysbench oltp_write_only --mysql-host=127.0.0.1 --mysql-socket=/tmp/mysql_30007.sock --mysql-user=root --mysql-password=111111 --mysql-db=test --time=12000 --report-interval=5 --table-size=250000 --threads=100 --tables=250 prepare

./sysbench oltp_read_only --mysql-host=127.0.0.1 --mysql-port=30007 --mysql-user=root --mysql-password=111111 --mysql-db=test --time=60 --report-interval=1 --table-size=250000  --threads=512 --tables=250  run

select event_name,CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024  from     performance_schema.memory_summary_global_by_event_name       order by CURRENT_NUMBER_OF_BYTES_USED desc LIMIT 10;

Hi Mr. Liang,

Thank you for your bug report.

We have run your longish test case and repeated the memory allocation and observed that MySQL server had freed all the memory that sysbench generated with that test.

We noticed that the amount of memory occupied by mysqld process was slightly smaller then during the test, but much larger then before the test began.

We knew that it is how malloc library works, so we wrote a short C program and forced the OS to free memory by allocating and using all the gigabytes that were not released by malloc library. It works, as it usually does and we have done it so many times.

Hence, it is not a bug. That is how malloc libraries work. They try to retain the memory with the process that is using it more, for the purpose that future allocations are much faster, so that kernel calls do not have to be made.

Hence, this is not a bug at all, this is just how malloc libraries work .....

Not a bug.

Hi MySQL

I used the below code check the memory but it was released. 
Does My SQL use a different way?

First the application's memory was 12.0g (Please see RES).
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                                                            
10875 root      20   0   12.0g  12.0g   2532 S   0.0 19.5   0:06.44 test02                                                                                                                                                             

Then it reduced to 2732 bytes.
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                                                                                            
10875 root      20   0   18768   2732   2532 S   0.0  0.0   0:07.07 test02                                                                                                                                                             

#include <cstdlib>
#include <string.h>
#include <stdio.h>

#include <iostream>
using namespace std;

int main() {
  cout << "!!!malloc 12G!!!" << endl;

  char* mem_1g[10];
  for (int i = 0; i < 12; i++) {
    mem_1g[i] = (char*)malloc(1024*1024*1024);
    memset(mem_1g[i],'$', 1024*1024*1024);
  }

  getchar();
  cout << "!!!free 12G!!!" << endl;

  for (int i = 0; i < 12; i++) {
    free(mem_1g[i]);
  }

  getchar();
  return 0;
}

Hi Mr. Liang,

Thank you ......

However, we must repeat that this has nothing to do with MySQL's memory allocation.

This is all done by system's malloc library which is dynamically linked, by OS, to mysqld process.

Not a bug/