You are here
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!
- Shinguz's blog
- Log in or register to post comments
Comments
MariaDB memory_used
An inside view from the MariaDB database instance can be received with:
Here the memory is listed as released when the
TRUCATE TABLE
is done.