MySQL is not releasing memory after memory tables are dropped

Question

After creating several tables of memory engine and dropping them, mysqld is not releasing all the corresponding memory.

Below are the exact steps to reproduce the problem. Memory usage is observed using htop (RES column).

1. Disable swap

   sudo swapoff -a
   

2. Launch mysql with memory limit of 4GB

   docker run -d -m 4g --shm-size 4g --name mysql_test \
       -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:5.7.25
   
   docker exec -it mysql_test bash
   
   # test to allocate 3G memory in /dev/shm
   dd if=/dev/zero of=/dev/shm/dummy bs=1M count=3K
   rm /dev/shm/dummy
   
   # create a named pipe for loading data
   mkfifo /var/lib/mysql-files/tmp.pipe
   
   # launch mysql client
   mysql -uroot
   

3. Create database

   create database if not exists mem;
   use mem;
   
   -- allow memory table of max size 4GB for this session
   set max_heap_table_size = 1024 * 1024 * 1024 * 4;
   

   mysqld memory usage: 193M

4. Create table and load data

   In mysql shell:

   create temporary table memory_tbl ( idx varchar(1000) ) engine=memory;
   load data infile '/var/lib/mysql-files/tmp.pipe' into table memory_tbl (idx);
   

   In another terminal:

   docker exec -it mysql_test sh -c 'yes 0 | head -1048576 > /var/lib/mysql-files/tmp.pipe'
   

   mysqld memory usage: 1210M

   Back to mysql shell to duplicate the table:

   create temporary table memory_tbl2 like memory_tbl;
   insert into memory_tbl2 select * from memory_tbl;
   

   mysqld memory usage: 2227M

   Duplicate the table again:

   create temporary table memory_tbl3 like memory_tbl;
   insert into memory_tbl3 select * from memory_tbl;
   

   mysqld memory usage: 3243M

5. Drop tables

   drop table memory_tbl;
   

   mysqld memory usage: 3244M

   drop table memory_tbl2;
   

   mysqld memory usage: 3244M

   drop table memory_tbl3;
   

   mysqld memory usage: 2302M

6. Try allocate memory

   In another shell:

   docker exec -it mysql_test bash
   
   # allocate 1.5G memory
   dd if=/dev/zero of=/dev/shm/dummy bs=1M count=1536
   rm /dev/shm/dummy
   
   # allocate 2G memory
   dd if=/dev/zero of=/dev/shm/dummy bs=1M count=2K
   # error occurs
   # dd: error writing '/dev/shm/dummy': Cannot allocate memory
   rm /dev/shm/dummy
   

I wonder if this is a bug of MySQL server. Otherwise, would there any way to ask MySQL to release the memory without restarting mysqld?

Testing environment:

• Ubuntu 18.04.1 LTS
• Kernel 4.15.0-34-generic
• Docker 18.06.1-ce
• mysql-5.7.25
• glibc 2.24

Answer 1

Per suggestion in a MariaDB server bug Memory not freed on memory table drop, replacing default glibc with jemalloc solves the problem.

Steps below shows how to launch mysqld with jemalloc:

docker run -it -m 4g --shm-size 4g --name mysql_test \
    -e MYSQL_ALLOW_EMPTY_PASSWORD=yes mysql:5.7.25 bash
apt-get update
apt-get install -y libjemalloc-dev
# adhoc preload jemalloc for ease of testing
# updating /etc/mysql/my.cnf is preferred for permanent setting
LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.1 docker-entrypoint.sh mysqld

Then we may proceed testing from step 3 in another shell. Note that memory usage of mysqld drops by about 1G for each drop table statement. At the end, memory usage drops to level similar to initial stage and creating a 3G file in /dev/shm is possible.

Update:

The actual library needed is libjemalloc1 in this case. libjemalloc-dev includes libjemalloc1 as a dependency and that's why it worked but at the price of installing a bunch of files we don't need. Thanks Michael - sqlbot for pointing this out.