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).
Disable swap
sudo swapoff -a
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
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
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
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
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