Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16446

create temporary table engine=memory does not release memory and tmp space



    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.2.15
    • 10.2.15
    • Server
    • None


      A workload that uses a lot in-memory temporary tables makes the mysqld process grow until it fills all the avaialble system memory and eventually the process is killed by OOM killer.

      While verifying this in my lab, I also found that the above statement is also using the ibtmp1 tablespace, and makes it grow enormously (it filled my disk during the test run).

      According to the documentation, the memory allocated by CREATE TEMPORARY TABLE .. ENGINE=MEMORY should be released when the session is terminated, however, this doesn't seem to be the case. Also, the enormous growth of the temporary innodb tablespace is kind of unexpected - only way to reclaim this space is to restart MariaDB.

      How to reproduce:

      sysbench --db-driver=mysql  --threads=48 --time=900  --mysql-host=localhost  --mysql-user=root ./create_temporary.lua run

      I am attaching the LUA file to this ticket.

      While this runs, you can monitor mysqld footprint and ibtmp1 growth with:

      while true; do ps -eAo pid,rss,args | grep mysqld | grep -v grep | grep -v safe; ls -lh /var/lib/mysql/ibtmp1; sleep 1; done

      Please note:

      show global variables like '%heap%';
      | Variable_name       | Value    |
      | max_heap_table_size | 16777216 |
      1 row in set (0.02 sec)


        1. create_temporary_fixed.lua
          1 kB
        2. create_temporary_proper.lua
          0.5 kB
        3. create_temporary.lua
          1 kB
        4. rickf.csv
          98 kB



            axel Axel Schwenke
            rpizzi Rick Pizzi
            1 Vote for this issue
            5 Start watching this issue



              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.