VSZ behaviour with MariaDB MEMORY tables

We recently had the situation that a customer complained about the Oom killer terminating the MariaDB database instance from time to time. The MariaDB database configuration was sized quit OK (about 50% of RAM was used for the database) but they did not have swap configured.

When we checked the memory for the specific mysqld process we found that VSZ was about 80 Gibyte (on a 64 Gibyte machine) and the RSS size was about 42 Gibyte. The very high VSZ value in combination with a lacking swap space and Oom killer let the alarm bells ring.

This customer was using a significant amount of (temporary) MEMORY tables (instead of TEMPORARY TABLE ... ENGINE = MEMORY) which are suspect to be the evildoer.

To verify if this could be the reason for the odd behaviour we have to know how MEMORY tables behave related to VSZ from the O/S point of view.

Creation of MEMORY table 1 (12 - 14):

SQL> SET GLOBAL max_heap_table_size = 1024*1024*1024;
SQL> SET SESSION max_heap_table_size = 1024*1024*1024;

SQL> CREATE TABLE test_m1 LIKE test;
SQL> ALTER TABLE test_m1 ENGINE = MEMORY;
SQL> INSERT INTO test_m1 SELECT * FROM test;
SQL> INSERT INTO test_m1 SELECT NULL, data, NULL FROM test_m1; 
...
ERROR 1114 (HY000): The table 'test_m1' is full

Creation of MEMORY table 2 (32 - 38):

SQL> CREATE TABLE test_m2 LIKE test_m1;
SQL> INSERT INTO test_m2 SELECT NULL, data, NULL FROM test_m1 LIMIT 100000;
...
ERROR 1114 (HY000): The table 'test_m2' is full

Creation of MEMORY table 3 (45 - 48):

SQL> CREATE TABLE test_m3 like test_m1;                                          
SQL> INSERT INTO test_m3 SELECT NULL, data, NULL FROM test_m1 LIMIT 500000; 
...
ERROR 1114 (HY000): The table 'test_m3' is full

Truncation of all 3 MEMORY tables (57 - 58):

SQL> TRUNCATE TABLE test_m1;
SQL> TRUNCATE TABLE test_m2;
SQL> TRUNCATE TABLE test_m3;

Drop of all 3 MEMORY tables had no effect (ca. 65):

SQL> DROP TABLE test_m1;
SQL> DROP TABLE test_m2;
SQL> DROP TABLE test_m3;

Restart of the database process releases the memory (71 - 74):

shell> restart
... SUCCESS!
Timeout is 60 seconds: . SUCCESS!