1

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
2

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.

  • 1
    Ubuntu 18.04, it appears that you only need to apt-get install libjemalloc1 rather than libjemalloc-dev. Installing the latter includes installation of the former as a dependency, so the libjemalloc1 package gets installed either way, but MySQL/MariaDB only appear to need the one file /usr/lib/x86_64-linux-gnu/libjemalloc.so.1, which is found in the libjemalloc1 package. – Michael - sqlbot Jul 31 at 15:56
  • It seems Oracle summarily dismissed what I assume to be your Bug #94647 without the slightest bit of consideration for the fact that they may indeed be partially at fault for using the external library in a suboptimal way. I can't say I'm really surprised, but it seems to me that there's really no good excuse for this level of disinterest on their part, particularly when MySQL seems so inexplicably leaky of memory sometimes, even though this is not, in the strictest sense, a memory leak. Thanks for this Q&A (and for that bug report). – Michael - sqlbot Jul 31 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.