Mysql – How to reduce the memory usage of a tiny MEMORY table in MySQL

mariadbMySQL

After running the following queries:

CREATE TABLE mem_test (i int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=MEMORY;
INSERT INTO mem_test () VALUES ();
SELECT table_rows,avg_row_length,data_length,index_length FROM INFORMATION_SCHEMA.TABLES WHERE table_name='mem_test';

I get this in MySQL 5.7.28:

+------------+----------------+-------------+--------------+
| table_rows | avg_row_length | data_length | index_length |
+------------+----------------+-------------+--------------+
|          1 |              8 |      126992 |       126984 |
+------------+----------------+-------------+--------------+

and this in MariaDB 10.4.10:

+------------+----------------+-------------+--------------+
| table_rows | avg_row_length | data_length | index_length |
+------------+----------------+-------------+--------------+
|          1 |              5 |     1677712 |      2093064 |
+------------+----------------+-------------+--------------+

My table will never grow anywhere close to the size of these pre-allocated buffers, so how can I get MySQL and MariaDB to waste less RAM? Or aren't they actually using what this query indicates??

Additional comparisons, for reference:

+-------+--------+------------+----------------+-------------+--------------+
| DB    | Engine | table_rows | avg_row_length | data_length | index_length |
+-------+--------+------------+----------------+-------------+--------------+
| MySQL | MEMORY |          1 |              8 |      126992 |       126984 |
| Maria | MEMORY |          1 |              5 |     1677712 |      2093064 |
| Both  | InnoDB |          1 |          16384 |       16384 |            0 |
| Both  | MyISAM |          1 |              7 |           7 |         2048 |
+-------+--------+------------+----------------+-------------+--------------+

I found a note on the MySQL forums that "memory tables grow by 'read_buffer_size' bytes when they need extending" (https://forums.mysql.com/read.php?92,405702,417606#msg-417606), but my numbers don't agree: I have read_buffer_size = 131072 in MySQL and 2097152 in MariaDB.

Best Answer

Although data_length and index_length aren't exact multiples of read_buffer_size, they are based on it. So adjusting read_buffer_size in my.cnf is the solution (thanks @Wilson Hauck), as Shane Bester pointed out on the MySQL forums:

memory tables grow by 'read_buffer_size' bytes when they need extending (https://forums.mysql.com/read.php?92,405702,417606#msg-417606)

Based on performance tests by Peter Zaitsev in 2007 (https://www.percona.com/blog/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/), the default 128K setting is generally best, and using smaller values brings very little penalty.

Related Question